We help IT Professionals succeed at work.

Converting Access query(IIF) to view(CASE)

267 Views
Last Modified: 2007-10-14
Hi,

How do I convert this access query to a SQL server view:  (IIF > CASE etc)

SELECT qryLATSubBer_B.LATPakID, ([SumOfStrekkendeMetersPerLengteInPak]*[Nbreedte]*[Ndikte]/1000000) AS NomM3PerPak, ([SumOfStrekkendeMetersPerLengteInPak]*[Abreedte]*[Adikte]/1000000) AS ActM3PerPak, IIf([tblL2.TypeVracht]="LSVCo",[ActuBreedte],[Actbreedte]) AS Abreedte, IIf([tblL2.TypeVracht]="LSVCo",[ActuDikte],[ActDikte]) AS Adikte, IIf([tblL2.TypeVracht]="LSVCo",[NomBreedte],[Nomibreedte]) AS Nbreedte, IIf([tblL2.TypeVracht]="LSVCo",[NomDikte],[NomiDikte]) AS Ndikte
FROM tblL2 RIGHT JOIN ((tblGegKopmaat RIGHT JOIN tblL3 ON tblGegKopmaat.Kopmaat = tblL3.Kopmaat) RIGHT JOIN ((tblBewerking RIGHT JOIN tblL4 ON tblBewerking.bewerking = tblL4.bewerking) RIGHT JOIN (qryLATSubBer_B LEFT JOIN tblL4LAT ON qryLATSubBer_B.LATPakID = tblL4LAT.LATPakID) ON tblL4.tbl4AUTO = tblL4LAT.tbl4AUTO) ON tblL3.tbl3AUTO = tblL4.tbl3AUTO) ON tblL2.tbl2AUTO = tblL3.WagenID;
Comment
Watch Question

Top Expert 2004
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
the comma is not fitting in the case syntax, just before each 'THEN'...

Question, I cannot use the (case-)fields directly in the calculations... ERROR: invalid column-names, Nbreedte, Ndikte,  Abreedte and Adikte... how do I fix that?

The query:
SELECT     dbo.qryLATSubBer_B.LATPakID,
                      CASE WHEN dbo.tblL2.TypeVracht = 'LSVCo' THEN ActuBreedte ELSE Actbreedte END AS Abreedte,
                      CASE WHEN dbo.tblL2.TypeVracht = 'LSVCo' THEN ActuDikte ELSE ActDikte END AS Adikte,
                      CASE WHEN dbo.tblL2.TypeVracht = 'LSVCo' THEN NomBreedte ELSE Nomibreedte END AS Nbreedte,
                      CASE WHEN dbo.tblL2.TypeVracht = 'LSVCo' THEN NomDikte ELSE NomiDikte END AS Ndikte,
                      dbo.qryLATSubBer_B.SumOfStrekkendeMetersPerLengteInPak * Nbreedte * Ndikte / 1000000 AS NomM3PerPak,
                      dbo.qryLATSubBer_B.SumOfStrekkendeMetersPerLengteInPak * Abreedte * Adikte / 1000000 AS ActM3PerPak
FROM         dbo.tblL2 RIGHT OUTER JOIN
                      dbo.tblGegKopmaat RIGHT OUTER JOIN
                      dbo.tblL3 ON dbo.tblGegKopmaat.Kopmaat = dbo.tblL3.Kopmaat RIGHT OUTER JOIN
                      dbo.tblBewerking RIGHT OUTER JOIN
                      dbo.tblL4 ON dbo.tblBewerking.bewerking = dbo.tblL4.bewerking RIGHT OUTER JOIN
                      dbo.qryLATSubBer_B LEFT OUTER JOIN
                      dbo.tblL4LAT ON dbo.qryLATSubBer_B.LATPakID = dbo.tblL4LAT.LATPakID ON dbo.tblL4.tbl4AUTO = dbo.tblL4LAT.tbl4AUTO ON
                      dbo.tblL3.tbl3AUTO = dbo.tblL4.tbl3AUTO ON dbo.tblL2.tbl2AUTO = dbo.tblL3.WagenID

Author

Commented:
When I put the CASE-statements instead of the invalid column names it works.....!!  

for the record:

dbo.qryLATSubBer_B.SumOfStrekkendeMetersPerLengteInPak * (CASE WHEN dbo.tblL2.TypeVracht = 'LSVCo' THEN NomBreedte ELSE Nomibreedte) * (CASE WHEN dbo.tblL2.TypeVracht = 'LSVCo' THEN ActuDikte ELSE ActDikte END) / 1000000 AS NomM3PerPak,

etc.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.