Unable to see SQL query

[Former Member]

hii,,

This is my selection formula

   {NAI_SUBMISSIONS.STUDY}  like  {?Study} and

//UpperCase (  {NAI_RESULTS.COMPONENT} ) in {?Component}  and

{NAI_SAMPLES.USER_SAMPLEID} like  {?User Sample ID} and

{?Start Date} <= DateTimeValue ({NAI_SAMPLES.TIMESTAMP}) and

{?End Date} >= DateTimeValue ({NAI_SAMPLES.TIMESTAMP}) and

UpperCase ( {NAI_RESULTS.STATUS}) in [{@Status1}, {@Status2} ] and

UpperCase ( {NAI_RESULTS.CONDITION}) in [{@Condition1}, {@Condition2} ] and

if {?Component} = '*' then  {NAI_RESULTS.COMPONENT}  like '*'

else  {NAI_RESULTS.COMPONENT} in {?Component}

when i execute and select Shoq SQl Query I am ubale to see date parameter int this why is this happening?

SELECT "NAI_SUBMISSIONS"."STUDY", "NAI_SAMPLES"."SAMPLE_ID", "NAI_SAMPLES"."USER_SAMPLEID", "NAI_RESULTS"."COMPONENT", "NAI_RESULTS"."UNITS", "NAI_RESULTS"."STATUS", "NAI_RESULTS"."CONDITION", "NAI_RESULTS"."TEXT_VALUE", "NAI_RESULTS"."TASK_ID", "NAI_SAMPLES"."TIMESTAMP", "NAI_RESULTS"."RESULT_ID"

FROM   ("OPS$GLIMSLA"."NAI_SUBMISSIONS" "NAI_SUBMISSIONS" INNER JOIN "OPS$GLIMSLA"."NAI_SAMPLES" "NAI_SAMPLES" ON "NAI_SUBMISSIONS"."SUBMISSION_ID"="NAI_SAMPLES"."SUBMISSION_ID") INNER JOIN "OPS$GLIMSLA"."NAI_RESULTS" "NAI_RESULTS" ON ("NAI_SAMPLES"."SAMPLE_ID"="NAI_RESULTS"."SAMPLE_ID") AND ("NAI_SAMPLES"."SUBMISSION_ID"="NAI_RESULTS"."SUBMISSION_ID")

WHERE  "NAI_SUBMISSIONS"."STUDY" LIKE 'CITY_WATER' AND "NAI_SAMPLES"."USER_SAMPLEID" LIKE '%' AND "NAI_RESULTS"."COMPONENT"='# Acceptable Bottles'

[JWiseman · Accepted solution]

hi Venkatesh,

as soon as you manipulate a field with a function inside of a record selection formula, then that line will not be passed to the server.

i would recommend

a) manipulating the date time field inside a SQL Expression field and then using the expression inside the record selection formula instead of the field

or

b) creating a new report off of a Commad object and writing out the command using the syntax in the Show SQL Query and appending a line for the date parameters, using the create parameter option in the command.

cheers,

jamie

[abhilash_kumar]

Hi Venkatesh,

It's not just the Date prompt that doesn't convert to 'where' clause in this situation. When you use functions like 'Uppercase' or Date functions, they're Not converted to where clause.

Use a SQL Expression instead.

-Abhilash