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
)

 

[lbass]

You are referencing a table in the 'where' clause that does not appear in your 'from' clause.

Also note that in XI R1, subqueries like this resulted in errors. I'm not sure whether this was corrected in R2. You could achieve the same thing in a command object though, once you've corrected the query.

-LB

 

[RustyAfro]

Right, I agree that if this query was parsed independtly of anything else Oracle would return this error as the table does not exist in the FROM.

But when this sub query is in the SELECT statement of a main query that does have these tables the server does parse it (I tested it ok).

According to an article in 2004 on Ken's site, it was possible to run a scaler sub query in a SQL Expression that is correlated to the main query driving the report:

http://www.kenhamady.com/news0411.shtml

Do you know if this was "fixed" in XI so that we had the "benefit" of not being able to do this anymore?

It does kind of seem that CR XI is just throwing the Expression directly at the server now to see if it parsed and not inside the main query driving the report.

Thanks for your help,

Joel

 

[RustyAfro]

Still cannot get this to work.

I did find this site though:

http://www.cogniza.com/blog/?p=26

It talks about the Invalid Column error. Some people responded with the same problem as I. However, nothing seemed to work to get CR to pass the SQL Expression with the main query.

 

[RustyAfro]

I found a solution to the problem. It was as I suspected. CR was definitely not passing the SQL Expression to the server as part of SELECT statement of the main query.

I had to change the SQL Expression as follows to make it parse inside the SELECT of the main. The solution seemed to be

1. Not using aliases inside the subquery.
2. Wrapping double quotes "" around the table.fieldnames of the fields referenced in the main query.

The reason I think the double quotes are necessary is because the main query does have those tables aliased without the "BKB" part. This website:

http://www.cogniza.com/blog/?p=26

Helped with understanding when double quotes are necessary.

It should be noted though that wrapping the c.fieldname in double quotes (where c is the alias as in the original query above) did not work. I had to remove all aliases from the subquery to get it to parse.

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

 

[lbass]

As I said earlier, it currently does not work as far as I know. Please see thread766-1307013 for more detail.

-LB

 

[RustyAfro]

LB, I understood what you were saying. My post above was just explaining that I found what the problem was and a way to get around it in CRXI.

Removing the aliases was the solution and works with other tables not referenced in the main query. But if you want a correlated subquery that uses a same table from main that joins to main, not having an alias would be a problem.

 

[RustyAfro]

Actually if the main alias is changed to something else, then even using the same table would be ok ;-)

 

[lbass]

The case where I was trying to use a subquery that no longer works (works in 8.0) is creating a summary by setting up a group within the subquery--which does require an alias, as in:

(select max(A.`date`) from table A
where A.`App ID` = table.`App ID`)

I don't know of another way to do this within a SQL expression.

-LB

 

[RustyAfro]

Try changing the name "table" in table.`App ID` to a different alias in the Database Expert. For example, just change it to
"Table0". Then in your correlated subquery that used to work in 8.0, change the "A" in "from table A" to the real name of the "table" (no aliases). Remove the aliases in the where and max to the same table name.

Not being able to use aliases anymore is definitely a bug, but this should work as a workaround. Hope that helps!

 

[lbass]

Sorry, doesn't work, unless I'm missing something. Why don't you rewrite the expression in my last post to correspond to your suggestion.

-LB

 

[RustyAfro]

What server is this? I noticed single quotes, is it SQL Server?

(select max(A.`date`) from table A
where A.`App ID` = table.`App ID`)

to

(select max(owner.table.date)
from owner.table
where owner.table.'App ID' = 'table0'.'App ID')

Make sure you have right clicked the real table in Database Expert and changed it's alias to table0. This is so that the self join will work.

Notice I only put a quote (or use double quotes if Oracle) around table0. The quote around the table and fields is only necessary for me if I am referencing an aliased table from the main (or if the field has a space).

I have gotten this to work for me in XI whether it's a self table join like above or a separate table completely in the subquery.

 

[lbass]

This is using the Xtreme database (Access) and those aren't single quotes. I tried:

(select max(Orders.`Order Date`)
from Orders
where Orders.`Customer ID` = Orders_x.`Customer ID`)

...where Orders_x is the alias table created in the main report. The error message is that it cannot find Orders_x--which makes sense to me, since the SQL expression will try to directly access the database, where Orders_x does not exist. Also did not work with:

(select max(Orders.Order Date)
from Orders
where Orders.Customer ID = `Orders_x`.`Customer ID`)

If you can get this to work with the Xtreme database, I'd like to see how you do it.

-LB

 

[RustyAfro]

Strange, but alright, I'll see if I can give it a shot in the day or two. I am connecting an Oracle database.

Last thought, try below (I added ` around `Orders_x`)

(select max(Orders.`Order Date`)
from Orders
where Orders.`Customer ID` = `Orders_x`.`Customer ID`)

 

[smcnulty2000]

I just dredged through my notes on this and the following query works in Crystal 9 against an Oracle 9i database:

(select ASSUMED_COST 
from STOL1APP.eng_part_revision where part_no="PURCHASE_ORDER_LINE_COMP"."COMPONENT_PART" 
and sysdate>str_active_dt 
and NVL(obsolete_dt,SYSDATE)>=sysdate 
and rev_no=(select max(rev_no) 
from STOL1APP.eng_part_revision where part_no="PURCHASE_ORDER_LINE_COMP"."COMPONENT_PART" 
and sysdate>str_active_dt 
and NVL(obsolete_dt,sysdate)>=sysdate) )

Which means that I think you are on the right track but must be dealing with a bug as lbass suggests.

I think I'd put a max around the assumed_cost at this point. I also don't know whether the non-use of aliases mattered. We haven't used this in a while due to some security middleware preventing it.

Hope this helps the discussion.

 

[lbass]

It is the use of aliases that is the issue. For example, the following works:

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

...as long as both Orders and Customer are tables in the main report.

-LB

 

[lbass]

Okay, to my surprise, I found something that works in CR 10, but it requires adding an alias table to the main report. Using the Xtreme database, I added Orders twice, and then linked the two tables on Order_ID. Note that after creating and adding the SQL expression to the body of the report, I tested whether I could link just on Customer ID, e.g., and the report crashed, so I think you need to link on fields that return a unique row.

The SQL expression that worked was:

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

Thanks, Rusty, for hitting upon the idea of physically adding the alias table to the main report. Although this is not ideal, it is a reasonable workaround.

-LB

 

[RustyAfro]

No problem. It seems that corellated subqueries in CR10 and above is not an exact science. Perhaps in CR10 adding the table twice is necessary and in CRXI it a little more fixed (but definitely not ideal either). I had to fidget for hours with it to finally get it work.

As always, thanks for adding to the discussion! ;-)

 

[lbass]

Using the Xtreme database, how could you do this in XI (I don't have this right now) that improves upon the CR10 method? What would the expression look like?

-LB

 

[RustyAfro]

I gave it a try but I am getting a different error all together:

Database Connector Error: 'DAO Error Code: 0xbb8
Source: DAO.Database
Description: Reserved Error (-3025); There is no description

My company is still using Access 97 (I know, horrible), so that may be the issue. However, I can open the Xtreme mdb file in Access 97 and run this query below in Access with no problems:

SELECT `Orders0`.`Order ID` AS Expr1, `Orders0`.`Order Amount` AS Expr2, `Orders0`.`Customer ID` AS Expr3, `Orders0`.`Employee ID` AS Expr4, `Orders0`.`Order ID` AS Expr5, (select max(Orders.`Order Date`)
from Orders
where Orders.`Customer ID` = `Orders0`.`Customer ID`) AS Expr6
FROM Orders AS Orders0;

I'm trying to figure out the DAO error so I can try it in Crystal.