Click here to Skip to main content
15,923,164 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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 23-Feb-23 18:15pm
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

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[^]
 
Share this answer
 
There should have been some trailing or leading junk data or space.please make sure whether data is in accordance to the requirement
 
Share this answer
 
v2
Comments
Richard Deeming 24-Feb-23 4:42am    
If you'd bothered to read the accepted solution, which was posted seven years ago, you'd understand why your answer is wrong.

You can't pass a string containing multiple comma-separated values to an In query and expect it to match on part of the string; it will try to match the entire list of values, which won't work.

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