Link to home
Start Free TrialLog in
Avatar of JDCam
JDCam

asked on

Crystal XI, SQL Expression linking

Experts,

I have a rather simple SQL expression that is giving me problems,

(
Select MIN(RCPT_LINE_NUM)
FROM e_RCPT_D5
WHERE RCPT_LEV1 = "E_RCPT_D5"."RCPT_LEV1"
AND COMP_CODE = "E_RCPT_H"."COMP_CODE"
AND Rcpt_Num = "E_RCPT_H"."RCPT_NUM"
)

The query runs, but the results seem to ignore the 1st condition in the where clause. THe 2 additional conditions (2nd table) are followed.

The field E_RCPT_D5"."RCPT_LEV1 should correspond to the row in the report
Avatar of JDCam
JDCam

ASKER

Sorry... I am aware that the link is referencing itself

FROM e_RCPT_D5
WHERE RCPT_LEV1 = "E_RCPT_D5"."RCPT_LEV1"

If this is all I have to work with, how Do I get past it?
Avatar of Mike McCracken
Shouldn't you be referencing fields in the report?

mlmcc
Avatar of JDCam

ASKER

That is the field in the report.
Sql expression is against the primary table.
What database are you using? If you're using Oracle then you probably won't be able to use a SQL Expression.  You can't correlate data in the report to a full SELECT statement in a SQL Expression with Oracle (or at least I've never been able to with any driver - most of the time, an error message is generated).

You might also try connecting to the database differently, such as ODBC vs. OLE/DB or vs. Native Driver - how you connect to the database dictates what you can do with a SQL Expression field.
CR just adds the SQL Expression as a sub-query in the main query.  I think you just need to make CR (or the db) understand that one of those RCPT_LEV1 references is to a column in the main query.

 For example, if the combined query was something like

SELECT <some columns>,
(
Select MIN(RCPT_LINE_NUM)
FROM e_RCPT_D5
WHERE RCPT_LEV1 = "E_RCPT_D5"."RCPT_LEV1"
AND COMP_CODE = "E_RCPT_H"."COMP_CODE"
AND Rcpt_Num = "E_RCPT_H"."RCPT_NUM"
)
FROM e_RCPT_D5


 Both RCPT_LEV1 and "E_RCPT_D5"."RCPT_LEV1" in the sub-query are going to default to referencing e_RCPT_D5 in the sub-query, so it ends up comparing the column to itself.

 You could try adding an alias for e_RCPT_D5 to the SQL Expression and using that for one of the references to RCPT_LEV1.  For the other reference, select that column in the field explorer window and double-click it to add it.  I've had times in the past where that seemed to help get the column pulled from the right place.

 If that doesn't work, you may have to add an alias to the table in the datasource for the report instead.  Under Database > "Database Expert", you can change a table name, which changes the alias that CR uses for that table.  Then if you use that alias for one of the references in your SQL Expression, the db will have to get it from the table in the main query, not the sub-query.

 James
@James That's not how it works.  When you see RCPT_LEV1 = "E_RCPT_D5"."RCPT_LEV1" that specifically means the first half is from the SQL Expression table and the second is from the field tree.  The way he's written it looks correct for a few different database engines.

@jay - for the report fields, did you type them that way or did you double-click or drag them down from the field tree?  Depending on the database, the syntax will be different, so it's best practice to bring them directly down from the tree.  If you didn't, please delete them and bring them down directly.
Avatar of JDCam

ASKER

Database is Oracle 9i
Fields were added direct from the tree

A band-aid fix is ugly but working. I added a second table to the body, that is not needed but makes an easy = join for the key columns. In the SQL Exp, I then changed the second part of the refernce to the reference to point to the 2nd table.  Working, but kind of clunky.

I am going to try the 'Alias' idea. Add the primary table a second time under an alias and reference that in the query.

Will report results
ASKER CERTIFIED SOLUTION
Avatar of Kurt Reinhardt
Kurt Reinhardt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of JDCam

ASKER

Agreed, the Alias is returning the same error. My band-aid with a second table continues to work. I will stay with that.
Avatar of JDCam

ASKER

Thanks