Home » SQL & PL/SQL » SQL & PL/SQL » select query
select query [message #304122] Tue, 04 March 2008 03:20 Go to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member

select a.brnch_dt_of_brnch_opn from
r_m_brnch a,r_m_t_brnch_transfer b
where a.brnch_bk_cd IN
(select c.s1 from sample c,r_m_t_brnch_transfer b where c.bname=b.brnch_name and rownum=1) and
a.brnch_brnch_no IN
(select c.s2 from sample c,r_m_t_brnch_TRANSFER B where c.bname=b.brnch_name and rownum=1) and
a.brnch_name=b.brnch_name and b.effective_date=a.brnch_dt_of_brnch_opn and rownum=1;

[/tab]


subqueries are working fine but whole query it is not giving any result , as no rows.for only one record iam checking which i have inserted in the sample and sample3 tables .help me

[Updated on: Tue, 04 March 2008 03:24]

Report message to a moderator

Re: select query [message #304125 is a reply to message #304122] Tue, 04 March 2008 03:33 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
please post the run result of your subquery and your query with test rows of the tables(If you match all these with your joining conditions you probably get your answer yourself).

in subquery,you are using rownum=1,why?

regards,

Re: select query [message #304127 is a reply to message #304125] Tue, 04 March 2008 03:43 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
SQL> select * from sample;

S1 S2 BNAME
--- ---- -------------------------
053 0043 GORAYA


if we execute whole query the result is:

no rows selected

if each subquery is executed then result , first sub q showing 053 and second sub q showing 0043 but from r_m_brnch master date of opening is not selecting.

-rownum is for accessing only one result from subquery result if multiple results come.

[Updated on: Tue, 04 March 2008 03:44]

Report message to a moderator

Re: select query [message #304131 is a reply to message #304122] Tue, 04 March 2008 03:59 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
now check

1.053 exists in a.brnch_bk_cd.
2.0043 exists in a.brnch_brnch_no.
and also
3.a.brnch_name=b.brnch_name and b.effective_date=a.brnch_dt_of_brnch_opn condition for above 1 and 2.

as i think,one or more than one from above is not true.


regards,

Re: select query [message #304136 is a reply to message #304131] Tue, 04 March 2008 04:15 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
1,2 are correct and existing in r_m_brnch master and 3 condition i have posted wrongly

3.b.effective_date>a.brnch_dt_of_brnch_opn

eventhen also same result no rows...
Re: select query [message #304137 is a reply to message #304122] Tue, 04 March 2008 04:18 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
if we execute by replacing subqueries with the values

SQL> select a.brnch_dt_of_brnch_opn from
r_m_brnch a,r_m_t_brnch_transfer b where
a.brnch_bk_cd='053' and a.brnch_brnch_no='0043' and a.brnch_name=b.brnch_name and b.effective_date>a.brnch_d
t_of_brnch_opn;

BRNCH_DT_
---------
30-DEC-98


[Updated on: Tue, 04 March 2008 04:19]

Report message to a moderator

Re: select query [message #304140 is a reply to message #304122] Tue, 04 March 2008 04:30 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
so, you have got the solution.

further i hope that you will post always in prescribed format.

regards,
Re: select query [message #304141 is a reply to message #304122] Tue, 04 March 2008 04:36 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
iam getting in that way by replacing the values but i need to do for all the records .so it should be generalised, that iam still getting the result as no rows.

sorry for not posting in prescribed format.I will post for the next time.
Re: select query [message #304143 is a reply to message #304122] Tue, 04 March 2008 04:44 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
Quote:
iam getting in that way by replacing the values but i need to do for all the records .so it should be generalised, that iam still getting the result as no rows.


first of all think what will be your generalised solution.
if you do so you have solution yourself.

oherwise

Quote:
post all tables with test cases with your desire output.


regards,

[Updated on: Tue, 04 March 2008 05:08]

Report message to a moderator

Re: select query [message #304145 is a reply to message #304143] Tue, 04 March 2008 04:58 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
select a.brnch_dt_of_brnch_opn from
r_m_brnch a,r_m_t_brnch_transfer b
where a.brnch_bk_cd IN
(select c.s1 from sample c,r_m_t_brnch_transfer b where c.bname=b.brnch_name and rownum=1) and
a.brnch_brnch_no IN
(select c.s2 from sample c,r_m_t_brnch_TRANSFER B where c.bname=b.brnch_name and rownum=1) and
a.brnch_name=b.brnch_name and b.effective_date>a.brnch_dt_of_brnch_opn;





r_m_brnch

BRNCH_DT_brnch_opn BRNCH_b_cd BRNCH_no BRNCH_NAME

--------- ----- ------
30-DEC-98 053 0043 GORAYA
31-DEC-95 060 0120 BANGA


sample

S1 S2 BNAME
--- ---- ----------

053 0043 GORAYA


desired output


BRNCH_DT_
---------
30-DEC-98

[Updated on: Tue, 04 March 2008 05:05]

Report message to a moderator

Re: select query [message #304146 is a reply to message #304122] Tue, 04 March 2008 05:15 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
read it carefully

http://www.orafaq.com/forum/t/88153/0/

regards,
Re: select query [message #304171 is a reply to message #304146] Tue, 04 March 2008 07:12 Go to previous messageGo to next message
mrkcse
Messages: 62
Registered: November 2007
Location: Mumbai,India
Member
THanks all for your support ,


I got the solution.


select
a.brnch_dt_of_brnch_opn
from
r_m_brnch a,
r_m_t_brnch_transfer b
where
(trim(a.brnch_bk_cd),trim(a.brnch_brnch_no))
IN
(select
trim(c.s1),trim(c.s2)
from
sample c,
r_m_t_brnch_transfer b
where
c.bname=b.brnch_name
and rownum=1)
and a.brnch_name=b.brnch_name
and b.effective_date>a.brnch_dt_of_brnch_opn
Re: select query [message #304196 is a reply to message #304171] Tue, 04 March 2008 08:30 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Next time, please format your code. You were pointed to the forum-rules; it's in there.
Previous Topic: REQUEST
Next Topic: Date Format - Y2k
Goto Forum:
  


Current Time: Sun Dec 04 14:50:08 CST 2016

Total time taken to generate the page: 0.10692 seconds