Select statement in SQL Expression 1

[RustyAfro]

Oracle 8i
CR XI R2

Hi everyone,

I have read that adding a select statement that correlates to a field in the main SELECT statement is possible. I tried doing so and was told I had an Invalid Column. "BKB437_REV_CD_COV" is the external table in question. Any ideas?

(SELECT
c.Rank
FROM bkb.uwvw_cpt300_rowid c
WHERE
c.med_ctrl_dimsn_key = BKB437_REV_CD_COV.MED_CTRL_DIMSN_KEY
and c.cpt_svc_dimsn_key = BKB437_REV_CD_COV.CPT_SVC_DIMSN_KEY
and c.rank = 1
)

 

[RustyAfro]

LB, I was able to duplicate your workaround with the table being added twice to get the alias to work.

I was then able to get it to work without the dual table.

So the only two tables I have in Database Expert are below
Customer
Orders_1 (an alias of Orders)

This query ran successfully below. The trick was to remove the table name from max(`Order Date`). Since the field is contained inside the parenthesis around the sub select statement, it doesn't get confused as to which table you want the max on the field to occur (in this case `Orders`)

(Select max(`Order Date`) from `Orders`
where `Orders`.`Customer ID` = `Orders_1`.`Customer ID`)

Please let me know if you are able to duplicate these results. If you can duplicate, then I think is confirms 100% that aliases cannot be used in the subquery SELECT statement but should be used on the main query tables if the correlated subquery will be joined back onto the same table.

Joel

 

[lbass]

In XI, I get an error with this exact statement, with only Orders_1 and Customer tables in the report.

-LB

 

[RustyAfro]

Maybe without the `` around Order then?

(Select max(`Order Date`) from Orders
where Orders.`Customer ID` = `Orders_1`.`Customer ID`)

I am able to run it both ways with no errors in CR XI.

Here is a link to download the report .rpt file:

http://www.mediafire.com/?2eysjmy0mg1

Here are a few print screens:
Print Screen 1[/LINK]
[url=http://aycu36.webshots.com/image/35355/2000181742203835712_rs.jpg]Print Screen 2[/LINK]

 

[lbass]

In CR X, adding only Orders as a table and then renaming it Orders_1, the following SQL expression is accepted with no errors, but the application immediately crashes:

(Select max(`Order Date`) from Orders
where Orders.`Customer ID` = `Orders_1`.`Customer ID`)

However, this does work:

(Select max(`Order Date`) from Orders
where `Orders`.`Customer ID` = `Orders_1`.`Customer ID`)

...which surprises me because the 'from' table has no quotes around it.

But guess what? The following also works, which is the closest to the method I ordinarily used in 8.0. For this, I only added the Orders table to the report(no renaming as an alias):

(Select max(`Order Date`) from Orders A
where A.`Customer ID` = Orders.`Customer ID`)

...so I believe the solution is not to specify the table in the summary expression. In 8.0, I would have used:

(Select max(A.`Order Date`) from Orders A
where A.`Customer ID` = Orders.`Customer ID`)

Thanks, Rusty (*)--this exchange has been very productive!

-LB