SQL Expression field in Crystal 10.0

[area4486]

Hi all, if anyone can help with this one, I will really appreciate it:
I'm using Crystal 10.0 and need to add a sql expression field to my report, the expression is like this:

SELECT "SMSPHdssc4z0"."smsdss"."pract_dim"."pract_rpt_name"
FROM "SMSPHdssc4z0"."smsdss"."pract_dim" A
WHERE "SMSPHdssc4z0"."smsdss"."pms_vst_fct"."prim_pract_no_key"=A."pract_key"

where:
"SMSPHdssc4z0" is the database
"smsdss" is the owner, and
"pract_dim" is a table name as well as "pms_vst_fct"

When I try to save the field expression, I get error:
"Error in compiling expression: Failed to open a row se. Details: ADO error code 0x80040e14. Source: Microsoft OLE DB provider for SQL server. Description: Incorrect syntax near the key-word 'SELECT'. SQL state:42000. Native error 156"

Any ideas?
Thanks

 

[CrystalizeCanada]

Hi,

First observation:
If A is the alias for the Pract_dim table then the alias should be used in the SELECT line.
Shouldn't it be SELECT A."pract_rpt_name" not what you have.

Second observation:
You mention the second table in the WHERE clause but there is no mention of it in the FROM clause

Hope this helps

Gordon BOCP
Crystalize

 

[area4486]

Thanks for your response Gordon.
I tried this:
SELECT A."pract_rpt_name"
FROM "SMSPHdssc4z0"."smsdss"."pract_dim" A, "SMSPHdssc4z0"."smsdss"."pms_vst_fct" B
WHERE B."prim_pract_no_key"=A."pract_key"

Still got the same error.

What I tried after that was ljoining the same table pract_dim twice, so I could do a second link on "SMSPHdssc4z0"."smsdss"."pract_dim" and "SMSPHdssc4z0"."smsdss"."pms_vst_fct" through their
."prim_pract_no_key" and "pract_key".

I don't know if I am confusing you. But still I would like to know why the sql expression is wrong.

Thanks.

 

[lbass]

Try:

(
SELECT "pract_rpt_name"
FROM "SMSPHdssc4z0"."smsdss"."pract_dim" A, "SMSPHdssc4z0"."smsdss"."pms_vst_fct" B
WHERE B."prim_pract_no_key"=A."pract_key"
)

This would only work if there can be only one value that meets the criterion. In versions 9 - XI, you cannot specify the alias in the select statement. Note that you must have both tables in the main report and the entire expression must be enclosed in parens.

-LB