[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Converting Access query(IIF) to view(CASE)

Posted on 2007-10-14
3
Medium Priority
?
202 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;
0
Comment
Question by:joop123456
  • 2
3 Comments
 
LVL 28

Accepted Solution

by:
bdreed35 earned 1000 total points
ID: 20073665
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
 

Author Comment

by:joop123456
ID: 20074069
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
 

Author Comment

by:joop123456
ID: 20074143
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

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question