Do you see anything wrong with my SQL?

Hi Experts!

I have a out of memory issue with a query and would like a sanity check - do you see anything glaringly obviously wrong with this - especially the CASE statement?

Select pieID as 'PIE Number', (SELECT CASE WHEN len(P.returnReason2) > 0 THEN P.returnReason + ' / ' + P.returnReason2 ELSE P.returnReason END) as 'Return Reason'
FROM tblInventory I
INNER JOIN tblPie P ON I.pieD = P.pieID


thank you!
EffinGoodAsked:
Who is Participating?
 
ralmadaCommented:
like this

Select 	pieID as 'PIE Number', 
	coalesce(P.returnReason + coalesce(' / ' + P.returnReason2, ''), '') as 'Return Reason' 
FROM tblInventory I 
INNER JOIN tblPie P ON I.pieD = P.pieID 

Open in new window

0
 
Dale BurrellDirectorCommented:
Best guess is that you have a lot of records in your tables - you're not limiting the results in any way so you will get every record back...
0
 
Bhavesh ShahLead AnalysistCommented:
this is not right way but still

Select pieID as 'PIE Number',
            CASE WHEN len(P.returnReason2) > 0 THEN P.returnReason + ' / ' + P.returnReason2 ELSE P.returnReason END as 'Return Reason'
FROM tblInventory I
INNER JOIN tblPie P ON I.pieD = P.pieID
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
ralmadaCommented:
out of memory issue with a query

Not enough details provided. Where do you get the error? In Management Studio, Reporting Services or the application level?

What is the exact error you get?

There could be many things that could cause an out of memory exception. It could be an index issue, it could be your logs are getting full, or simply that your server needs an overhaul.

I don't see anything wrong with the query, but I would personally change it to:

Select 	pieID as 'PIE Number', 
	P.returnReason + coalesce(' / ' + P.returnReason2, '') as 'Return Reason' 
FROM tblInventory I 
INNER JOIN tblPie P ON I.pieD = P.pieID 

Open in new window


Btw, what's the datatype for returnReason and returnReason2? I hope they are not TEXT. If so change them to varchar
0
 
EffinGoodAuthor Commented:
Hi Ralmada,

I like your solution - it is cleaner code. Thank you - just one thing - how can I print a blank value if P.returnReason and P.returnReason2 are both null? In your case presented, I get a "/" when both are null - how can I just get a null value?
0
 
EffinGoodAuthor Commented:
awesome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.