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

x
?
Solved

How to simplify my SQL statement?

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

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 55

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 55

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 55

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 55

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 55

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
Loops Section Overview
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…

580 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