?
Solved

How to simplify my SQL statement?

Posted on 2016-12-01
14
Medium Priority
?
71 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 53

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 53

Accepted Solution

by:
Ryan Chong earned 2000 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
Understanding Linux Permissions

Linux for beginners: How to view the permissions associated with files and directories and also how you can change them.

 
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 53

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 53

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 53

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 53

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

800 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