Access Queries to SQL Views

[fxplorr]

I don't know if I'm posting this in the correct forum, so I apologize if it should be elsewhere.

I'm in the midst of converting one of our Access DB's to use an SQL backend. I already have the tables converted but I was also pondering converting some of my more heavily used queries to SQL views. I'm using Enterprise Manager and I don't have an extensive knowledge of SQL and I ran into a road block with my Access queries that contain fields based on "iif" statements like the following:

Fieldname: IIf([type]="A",IIf([FLDR]="B",[LENGTH]*[WIDTH]*[DEPTH],([LENGTH]+0.375)*([WIDTH]+0.375)*([DEPTH]+IIf([FLDR]="C",0.5,0.625)))/1728,Null)

Would this even be worth the effort or should I just let them on the Access FrontEnd? If it is worth it, how can I go about creating a field in a "view" that can do the same thing as the "iif's" I use in the Access query fields.

If this is unclear, I can email screenshots and detailed examples.

Thanks in advance.

 

[hmckillop]

try something like this

select 
CASE WHEN [type]="A" AND [FLDR]="B" THEN [LENGTH]*[WIDTH]*[DEPTH]
	WHEN [type]="A" AND [FLDR]="C" THEN (([LENGTH]+0.375)*([WIDTH]+0.375)*([DEPTH]+ 0.5))/1728
    WHEN [type]="A" AND [FLDR]<> "C" AND [FLDR]<>"B" THEN (([LENGTH]+0.375)*([WIDTH]+0.375)*([DEPTH]+0.625))/1728
	ELSE NULL
END as fieldname1
FROM Mytable

"I'm living so far beyond my income that we may almost be said to be living apart

 

[fxplorr]

Grrrrr...."The Query Designer does not support the CASE SQL construct.

 

[vongrunt]

Exactly what version of SQL Server are you using?

My EM designer (SQL2k SP4) handles CASE constructs fine... but of course there are other known limitations (no UNIONs supported, limited representation of derived tables, some bugs etc). So in a long run, may I suggest using Query Analyzer?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)