Adding DB2 SQL statements in Record Selection

[antonx]

Hi,

I'm using Crystal Report 10 (will soon be upgrading to XI) and using DB2 tables.

Every month, I open Crystal reports and change the dates in the record selection criteria. I would like to stop manually changing the dates in Crystal and automate this report and then schedule it in CE. I do not want to have to enter date parameters.

Last month, the report ran with the following record selection:

{RPLC_T.CREATE_DT} <= Date (2007, 12, 27);

While I was trying to automate this report, I know that to obtain Date (2007, 12, 27), I can also run the following query:

SELECT MAX(A.CLNDR_DT)
FROM BP.DATE_DMN_T A,
(SELECT CURRENT DATE as CURRENT_DT, CLNDR_DT, FSCL_MONTH_END_DT,
CAST((SUBSTR(CHAR(FSCL_MONTH_END_DT - 1 MONTH) ,1,4) ||
SUBSTR(CHAR(FSCL_MONTH_END_DT - 1 MONTH) ,6,2)) AS INTEGER) AS PREV_FSCL_YR_MONTH_NUM
FROM BP.DATE_DMN_T
WHERE CLNDR_DT = CURRENT DATE) P
WHERE A.FSCL_YR_MONTH_NUM = P.PREV_FSCL_YR_MONTH_NUM;

Can I create a formula that contains the above SQL statement and then use the formula in the record selection criteria such as:

{RPLC_T.CREATE_DT} <= MAXDATE);

MAXDATE would of course would contain the SQL SELECT statement above.

I tried creating an SQL Expression with the above SQL SELECT (SELECT MAX(A.CLNDR_DT) ....) but it gives me an error:
Error in compiling SQL Expression : Query Engine Error: IBM....

What is the most efficient way to fix the record selection problem I'm having.

Thank you in advance.

 

[Madawc]

There are a lot of useful Date commands in Crystal, have you tried them? DateDiff and DatePart are particularly useful, as is currentdate.

I'd suggest you do a few test reports to see what they can do.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Not sure if this will help overall, but in CR 9 to 11, subqueries in SQL expressions will not work with an alias table in the summary, so try removing "A", as in:

(
SELECT MAX(CLNDR_DT)
FROM BP.DATE_DMN_T A,
(SELECT CURRENT DATE as CURRENT_DT, CLNDR_DT, FSCL_MONTH_END_DT,
CAST((SUBSTR(CHAR(FSCL_MONTH_END_DT - 1 MONTH) ,1,4) ||
SUBSTR(CHAR(FSCL_MONTH_END_DT - 1 MONTH) ,6,2)) AS INTEGER) AS PREV_FSCL_YR_MONTH_NUM
FROM BP.DATE_DMN_T
WHERE CLNDR_DT = CURRENT DATE) P
WHERE A.FSCL_YR_MONTH_NUM = P.PREV_FSCL_YR_MONTH_NUM;
)

They also have to be enclosed in parens. This still might not be sufficient to make the SQL expression work. Don't know what database you are using, but you probably have to add the appropriate punctuation around fields and maybe tables. If you get this part to work, you can set the SQL expression up in your record selection formula like this:

{RPLC_T.CREATE_DT} <= {%MAXDATE}

There are probably other ways of doing this within CR without using a SQL expression, but I can't follow exactly what this one is doing.

-LB

 

[antonx]

I'm using DB2 database.

You may be right. I cannot use subqueries in the SQL Expression. As you suggested, I removed the alias "A" and also enclosed the table names with double quotes or parenthesis and it was not successful. Still got the error message when compling the SQL expression:

Error in compiling SQL Expression : As unexpected token "(" was found following "". Expected tokens may include: ", FROM INTO".

The SQL Statement runs successfully in IBM Query but it does not run successfully in SQL Expression.

Looks like I will have to write an SQL statement and include the above SQL statement with other tables and create it in the Add Command window.

 

[lbass]

Plase paste the exact SQL expression you are trying into the thread--with punctuation. In the SQL expression, you MUST use parens around subqueries, but I'm wondering where you are entering those double quotes.

-LB

 

[antonx]

Using DB2, I put the double quotes around the qualifier which is "BP" and the table name which is "DATE_DMN_T".

I removed the double quotes only on the qualifier and then tried other combinations and still got the same error message when compiling the SQL expression:

Error in compiling SQL Expression :
Query Engine Error: '42601:[IBM][CLI Driver][DB2] SQL1099N The use of the reserved word "FOR" following "" is not valid. Expected tokens may include: ") UNION EXCEPT". SQLSTATE=42601

This SQL query runs successfully when using an IBM utility for running SQL queries.


(
SELECT MAX(CLNDR_DT)
FROM "BP"."DATE_DMN_T",

(SELECT CURRENT DATE as CURRENT_DT,
CLNDR_DT,
FSCL_MONTH_END_DT,
CAST((SUBSTR(CHAR(FSCL_MONTH_END_DT - 1 MONTH) ,1,4) ||
SUBSTR(CHAR(FSCL_MONTH_END_DT - 1 MONTH) ,6,2)) AS INTEGER)
AS PREV_FSCL_YR_MONTH_NUM
FROM "BP"."DATE_DMN_T"
WHERE CLNDR_DT = CURRENT DATE) P

WHERE FSCL_YR_MONTH_NUM = P.PREV_FSCL_YR_MONTH_NUM;
)

 

[lbass]

You shouldn't have deleted the alias "A" from everything. Try:

(
SELECT MAX("CLNDR_DT")
FROM "BP"."DATE_DMN_T" [red]A[/red], [leave it here]

(SELECT CURRENT DATE as CURRENT_DT,
"CLNDR_DT",
"FSCL_MONTH_END_DT",
CAST((SUBSTR(CHAR("FSCL_MONTH_END_DT" - 1 MONTH) ,1,4) ||
SUBSTR(CHAR("FSCL_MONTH_END_DT" - 1 MONTH) ,6,2)) AS INTEGER)
AS PREV_FSCL_YR_MONTH_NUM
FROM "BP"."DATE_DMN_T"
WHERE "CLNDR_DT" = CURRENT DATE) P

WHERE [red]A.[/red]"FSCL_YR_MONTH_NUM" = P.PREV_FSCL_YR_MONTH_NUM;
)

But you are only adding in punctuation on some of the fields--and I think you need it on all of them--unless these are some kind of custom functions? I tried to add these in, but what you are doing is unfamiliar to me, so I might have it wrong.

-LB