Show sql

[sameer11]

Hi,

I like to clarify doubt, As I read few articles here it says filter condition should appear in the show sql to speed up the report.

When using record selection to intergate parameters in the report and that will show in the show SQL where clause. But when the parameter is quered for ALL or a value or multiple values for examples:

In the record selection:


{table.CustName} = {?CustomerName}

//When show SQL is used you can see the sql query for the Parameter

select * from table
where {table.CustName} = 'MyName'

If I use this formula for checking "all"

If {?parameter} = '*' then true
else
{table.CustName} = {?CustomerName}

//In this case the Show sql will be


select * from table

// Here I could use the alternate since this a string parameter and also works for '*'

{table.CustName} like {?CustomerName}

Show Sql would be


select * from table
where {table.CustName} like '*'


What would be the alternate If the parameter is a number (customer Id)
How can I do this to run the report for single id or mutliple id or all id's?

I tried to be as clear as possible with my question. Hope you understand

Any help is appericated

Thanks,
Sameer

 

[Turkbear]

Hi,
Synapsevampire has a FAQ that explains that:

faq766-3826

Lots of very good info..




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I would set up the parameter to have an value option of 0, and then either use a description of "All" or instruct the user to select 0 if they want to see all IDs. Then set up the record selection formula like:

(
if {?Id} <> 0 then
{table.ID} = {?ID} else
if {?ID} = 0
then true
)

-LB

 

[sameer11]

Yes! you are right, lbass. But this record selection would not be passed to the sql where clause in the Show sql option. My question is there any alternate to this as I can pass the string parameter to the "where clause" in the sql using "Like". Not sure how I can do for a number parameter.
using you above mentioned formula is not an issue for me. But I am just curious.


Thanks Turkbear for the refering the article. It's really good one and Thanks to the author of the article.


Thanks,
Sameer

 

[lbass]

Yes, actually, it should pass to the SQL.

-LB

 

[sameer11]

I tired to check again but I do not see the id passed in the where. I did miss something? Iam using CR 8.5

Thanks,
Sameer

 

[lbass]

Should pass. What is your entire record selection formula?

-LB

 

[sameer11]

This is in record selection:
(
{VW.requested_date} in {?start_date} to {?end_date}
)
and
(
{VW.line_id} like {?line_id}
)
and
(
{VW.location_active} like {?SL_STATUS}
)

and
( if {?customer_id} <> 0 then
{VW.customer_id} = {?customer_id}
else
if {?@customer_id} = 0 then
true
)


This is in the show sql where clause:

WHERE
VW.requested_date >= "Jan 1 1900 00:00:00AM" AND
VW.requested_date < "Jan 1 3001 00:00:00AM" AND
VW.line_id LIKE '%' AND
VW.service_location_active LIKE '%'

 

[Turkbear]

Hi,
Unless you are supplying the % signs in the parameter input string,change this:

[COLOR=red]
(
{VW.line_id} like {?line_id}
)
and
(
 {VW.location_active} like {?SL_STATUS}
) 
[/color]

to

[COLOR=blue]
(
{VW.line_id} like "%" + {?line_id} + "%"
and
(
 {VW.location_active} like "%" + {?SL_STATUS} + "%"

) 
[/color]

The parameter value was not being added to the wildcard needed with a like operator..
...






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[sameer11]

I have defined a default values for the parameters as '*'
and set description to 'All' so usere chose * for all values and it's pass as '%' in the Show Sql.

Thanks,
Ratna

 

[lbass]

What I showed you was the standard way to ensure passage to the SQL. Anyway, also try:

and
(
(
{?customer_id} <> 0 and
{VW.customer_id} = {?customer_id}
) or
{?@customer_id} = 0
)

-LB

 

[sameer11]

Hm...Tired it.. did not work. Does verison limits this?
Why can't I do it when you are able to pass this record selection to SQL?

Thanks,
Ratna

 

[lbass]

Try removing the other record selection criteria and then see if it passes. Sometimes the order of the clauses can affect what passes.

I copied your typo when I last posted. The formula should be:

(
(
{?customer_id} <> 0 and
{VW.customer_id} = {?customer_id}
) or
{?customer_id} = 0
)

-LB