apply case in where condition - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

apply case in where condition

Author

Topic

Aasif
Starting Member

2 Posts

Posted - 2011-04-27 : 10:32:40

I have stored procedure that will generate reports based on condition
I have written sp like below.
ALTER PROC [dbo].[T1]
AS
BEGIN
;with sums AS
(
select SOP30300.ITEMNMBR AS ITEMNUMBER
,SOP30200.DOCDATE AS DOCDATE
,IV00101.ITEMTYPE AS ITEMTYPE
,IV00101.USCATVLS_1 AS USCATVLS_1
,IV00101.USCATVLS_2 AS USCATVLS_2
,IV00101.USCATVLS_3 AS USCATVLS_3
,IV00101.ITMGEDSC AS ITMGEDSC
,SOP30300.UNITCOST AS KITCOST
--,IV00104.SEQNUMBR AS SEQNUMBR
--,IV00104.CMPTITNM AS CMPTITNM
--,IV00104.CMPITUOM AS CMPITUOM
--,IV00104.CMPITQTY AS CMPITQTY
--,IV00104.CMPSERNM AS CMPSERNM
,(SELECT (CURRCOST) FROM IV00101 WHERE ITEMNMBR = SOP30300.ITEMNMBR)AS COST
--,(SELECT (SEQNUMBR) FROM IV00104 WHERE ITEMNMBR = SOP30300.ITEMNMBR)AS COMPONENTSEQNUMBR
FROM SOP30200
INNER JOIN SOP30300 ON SOP30200.SOPTYPE=SOP30300.SOPTYPE
INNER JOIN IV00101 ON SOP30300.ITEMNMBR=IV00101.ITEMNMBR
INNER JOIN IV00104 ON IV00101.ITEMNMBR = IV00104.ITEMNMBR
WHERE IV00101.ITEMTYPE=(CASE WHEN (IV00101.ITEMTYPE=3)THEN
(SELECT IV00104.SEQNUMBR
,IV00104.CMPTITNM
,IV00104.CMPITUOM
,IV00104.CMPSERNM FROM IV00104
INNER JOIN IV00101 ON IV00104.ITEMNMBR = IV00101.ITEMNMBR)END)
--WHERE IV00101.ITEMNMBR = IV00104.ITEMNMBR)END
GROUP BY SOP30300.ITEMNMBR
,SOP30200.DOCDATE
,IV00101.ITEMTYPE
,IV00101.USCATVLS_1
,IV00101.USCATVLS_2
,IV00101.USCATVLS_3
,IV00101.ITMGEDSC
,SOP30300.UNITCOST
--,IV00104.SEQNUMBR
--,IV00104.CMPTITNM
--,IV00104.CMPITUOM
--,IV00104.CMPITQTY
--,IV00104.CMPSERNM
)
SELECT ITEMNUMBER
,DOCDATE
,ITEMTYPE
,USCATVLS_1
,USCATVLS_2
,USCATVLS_3
,ITMGEDSC
,COST
,KITCOST
,SEQNUMBR
,CMPTITNM
,CMPITUOM
,CMPITQTY
,CMPSERNM
--,ITEMCOST
FROM sums
END
when execute it shows error Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I want to get record from IV00104 if ItemType=3 for that i need nested query Can anybody help me to get out of this problem.
Thanks,
Aasif Khan

Asifkhan

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-27 : 10:35:01

i think you need to specify the columns in the cte...


;with sums (ITEMNUMBER, DOCDATE, ITEMTYPE...) AS
(
select SOP30300.ITEMNMBR AS ITEMNUMBER
,SOP30200.DOCDATE AS DOCDATE
,IV00101.ITEMTYPE AS ITEMTYPE
,IV00101.USCATVLS_1 AS USCATVLS_1
,IV00101.USCATVLS_2 AS USCATVLS_2
,IV00101.USCATVLS_3 AS USCATVLS_3
,IV00101.ITMGEDSC AS ITMGEDSC
,SOP30300.UNITCOST AS KITCOST
--,IV00104.SEQNUMBR AS SEQNUMBR
--,IV00104.CMPTITNM AS CMPTITNM
--,IV00104.CMPITUOM AS CMPITUOM
--,IV00104.CMPITQTY AS CMPITQTY
--,IV00104.CMPSERNM AS CMPSERNM
...

Not sure I follow this:


WHERE IV00101.ITEMTYPE=(
			CASE WHEN (IV00101.ITEMTYPE=3)
					THEN (
						SELECT 	IV00104.SEQNUMBR
								,IV00104.CMPTITNM
								,IV00104.CMPITUOM
								,IV00104.CMPSERNM  -- these are your issue
						FROM IV00104 
						INNER JOIN IV00101 
						ON IV00104.ITEMNMBR = IV00101.ITEMNMBR
						)
					END
				)

I don't really understand what you are trying to do though.. How do you know which row to pull from IV00104?

Corey

I Has Returned!!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-27 : 10:46:39

Is this what you are trying to do??

ALTER PROC [dbo].[T1]
AS
BEGIN
;with sums AS
(
select SOP30300.ITEMNMBR AS ITEMNUMBER
,SOP30200.DOCDATE AS DOCDATE
,IV00101.ITEMTYPE AS ITEMTYPE
,IV00101.USCATVLS_1 AS USCATVLS_1
,IV00101.USCATVLS_2 AS USCATVLS_2
,IV00101.USCATVLS_3 AS USCATVLS_3
,IV00101.ITMGEDSC AS ITMGEDSC
,SOP30300.UNITCOST AS KITCOST
,IV00104.SEQNUMBR AS SEQNUMBR
,IV00104.CMPTITNM AS CMPTITNM
,IV00104.CMPITUOM AS CMPITUOM
,IV00104.CMPITQTY AS CMPITQTY
,IV00104.CMPSERNM AS CMPSERNM
,(SELECT (CURRCOST) FROM IV00101 WHERE ITEMNMBR = SOP30300.ITEMNMBR)AS COST
,(SELECT (SEQNUMBR) FROM IV00104 WHERE ITEMNMBR = SOP30300.ITEMNMBR)AS COMPONENTSEQNUMBR
FROM SOP30200
INNER JOIN SOP30300 ON SOP30200.SOPTYPE=SOP30300.SOPTYPE
INNER JOIN IV00101 ON SOP30300.ITEMNMBR=IV00101.ITEMNMBR
INNER JOIN IV00104
ON IV00101.ITEMNMBR = IV00104.ITEMNMBR
AND IV00101.ITEMTYPE=3
GROUP BY SOP30300.ITEMNMBR
,SOP30200.DOCDATE
,IV00101.ITEMTYPE
,IV00101.USCATVLS_1
,IV00101.USCATVLS_2
,IV00101.USCATVLS_3
,IV00101.ITMGEDSC
,SOP30300.UNITCOST
,IV00104.SEQNUMBR
,IV00104.CMPTITNM
,IV00104.CMPITUOM
,IV00104.CMPITQTY
,IV00104.CMPSERNM
)

SELECT ITEMNUMBER
,DOCDATE
,ITEMTYPE
,USCATVLS_1
,USCATVLS_2
,USCATVLS_3
,ITMGEDSC
,COST
,KITCOST
,SEQNUMBR
,CMPTITNM
,CMPITUOM
,CMPITQTY
,CMPSERNM
--,ITEMCOST
FROM sums
END

Corey

I Has Returned!!

Aasif
Starting Member

2 Posts

Posted - 2011-04-27 : 10:54:09

Actually IV00104 is KIT ITEM DETAILS table n IV00101 is Item master table itemtype field is in IV00101 n details are in IV00104.
SOP30300 is Sales transaction details table n Sales transaction table header SOP30200.
I want to pull all the rows which matches the item no with IV00101 then I want to perform calculation.
Thanks for your reply.

Asifkhan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources