Solved

How to simplify my SQL statement?

Posted on 2016-12-01
14
52 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
  • 8
  • 6
14 Comments
 
LVL 50

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 50

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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 50

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 50

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 50

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 50

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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