Solved

Crystal Report Select Statements, One works other doesn't

Posted on 2011-03-03
37
971 Views
Last Modified: 2012-05-11
I wrote a fairly simple select statment that has basically three parts: a date component, a rate component and a code component.  The records returned should have a stop date that is null or >= to the current date, fall within a range (ex. >30 and <= 50) and have a certain code associated with it.  When I have only one set of rate/codes the stop dates that are null and/or >= to the current date are returned as the should.  If I add additional rate/codes parameters only the null stop dates are returned.  I have tried MANY combinations of "OR" statements and it will not return all the records.  Here is a sample of my code.  The first piece works, the second doesn't.  Any ideas?

This works:

(ISNULL({%DateConversionStop}) OR {%DateConversionStop} >=(CurrentDate)) and
({TABLE.RATE} > 30) and
({TABLE.RATE} <= 50) and
{TABLE2.CODE} in ["102", "1025", "1026"]



This does NOT work:
(ISNULL({%DateConversionStop}) OR {%DateConversionStop} >=(CurrentDate)) and
((({TABLE.RATE} > 30) and
({TABLE.RATE} <= 50) and
{TABLE2.CODE} in ["102", "1025", "1026"])
 OR
(({TABLE.RATE} > 10) and
({TABLE.RATE} <= 30) and
{TABLE2.CODE} in ["390" , "391" , "1787"]))
0
Comment
Question by:LindaOKSTATE
  • 19
  • 8
  • 5
  • +2
37 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 35031887
At a high level, nothing jumps out at me.  I cleaned up the code a little bit by removing unnecessary parentheses and indenting it to make it easier to read.   It seems pretty straightforward:  Check the dates and then one of the two sets of Rates/Codes criteria has to be met.

If you exclude the stop date check from the code below, does it return sets of data for both sets of rates and codes?

~Kurt
(
ISNULL({%DateConversionStop}) OR {%DateConversionStop} >=(CurrentDate)
) and
(
  (
  {TABLE.RATE} > 30 and
  {TABLE.RATE} <= 50 and
  {TABLE2.CODE} in ["102", "1025", "1026"]
  )
  OR
  (
  {TABLE.RATE} > 10 and
  {TABLE.RATE} <= 30 and
  {TABLE2.CODE} in ["390" , "391" , "1787"]
  )
)

Open in new window

0
 

Author Comment

by:LindaOKSTATE
ID: 35032055
Yes, if I leave off the date componet both sets show up.  Also, depending how I structure the statement, I can sometimes get the Null dates to show up for both sets, but I have not been able to get both null and >=current date to show up with more than one set of rates/codes.  I haad another person at my work look at it and they come up with the same coding as I did but it didn't work for them either.

0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 35032113
What's the code behind your SQL Expression?

~Kurt
0
 

Author Comment

by:LindaOKSTATE
ID: 35032128
Where is that?
0
 

Author Comment

by:LindaOKSTATE
ID: 35032146
I got it but it is very long because there are actually many more codes than what i used for an example
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 35032156
{%DateConversionStop} is a SQL Expression field, so you'll need to drill down through your Field Explorer into the SQL Expression section and edit it to see what it's doing.  Personally, I'd probably change the SQL Expression so that a NULL is converted to Today's date (or some date in the future).  That way, you don't need to check for NULLs at all in your record selection criteria - just use {%DateConversionStop} >=(CurrentDate)

~Kurt
0
 

Author Comment

by:LindaOKSTATE
ID: 35032234
OK, I can try that.  The {%DateConversionStop} is converted from a string (20110303) to a date (3/3/2011).  I would suspect that as the problem but since it works when there is only one set of rates/codes why would it not work with more than one rate/code set?  These dates are for medical reports so I have to be careful what I convert the null to.
0
 

Author Comment

by:LindaOKSTATE
ID: 35033367
I have looked at the SQL and nothing stands out.  They are identical except at the bottom it adds
 OR  
  (  
  {TABLE.RATE} > 10 and  
  {TABLE.RATE} <= 30 and  
  {TABLE2.CODE} in ["390" , "391" , "1787"]  
  )  
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35033405
Can you post the SQL the report is using?

mlmcc
0
 

Author Comment

by:LindaOKSTATE
ID: 35033541
Here is the code with the table names changed and just a few codes that should produce  11 records. This code gives NO records now, it should be returning 11 records, 3 with null dates and 8 records with dates ranging from 3/32011 to 3/7/2011

 SELECT
"TABLE1"."STATION",
"TABLE1"."BED_ABBREV",
"TABLE2"."ID2",
"TABLE2"."PRIMARY_NAME",
"TABLE2"."DOSE", "
TABLE2"."ROUTE",
"TABLE2"."LATIN_DIR_ABBR",
"TABLE1"."PAT_NUM",
"TABLE1"."PTNAME",
"TABLE1"."RATE",
"TABLE1"."PTAGE",
"TABLE2"."CODE",

({fn CONVERT(
STRING(Left( TABLE2.START_DATE, 4),
'-',
Right(Left( TABLE2.START_DATE, 6),2),
'-',
Right( TABLE2.START_DATE, 2)
)
, SQL_DATE
)}
),

 ({fn CONVERT(
STRING(Left( TABLE2.STOP_DATE, 4),
'-',
Right(Left( TABLE2.STOP_DATE, 6),2),
'-',
Right( TABLE2.STOP_DATE, 2)
)
, SQL_DATE
)})
 
 FROM  
 "SQLUser"."TABLE1" "TABLE1", "SQLUser"."TABLE2" "TABLE2"

 WHERE  
("TABLE1"."ID"="TABLE2"."ID")

AND

(({fn CONVERT(
STRING(Left( TABLE2.STOP_DATE, 4),
'-',
Right(Left( TABLE2.STOP_DATE, 6),2),
'-',
Right( TABLE2.STOP_DATE, 2)
)
, SQL_DATE
)}) IS  NULL  

OR

({fn CONVERT(
STRING(Left( TABLE2.STOP_DATE, 4),
'-',
Right(Left( TABLE2.STOP_DATE, 6),2),
'-',
Right( TABLE2.STOP_DATE, 2)
)
, SQL_DATE
)})>={d '2011-03-03'})

AND (("TABLE1"."RATE">10 AND "TABLE1"."RATE"<=30)

AND
("TABLE2"."CODE"='868' OR
"TABLE2"."CODE"='869')

OR

("TABLE1"."RATE">30 AND
"TABLE1"."RATE"<=50)

AND

("TABLE2"."CODE"='1789' OR
"TABLE2"."CODE"='2273' OR
"TABLE2"."CODE"='2628' OR
"TABLE2"."CODE"='2644' OR
"TABLE2"."CODE"='310'))

Thanks for your time.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35034061
Are the codes user supplied?

This part
AND (("TABLE1"."RATE">10 AND "TABLE1"."RATE"<=30)

AND
(
("TABLE2"."CODE"='868' OR
"TABLE2"."CODE"='869')

OR

("TABLE1"."RATE">30 AND
"TABLE1"."RATE"<=50)
)
AND
(
("TABLE2"."CODE"='1789' OR
"TABLE2"."CODE"='2273' OR
"TABLE2"."CODE"='2628' OR
"TABLE2"."CODE"='2644' OR
"TABLE2"."CODE"='310'))
)

I added a set () around each part of the rate and code checks.

mlmcc
0
 
LVL 19

Expert Comment

by:GJParker
ID: 35034624
mlmcc 's doesn't look right to me, try this

AND
(
    (
    "TABLE1"."RATE" > 10 AND "TABLE1"."RATE" <= 30
    AND
        (
        "TABLE2"."CODE"='868' OR "TABLE2"."CODE"='869'
        )
    )

OR
    (
    "TABLE1"."RATE" > 30 AND "TABLE1"."RATE" <= 50
    AND
        (
    "TABLE2"."CODE"='1789' OR
    "TABLE2"."CODE"='2273' OR
    "TABLE2"."CODE"='2628' OR
    "TABLE2"."CODE"='2644' OR
    "TABLE2"."CODE"='310'
        )  
    )
)
0
 

Author Comment

by:LindaOKSTATE
ID: 35037509
Neither of the parentheses solutions worked, I got no data returned.  The codes were typed in by me, and they are the same codes I typed in MS Access which does return the data correctly.  Another co-worker was saying something about selecting the largest data pool first then drilling down to the data I was looking for, such as pulling all the rates and/or codes and then pulling out the null dates and the >=(CurrentDate)s.  I did put the date part at the end of the statement and that didn't work either.  Does anyone know what she was talking about?  Something about the way SQL statements limit the data.

Thanks again.
0
 
LVL 19

Expert Comment

by:GJParker
ID: 35037598
Both of the SQL statements provided are valid, the fact that they return no records is because no records match either of the statements and it's hard to correct the statements without having access to your data.

If i were you I would try building up the WHERE clause a bit at time and monitor the results as this will give you an indication of which part is incorrect i.e.

run it with just the date test(s) and check the records you expect in the final results are their then add in each of the separate clauses and see what makes them dissapear.

HTH
0
 

Author Comment

by:LindaOKSTATE
ID: 35041303
I showed this code to  a SQL programmer.  He thinks that it is a bug that Crystal Reports won't run this code.  Unless someone else has any ideas I will be closing this question.  I plan to run a query from MS Access and run the Crystal Report from the MS Access query.
0
 
LVL 34

Expert Comment

by:James0628
ID: 35043400
You're making changes to a SQL Expression field, correct?  Can you try entering lines like the ones that GJParker posted and then go to Database > "Show SQL Query" and see how it looks?  I don't normally use SQL Expressions, but I think they show up there.  If so, you could see if CR appears to be using the SQL as written, or has changed it in some way.

 James
0
 

Author Comment

by:LindaOKSTATE
ID: 35043946
I went to the Show SQL window of the one that worked and the one with another rate/code clause added.  I copied them to Notepad and printed both SQL statements and went through them line by line to compare.  There was no difference in the code until the added rate/code clause and that looked like the clause from the one that worked except for the actual values.

Linda
0
 
LVL 34

Expert Comment

by:James0628
ID: 35048256
OK, I'm not really following.  Are you saying that the values were wrong, or just that they were different (but were _supposed_ to be different)?  If they were wrong, what was wrong with them?  It might also help it you post the relevant sections from the two queries (the one that worked and the one that didn't work).

 James
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:LindaOKSTATE
ID: 35048438
Well, I am not sure what you mean.  I have explained the best I can.  

This works:

(ISNULL({%DateConversionStop}) OR {%DateConversionStop} >=(CurrentDate)) and
({TABLE.RATE} > 30) and
({TABLE.RATE} <= 50) and
{TABLE2.CODE} in ["102", "1025", "1026"]

This does NOT work:
(ISNULL({%DateConversionStop}) OR {%DateConversionStop} >=(CurrentDate)) and
((({TABLE.RATE} > 30) and
({TABLE.RATE} <= 50) and
{TABLE2.CODE} in ["102", "1025", "1026"])
 OR
(({TABLE.RATE} > 10) and
({TABLE.RATE} <= 30) and
{TABLE2.CODE} in ["390" , "391" , "1787"]))

The only difference is the "OR" statement added to the bottom.  The parentheses have been added and removed and regrouped many, many times and ways, so I don't think that is the problem.  When the first query is run, both the records with null stop dates AND stop dates >=(CurrentDate) are returned.  If the second rate/code clause is added
    OR
    (({TABLE.RATE} > 10) and
    ({TABLE.RATE} <= 30) and
    {TABLE2.CODE} in ["390" , "391" , "1787"]


only the records with the null stop dates are returned.  I created the same query in MS Access to be able to compare the records that are returned.  This is basically the select statement used but without all the codes to make it easier.
0
 

Author Comment

by:LindaOKSTATE
ID: 35048616
By values I meant the code was identical except for the rates and code values, meaning all the parentheses and brackets, commas and everything were identical. The values that were different are
>10 and <=30 instead of >30 and <=50, and using codes 390, 391 and 1787 instead of 102, 1025, 1026.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35051513
Can you explain in English what you need the selection to be?

mlmcc
0
 

Author Comment

by:LindaOKSTATE
ID: 35052552
I have codes that represent medications.  I have rates that represent  the medication levels in the body.  When a patient is on a certain medication (code) I need to know when the medications get to a certain level in the body.  Some medications are important at a certain level (rate) (i.e.  >10 and <=30) and some others are important at a different rate  (i.e. >30 and <=50, or >0 and <=10).  The patient needs to still be on the medication, therefore, the stop date has to be either today or in the future >=(CurrentDate) or the stop date of the medication has not been determined so therefore is null.  Hopefully, this makes this query easier to understand.

Thanks,
Linda
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35052999
So you want to find all cases where
Stop date is NULL OR set and after today
AND
Medication level is >value and < value2

This test
{TABLE.RATE} > 10 and
{TABLE.RATE} <= 30 and
{TABLE2.CODE} in ["390" , "391" , "1787"]

Rate > 10 and Rate < 30 and It is a specific medication (1 of 3)
So you want to find all paients on 1 of the 3 medications and where the rate is in the critical level.

Find all patients currently on medication
IsNull({StopDate}) OR {StopDate} >= CurrentDate

Medications  ["390" , "391" , "1787"] Critical range 10-30
{TABLE.RATE} > 10 and {TABLE.RATE} <= 30 and {TABLE2.CODE} in ["390" , "391" , "1787"]

Medications  ["102", "1025", "1026"]  Critical range 30-50
{TABLE.RATE} > 30 and {TABLE.RATE} <= 50 and {TABLE2.CODE} in ["102", "1025", "1026"]



So putting it all together

Currrently on medication
AND
Med1 OR Med2

(
IsNull({StopDate}) OR {StopDate} >= CurrentDate
)
AND
 (
  (
     {TABLE.RATE} > 30 and {TABLE.RATE} <= 50 and {TABLE2.CODE} in ["102", "1025", "1026"]
  )
  OR
  (
    {TABLE.RATE} > 10 and {TABLE.RATE} <= 30 and {TABLE2.CODE} in ["390" , "391" , "1787"]
  )
)

mlmcc
0
 

Author Comment

by:LindaOKSTATE
ID: 35053254
Yes, this is the code I have tried that doesn't work.  
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35053327
Where are you putting it?

Are you using parameters for the values?

mlmcc
0
 

Author Comment

by:LindaOKSTATE
ID: 35053404
I put it in the records selection area.  the values are hard coded.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35053448
If you use


(
IsNull({StopDate}) OR {StopDate} >= CurrentDate
)
AND
(
   {TABLE.RATE} > 30 and {TABLE.RATE} <= 50 and {TABLE2.CODE} in ["102", "1025", "1026"]
)
You get a set of records.  Are they the correct records?

If you then try

(
IsNull({StopDate}) OR {StopDate} >= CurrentDate
)
AND
(
    {TABLE.RATE} > 10 and {TABLE.RATE} <= 30 and {TABLE2.CODE} in ["390" , "391" , "1787"]
)

You get a different set of records.  Are they correct?

When you put the 2 together what records do you get?

mlmcc
0
 

Author Comment

by:LindaOKSTATE
ID: 35053553
If i use either of the codes with only one rate/code set i get records with null stop dates and >+Current stop dates. These records are correct.  When you put the two together only the null stop dates come up.

Linda
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35053641
Try it this way
(
  (
     {TABLE.RATE} > 30 and {TABLE.RATE} <= 50 and {TABLE2.CODE} in ["102", "1025", "1026"]
  )
  OR
  (
    {TABLE.RATE} > 10 and {TABLE.RATE} <= 30 and {TABLE2.CODE} in ["390" , "391" , "1787"]
  )
)

AND
(
IsNull({StopDate}) OR {StopDate} >= CurrentDate
)

mlmcc
0
 

Author Comment

by:LindaOKSTATE
ID: 35053753
I can't right now cause my remote computer is shut off.  But to tell you, I did already try this and it gives the same result (only null stop date records) and when you look at the code, the program puts the date clause back at the top.  The only think I can think of is the converted field for the stop date --{%DateConversionStop}--is the problem, but why it works with one set of rate/codes and not two or three doesn't make sense.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35053779
What is that formula?

mlmcc
0
 

Author Comment

by:LindaOKSTATE
ID: 35053978
(({fn CONVERT(
STRING(Left( TABLE2.STOP_DATE, 4),
'-',
Right(Left( TABLE2.STOP_DATE, 6),2),
'-',
Right( TABLE2.STOP_DATE, 2)
)
, SQL_DATE
)}) IS  NULL  

OR

({fn CONVERT(
STRING(Left( TABLE2.STOP_DATE, 4),
'-',
Right(Left( TABLE2.STOP_DATE, 6),2),
'-',
Right( TABLE2.STOP_DATE, 2)
)
, SQL_DATE
)})>={d '2011-03-03'})

This is from the SQL above.  The SQL expression was similar but I can't get to it right now.
0
 

Author Comment

by:LindaOKSTATE
ID: 35063628
{fn CONVERT(
STRING(Left( Table2.STOP_DATE, 4),
'-',
Right(Left( Table2.STOP_DATE, 6),2),
'-',
Right( Table2.STOP_DATE, 2)
)
, SQL_DATE
)}
0
 
LVL 34

Accepted Solution

by:
James0628 earned 250 total points
ID: 35067015
That's what's in %DateConversionStop?  If so, it seems like you could just get rid of the expression and do the conversion in the record selection.  Maybe that will help.

 If I'm reading that correctly, Table2.STOP_DATE is a string in YYYYMMDD format.  You could convert that in CR using very similar code.

(ISNULL({%DateConversionStop}) OR {%DateConversionStop} >=(CurrentDate)) and

 could be replaced by

(IsNull ({Table2.STOP_DATE}) or Date (Left ({Table2.STOP_DATE}, 4) + "-" +
 Right (Left ({Table2.STOP_DATE}, 6), 2) + "-" +
 Right ({Table2.STOP_DATE}, 2)) >= CurrentDate) and

 The conversion could be done in other ways.  I just thought it might be easier for you if it followed the same basic approach as the old code.

 The question is whether or not that test would be passed to the server.  You can go to "Show SQL Query" and see if the test is included in the query that CR is passing to the server.  If not, it could have a big impact on the report's performance.  Hopefully it would be passed.  If not, it might be possible to do the conversion in a different way that would be passed.  Even if it's not passed to the server, it still might be worth a try, just to see if it produces the records that you're looking for.

 James
0
 
LVL 34

Expert Comment

by:James0628
ID: 35086871
So, replacing the expression with a conversion in CR fixed the problem?  Great.  Maybe your SQL programmer was right and there's some kind of bug in CR.  FWIW, I don't use SQL expressions (almost all of my reports use stored procedures, and you can't use SQL expressions with those), but using one in a record selection formula does seem a bit odd to me.  But, like I said, I don't use them, so maybe it's just me.  IAC, maybe CR had a problem with that too and was losing something in the translation or something.

 IAC, I'm glad that you were finally able to get the report working.

 James
0
 

Author Comment

by:LindaOKSTATE
ID: 35087505
I meant to tell you that the report seems to be working fine.  Seems like a very strange quirk that the SQL had to be inthe secord selection instead of pulling it from the converted field, esp since it worked on the simplier report.  But thanks anyway, I was beginning to think this was going to remain a mystery.  And thanks to everyone else who tried to help.

Linda
0
 
LVL 34

Expert Comment

by:James0628
ID: 35106272
I can only guess that it had something to do with how CR was combining the main query, including the record selection, with the SQL expression, which is (I think) really a separate query.

 James
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now