"Subquery returned more than 1 value. This is not permitted..." error

[Barno77]

Basically I have an app with 3 buttons that populate a Gridview and all but 1 button work and the one that doesn't work brings back this error.

any ideas?

[deric]

I believe it is an error in your SQL query... Please check your SQL statement for that particular button. If you'll encounter trouble with your SQL quries, please post it on the appropriate SQL forum..

[DrBunchman]

Yes, this is a SQL error. If you print the SQL here that's generated by the click of this button I'll take a look at it for you.

Dr B

[Barno77]

Yes, this is a SQL error. If you print the SQL here that's generated by the click of this button I'll take a look at it for you.

Dr B

here's the sql query:

   
SELECT 
SO2_SOEntryDetailLine.SalesOrderNumber, 
SO1_SOEntryHeader.Division, 
SO1_SOEntryHeader.CustomerNumber, 
SO1_SOEntryHeader.BillToName, 
CA_OPS_PICKSEQS.LastPickSeq AS [Pick Ticket], 
SO2_SOEntryDetailLine.ItemNumber, 
SO2_SOEntryDetailLine.WhseCode, 
SO2_SOEntryDetailLine.LineIndex, 
SO1_SOEntryHeader.OrderStatus, 
CA_APICK_QTYS.QtyOrdered, 
CA_APICK_QTYS.QtyAvailable + CA_APICK_QTYS.QtyLoadAvailable AS QtyAvailable, 
CA_APICK_QTYS.QtyPicked AS QtyShipped, 
ISNULL(CA_APICK_LOAD_QTYS.TicketRelease, 0) AS TicketRelease, 
CA_OPS_PICKSEQS.LastPickDateTime, 
CA_APICK_QTYS.PromiseDate, 
SO_90_UDF_Sales_Order.CB_UDF_SOH_OK, 
SO2_SOEntryDetailLine.Extension 
FROM 
CA_APICK_LOAD_QTYS 
RIGHT OUTER JOIN CA_APICK_QTYS ON CA_APICK_LOAD_QTYS.WhseCode = CA_APICK_QTYS.WhseCode AND CA_APICK_LOAD_QTYS.ItemNumber = CA_APICK_QTYS.ItemNumber AND CA_APICK_LOAD_QTYS.SalesOrderNumber = CA_APICK_QTYS.SalesOrderNumber 
FULL OUTER JOIN SO2_SOEntryDetailLine 
INNER JOIN SO1_SOEntryHeader ON SO2_SOEntryDetailLine.SalesOrderNumber = SO1_SOEntryHeader.SalesOrderNumber 
LEFT OUTER JOIN CA_OPS_PICKSEQS ON SO2_SOEntryDetailLine.WhseCode = CA_OPS_PICKSEQS.WhseCode AND SO2_SOEntryDetailLine.SalesOrderNumber = CA_OPS_PICKSEQS.SaleOrderNumber ON CA_APICK_QTYS.SalesOrderNumber = SO2_SOEntryDetailLine.SalesOrderNumber AND CA_APICK_QTYS.LineIndex = SO2_SOEntryDetailLine.LineIndex 
LEFT OUTER JOIN SO_90_UDF_Sales_Order ON CA_APICK_QTYS.SalesOrderNumber = SO_90_UDF_Sales_Order.SalesOrderNumber 
WHERE 
(SO2_SOEntryDetailLine.ItemNumber IS NOT NULL)

[DrBunchman]

I can't see an obvious problem - have you got a Query Analyzer of something like that which you can run the query in?

If so then run it in there and it should give you the line that's causing the error.

Dr B

[jhardman]

here's the sql query:

On a scale of 1 to 10, I would rate this ~8.5 of the "most complex SQL statements I've ever seen". I think the error is line 22 where you don't specify how SO2_SOEntryDeta ilLine joins. Was this generated by visual studio, or did you come up with it yourself? Exactly what does it pull up? Would it be OK to simplify it? for example, if you were OK with just inner joins, you could say:

      SELECT * FROM SO2_SOEntryDetailLine, SO1_SOEntryHeader,
      CA_OPS_PICKSEQS, CA_APICK_QTYS, CA_APICK_LOAD_QTYS, 
      SO_90_UDF_Sales_Order, SO2_SOEntryDetailLine
      WHERE CA_APICK_LOAD_QTYS.WhseCode = CA_APICK_QTYS.WhseCode 
      AND CA_APICK_LOAD_QTYS.ItemNumber = CA_APICK_QTYS.ItemNumber 
      AND CA_APICK_LOAD_QTYS.SalesOrderNumber = CA_APICK_QTYS.SalesOrderNumber
      AND SO2_SOEntryDetailLine.SalesOrderNumber = SO1_SOEntryHeader.SalesOrderNumber
      AND SO2_SOEntryDetailLine.WhseCode = CA_OPS_PICKSEQS.WhseCode 
      AND SO2_SOEntryDetailLine.SalesOrderNumber = CA_OPS_PICKSEQS.SaleOrderNumber 
      AND CA_APICK_QTYS.SalesOrderNumber = SO2_SOEntryDetailLine.SalesOrderNumber 
      AND CA_APICK_QTYS.LineIndex = SO2_SOEntryDetailLine.LineIndex
      AND CA_APICK_QTYS.SalesOrderNumber = SO_90_UDF_Sales_Order.SalesOrderNumber
      AND (SO2_SOEntryDetailLine.ItemNumber IS NOT NULL)