locked
sql nested select RRS feed

  • Question

  • User-7838209 posted

    i have 2 databases i select some filds from 1 and join with other field in the second 
    i need the value selected from table 1 to be displayed in a way
    that instead of value = 1 it display "gen clnc"
    the code is

    select distinct a.ENBR , a.ENAM AS,SUBSTR(b.VSTDTE,1,9), CLINTYP is( SELECT CASE when CLINTYP = '1' then 'Gen. Clinic' when CLINTYP = '3' then 'both Clinic'else 'Dent. Clinic' end FROM CLNCVST where VSTDTE='15-May-13' )from MAINFRAME.EMPBAS a inner join CLINIC.CLNCVST b ON a.EMPNBR=b.EMPNBR where b.EMPNBR IN (select EMPNBR from CLINIC.CLNCVST where VSTDTE='15-May-13')and VSTDTE='15-May-13'



     

    its mesing up any help

    Monday, May 20, 2013 3:29 AM

Answers

  • User-7838209 posted

    here is the solution  

    select distinct a.EMPNBR AS "Emp Number", a.EMPNAM AS "Emp. Name",SUBSTR(b.VSTDTE,1,9) AS "Visite",CASE when b.CLINTYP = 1 then 'Gen. Clinic' when b.CLINTYP = 3 then 'both Clinic'else 'Dent. Clinic' end from MAINFRAME.EMPBAS  a inner join  CLINIC.CLNCVST  b ON a.EMPNBR=b.EMPNBR where b.EMPNBR IN (select EMPNBR from CLINIC.CLNCVST  where VSTDTE='20-May-13')and  VSTDTE= '20-May-13'



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, May 26, 2013 8:45 AM

All replies

  • User-578610739 posted

    Hi,

    Is there same table(CLNCVST) available in both db.   Then use

    FROM CLINIC.CLNCVST
    Monday, May 20, 2013 7:05 AM
  • User-7838209 posted

    no clncvst only in 1 db

    i tried the following 

    select distinct a.EMPNBR AS "Emp Number", a.EMPNAM AS "Emp. Name",SUBSTR(b.VSTDTE,1,9) AS "Visite",(SELECT  CASE when CLINTYP = '1' then 'Gen. Clinic' when CLINTYP = '3' then 'both Clinic'else 'Dent. Clinic' end  FROM CLNCVST where  VSTDTE='15-May-13')
     from MAINFRAME.EMPBAS  a inner join  CLINIC.CLNCVST  b ON a.EMPNBR=b.EMPNBR where b.EMPNBR IN (select EMPNBR from CLINIC.CLNCVST  where VSTDTE='20-May-13')and  VSTDTE= '20-May-13'  

     it return the value null with colum name "(SELECT CASE when CLINTYP = '1' then 'Gen. Clinic' when CLINTYP = '3' then 'both Clinic'else 'Dent. Clinic' end FROM CLNCVST where VSTDTE='15-May-13')" it consider this select statment as column header



    Wednesday, May 22, 2013 2:58 AM
  • User-7838209 posted

    here is the solution  

    select distinct a.EMPNBR AS "Emp Number", a.EMPNAM AS "Emp. Name",SUBSTR(b.VSTDTE,1,9) AS "Visite",CASE when b.CLINTYP = 1 then 'Gen. Clinic' when b.CLINTYP = 3 then 'both Clinic'else 'Dent. Clinic' end from MAINFRAME.EMPBAS  a inner join  CLINIC.CLNCVST  b ON a.EMPNBR=b.EMPNBR where b.EMPNBR IN (select EMPNBR from CLINIC.CLNCVST  where VSTDTE='20-May-13')and  VSTDTE= '20-May-13'



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, May 26, 2013 8:45 AM