SQL Expression - How to use it 3

[beanNut]

I'm trying to find a good example of using SQL Expression.
I also have a SQL query example here and would like to get idea of best way to solve it. I may need a "Paradigm Shift"
here. I originally thought I could drop the SQL query directly into the SQL expression and "Viola" it would work as desired. Doesn't work as I thought. So could use some help. How would you accomplish this using Crystal and how what are some uses for using the SQL Expression? Here's the query --

select *
from merchant.daily md
where trunc(md.transdate) >= to_date('10/01/2004', 'mm/dd/yyyy')
and trunc(md.transdate) < to_date('11/01/2004', 'mm/dd/yyyy')
and not exists (select * from retail.sales rs
where md.pnref = rs.pnref)

Thanks in Advance.

 

[vidru]

Read the FAQ for a general overview: faq149-1376

You can also check out this whitepaper from BO:
Using the SQL Expression Builder

-dave

 

[rosemaryl]

Dear BeanNut,

I use SQL expressions a lot in my reporting. I don't find that any of the existing articles show the true flexibilty that these supply.

Reasons to use:

Improve Report Performance:

You want to select records based on the result of a "formula" which is in-efficient, or you want to select records without joining to another table to get the data.

Example 1:

I need to select records that have aged x days and I don't want to use Group formula due to the performance hit.

I can create a sql expression:

(datediff(d,Table."First Date",Table."Second Date"))

This will return the aging in days and now I can select records where age > x

Example 2:

This one came up in an app specific forum on which I post just yesterday. The user need to select records for the report when a status had been changed to specific value in the last 7 days. This data is stored in a related table that has many, many records for each master record and would make a serious performance hit, especially since he didn't actually need to report any of the fields from that table and he would have to bring the database down to add the field that would show any date in the last 7 days the status was changed on these records:

I created the following sql expression (Note the view Incident is what is being reported on and the _TELDETAI_ is a table where the details records for the table on which the incident views is based are located)

(Select isnull(Max(T.PDATE),Null)
from "_SMDBA_"."_TELDETAI_" T
where
T."PROBLEM #" = Incident."Incident #"
and T.Action = 41
and rtrim(Substring(T.DESCRIPTION,28,250)) = 'INITIATE'
and
(T.PDATE >= CONVERT(VARCHAR(8),(getdate()-7),101)
and
T.PDATE < CONVERT(VARCHAR(8),(getdate()+1),101)
)
)

So, my selection criteria simply became:
Not (isnull({%StatusDate}))

Pulled just those records.

SQL Expressions can be used to run a udf (User Defined Function).

SQL expressions are infinitely useful.

Using a part of your example so that you could select records that met your criteria:

This won't work ...

select *
from merchant.daily md
where trunc(md.transdate) >= to_date('10/01/2004', 'mm/dd/yyyy')
and trunc(md.transdate) < to_date('11/01/2004', 'mm/dd/yyyy')
and not exists (select * from retail.sales rs
where md.pnref = rs.pnref)

You are limited in a SQL Expression to whatever rules your database has for a subquery in a main select statement. That is actually the definition of a SQL Expression. They can be correllated, as my first example was, in that it references a field in the main select and therefore can return different results for each row. Or, they can be non-correlated meaning they return 1 result (the same result for each row). An example of a non-correlated one:

(Select Isnull(Count("SEQUENCE"),0)
from "_SMDBA_"."_TELMASTE_"
)

That would give me a count of the records in that table. You could also add a where clause so that you only counted the current year's records, useful in that I could then divide the number of records this month by the total for a year to get the percentage that this months tickets to the year total.

And so on....

If we wanted to do something with your criteria I think a case when would be helpful

I think you must be using Oracle as SQL doesn't have trunc and to_date ... but you can use Case in Oracle 8.17 and higher.

Let's say you have the table Merchant.Daily in your report..

(Case when
Merchant."Transdate" >= to_date('10/01/2004','mm/dd/yyyy')
and
Merchant.transdate < to_date('11/02/2004', 'mm/dd/yyyy')
then 1
else 0
end)

Now, you would see a 1 or a 0 when you drop the field onto the report and you could even select records based upon them.

I have a report that I did where the customer's logic was so convoluted that I had 90 when statements in a sql expression so that I could group the report and display the group headers he wanted.

Example 3:
I want to use a UDF to calculate info. In this example a UDF is used to transofrm the age in seconds to a dhms string (I got tired of entering the formula directly):

(dbo.ral_dhms(Incident."Duration:"))

It returns the seconds formatted as DD:HH:MM:SS for me.

Sorry for the long post .. but it is a complicated topic.

I hope you found the information useful.

regards,
ro



Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[synapsevampire]

Very nicely stated, *Ro*!

If you're using CR 9 or above, you can use the Add Command and use your own SQL anyway, iclusive of subqueries and parameters directly against the SQL.

BeanNut, at least post your software version.

-k

 

[chelseatech]

I've found a few situations where a SQL expression has dramaticly improved perfomance.

At an early Info 6 site I had a report that to 80-100 mins to process. Using the Oracle NVL function inside a SQL epxression reduced that to 3 minutes.

If you have formulas inside your record selection formula see if you can replace them with SQL expressions.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm