UTC SQL Expression

[Ox73]

Hi, I would like to know if there is a way to use this expression only after the records have been extracted from the database in crystal...I have this SQL Expression formula and want to only calculate it once it is in Crystal not while reading the records.
*************FORMULA***************
tz_converter.convert_utc_tm_to_local_tm("TABLE_NAME"."DATE_TIME_IN_UTC", (select time_authority_id from tz_time_authority where name='Central'))
************************************

This formula is converting my UTC date time to Central but it does it while readind the records and I want after the record is extracted because it causes the report to run slower...or is there a better formula to use?

 

[synapsevampire]

SQL is executed on databases, Crystal is a report writer front end. You might do the equivalent using a UFL in Crystal, depending upon your Crystal version, but Crystal doesn't execute SQL, the database does.

Example of a UFL is at

http://www.milletsoftware.com

as Visual Cut Light.

You'll be better served to describe your environment and requirements rather than trying to state the architecture until you get a better understanding of SQL, databases and Crystal.

We don't know what software you're using.

We don't know where you are using the conversion.

If you're converting on every row, then the database will prove MUCH faster than Crystal.

If you only need the result ONCE per report, then create a subreport and place it in there and use a shared variable to return the value to Crystal.

-k

 

[Ox73]

I am using this formula in Crystal 10 under SQL Expression fields. So it is being ran in Crystal against the DB.

 

[synapsevampire]

No, it is not being ran in crystal against the DB, it is passed as part of the SQL query that is created by the Crystal GUI and ran by the database. Not run in Crystal, passed in the SELECT line in SQL and ran on the database.

Check Database->Show SQL Query and you'll see it embedded within.

I gather you didn't try anything offered, instead deciding to help me understand databases and Crystal.

Still don't know where you intend to use the SQL Expression in the report... If it's just once, then use a subrepoirt in the report header and it will execute only once, if you need it per row, then it's fater as SQL then it would be in Crystal.

Pity, but there's not much to be done until you decide that to share your actual requirements.

I would suggets posting example data and expect3d output as your descriptions fail and your understanding of the technology still a tad green.

-k

 

[Ox73]

Thanks for the tip looks like UFL does the same conversion as TZ.