SQL Expression with SubQuery

[dellguy]

I'm using CR XI on Oracle 10g. I can not get a SQL Expression to work. Are subqueies (select within a select) allowed in SQL Expressions?

 

[lbass]

You can create subqueries using the SQL expression editor and use subqueries within subqueries--but the SQL expression only returns one value (although this could be one value per group), not a set of values. Note also that subqueries (even if this is the entire expression) must be enclosed in parens.

In CR XI, note that you cannot reference the table within the summary (corrected in CR 2008, I think), so your subquery might look like this:

(
select sum(`amount`)
from table A
where A.`ID` = table.`ID`
)

This would return the sum of amount at the ID group level.

-LB

 

[dellguy]

This is the actual SQL Expression that is not working. I get a ORA-00904 "ARINVOICE"."INVOICE_DATE": Invalid identifier


(
SELECT "HIST_ARINVT_ELEMENTS"."STD_COST"
FROM "HIST_ARINVT_ELEMENTS"
WHERE "HIST_ARINVT_ELEMENTS"."ID" =
(
(SELECT MAX("HIST_ARINVT_ELEMENTS"."ID")
FROM "HIST_ARINVT_ELEMENTS"
WHERE "HIST_ARINVT_ELEMENTS"."ARINVT_ID" = ARINVT_ELEMENTS.ARINVT_ID
AND "HIST_ARINVT_ELEMENTS"."ELEMENTS_ID" = ARINVT_ELEMENTS.ELEMENTS_ID
AND "HIST_ARINVT_ELEMENTS"."ARCHIVE_DATE" < "ARINVOICE"."INVOICE_DATE")
)
)

 

[lbass]

Try the following. I added a link between the A and C tables (not sure whether this is the right linking though), removed the table reference in the summary and made a few other little changes.

(
SELECT "HIST_ARINVT_ELEMENTS"."STD_COST"
FROM "HIST_ARINVT_ELEMENTS" A
WHERE A."ID" =
(
SELECT MAX("ID")
FROM "HIST_ARINVT_ELEMENTS" A, "ARINVT_ELEMENTS" B, "ARINVOICE" C
WHERE A."ARINVT_ID" = B."ARINVT_ID" AND
A."ARINVT_ID" = C."ARINVT_ID" and
A."ELEMENTS_ID" = B."ELEMENTS_ID" AND
A."ARCHIVE_DATE" < C."INVOICE_DATE"
)
)

-LB

 

[dellguy]

@LB: Tried your code and now I get the same error on "HIST_ARINVT_ELEMENTS"."STD_COST".

So I changed it to A."STD_COST" and then it complained about C."ARINVT_ID" (same error).

???

 

[lbass]

The tables that you reference need to be in your main report somewhere--is that the case? (Even though the SQL expression will go directly to the database to get the data).

-LB