Home » SQL & PL/SQL » SQL & PL/SQL » problem while selecting from multiple table
problem while selecting from multiple table [message #287110] Tue, 11 December 2007 01:51 Go to next message
raghunalumachu
Messages: 12
Registered: November 2007
Location: MUMBAI
Junior Member
I have two tables transaction and currentstatus with columns
as below
transaction currentstatus
------------ -----------------
refno refno
negendtime
riskendtime
lossendtime
appno
the refno in transaction table is dynamically retrieved from other 3 tables.
Individually both the queries are working correctly but i want to combine in to a single query.

1st query:transaction table
-----------------------------
select refno 
from transaction 
(select refno from where status='y' a 
union 
select refno from b where status='n' 
union 
select refno from c where status='all') 

2nd query:currentstatus table
------------------------------
select Case  When negendtime is not null THEN   negendtime else 
Case When riskendtime is  not null THEN   riskendtime else 
case When lossendtime is not null THEN   lossendtime 
end end end 
from currentstatus where refno='123'

now i would like to combine these two queries in to single query.
i had tried like this:
myquery:
---------
select Case  When negendtime is not null THEN   negendtime else
Case When riskendtime is  not null THEN   riskendtime else 
case When lossendtime is not null THEN   lossendtime 
end end end,curr.appno,a.refno 
from currentstatus curr,transaction d  
where a.refno in 
(select refno from where status='y' a 
union 
select refno from b where status='n' 
union 
select refno from c where status='all') a and a.refno=d.refno

but i am not able to execute this query..showing error as
SQL command not properly ended ....

Thanks in Advance,
Raghu.


[edited by Taj: add code tags (please add next time code tags before posting)]
{edited by Michel: limit the line to 80 characters]


[Updated on: Tue, 11 December 2007 02:01] by Moderator

Report message to a moderator

Re: problem while selecting from multiple table [message #287115 is a reply to message #287110] Tue, 11 December 2007 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste the execution.
SQL*Plus will point you where the error is.

Regards
Michel
Re: problem while selecting from multiple table [message #287123 is a reply to message #287110] Tue, 11 December 2007 02:32 Go to previous messageGo to next message
raghunalumachu
Messages: 12
Registered: November 2007
Location: MUMBAI
Junior Member
This is my query exactly in sql plus

select Case When negendtime is not null THEN negendtime else
Case When riskendtime is not null THEN riskendtime else 
case When lossendtime is not null THEN lossendtime 
end end end,curr.appno,a.refno 
from currentstatus curr,transaction d 
where a.refno in 
(select refno from where status='y' a 
union 
select refno from b where status='n' 
union 
select refno from c where status='all')
a and a.refno=d.refno



ERROR at line 1:
ORA-00933: SQL command not properly ended

[edited by Taj: add code tags and format sql statment(last time i format query for you]

[Updated on: Tue, 11 December 2007 02:35] by Moderator

Report message to a moderator

Re: problem while selecting from multiple table [message #287125 is a reply to message #287123] Tue, 11 December 2007 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64116
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

(select refno from where status='y' a

What is this final "a"? Should not it be after "from"?

Regards
Michel

[Updated on: Tue, 11 December 2007 02:36]

Report message to a moderator

Re: problem while selecting from multiple table [message #287128 is a reply to message #287125] Tue, 11 December 2007 02:44 Go to previous messageGo to next message
raghunalumachu
Messages: 12
Registered: November 2007
Location: MUMBAI
Junior Member
sorry, i made a mistake
the following is the query ....

select Case When negendtime is not null THEN negendtime else
Case When riskendtime is not null THEN riskendtime else
case When lossendtime is not null THEN lossendtime
end end end,curr.appno,t.refno
from currentstatus curr,transaction d
where t.refno in
(select refno a from where status='y'
union
select refno from b where status='n'
union
select refno from c where status='all')
t and t.refno=d.refno
Re: problem while selecting from multiple table [message #287138 is a reply to message #287128] Tue, 11 December 2007 03:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Look what happens if you format your code:

select case when negendtime is not null 
            then negendtime
            else case when riskendtime is not null 
                      then riskendtime
                      else case when lossendtime is not null 
                                then lossendtime
                           end 
                 end 
       end
,      curr.appno
,      t.refno
from   currentstatus curr
      ,transaction   d
where  t.refno in (select refno a
                   from   
                   where status = 'y'
                   union
                   select refno
                   from   b
                   where  status = 'n'
                   union
                   select refno
                   from   c
                   where  status = 'all') t
and    t.refno = d.refno


All of a sudden, it becomes clear that you have no table-name in your from-clause in the first union.

By the way, the nested case can be rewritten using coalesce.
Re: problem while selecting from multiple table [message #287139 is a reply to message #287128] Tue, 11 December 2007 03:21 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

select Case When negendtime is not null THEN negendtime else
Case When riskendtime is not null THEN riskendtime else
case When lossendtime is not null THEN lossendtime
end end end,curr.appno,t.refno
from currentstatus curr,transaction d
where t.refno in
(select refno a from where status='y' --correct this statement
union
select refno from b where status='n'
union
select refno from c where status='all')
t and t.refno=d.refno


regards,
Re: problem while selecting from multiple table [message #287150 is a reply to message #287139] Tue, 11 December 2007 03:38 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
Michel Cadot:
Quote:

Use SQL*Plus and copy and paste the execution.
SQL*Plus will point you where the error is.



try this :
select Case When negendtime is not null THEN negendtime else
Case When riskendtime is not null THEN riskendtime else
case When lossendtime is not null THEN lossendtime
end end end,curr.appno,t.refno
from currentstatus curr,transaction d
where and t.refno=d.refno and t.refno in
(select refno from a where status='y' 
union
select refno from b where status='n'
union
select refno from c where status='all')


why don't you make use of coalesce as Frank suggested?

regards,

my mistake too.after reading Frank's reply i realised that.just missed out on that detail.



[Updated on: Tue, 11 December 2007 03:59]

Report message to a moderator

Re: problem while selecting from multiple table [message #287153 is a reply to message #287150] Tue, 11 December 2007 03:49 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
There is no table defined with alias t.
You can't just go and define objects to use in your where clause anywhere in the query you like..
Get rid of all the references to t and replace the
where t.refno in (..)

with
where d.refno in (...)


select coalesce(negentime, riskendtime, lossendtime)
,      curr.appno
,      d.refno
from   currentstatus curr
,      transaction   d
where  d.refno in (select refno
                   from   a
                   where  status = 'y'
                   union
                   select refno
                   from   b
                   where  status = 'n'
                   union
                   select refno
                   from   c
                   where  status = 'all')


Now execute that in sqlplus, and copy-paste the outcome.
Add [code] before your paste here and [/code] after it to keep it readable.

[Updated on: Tue, 11 December 2007 03:54]

Report message to a moderator

Previous Topic: Delete records with limit
Next Topic: Avarege over 5 minutes
Goto Forum:
  


Current Time: Tue Dec 06 02:25:43 CST 2016

Total time taken to generate the page: 0.31636 seconds