嵌套的sql语句 - 需要从表中选择的值 [英] nested sql statment - need the value selected from table

问题描述

我有2个数据库我从1中选择一些filds并在第二个中加入其他字段

i需要从表1中选择的值以某种方式显示

而不是值= 1它显示gen clnc

代码是

 选择  distinct  a.ENBR，a.ENAM  AS ，SUBSTR（b.VSTDTE， 1 ， 9 ），CLINTYP  （ SELECT   CASE  何时 CLINTYP = '  1' 然后 '  Gen.Clinic'   CLINTYP = '  3' 然后 '  Clinic'  else  '  Dent。诊所' 结束  FROM  CLNCVST 其中 VSTDTE = '  15-May-13'）来自 MAINFRAME.EMPBAS a  inner   join  CLINIC.CLNCVST b  ON  a.EMPNBR = b.EMPNBR 其中​​ b.EMPNBR  IN （< span class =code-keyword>选择 EMPNBR 来自 CLINIC.CLNCVST 其中 VSTDTE = '  15-May-13'）和 VSTDTE = '  15-May-13' 

它的任何帮助

解决方案

这里是解决方案

 选择  distinct  a.EMPNBR  AS    Emp Number，a.EMPNAM  AS   的Emp。名称，SUBSTR（b.VSTDTE， 1 ， 9 ） AS    Visite， CASE   时b.CLINTYP =  1  然后 '  Gen.Clinic'  when  b.CLINTYP =  3  然后 '  Clinic'  else  '  Dent.Clinic'  end  来自 MAINFRAME。 EMPBAS a  inner   join  CLINIC.CLNCVST b  ON  a.EMPNBR = b.EMPNBR 其中 b.EMPNBR  IN （选择 EMPNBR 来自 CLINIC.CLNCVST 其中​​ VSTDTE = '  20-May-13'）和 VSTDTE = < span class =code-string>'  20-May-13' 

< blockquote>我尝试了以下

 选择  distinct  a.EMPNBR  AS    Emp Number，a.EMPNAM  AS   的Emp。名称，SUBSTR（b.VSTDTE， 1 ， 9 ） AS    Visite，（ SELECT   CASE  何时 CLINTYP =  '  1' 然后 '  Gen.Clinic' 当 CLINTYP = '  3' 然后 '  Clinic'  else  '  Dent.Clinic'  end   FROM  CLNCVST 其中 VSTDTE = '  15-May-13'）
 来自 MAINFRAME.EMPBAS  inner   join  CLINIC.CLNCVST b  ON  a.EMPNBR = b.EMPNBR 其中 b.EMPNBR  IN （选择 EMPNBR 来自 CLINIC.CLNCVST 其中 VSTDTE = '  20-May-13'）和 VSTDTE = '  20-May-13' 

它返回值为null的colum name（当CLINTYP =''1'时选择CASE，然后''Gen. Clinic''当CLINTYP ='''3''然后'''Clinic''''''''''''''''''' Clinic''结束于CLNCVST，其中VSTDTE =''15-May-13''）它认为这个选择语句为列标题

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

解决方案

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'

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

这篇关于嵌套的sql语句 - 需要从表中选择的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！