Click here to Skip to main content
14,921,361 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
My sub query returns a single value and that value consists of multiple value which are separated by comma.
My sub query is like this:-
SQL
select ApplGLCd from ebreportstructureho
where grpname = 'Appendix'

and it's output is this:-0034,0035,0064,0026,0030,0031,0056

When I use the sub query with the Stored Procedure though it don't give any sort of error but it is supposed to return some data which unfortunately not happening.

What I have tried:

SQL
SELECT date,SUM(clcr-cldr) AS ClosingCr FROM ebgltransaction  
WHERE brncd='0002' AND glcd IN (select ApplGLCd from ebreportstructureho
where grpname = 'Appendix') AND date BETWEEN '20151001' AND '20151031'
GROUP BY date
ORDER BY date
Posted
Updated 7-Feb-16 20:47pm
v2
Comments
Tomas Takac 8-Feb-16 2:24am
   
I guess the data isn't there then.
Member 11579819 8-Feb-16 2:27am
   
Data is there. If I just run the sub query it returns the value and if I hard code the value returned by the sub query then my Stored Procedure works fine. But if I use Sub query within Stored procedure then it do not give any output.
aarif moh shaikh 8-Feb-16 2:35am
   
I think one of all conditions are returning null. please check it
Member 11579819 8-Feb-16 2:40am
   
I checked that with all the values... None is null among them.
Jörgen Andersson 8-Feb-16 3:13am
   
You're breaking the first Normal Form right there.
While Asifs solution fixes your immediate problem it's not a long term solution. Read up on normalization

1 solution

You need to break your code in two parts. First will fill a table variable by splitting returned result of a sub query and the second part will use this table variable for getting result. A pseudo code would be like this

SQL
DECLARE @TBL TABLE
( 
   GL_ID VARCHAR(100)
) 

INSERT INTO @TBL (GL_ID)
SELECT Item 
FROM   (select ApplGLCd from ebreportstructureho
where grpname = 'Appendix') T CROSS APPLY dbo.SplitStrings_Moden(T.ApplGLCd,',')

SELECT date,SUM(clcr-cldr) AS ClosingCr 
FROM ebgltransaction 
     INNER JOIN @TBL T ON ebgltransaction.glcd = T.GL_ID
WHERE brncd='0002' 
AND date BETWEEN '20151001' AND '20151031'
GROUP BY date
ORDER BY date


Please refer below for split functions
Split strings the right way - or the next best way[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900