Solved

How to simplify my SQL statement?

Posted on 2016-12-01
14
55 Views
Last Modified: 2016-12-07
Hi Experts,

I'm in middle designing a SQL dynamically by code and need a way to

1- Proper construct the SQL
2- To have it done in a clear manner

here is an extract of the statement.

 
SELECT * FROM fn_frmJcahoReportsFrm(562,571) 
WHERE  Facility1_LastDay >= '10/1/2016'
And (
		( EmpReqDocs like '%License%' and ID in (Select ID from Employeestbl where LicenseExpires >= '12/1/2016')
		) 
		 Or ( EmpReqDocs like '%Malpractice%' and ID in (Select ID from Employeestbl where MalpracticeExpires >= '12/1/2016')
			 )
		)
		 
		  ORDER BY JcahoDueDate

Open in new window

The intended are the following 2 conditions:
1- Facility1_LastDay >= '10/1/2016'
2- either EmpReqDocs like '%License%' and ID in (Select ID from Employeestbl where LicenseExpires >= '12/1/2016')
or EmpReqDocs like '%Malpractice%' and ID in (Select ID from Employeestbl where MalpracticeExpires >= '12/1/2016')

In reality there are many more documents, however I believe once I have this done correctly the rest would follow the same pattern..
0
Comment
Question by:bfuchs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41909567
SELECT * FROM fn_frmJcahoReportsFrm(562,571)
WHERE  Facility1_LastDay >= '10/1/2016'
And (
            ( EmpReqDocs like '%License%' and ID in (Select ID from Employeestbl where LicenseExpires >= '12/1/2016')
            )
             Or ( EmpReqDocs like '%Malpractice%' and ID in (Select ID from Employeestbl where MalpracticeExpires >= '12/1/2016')
                   )
            )
             
              ORDER BY JcahoDueDate

your SQL statement above looks correct to meet your selection criterion. nothing need to be changed from your statement
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41909571
@Ryan,

1- While testing it I get same results regardless of document selected or date specified, which really does not make sense.
2- It looks confusing to me, looking for a way to make it stand clear before it gets longer..

Thanks,
Ben
0
 
LVL 51

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41909572
>>While testing it I get same results regardless of document selected or date specified, which really does not make sense
that's based on your selection criterion that the returned records are matched and hence displayed accordingly.

to make thing simpler, you may break your SQL statement by removing some criteria and test accordingly.
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 4

Author Comment

by:bfuchs
ID: 41909698
@Ryan,

Actually I see you right.

However when I do add the rest of the documents I get the attached error, no idea which line is causing the error, tried breaking it into multiple lines but error always shows on first line, any suggestion how to debug it?

Thanks,
Ben
Untitled.png
0
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41909700
can you post the script for function; fn_frmJcahoReportsFrm ?

it seems that's a data conversion error
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41909706
it seems that's a data conversion error
It definitely is, my question is just what is the easiest way to find out which part of the query breaks it, meaning which field is not a datetime?

fyi, here is the function..
FUNCTION [dbo].[fn_frmJcahoReportsFrm](@Fac1 nvarchar(50),
@Fac2 nvarchar(50))
RETURNS TABLE
AS
RETURN ( SELECT     TOP 100 PERCENT  dbo.OrientationNotes.Note,dbo.view_frmJcahoReportsFrm.*, dbo.OrientationNotes.Initial, fn_frmJcahoReportsFrm.MaxNoteDate, 
                      fn_frmJcahoReportsFrm.MaxNoteID
FROM         dbo.view_frmJcahoReportsFrm LEFT OUTER JOIN
                      dbo.fn_frmJcahoReportsFrmLastNotes(@Fac1, @Fac2) fn_frmJcahoReportsFrm ON 
                      dbo.view_frmJcahoReportsFrm.ID = fn_frmJcahoReportsFrm.EmployeeID LEFT OUTER JOIN
                      dbo.OrientationNotes ON fn_frmJcahoReportsFrm.MaxNoteID = dbo.OrientationNotes.ID
ORDER BY dbo.view_frmJcahoReportsFrm.FacName, dbo.view_frmJcahoReportsFrm.EmpName 
)

Open in new window


Thanks,
Ben
0
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41909709
can you also check if these objects involves in data conversion?

  • dbo.view_frmJcahoReportsFrm
  • fn_frmJcahoReportsFrmLastNotes(@Fac1, @Fac2)
  • dbo.OrientationNotes

do you have statement like Convert or Cast in your scripts?
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41909711
If I select the query as OP then it works fine, just the additional criteria is causing the data conversion error, not sure why are you looking at the views involved, the problem must be in the where clause..

Thanks,
Ben
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 41909717
Hi Ryan,

I'm closing this post as the question the way it was posted was already answered.

However if you do come up with a way to figure out my current problem I would appreciate.

P.S. leaving work early, will check again on sunday, have a nice weekend!

Thanks,
Ben
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41912498
Hi Ryan,

I simply re-organized the SQL statement, basically removed spaces and had them in one line (see attached), and then it worked.

Wondering what could be the reason??

Thanks,
Ben
Untitled.png
0
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41915822
sorry to come back lately...

I simply re-organized the SQL statement, basically removed spaces and had them in one line (see attached), and then it worked.

removing the spaces will probably not resolved your issue.

The "Conversion failed when converting datetime from character string" error will not disappear by reformatting your SQL statement into one liner.

I think is that you are purging your data and then follow by a re-run? if the data is gone, result could be different.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41917150
Hi Ryan,
removing the spaces will probably not resolved your issue.

The "Conversion failed when converting datetime from character string" error will not disappear by reformatting your SQL statement into one liner.
For me that didn't make sense either..
I think is that you are purging your data and then follow by a re-run?
Not sure what this means?
if the data is gone, result could be different.
That makes sense, but in that case sooner or later we will be faced with the problem again, correct?

Thanks,
Ben
0
 
LVL 51

Expert Comment

by:Ryan Chong
ID: 41917428
hi
I think is that you are purging your data and then follow by a re-run?
what i mean is that possible the data you're reading were changed which now it didn't produce the same error?

So, it could be due to some data error which makes your scripts failed to be executed.

hope this clarifies
0
 
LVL 4

Author Comment

by:bfuchs
ID: 41917446
Got you.

Hope this doesn't happen again, and if it does I will open a new thread and post a link over here.

Thanks,
Ben
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

730 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