Subquery in SQL expression

[cherokee10]

I have Crystal 8.5.

I am trying to add a subquery in a SQL Expression, can I?

This is what I've typed in my SQL Expression Editor.

SELECT * FROM VENDOR_RATE_OVERALL VENDOR_RATE_OVERALL WHERE VENDOR = '79148'

But I get syntax error right after the "FROM".

Carlos

 

[MSBrady]

I'm no SQL guru but try removing the 2nd VENDOR_RATE_OVERALL or adding 'as' to alias it

 

[Turkbear]

Hi,
Where is the subquery? What you posted ( except for the extra table name reference) is a 'plain' Sql query..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cherokee10]

Hi again...

The questions is if I can add a subquery in a SQL Expression Editor, to obtain a %mysqlexpression. Where
%mysqlexpression is SELECT * FROM VENDOR_RATE_OVERALL WHERE VENDOR = '79148'.

I want my SQL query show me something like this:

SELECT
AM_RECORD."AUDIT_STATUS", AM_RECORD."SUPPLIER", AM_RECORD."DATE_MODIFIED",
FROM
"QA"."AM_RECORD" AM_RECORD
WHERE
AM_RECORD."DATE_MODIFIED" >= {ts '2006-02-22 10:49:00.00'} AND
AM_RECORD."DATE_MODIFIED" < {ts '2006-02-28 14:51:01.00'} AND
(SELECT * FROM VENDOR_RATE_OVERALL WHERE VENDOR = '79148')
ORDER BY
AM_RECORD."SUPPLIER" ASC


Is it possible to do this with the SQL Expression editor?

Thanks again.

Carlos

 

[synapsevampire]

A SQL Expression is NOT going to return numerous values, that's not how SQL works, much less Crystal.

I suggest that you speak to your dba or read a bit to learn more about SQL.

Successful posts tend to include:

Database/connectivity
Example data
Expected output

Asking about SQL Expressions should be secondary to explaining the requirements.

-k

 

[cherokee10]

I only want to check if the a vendor exists in VENDOR_RATE_OVERALL table. I know that the syntax isn't perfect now... but again, my question is this,

IS IT POSSIBLE TO WRITE A SUBQUERY IN THE SQL EXPRESSION EDITOR YES OR NO? [/color red]

Thanks again.

Carlos

 

[lbass]

You could create a SQL expression of this form:

(select max(A.`VENDOR`) from VENDOR_RATE_OVERALL A where
A.`VENDOR` = '79148')

Let's say you called this {%vdr}. Then in the record selection formula, you could use:

{%vdr} <> ""

This would only return records if that vendor was present in the table. You can't use a SQL expression to return multiple fields from the table based on the presence of that vendor though.

-LB

 

[synapsevampire]

YES

-k

 

[cherokee10]

Thanks for your help, it is appreciated.

Carlos