Início / user-38759

user38759's questions

Martin Hope
user38759
Asked: 2014-07-09 06:58:44 +0800 CST
  • 1
create view ENTRP_SERVS.V_IRS_BULK as
Select 
  Atr.Ssn_Tx
,Names.First_Na
,Names.Middle_Na
,Names.Last_Na
,Reff.Ref_Cd_Desc_Tx Suffix_Na
,MBR.PRSN_MBRSH_ID,
T_PRSN_MBRSH_TAX.FILTER_TYPE_CD

 From Hix.T_Enrt En Inner Join Hix.T_Prsn_Enrt Pe On En.Enrt_Id = Pe.Enrt_Id And En.Enrt_Status_Cd In (428,439, 430 ) And Pe.Active_In = 'Y' And (Pe.Retro_In Is Null Or Pe.Retro_In = 'N') 
Left Outer Join Hix.T_Aptc Ap On En.Enrt_Id = Ap.Enrt_Id
Left Outer Join Hix.T_Prsn_Addl_Attr Atr On Pe.Prsn_Mbrsh_Id = Atr.Prsn_Mbrsh_Id 
Left Outer Join
    (
                    Select nAc.Prsn_mbrsh_Id,  First_Na , Middle_Na , Last_Na, Suffix_Cd From Hix.T_Prsn_Name Na, Hix.t_prsn_mbrsh_prsn_name_ac nAc
                                                   where na.prsn_name_id = nAc.prsn_Name_Id
    ) Names On Pe.prsn_mbrsh_id = Names.prsn_mbrsh_id
Left Outer Join Hix.T_Ref_Data Reff On Names.Suffix_Cd = Reff.Ref_Data_Id
Left Outer Join Hix.T_Ref_Data Re On Atr.GENDER_CD = Reff.Ref_Data_Id
Left Outer Join Hix.T_PRSN_MBRSH MBR on MBR.PRSN_MBRSH_ID =  Pe.PRSN_MBRSH_ID

Left Outer Join

(SELECT (CASE WHEN FILLING_STATUS_CD IN (2061,2062,2063) OR  (FILLING_STATUS_CD=2063 AND PRIM_TX_FILER_IN ='Y') THEN 'PRIMARY'
             WHEN FILLING_STATUS_CD=2063 AND PRIM_TX_FILER_IN ='N' THEN 'SPOUSE'
             WHEN FILLING_STATUS_CD IN (2066, 2068) THEN 'DEPENDENT'
             ELSE NULL
             END) AS FILTER_TYPE_CD,PRSN_MBRSH_ID FROM HIX.T_PRSN_MBRSH_TAX  WHERE TAX_FL_YR_NB=YEAR(CURRENT DATE)-2)T_PRSN_MBRSH_TAX
    ON PE.PRSN_MBRSH_ID=T_PRSN_MBRSH_TAX.PRSN_MBRSH_ID WHERE T_PRSN_MBRSH_TAX.FILTER_TYPE_CD IS NOT NULL

Eu tenho essa visão e quero remover o apóstrofo (') das colunas FIRST_NA, MIDDLE_NA e LAST_NA. Como eu posso fazer isso?

db2 replace
Martin Hope
user38759
Asked: 2014-05-22 08:52:27 +0800 CST
  • 2

Na exibição a seguir, desejo adicionar uma coluna (não alterar a exibição) chamada digamos "ABC" que mostra a data no formato YYYYMM(sem data, apenas ano e mês). Como eu posso fazer isso? Estou usando db2.

create view VIEW_IRS1 as Select 
MBR.PRSN_MBRSH_ID
, Atr.Ssn_Tx
, Names.First_Na
, Names.Middle_Na
, Names.Last_Na
, Reff.Ref_Cd_Desc_Tx Suffix_Na
,'FALSE' AS "Request Title II Monthly Income Verification"

From Hix.T_Enrt En 
Inner Join Hix.T_Prsn_Enrt Pe On En.Enrt_Id = Pe.Enrt_Id And En.Enrt_Status_Cd In 
(428,439, 430 ) And Pe.Active_In = 'Y' And (Pe.Retro_In Is Null Or Pe.Retro_In = 'N') 

Left Outer Join Hix.T_Aptc Ap On En.Enrt_Id = Ap.Enrt_Id

Left Outer Join Hix.T_Prsn_Addl_Attr Atr On Pe.Prsn_Mbrsh_Id = Atr.Prsn_Mbrsh_Id

Left Outer Join
(                   
    Select nAc.Prsn_mbrsh_Id,  First_Na , Middle_Na , Last_Na, Suffix_Cd From 
    Hix.T_Prsn_Name Na, Hix.t_prsn_mbrsh_prsn_name_ac nAc
    where na.prsn_name_id = nAc.prsn_Name_Id
) Names On Pe.prsn_mbrsh_id = Names.prsn_mbrsh_id

Left Outer Join Hix.T_Ref_Data Reff On Names.Suffix_Cd = Reff.Ref_Data_Id

Left Outer Join Hix.T_Ref_Data Re On Atr.GENDER_CD = Reff.Ref_Data_Id

Left Outer Join Hix.T_PRSN_MBRSH MBR on MBR.PRSN_MBRSH_ID=Pe.PRSN_ENRT_ID;
db2 view
Martin Hope
user38759
Asked: 2014-05-22 07:21:26 +0800 CST
  • 0

Eu tenho essa visão com a seguinte definição.

create view VIEW_IRS1 as Select 
MBR.PRSN_MBRSH_ID
, Atr.Ssn_Tx
, Names.First_Na
, Names.Middle_Na
, Names.Last_Na
, Reff.Ref_Cd_Desc_Tx Suffix_Na
,'FALSE' AS [Request Title II Monthly Income Verification]

From Hix.T_Enrt En 
Inner Join Hix.T_Prsn_Enrt Pe On En.Enrt_Id = Pe.Enrt_Id And En.Enrt_Status_Cd In (428,439, 430 ) And Pe.Active_In = 'Y' And (Pe.Retro_In Is Null Or Pe.Retro_In = 'N') 

Left Outer Join Hix.T_Aptc Ap On En.Enrt_Id = Ap.Enrt_Id

Left Outer Join Hix.T_Prsn_Addl_Attr Atr On Pe.Prsn_Mbrsh_Id = Atr.Prsn_Mbrsh_Id

Left Outer Join
(                   
    Select nAc.Prsn_mbrsh_Id,  First_Na , Middle_Na , Last_Na, Suffix_Cd From Hix.T_Prsn_Name Na, Hix.t_prsn_mbrsh_prsn_name_ac nAc
    where na.prsn_name_id = nAc.prsn_Name_Id
) Names On Pe.prsn_mbrsh_id = Names.prsn_mbrsh_id

Left Outer Join Hix.T_Ref_Data Reff On Names.Suffix_Cd = Reff.Ref_Data_Id

Left Outer Join Hix.T_Ref_Data Re On Atr.GENDER_CD = Reff.Ref_Data_Id

Left Outer Join Hix.T_PRSN_MBRSH MBR on MBR.PRSN_MBRSH_ID=Pe.PRSN_ENRT_ID;

Desejo criar uma nova exibição (não alterar essa exibição) com um nome de coluna adicional, digamos "ABC", cujo valor sempre deve ser FALSE. Como eu posso fazer isso?

db2 view