Show SQL Query...

[harding1031]

CR 9.0, Can someone please explain to me what the "Show SQL Query... dialog box" shows. It does not have all the conditions that I set in the Selection Expert. The report is correct, but I think it can be a little more efficient. TIA.

 

[kskid]

It depends on what and how you are performing the Select Expert.

You might post more technical information

Crystal version
Your links
Select Expert formula
Show SQL Query to see what is sent to the database.

-LW

 

[harding1031]

I am using:
CR Version 9.0
I have no DB links, only using one DB table.

Selection Expert Formula:
(DateValue({IMITREP.KT42A}) = DateValue({?Date}) + 1 or
DateValue({IMITREP.KT42A}) = DateValue({?Date}) ) and
{IMITREP.SC1ERA} in ["11", "13"] and
{IMITREP.SC13FA} = "Y"

SQL Query:
SELECT "IMITREP"."KT43A", "IMITREP"."SCEOA", "IMITREP"."SCNIA", "IMITREP"."KT42A", "IMITREP"."SC1ERA", "IMITREP"."SCFAA", "IMITREP"."SCGJA", "IMITREP"."SC3YAA", "IMITREP"."MB3KA", "IMITREP"."SC1CBA", "IMITREP"."SC13FA"
FROM "SCIM400"."IMS20PDTA"."IMITREP" "IMITREP"
WHERE ("IMITREP"."SC1ERA"='11' OR "IMITREP"."SC1ERA"='13') AND "IMITREP"."SC13FA"='Y'
ORDER BY "IMITREP"."SCEOA", "IMITREP"."KT42A", "IMITREP"."KT43A

 

[ianoctdec]

TIA,
The first two lines of your formula don't get passed to SQL query because of the DateValue function. Instead, you might want to create a SQL expression and put that in your record selection formula.

Dana

 

[harding1031]

Thanks, but how and where would I create the SQL.The ?Date
field is a parameter field. I don't know the date until the report fires and gets the parameter data. TIA.

 

[vidru]

What is the data type of {IMITREP.KT42A}? If it's stored as a Date or a DateTime, then you wouldn't need to use the DateValue function, just make sure your parameter is set to a Date datatype:

({IMITREP.KT42A}) in {?Date} to {?Date} + 1 ) and
{IMITREP.SC1ERA} in ["11", "13"] and
{IMITREP.SC13FA} = "Y"

If it's not stored as a date, can you supply some sample data?

-dave

 

[harding1031]

Thanks, the date is stored as a string "2004-12-22" and
?Date is a string, I only used the DateValue function to increment the date in the Selection Expert formula.