Hello,

When trying to use a subquery I'm getting the error: [QODBC] Expected lexical element not found: <identifier>

Th following instruction, is to insert one of multiple salesreceiptline items, where the values are hard coded and it works.

insert into SalesReceiptLine (SalesReceiptLineItemRefListID, SalesReceiptLineItemRefFullName, SalesReceiptLineDesc,
SalesReceiptLineRate, SalesReceiptLineSalesTaxCodeRefListID,
SalesReceiptLineSalesTaxCodeRefFullName, SalesReceiptLineClassRefListID,
SalesReceiptLineClassRefFullName, SalesReceiptLineQuantity, FQSaveToCache)
Values ('670000-1193756172', '1-002 A', 'Regular', 1.796, '20000-1193755957', 'Non', '70000-1193756180', 'RIO 1', 2599, 1)

The same instruction is executed below however subsituting the hard coded values by fields from Quickbook and oracle (those of oracle are hardcoded in this case)

insert into SalesReceiptLine (SalesReceiptLineItemRefListID, SalesReceiptLineItemRefFullName, SalesReceiptLineDesc, SalesReceiptLineRate,
SalesReceiptLineSalesTaxCodeRefListID, SalesReceiptLineSalesTaxCodeRefFullName, SalesReceiptLineClassRefListID, SalesReceiptLineClassRefFullName,
SalesReceiptLineQuantity, FQSaveToCache)
values ( select ListID as SalesReceiptLineItemRefListID, FullName as SalesReceiptLineItemRefFullName, Description as SalesReceiptLineDesc,
SalesPrice as SalesReceiptLineRate, SalesTaxCodeRefListID as SalesReceiptLineSalesTaxCodeRefListID, SalesTaxCodeRefFullName as SalesReceiptLineSalesTaxCodeRefFullName,
(select ListID from Class where FullName = 'RIO 1') as SalesReceiptLineClassRefListID, 'RIO 1' as SalesReceiptLineClassRefFullName, 2955 as SalesReceiptLineQuantity, 1 as FQSaveToCache from item where FullName = '1-002 A' )

The result of the execution of this instruction is the error mentioned above.

Executing only the select which includes the subquery has the same result.

select ListID as SalesReceiptLineItemRefListID, FullName as SalesReceiptLineItemRefFullName, Description as SalesReceiptLineDesc,
SalesPrice as SalesReceiptLineRate, SalesTaxCodeRefListID as SalesReceiptLineSalesTaxCodeRefListID, SalesTaxCodeRefFullName as SalesReceiptLineSalesTaxCodeRefFullName,
(select ListID from Class where FullName = 'RIO 1') as SalesReceiptLineClassRefListID, 'RIO 1' as SalesReceiptLineClassRefFullName, 2955 as SalesReceiptLineQuantity, 1 as FQSaveToCache
from item where FullName = '1-002 A'

The subquery, is according to rules, only one field is selected, it is between parentesis () and can be part of a select statement either as a field or in a where clause etc.

Please let me know what I'm doing wrong.

Regards,

Alecio

Your syntax is incorrect, you need to remove the "values" command and include a where clause when using select subqueries in an insert. Here's an example that works:-

INSERT INTO "InvoiceLine" ("CustomerRefListID", "RefNumber", "Memo", "InvoiceLineItemRefListID",
"InvoiceLineDesc", "InvoiceLineRate", "InvoiceLineAmount", "InvoiceLineSalesTaxCodeRefListID",
"FQSaveToCache")
Select "CustomerRefListID", {fn CONCAT('I', "RefNumber")} as "RefNumber",
{fn CONCAT('Sales Order ', "RefNumber")} as "Memo","SalesOrderLineItemRefListID",
"SalesOrderLineDesc", "SalesOrderLineRate", "SalesOrderLineAmount", "SalesOrderLineSalesTaxCodeRefListID",
"FQSaveToCache" from SalesOrderLine where "CustomerRefFullName" ='Lamb, Brad:Room Addition' and "RefNumber"='201'
and "SalesOrderLineSeqNo"=1

Thank you Tom for your quick response.

The insert is wrong however I sent the select with a subquery which is also giving me the error: [QODBC] Expected lexical element not found: <identifier>. I cannot do without the subquery, reason why I submitted this problem. I'm resubmitting the select with the subquery for your review.

select ListID as SalesReceiptLineItemRefListID, FullName as SalesReceiptLineItemRefFullName, Description as SalesReceiptLineDesc,
SalesPrice as SalesReceiptLineRate, SalesTaxCodeRefListID as SalesReceiptLineSalesTaxCodeRefListID, SalesTaxCodeRefFullName as SalesReceiptLineSalesTaxCodeRefFullName,
(select ListID from Class where FullName = 'RIO 1') as SalesReceiptLineClassRefListID, 'RIO 1' as SalesReceiptLineClassRefFullName, 2955 as SalesReceiptLineQuantity, 1 as FQSaveToCache from item where FullName = '1-002 A'

Once this issue is taken care of, I can proceed with the insert.

Regards,

Alecio

The curent supported syntax for nested sub-selects WITHIN SELECTS & "exists", is:-

SELECT * FROM EMPLOYEE
     WHERE NAME IN (SELECT NAME FROM EMPLOYEE)

SELECT OWNERFIRSTNAME, OWNERLASTNAME
     FROM ANTIQUEOWNERS
     WHERE EXISTS (SELECT * FROM ANTIQUES WHERE ITEM = 'Chair')

Ouch.

I won't be able to use this insert in the way that would have been best and contained.

There are several articles on the WEB and Microsoft has an article, which I'm just quoting the first part of:

This article can be found at http://msdn2.microsoft.com/en-us/library/ms189575.aspx

Will QODBC eventually have this functionality, as it is very handy at times.

The reason why I tried using this subquery is because I need to get data out of two separate tables, being Item and Class, which have no relation whatsoever. I cannot have two select statements following each other netiher, where in the first select I would get the Item data and in the second I would get the class info and other external data I would get from Oracle or any other DB, to populate the insert fields.

Any suggestion is welcome.

Thanks for your promptness in replying.

Alecio

There are two seperate issues here.

a) The use of sub-queries (SELECTs) within a INSERT statement.

This isn't a problem, an example is shown above.

b) The use of sub-queries (SELECTs) within a SELECT statement.

Only the WHERE format above is currently supported. I've already asked for sub-queries to be added to UPDATEs as there's a extremely good case in being able to update a table based on a value from a another table, for example:

UPDATE Invoice SET CustomFieldContract = (Select CustomFieldContract
from Customer where FullName='Data Access Worldwide')
where CustomerRefFullName='Data Access Worldwide'

As far as SELECT statements with sub-queries are concerned, for now, simply JOIN the tables. This query works:

SELECT salesreceipt.TxnID, salesreceipt.CustomerRefFullName,
salesreceipt.Refnumber, salesreceipt.DepositToAccountRefFullname,
salesreceipt.TotalAmount, DepositLine.DepositToAccountRefFullname
FROM {oj salesreceipt LEFT OUTER JOIN DepositLine
ON salesreceipt.TxnID = DepositLine.DepositLineTxnID}
Where salesreceipt.DepositToAccountRefFullname = 'Undeposited Funds'
and DepositLine.DepositToAccountRefFullname is NULL

as expected and this even works:

Select TxnID, RefNumber FROM salesreceipt where salesreceipt.TxnID =
(SELECT salesreceipt.TxnID
FROM {oj salesreceipt LEFT OUTER JOIN DepositLine
ON salesreceipt.TxnID = DepositLine.DepositLineTxnID}
Where salesreceipt.DepositToAccountRefFullname = 'Undeposited Funds'
and DepositLine.DepositToAccountRefFullname is NULL)

Wow!!! But this fails to delete the salesreceipt:

Delete FROM salesreceipt where salesreceipt.TxnID =
(SELECT salesreceipt.TxnID
FROM {oj salesreceipt LEFT OUTER JOIN DepositLine
ON salesreceipt.TxnID = DepositLine.DepositLineTxnID}
Where salesreceipt.DepositToAccountRefFullname = 'Undeposited Funds'
and DepositLine.DepositToAccountRefFullname is NULL)

Calling the TnxID directly instead of course works:

DELETE FROM salesreceipt where TxnID = 'F73-1197677537'

Got the insert to work using join.

Thanx for your help.

Alecio