Error with CASE WHEN (1 Viewer)

K

Ksabai

Registered User.
Local time
Yesterday, 17:29
Joined
Jul 31, 2017
Messages
104
iam getting the Incorrect Near the Keyword 'AS', Can anyone help me.

The following is the code.

SELECT CASE WHEN [tblcontract].[ShipCont]=2 OR [tblcontract].[ShipCont]=3 THEN CASE WHEN [tblcontract].[DtShip02] IS NOT NULL AND [tblcontract].[DtShip03] IS NOT NULL AND [tblcontract].[DtShip04] IS NOT NULL THEN [tblContract].[DtShip04] WHEN [tblcontract].[DtShip02] IS NOT NULL AND [tblcontract].[DtShip03] IS NOT NULL THEN [tblContract].[DtShip03]
WHEN [tblcontract].[DtShip02] IS NOT NULL THEN [tblContract].[DtShip02] WHEN [tblcontract].[DtShip01] IS NOT NULL THEN [tblContract].[DtShip01] ELSE NULL END AS LSDDate, ContNo, QtyShip01 AS Qty, Buyer
FROM dbo.tblcontract
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:29
Joined
May 7, 2009
Messages
19,228
something like this:
Code: 
SELECT LSDATE = 
     CASE 
         WHEN [tblcontract].[ShipCont]=2 OR [tblcontract].[ShipCont]=3 THEN 
               CASE 
                   WHEN [tblcontract].[DtShip02] IS NOT NULL AND [tblcontract].[DtShip03] IS NOT NULL AND [tblcontract].[DtShip04] IS NOT                          NULL THEN [tblContract].[DtShip04] 
                   WHEN [tblcontract].[DtShip02] IS NOT NULL AND [tblcontract].[DtShip03] IS NOT NULL THEN [tblContract].[DtShip03]
                   WHEN [tblcontract].[DtShip02] IS NOT NULL THEN [tblContract].[DtShip02] 
                   WHEN [tblcontract].[DtShip01] IS NOT NULL THEN [tblContract].[DtShip01] 
                   ELSE NULL 
               END
         ELSE NULL 
      END,
ContNo, QtyShip01 AS Qty, Buyer
FROM dbo.tblcontract
or
Code: 
SELECT 
     CASE 
         WHEN [tblcontract].[ShipCont]=2 OR [tblcontract].[ShipCont]=3 THEN 
               CASE 
                   WHEN [tblcontract].[DtShip02] IS NOT NULL AND [tblcontract].[DtShip03] IS NOT NULL AND [tblcontract].[DtShip04] IS NOT                          NULL THEN [tblContract].[DtShip04] 
                   WHEN [tblcontract].[DtShip02] IS NOT NULL AND [tblcontract].[DtShip03] IS NOT NULL THEN [tblContract].[DtShip03]
                   WHEN [tblcontract].[DtShip02] IS NOT NULL THEN [tblContract].[DtShip02] 
                   WHEN [tblcontract].[DtShip01] IS NOT NULL THEN [tblContract].[DtShip01] 
                   ELSE NULL 
               END
         ELSE NULL 
      END AS LSDDate,
ContNo, QtyShip01 AS Qty, Buyer
FROM dbo.tblcontract
 
K

Ksabai

Registered User.
Local time
Yesterday, 17:29
Joined
Jul 31, 2017
Messages
104
Iam having trouble here now, can some one help

SELECT CASE WHEN ([tblcontract].[ShipCont] = 4 OR
[tblcontract].[ShipCont] = 7) AND [tblcontract].[DtShip01] IS NOT NULL THEN [tblcontract].[DtShip01] ELSE NULL END AS LSDDate, ContNo
CASE WHEN [tblcontract].[ShipCont] = 4 THEN [tblcontract].[QtyShip01] ELSE NULL END AS Qty,Buyer
FROM dbo.tblcontract
 
K

Ksabai

Registered User.
Local time
Yesterday, 17:29
Joined
Jul 31, 2017
Messages
104
Many Thanks for the earlier post, iam just doing one more step at a time.
Iam having issues with below with the WHERE Clause, WITHOUT WHERE its working, Can someone tell me why.

SELECT CASE WHEN ([tblcontract].[ShipCont] = 4 OR
[tblcontract].[ShipCont] = 7) AND [tblcontract].[DtShip01] IS NOT NULL THEN [tblcontract].[DtShip01] ELSE NULL END AS LSDDate, ContNo, CASE WHEN [tblcontract].[ShipCont] = 4 THEN (([tblcontract].[QtyShip01])
/ ((1 + (CASE WHEN [tblcontract].[DtShip02] IS NOT NULL THEN 1 ELSE 0 END)) + (CASE WHEN [tblcontract].[DtShip03] IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN [tblcontract].[DtShip04] IS NOT NULL
THEN 1 ELSE 0 END))) ELSE [tblcontract].[QtyShip01] END AS Qty, Buyer
FROM dbo.tblcontract
WHERE CASE WHEN ([tblcontract].[ShipCont] = 4 OR
[tblcontract].[ShipCont] = 7) AND [tblcontract].[DtShip01] IS NOT NULL THEN [tblcontract].[DtShip01] ELSE NULL END, CASE WHEN [tblcontract].[ShipCont] = 4 THEN (([tblcontract].[QtyShip01])
/ ((1 + (CASE WHEN [tblcontract].[DtShip02] IS NOT NULL THEN 1 ELSE 0 END)) + (CASE WHEN [tblcontract].[DtShip03] IS NOT NULL THEN 1 ELSE 0 END) + (CASE WHEN [tblcontract].[DtShip04] IS NOT NULL
THEN 1 ELSE 0 END))) ELSE [tblcontract].[QtyShip01] END AS Qty, Buyer
 
K

Ksabai

Registered User.
Local time
Yesterday, 17:29
Joined
Jul 31, 2017
Messages
104
Yeah put some efforts and got is solved by just the following:

WHERE (CASE WHEN ([tblcontract].[ShipCont] = 4 OR
[tblcontract].[ShipCont] = 7) AND [tblcontract].[DtShip04] IS NOT NULL THEN [tblcontract].[DtShip04] ELSE NULL END IS NOT NULL)
 
W

WayneRyan

AWF VIP
Local time
Today, 01:29
Joined
Nov 19, 2002
Messages
7,122
Ksabai,

Your where clause does not need the Case statement.

The case statement will either return:
1) Null
2) [tblcontract].[DtShip04] if ShipCont in (4,7)

Its simpler to just use the criteria in the CASE in your where clause.

Code: 
WHERE ([tblcontract].[ShipCont] = 4 OR
       [tblcontract].[ShipCont] = 7) AND 
      [tblcontract].[DtShip04] IS NOT NULL

Wayne
 
You must log in or register to reply here.

Similar threads

T
Count distinct
Replies
8
Views
493
Pat Hartman
P
N
Solved Storing received Json from the internet into Parent table & child table at one go
2
Replies
21
Views
635
nector
N
S
Hi. I am new to this group and I am not a profesional. I am 66 years old and a retired Bank Officer active with Retired Bank Officers community.
Replies
8
Views
294
Suresh1957
S
N
Solved Big variation when sending data through Cloud SQL Server Vs MS Access
Replies
10
Views
376
Pat Hartman
P
D
Problem with IIF statement and filtering a query
Replies
4
Views
554
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom