Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

SQL Expression Editor problems

Hello all,
Can somebody give me some code examples of how to use the SQL Expression Editor in CR 8.5?  The documentation from Seagate on this part of the software is close to being non-existent.  I am running CR on an Oracle 8i database.

Another question:  Can I use subqueries in CR 8.5?

Any help is appreciated.

Thanks,
cr_db
0
cr_db
Asked:
cr_db
2 Solutions
 
mnyeCommented:
it runs on the native database launguage, so just stay within the parameters of PL/SQL and it will work fine.

hth
late
matt
0
 
srini_jgCommented:
Hi,

Here is an example you asked.

Let us say you have a sales table and has a column DOCDATE which is defined as type DATE.

In a report using this table the user wants to vies the sales between two periods. For this you define two parameters in your report namley p_from ( PERIOD FROM ) as string parameter and the value is taken in the format YYYYMM. And another p_to ( period to ) as string parameter and the value is taken as (YYYYMM). The parameters are referred to in the report with the syntax {?p_from} and
{?p_to}

To select only records between these periods in the record select expert you will have to write lot of code to convert the DOCDATE into string fro comparison.

Instead you use the SQL EXpression editor and create a field called REC_YYYYMM as
     to_char(docdate,'YYYYMM'). You will notice to_char is a general SQL expression. This when used in the record select expert or for printing is used with the syntax
{%REC_YYYYMM}

Now in the record select expert simply put it as
{%REC_YYYYMM} in {?p_from} to {?p_to}
Note the syntax. Ofcourse you can choose the SQL Expression field and parameters field from the Record Selection Edit Formula.


Trust the above clarifies.

Srini





0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now