Home » SQL & PL/SQL » SQL & PL/SQL » Date filter problem
Date filter problem [message #217744] Mon, 05 February 2007 04:12 Go to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

Hii all

I have a query that is not filter on "date" although i put
a condition of date in it
the query is (it doesnot matter the details)

 SELECT GIT.CODE ,GIT.GPA_CODE,
DECODE(NVL(LOCKMODE,'N'),'Y',MRSXXXXXX.NO,MRSXXXXXX.MRS_NO) MRS_NO,
NVL(SUM(G_ITEM.F_CONVERPA(GIT.CODE,G_ITEM.F_STORAGEPA(MRSITXXXX.GST_GBR_GCO_CODE,MRSITXXXX.GST_GBR_CODE,
         MRSITXXXX.GST_CODE,MRSITBAXX.BATCHNO,MRSITXXXX.GIT_CODE),GIT.GPA_CODE,
            DECODE(NVL(LOCKMODE,'N'),'Y',MRSITBAXX.QTY-NVL(MRSITBAXX.USEDQTY,0), -MRSITBAXX.QTY))),0) QTY
  FROM MRSXXXXXX,MRSITXXXX,MRSITBAXX,GITXXXXXX GIT
WHERE MRSXXXXXX.GBR_GCO_CODE    = '01'
   AND MRSXXXXXX.GBR_CODE         =  '01'
   AND MRSITXXXX.MRS_GBR_GCO_CODE = MRSXXXXXX.GBR_GCO_CODE
   AND MRSITXXXX.MRS_GBR_CODE     = MRSXXXXXX.GBR_CODE
   AND MRSITXXXX.MRS_NO           = MRSXXXXXX.NO
   AND MRSITXXXX.GIT_CODE           = GIT.CODE
  AND MRSITBAXX.MRSIT_MRS_GBR_GCO_CODE = MRSITXXXX.MRS_GBR_GCO_CODE
   AND MRSITBAXX.MRSIT_MRS_GBR_CODE     = MRSITXXXX.MRS_GBR_CODE
   AND MRSITBAXX.MRSIT_MRS_NO           = MRSITXXXX.MRS_NO
  AND MRSITBAXX.MRSIT_SER              = MRSITXXXX.SER
    AND ((NVL(LOCKMODE,'N')='Y' AND MRSITBAXX.QTY > NVL(MRSITBAXX.USEDQTY,0))     OR NVL(LOCKMODE,'N')='N')
and TRUNC(MRSXXXXXX.DATEG) ='02-JAN-2007'
  AND MRSXXXXXX.PROCESSED   = 'Y'
  AND MRSITBAXX.ACTIVE              = 'Y'
GROUP BY GIT.CODE,GIT.DESCL, GIT.GPA_CODE,DECODE(NVL(LOCKMODE,'N'),'Y',MRSXXXXXX.NO,MRSXXXXXX.MRS_NO)
/



when i remove part of the "selection" and the "group by"
the query filter on date
so that the query by like that
 SELECT GIT.CODE ,GIT.GPA_CODE
  FROM MRSXXXXXX,MRSITXXXX,MRSITBAXX,GITXXXXXX GIT
WHERE MRSXXXXXX.GBR_GCO_CODE    = '01'
   AND MRSXXXXXX.GBR_CODE         =  '01'
   AND MRSITXXXX.MRS_GBR_GCO_CODE = MRSXXXXXX.GBR_GCO_CODE
   AND MRSITXXXX.MRS_GBR_CODE     = MRSXXXXXX.GBR_CODE
   AND MRSITXXXX.MRS_NO           = MRSXXXXXX.NO
   AND MRSITXXXX.GIT_CODE           = GIT.CODE
  AND MRSITBAXX.MRSIT_MRS_GBR_GCO_CODE = MRSITXXXX.MRS_GBR_GCO_CODE
   AND MRSITBAXX.MRSIT_MRS_GBR_CODE     = MRSITXXXX.MRS_GBR_CODE
   AND MRSITBAXX.MRSIT_MRS_NO           = MRSITXXXX.MRS_NO
  AND MRSITBAXX.MRSIT_SER              = MRSITXXXX.SER
    AND ((NVL(LOCKMODE,'N')='Y' AND MRSITBAXX.QTY > NVL(MRSITBAXX.USEDQTY,0))     OR NVL(LOCKMODE,'N')='N')
and TRUNC(MRSXXXXXX.DATEG) ='02-JAN-2007'
  AND MRSXXXXXX.PROCESSED   = 'Y'
  AND MRSITBAXX.ACTIVE              = 'Y'
 



Thanks for everyone helped and helping me

[Updated on: Mon, 05 February 2007 04:23]

Report message to a moderator

Re: Date filter problem [message #217746 is a reply to message #217744] Mon, 05 February 2007 04:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
'02-jan-2007' is not a date. It is a string.

Quote:
AND ((NVL(LOCKMODE,'N')='Y' AND MRSITBAXX.QTY > NVL(MRSITBAXX.USEDQTY,0)) OR NVL(LOCKMODE,'
and trunc(mrsxxxxxx.dateg) ='02-jan-2007'

This does not compute
Re: Date filter problem [message #217748 is a reply to message #217744] Mon, 05 February 2007 04:24 Go to previous message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

ok it is right now
Previous Topic: [ORA-04091] - Mutating table... Very,.very strange behavior!!!
Next Topic: Cursor for loops
Goto Forum:
  


Current Time: Sat Dec 10 12:44:42 CST 2016

Total time taken to generate the page: 0.21223 seconds