Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Do you see anything wrong with my SQL?

Posted on 2012-09-20
6
Medium Priority
?
559 Views
Last Modified: 2012-09-21
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!
0
Comment
Question by:EffinGood
6 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 38420338
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 38420767
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
 
LVL 41

Expert Comment

by:ralmada
ID: 38422671
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:EffinGood
ID: 38422855
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
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 38423041
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
 

Author Closing Comment

by:EffinGood
ID: 38423201
awesome
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question