Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 92
  • Last Modified:

How to simplify my SQL statement?

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
bfuchs
Asked:
bfuchs
  • 8
  • 6
1 Solution
 
Ryan ChongCommented:
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
 
bfuchsAuthor Commented:
@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
 
Ryan ChongCommented:
>>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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
bfuchsAuthor Commented:
@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
 
Ryan ChongCommented:
can you post the script for function; fn_frmJcahoReportsFrm ?

it seems that's a data conversion error
0
 
bfuchsAuthor Commented:
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
 
Ryan ChongCommented:
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
 
bfuchsAuthor Commented:
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
 
bfuchsAuthor Commented:
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
 
bfuchsAuthor Commented:
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
 
Ryan ChongCommented:
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
 
bfuchsAuthor Commented:
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
 
Ryan ChongCommented:
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
 
bfuchsAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now