CRXI,Oracle, after adding anSql Expression the rp will not complete. 1

[AndersonCJA]

Hi,

I have a simple report and want to add a column using an sql expression. Alone the report runs fine, and alone the sql expression works fine, when the sql expression is added to the report, the report runs and runs and runs and runs I finally have to end it abnormaly. I can use any help or advice.

Thank you in advance so much for taking the time to help

SQL EXPRESSION =

(SELECT
LTRIM(TO_CHAR(TABLE6.AMTDUE,'$999,999,999.99')) AMTDUE
FROM
TABLE5 TABLE5,
TABLE6 TABLE6
WHERE
TABLE1."CUST#" = TABLE5."CUST#" AND
TABLE6.CUST# = TABLE5.CUST# AND
TABLE6.SUBCUST# = TABLE5.SUBCUST# AND
TABLE5.DCD = 'AE'' AND
TABLE5. DTYP = 'IO' AND
TABLE6.DATE =
(SELECT MAX(Z.DATE)
FROM TABLE6 Z
WHERE Z.CUST# =TABLE6.CUST# AND
Z.SUBCUST# = TABLE6.CUST#))

 

[hilfy]

Remember that the SQL statement in the SQL Expression gets called for EVERY RECORD in the query! I don't see where this is linking to anything in your data, either.

In general it's not a good practice to put a whole SQL statement in a SQL Expression. If you want this to run for every record returned in the report based on data from that record, I would move your SQL to a Stored Function and then call your function in the SQL Expression. I use this particular technique frequently and it works well. For example, to get a user name from a key we use:

zcUtils.GetUserLNameFirst("EMPLOYEE.EMPLOYEE_KEY")

where Employee is a table that's part of the report.

-Dell



A computer only does what you actually told it to do - not what you thought you told it to do.

 

[AndersonCJA]

Hi,

Thank you for the reply. I did intend for the sql expression to bring me the due amt for each cust# in the report. My attempt to link the sql expression to the report was by table1.cust# and Table5.cust#. table1.cust# is in the main report. I did not realize that using an sql expresssion this way, was not a good thing to do. Unfortunately, I have no authority or experience in creating a stored procedure like you speak of. Using a subreport also causes the report to drag.

I do appreciate your helpful feedback.