SQL Expression Editor problems

Posted on 2003-03-28
Medium Priority
Last Modified: 2008-02-01
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.

Question by:cr_db
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

mnye earned 100 total points
ID: 8226232
it runs on the native database launguage, so just stay within the parameters of PL/SQL and it will work fine.


Assisted Solution

srini_jg earned 100 total points
ID: 8243738

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

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

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.



Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question