Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to apply EXISTS here?

Author  Topic 

barnabeck
Posting Yak Master

236 Posts

Posted - 2013-09-26 : 05:02:21
I have this Query:
select WS_CONTRACTORCUSTOMER,
SALESLINE.SALESSTATUS,
SUM(CASE WHEN SALESLINE.SALESSTATUS =1 THEN REMAINSALESPHYSICAL ELSE REMAINSALESFINANCIAL END * SALESLINE.SALESPRICE) as total
from SALESLINE
left outer join SALESTABLE ON SALESLINE.SALESID = SALESTABLE.SALESID and SALESTABLE.DATAAREAID ='ES75'
left outer join INVENTTABLE on SALESLINE.ITEMID = INVENTTABLE.ITEMID and INVENTTABLE.DATAAREAID ='ES75'
where SALESLINE.DATAAREAID ='ES75' and
(SALESLINE.SALESSTATUS =1 OR SALESLINE.SALESSTATUS =2) and
(SALESLINE.SALESTYPE =3 OR SALESLINE.SALESTYPE = 4) and
(SalesTable.ReturnStatus = 3 OR ISNULL(SalesTable.ReturnStatus,0) = 0) and
WK_SalesVariant <> 5 and
(ItemGroupID LIKE 'I%' OR ItemGroupID = 'SV-CERT' OR ItemGroupID = 'SV-SERV') and
SALESLINE.SHIPPINGDATECONFIRMED <= GETDATE()
GROUP BY WS_CONTRACTORCUSTOMER, SALESLINE.SALESSTATUS
ORDER BY WS_CONTRACTORCUSTOMER, SALESLINE.SALESSTATUS


Now I need to substitute the reference-date GETDATE() with the date returned by this query:
select TOP 1 Invoicedate, COUNT (INVOICEDATE) as cnt 
from (select TOP 600 INVOICEDATE
from CUSTINVOICETRANS
where DATAAREAID = 'ES75' and Invoicedate <= '2013-07-29'
ORDER BY INVOICEDATE desc)a
GROUP BY INVOICEDATE
ORDER BY cnt desc


If I do it by just wrapping the query into brackets I get this error message: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.. I was trying that but don't succeed, cause I never used EXISTS in such a context.

Martin

barnabeck
Posting Yak Master

236 Posts

Posted - 2013-09-26 : 05:08:17
OK... my fault. I just saw that my inner query returns two columns...
Go to Top of Page
   

- Advertisement -