I'm writing a SQL Query in VB2003 against an MS SQL Server 2000 database. Basically, I"m trying to replicate an Access query (which works) in MS SQL language. The problem is, I can't seem to make a nested CASE statement work.
Here is my original query:
[code] SELECT Left(dbo_porel.jobnum,5) AS ShortJobNum,
IIf(dbo_porel.jobseqtype="
IIf(Len(nz(dbo_opmaster.sh
(dbo_porel.relqty*dbo_pode
FROM (((dbo_porel
INNER JOIN dbo_podetail ON (dbo_porel.ponum = dbo_podetail.ponum) AND (dbo_porel.poline = dbo_podetail.poline))
LEFT JOIN dbo_joboper ON (dbo_porel.jobnum = dbo_joboper.jobnum) AND (dbo_porel.assemblyseq = dbo_joboper.assemblyseq) AND (dbo_porel.jobseq = dbo_joboper.oprseq))
LEFT JOIN dbo_opmaster ON dbo_joboper.opcode = dbo_opmaster.opcode)
LEFT JOIN dbo_partclass ON dbo_podetail.classid = dbo_partclass.classid
WHERE Left(dbo_porel.jobnum,5) Between "36640" And "36646" [/code]
Specifically, I'm dealing with this line:
[code]
IIf(dbo_porel.jobseqtype="
IIf(Len(nz(dbo_opmaster.sh
I'm trying to rewrite this in something MS SQL will luck and frankly, I"ve been without much success. My latest attempt was as follows:
[code]
Headers = Case
WHEN porel.jobseqtype='M' THEN
(WHEN Len(IsNull(partclass.short
Else partclass.shortchar01)
ELSE
(WHEN Len(IsNull(opmaster.shortc
ELSE opmaster.shortchar01)
End[/code]
I'd sure appreciate someone's assistance on this. All my searches so far have not indicated how to do the following:
If Value
THEN
IF Value
THEN
ELSE
THEN
ELSE
IF Value
THEN
ELSE
THEN
END IF
Thank you!
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Headers = CASE
WHEN porel.jobseqtype='M'
THEN CASE
WHEN Len(IsNull(partclass.short
THEN 'Unassigned'
ELSE partclass.shortchar01
END
ELSE CASE
WHEN Len(IsNull(opmaster.shortc
THEN 'Unassigned'
ELSE opmaster.shortchar01
END
END
----------------
This code works! Thanks! AngelIII - you got me 99% of the way there, but vadimrapp1 's actual syntax is what saved me (angelIII's code was missing CASE).
How do I assign the points?
Business Accounts
Answer for Membership
by: angelIIIPosted on 2007-07-20 at 06:35:13ID: 19530791
you are were close:
Headers = CASE
WHEN porel.jobseqtype='M'
THEN WHEN Len(IsNull(partclass.short
THEN 'Unassigned'
ELSE partclass.shortchar01
END
ELSE WHEN Len(IsNull(opmaster.shortc
THEN 'Unassigned'
ELSE opmaster.shortchar01
END
END