Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
Click Here
|
Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
|
Error using SubQuery |
Author |
Message |
|
Posted : 2008-02-26 23:41:12 |
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
|
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-02-27 08:52:38 |
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 |
|
|
|
|
Posted : 2008-02-27 09:10:19 |
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 |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-02-27 09:15:02 |
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') |
|
|
|
|
Posted : 2008-02-27 09:44:23 |
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:
SQL Server 2005 Books Online (September 2007)
Subquery Fundamentals
A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. In this example a subquery is used as a column expression named MaxUnitPrice in a SELECT statement.
SELECT Ord.SalesOrderID, Ord.OrderDate, (SELECT MAX(OrdDet.UnitPrice) FROM AdventureWorks.Sales.SalesOrderDetail AS OrdDet WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice FROM AdventureWorks.Sales.SalesOrderHeader AS Ord
A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.
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 |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2008-02-27 10:48:57 |
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'
|
|
|
|
|
Posted : 2008-02-27 12:41:30 |
Got the insert to work using join.
Thanx for your help.
Alecio |
|
|
|
|