I having a trouble with the oracle sql query below, can some help me. I am trying to get a count of all JS records parts purchase in November 2006. The sub query is working find. I am getting an syntax error in the select Count query.
Select Count(*) as JS_Parts
From ITEM
Where ITEM IN
(Select CUSTOMERID,
CUSTOMERFIRSTNAME,
CUSTOMERLASTNAME,
INVOICEID,
INVOICEDATE,
ITEMID,
DELQUANTITY,
SOQUANTITY,
RETAILPRICE,
DISCOUNT,
EXTENDEDAMOUNT,
DESCRIPTION
From CUSTOMER, INVOICE, ITEM
Where CUSTOMER.CUSTOMERID = INVOICE.FK_CUSTOMERID and
INVOICE.INVOICEID = ITEM.FK_INVOICEID and
CUSTOMERID = 2250 and
to_char(INVOICEDATE,'MM') = '11' and to_char(INVOICEDATE,'YYYY') = '2006');
Loading
Jignesh TrivediPosted Mar 18, 2014, 2:36 AM
Try
Select Count(*) as JS_Parts
From ITEM
Where ITEM IN
(Select
ITEMID
From CUSTOMER, INVOICE, ITEM
Where CUSTOMER.CUSTOMERID = INVOICE.FK_CUSTOMERID and
INVOICE.INVOICEID = ITEM.FK_INVOICEID and
CUSTOMERID = 2250 and
to_char(INVOICEDATE,'MM') = '11' and to_char(INVOICEDATE,'YYYY') = '2006');
When ever you use IN with sub query, sub query must have only one column.
hope this will help you.