Converting Access query(IIF) to view(CASE)

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;
joop123456Asked:
Who is Participating?
 
bdreed35Commented:
Try this:

SELECT       qryLATSubBer_B.LATPakID,
      (SumOfStrekkendeMetersPerLengteInPak * Nbreedte * Ndikte/1000000) AS NomM3PerPak,
      (SumOfStrekkendeMetersPerLengteInPak * Abreedte * Adikte/1000000) AS ActM3PerPak,
      case when tblL2.TypeVracht='LSVCo' then ActuBreedte else Actbreedte end AS Abreedte,
      case when tblL2.TypeVracht='LSVCo',then ActuDikte else ActDikte end AS Adikte,
      case when tblL2.TypeVracht='LSVCo',then NomBreedte else Nomibreedte end AS Nbreedte,
      case when tblL2.TypeVracht='LSVCo',then NomDikte else NomiDikte end 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
0
 
joop123456Author 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
0
 
joop123456Author 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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.