Home » SQL & PL/SQL » SQL & PL/SQL » HOW TO CHANGE SQL QUERY TO GET SPECIFIC RESULT
HOW TO CHANGE SQL QUERY TO GET SPECIFIC RESULT [message #317081] Tue, 29 April 2008 08:04 Go to next message
ambs
Messages: 1
Registered: April 2008
Location: PAKISTAN
Junior Member
HAVE FOLLOWING QUERY AND ITS FUNCTIONING BUT I WANT ALL THOSE RECORD SHOULD BE DISPLADE WICH HAVE NO TRANSACTION IN SALES ANS SALES RETURN WHILE RETREVING DATA FROM DATABASE.
I TRIED TO USE + OPERATOR BUT I DON'T KNOW HOW TO CHANGE THE QUERY TO GET THE RESULT

MAIN TABLE IS BEING USED TO FETCH RECORD
SALES_HEADER,SALES_DETAIL
SALES_HEADERR,SALES_DETAILR
STOCK_REG



AND THE QUERY IS


select s.item_code,S.DESCR,s.GRAND_TOTAL,
sum(decode(d.dayno,01,NVL(S.QTY,0),0)) DAY1,
sum(decode(d.dayno,02,NVL(S.QTY,0),0)) DAY2,
sum(decode(d.dayno,03,NVL(S.QTY,0),0)) DAY3,
sum(decode(d.dayno,04,NVL(S.QTY,0),0)) DAY4,
sum(decode(d.dayno,05,NVL(S.QTY,0),0)) DAY5,
sum(decode(d.dayno,06,NVL(S.QTY,0),0)) DAY6,
sum(decode(d.dayno,07,NVL(S.QTY,0),0)) DAY7,
sum(decode(d.dayno,08,NVL(S.QTY,0),0)) DAY8,
sum(decode(d.dayno,09,NVL(S.QTY,0),0)) DAY9,
sum(decode(d.dayno,10,NVL(S.QTY,0),0)) DAY10,
sum(decode(d.dayno,11,NVL(S.QTY,0),0)) DAY11,
sum(decode(d.dayno,12,NVL(S.QTY,0),0)) DAY12,
sum(decode(d.dayno,13,NVL(S.QTY,0),0)) DAY13,
sum(decode(d.dayno,14,NVL(S.QTY,0),0)) DAY14,
sum(decode(d.dayno,15,NVL(S.QTY,0),0)) DAY15,
sum(decode(d.dayno,16,NVL(S.QTY,0),0)) DAY16,
sum(decode(d.dayno,17,NVL(S.QTY,0),0)) DAY17,
sum(decode(d.dayno,18,NVL(S.QTY,0),0)) DAY18,
sum(decode(d.dayno,19,NVL(S.QTY,0),0)) DAY19,
sum(decode(d.dayno,20,NVL(S.QTY,0),0)) DAY20,
sum(decode(d.dayno,21,NVL(S.QTY,0),0)) DAY21,
sum(decode(d.dayno,22,NVL(S.QTY,0),0)) DAY22,
sum(decode(d.dayno,23,NVL(S.QTY,0),0)) DAY23,
sum(decode(d.dayno,24,NVL(S.QTY,0),0)) DAY24,
sum(decode(d.dayno,25,NVL(S.QTY,0),0)) DAY25,
sum(decode(d.dayno,26,NVL(S.QTY,0),0)) DAY26,
sum(decode(d.dayno,27,NVL(S.QTY,0),0)) DAY27,
sum(decode(d.dayno,28,NVL(S.QTY,0),0)) DAY28,
sum(decode(d.dayno,29,NVL(S.QTY,0),0)) DAY29,
sum(decode(d.dayno,30,NVL(S.QTY,0),0)) DAY30,
sum(decode(d.dayno,31,NVL(S.QTY,0),0)) DAY31,
sum(S.qty) TU

FROM
(select nvl(sales_detail.qty+sales_detail.bonus,0) as qty,
sales_detail.item_code AS ITEM_CODE,
stock_reg.descr AS descr,
s_date as sale_date,stock_reg.GRAND_TOTAL AS GRAND_TOTAL FROM
SALES_DETAIL,SALES_HEADER,STOCK_REG
WHERE SALES_DETAIL.S_ID = SALES_HEADER.S_ID AND
SALES_DETAIL.ITEM_CODE = STOCK_REG.ITEM_CODE(+) AND
STOCK_REG.COMCODE='C1'
UNION all
select -1*nvl(sales_detailR.qty+sales_detailR.bonus,0) as qty,
sales_detailR.item_code AS ITEM_CODE,
stock_reg.descr AS descr,
RETURN_date as sale_date,stock_reg.GRAND_TOTAL AS GRAND_TOTAL FROM
SALES_DETAILR,SALES_HEADERR,STOCK_REG
WHERE SALES_DETAILR.SR_ID = SALES_HEADERR.SR_ID AND
SALES_DETAILR.ITEM_CODE = STOCK_REG.ITEM_CODE(+) AND
STOCK_REG.COMCODE='C1') S,
(select level as dayno
from dual
connect by level <= 31) d
where d.dayno = to_char(s.sALE_date, 'DD') AND TO_DATE(S.SALE_DATE) BETWEEN '&FROMDATE' AND '&TODATE'
GROUP BY S.ITEM_CODE,S.DESCR,S.GRAND_TOTAL
Re: HOW TO CHANGE SQL QUERY TO GET SPECIFIC RESULT [message #317086 is a reply to message #317081] Tue, 29 April 2008 08:20 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
ambs wrote on Tue, 29 April 2008 09:04
TO_DATE(S.SALE_DATE) BETWEEN '&FROMDATE' AND '&TODATE'



Error 1: you do not use a TO_DATE on a DATE column
Error 2: If it is a VARCHAR2, then you left out the format mask.
Error 3: You cannot compare a DATE column to a character string.

[edit] fixed typo.

[Updated on: Tue, 29 April 2008 08:21]

Report message to a moderator

Re: HOW TO CHANGE SQL QUERY TO GET SPECIFIC RESULT [message #317087 is a reply to message #317081] Tue, 29 April 2008 08:25 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Error 1: You posted in UPPER CASE
Error 2: You didn't format
Error 3: You posted in Expert forum

This is NOT an expert question.

It is clearly stated in the forum description: "Newbies should not post to this forum!"
Expert notion is defined in the sticky: Not an EXPERT? Post in the NEWBIES forum, NOT here
Rules are described in: OraFAQ Forum Guide
Read them, follow them including what is said about formatting.

Regards
Michel
Previous Topic: please check the query?
Next Topic: Grouping by a Date Range
Goto Forum:
  


Current Time: Wed Dec 07 07:00:17 CST 2016

Total time taken to generate the page: 0.10911 seconds