Home » SQL & PL/SQL » SQL & PL/SQL » help needed on query (oracle 9i,win xp)
help needed on query [message #326938] Fri, 13 June 2008 02:39 Go to next message
sharathmpatil
Messages: 38
Registered: February 2008
Location: Bangalore
Member

Hi,
I have a query written in report builder.The end user will have 2 radio buttons in the front end ,like for selection of customers he can select all or selected customers.When he selects the selected customers radio button the query is not retrieving all the recirds related to it.How I can acheieve this?


select uom.code uom_code,gan3.lot_no lot_no,
imas.code||' '||imas.rev_no item_code, imas.name item_name,pref.pref||' / '||gan3.doc_no gan_no,gan3.doc_date gan_date,
gan3.qty gan_qty, insp.sample_qty sample_qty, insp.acc_qty acc_qty, insp.qty insp_qty, --insp.qty - insp.acc_qty rej_qty
sample.code sample_code, ins.pref||'/'||insp.doc_no insp_doc, insp.doc_date insp_date, reason.code
reason_code, reason.name reason_name, supp.code supp_code, supp.name supp_name, insp5.qty rej_qty

from uom, imas,insp,gan3,sample,dcode reason, dcode pref, gl supp,gan2,gan,dcode ins, insp5

where uom.sl_no = imas.unit_sl and
supp.sl_no = gan.party_sl and
gan3.gan2_sl = gan2.sl_no and
gan2.gan_sl = gan.sl_no and
pref.sl_no = gan3.type_sl and
insp.type_sl = ins.sl_no and
imas.sl_no = insp.item_sl and
gan3.sl_no = insp.gan3_sl and
sample.sl_no = imas.sample_sl and
insp.co_code = :co_code and
insp.sl_no=insp5.insp_sl(+) and
insp5.reason_sl=reason.sl_no(+) and
insp.doc_date between to_date(:from_date) and to_date(:to_date) and
(lower(:parties) <> 'all' and gan.party_sl in (select sl_no from allsel where user_no = :user_no and temp_name = 'parties' )or lower(:parties) = 'all') and
(lower(:items)<> 'all' and insp.item_sl in (select sl_no from allsel where user_no = :user_no and temp_name = 'items' )or lower(:items) = 'all')
order by supp_code, item_code, insp_doc,insp_date


Thanks in advance


Re: help needed on query [message #326941 is a reply to message #326938] Fri, 13 June 2008 02:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do :parties and :items always contain a value? i.e. are they never NULL?

Also, add an extra set of parentheses like this:
and  (  (   lower(:parties) <> 'all' 
        and gan.party_sl in (select sl_no 
                             from   allsel 
                             where  user_no = :user_no 
                             and    temp_name = 'parties' 
                            )
        )
     or lower(:parties) = 'all'
     ) 
and  ....
Re: help needed on query [message #326942 is a reply to message #326941] Fri, 13 June 2008 02:48 Go to previous messageGo to next message
sharathmpatil
Messages: 38
Registered: February 2008
Location: Bangalore
Member

hi frank,
yes, they are never null.

Regards.
Re: help needed on query [message #326944 is a reply to message #326938] Fri, 13 June 2008 02:54 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
This doesn't make any sense.

Quote:

...
insp.sl_no=insp5.insp_sl(+) and
insp5.reason_sl=reason.sl_no(+) and
insp.doc_date between to_date(:from_date) and to_date(:to_date) and
...

See the explain plan for this query you will understand what I mean.

check this link.

http://www.orafaq.com/node/855

Regards

Raj

P.S : From next time please format your post, follow the guidelines and post the query with the sample data and expected output. Last but not least post your oracle version with 4 decimals.
Re: help needed on query [message #327024 is a reply to message #326938] Fri, 13 June 2008 08:07 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
sharathmpatil wrote on Fri, 13 June 2008 03:39

insp.doc_date between to_date(:from_date) and to_date(:to_date) and



Also, this is not how to use TO_DATE properly. Where is the format string?
Previous Topic: SQL Output Formatting
Next Topic: Tablespace monitoring (merged)
Goto Forum:
  


Current Time: Sun Dec 04 23:01:10 CST 2016

Total time taken to generate the page: 0.13921 seconds