SQL Syntax problems

[amrnewman]

Hi

Am having problems with SQL syntax (see thread149-1010570 fro details). Expression is:

(SELECT AKA."MEDIA_CODE"
FROM
SALES_ITEM AKA
WHERE
AKA."SALES_DOCUMENT_NUM" = SALES_ITEM."SALES_DOCUMENT_NUM" and AKA."SALES_ITEM_NUM" = 1)

All the information I hvae regarding the datasource is:

Databse: Maginus
ODBC Oracle 8
CR8.5
Properties from Data Explorer data source is
Database DLL Name=pdsodbc.dll
Server Type=ODBC - CROR8V36
Server=CROR8V36
Datasource Type=SQL

When checking the expression I get the following error messages:

ODBC Error:[MERANT][ODBC Oracle 8 driver][Oracle8] ORA-00942: table of view does not exist

followed by

Error in compiling SQL Expression: Syntax error found here

and when I press OK the cursor appears afetr SELECT. Any suggestions as lbass has triled this and works for him but not for me!! Thanks for all your help LB.

Ade

 

[synapsevampire]

LB probably tested on SQL Server.

First verify that you are able to hit the table using:

SELECT count(*)
FROM
SALES_ITEM
WHERE
SALES_ITEM_NUM = 1

If so, then you may have to try different combinations of the syntax, also remember to check the Database->Show SQL Query to see what is being passed.

-k

 

[lbass]

That's a different error message from the one you mentioned in your earlier thread. Make sure you have added a field from the Sales_Item field to your report somewhere. Also, I was assuming that the fields in this expression are all coming from the Sales_Item table, is that correct?

-LB

 

[amrnewman]

THanks for all your help guys. Have got it ot work by trial end error: final expression was:

(SELECT SALES_ITEM_1."MEDIA_CODE"
FROM
MAGLIVE. SALES_ITEM SALES_ITEM_1
WHERE
SALES_ITEM_1."SALES_DOCUMENT_NUM" = SALES_ITEM."SALES_DOCUMENT_NUM"
AND SALES_ITEM_1."SALES_ITEM_NUM" = 1)


where MAGLIVE is the datasource.

THanks again, Ade

 

[lbass]

I'm so glad you got this to work. Essentially you substituted your own alias table name "Sales_Item_1" for my "AKA", but it looks like the key thing was adding the datasource prefix of "MAGLIVE." to the actual table name.

-LB