Click here to Skip to main content
15,860,844 members
Please Sign up or sign in to vote.
3.67/5 (3 votes)
See more:
Dear Friends,

Here iam new to Oracle 11g and so i want to know how to join two tables in oracle11g and assign dynamic coloum to that query

1:Query: select ISREQUEST,ISREQUESTTYPEID,ISREQUESTDATE from tbl_isrequest where status=3 OR status=2 and igapppersonmid=787670;


Output:ISREQUEST ISREQUESTTYPEID ISREQUESTDATE

3 , 1 , 18-OCT-13 06.39.32.000000000 PM

1 , 1 , 18-OCT-13 06.39.32.000000000 PM


2 , 1 , 18-OCT-13 06.39.32.000000000 PM


7 , 2 , 18-OCT-13 06.39.32.000000000 PM


9 , 2 , 18-OCT-13 06.39.32.000000000 PM


2:Query:SELECT count(*) from asas.tbl_isinterrogators where isrequest =1 and persontype = 1

OutPut:Count
1

From this two queries i want the output like this

Output:ISREQUEST ISREQUESTTYPEID ISREQUESTDATE CheckCondiction

3 , 1 , 18-OCT-13 06.39.32.000000000 PM , False

1 , 1 , 18-OCT-13 06.39.32.000000000 PM , True


2 , 1 , 18-OCT-13 06.39.32.000000000 PM , False


7 , 2 , 18-OCT-13 06.39.32.000000000 PM , False


9 , 2 , 18-OCT-13 06.39.32.000000000 PM , False


CheckCondiction is another coloumn which should generate through query and isrequest is the common coloumn for the both queries.if you people know the solution share with me.



Regards,

AnilKumar.D
Posted
Updated 21-Oct-13 23:56pm
v2
Comments
eivan1815 5-Nov-13 7:28am    
hi Amir
excellent
hanie2 5-Nov-13 7:41am    
excellent

1 solution

Hi you can use nested query and DECODE() function for solve this problem, like this :

SQL
select i.ISREQUEST,
       i.ISREQUESTTYPEID,
       i.ISREQUESTDATE,
       decode((SELECT count(*)
                from asas.tbl_isinterrogators s
               where s.isrequest = i.ISREQUEST
                 and s.persontype = 1),
              0,
              'FALSE',
              'TRUE') as isinterrogators
  from tbl_isrequest i
 where i.status = 3
    OR i.status = 2
   and i.igapppersonmid = 787670;
 
Share this answer
 
v2
Comments
Member 10381235 5-Nov-13 7:23am    
it's very good
hanie2 5-Nov-13 7:35am    
very good
sahar khalilnejad 5-Nov-13 8:09am    
it s the best

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900