October 1, 2012 at 12:17 pm
Hi,
I need some help in writing a query for this below condition.
Source Column: A.BLEI_CK
Target Column:B.BILL_ENTY_SK
IF A.BLEI_CK is NULL then set B.BILL_ENTY_SK to -3 (Not Applicable) ELSE IF
A.BLEI_CK is NOT NULL THEN Perform a LOOKUP WHERE C.SRC_UNQ_BILL_ENTY_ID = A.BLEI_CK and RETURN C.BILL_ENTY_SK(this value would be populated in the target column) ELSE IF
Lookup fails then set to -1 (Invalid)
we have three conditions here. If the source column is null, then set the target column to -3
If the source column has a valid value then do a lookup and return some value based on the condition given above and if the lookup fails then populate -1 in the target column to -1
October 1, 2012 at 1:04 pm
Can't help much without knowing what your tables look like (create scripts would be best).
Assuming you have a table with the valid lookup values, you can query that.
Might be a single Case statement, might be simpler as a Coalesce, but I can't tell which from what you've posted.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 1, 2012 at 1:30 pm
Hi,
Thanks for the response. I have given some details below. Let me know if you need more information.
Source Table:
CREATE TABLE [dbo].[CMC_BLCO_COMM_ITEM](
[BLEI_CK] [int] NOT NULL,
[CSPI_ID] [char](8) NOT NULL,
[PMFA_ID] [char](2) NOT NULL,
[BLCO_SEQ_NO] [smallint] NOT NULL,
[COAR_ID] [char](12) NOT NULL,
[BLCO_EFF_DT] [datetime] NOT NULL,
[BLCO_TERM_DT] [datetime] NOT NULL,
[BLCO_PCT] [int] NOT NULL,
[ROW_MODIFICATION_TYPE] [char](1) NOT NULL,
[CR_RUN_CYC_EXCTN_SK] [int] NOT NULL,
[LST_UPDT_RUN_CYC_EXCTN_SK] [int] NOT NULL
) ON [WPS_STG]
Target Table:
CREATE TABLE [dbo].[BILL_CMSN](
[BILL_CMSN_SK] [int] NOT NULL,
[SRC_UNQ_BILL_ENTY_ID] [int] NOT NULL,
[PDT_PLAN_ID] [varchar](50) NOT NULL,
[FEE_DISC_ID] [varchar](50) NOT NULL,
[BILL_CMSN_SEQ_NUM] [smallint] NOT NULL,
[SRC_SYS_CD] [varchar](30) NOT NULL,
[CR_RUN_CYC_EXCTN_SK] [int] NOT NULL,
[LST_UPDT_RUN_CYC_EXCTN_SK] [int] NOT NULL,
[CRD_DTTM] [datetime] NOT NULL,
[UPDT_DTTM] [datetime] NOT NULL,
[SRC_SYS_CD_SK] [int] NOT NULL,
[BILL_ENTY_SK] [int] NOT NULL,
[PDT_PLAN_SK] [int] NOT NULL,
[CMSN_ARGT_SK] [int] NOT NULL,
[BILL_CMSN_EFF_DT_SK] [int] NOT NULL,
[BILL_CMSN_TRMN_DT_SK] [int] NOT NULL,
[BILL_CMSN_PREM_PCT] [int] NULL,
) ON WPS_IDS
Look Up Table:
CREATE TABLE [dbo].[BILL_ENTY](
[BILL_ENTY_SK] [int] NOT NULL,
[SRC_UNQ_BILL_ENTY_ID] [int] NOT NULL,
[SRC_SYS_CD] [varchar](30) NOT NULL,
[CR_RUN_CYC_EXCTN_SK] [int] NOT NULL,
[LST_UPDT_RUN_CYC_EXCTN_SK] [int] NOT NULL,
[CRD_DTTM] [datetime] NOT NULL,
[UPDT_DTTM] [datetime] NOT NULL,
[SRC_SYS_CD_SK] [int] NOT NULL,
[BILL_GRP_SK] [int] NOT NULL,
[GRP_SK] [int] NOT NULL,
[SUB_GRP_SK] [int] NOT NULL,
[SUBS_SK] [int] NOT NULL,
[ALT_FUNDG_AGRE_SK] [int] NOT NULL,
[BILL_LVL_CD_SK] [int] NOT NULL,
[BILL_ENTY_LST_BILL_DUE_DT_SK] [int] NOT NULL,
[BILL_ENTY_LST_BILL_END_DT_SK] [int] NOT NULL,
[BILL_ENTY_LST_APV_BILL_DUE_DT_SK] [int] NOT NULL,
[BILL_ENTY_LST_BILL_PMT_DUE_DT_SK] [int] NOT NULL,
[BILL_ENTY_LST_TOLRNC_PAID_BILL_DUE_DT_SK] [int] NOT NULL,
[BILL_ENTY_LST_RCONCL_BILL_DUE_DT_SK] [int] NOT NULL,
[BILL_ENTY_LST_BILL_GNT_DT_SK] [int] NOT NULL,
[BILL_ENTY_LST_MLTP_MODE_DUE_DT_SK] [int] NOT NULL,
[BILL_ENTY_LST_MLTP_MODE_END_DT_SK] [int] NOT NULL,
[BILL_ENTY_BILL_BAT_STU_CD_SK] [int] NOT NULL,
[BILL_ENTY_LST_BILL_MNL_ALLOCS_DUE_DT_SK] [int] NOT NULL,
[BILL_ENTY_PRCS_STU_CD_SK] [int] NOT NULL,
[BILL_ENTY_SSPND_BILL_CD_SK] [int] NOT NULL,
[BILL_ENTY_LST_ARCV_DT_SK] [int] NOT NULL,
[BILL_ENTY_ELIG_THRU_DT_UPDT_CD_SK] [int] NOT NULL,
[BILL_ENTY_TOLRNC_METH_CD_SK] [int] NOT NULL,
[BILL_ENTY_NET_DUE_AMT] [money] NOT NULL,
[BILL_ENTY_NET_CURR_OUTSTND_SUSP_AMT] [money] NOT NULL,
[BILL_ENTY_RCONCL_IND] [char](1) NOT NULL,
[BILL_ENTY_SUBS_RATNG_IND] [char](1) NOT NULL,
[BILL_ENTY_RMTC_TOLRNC_AMT] [money] NOT NULL,
[BILL_ENTY_RMTC_TOLRNC_PCT] [int] NULL,
[SRC_UNQ_BILL_LVL_ID] [int] NOT NULL ) ON WPS_IDS
Condition:
I am working on DW project where I need to validate if the data from the Source Column has populated correctly on the Target Column based on a lookup condition. I have give the
Create table statements for the Source table,Target table and the Lookup table above. THe actual columns used in this condition are highlighted. The condition is given below,
If WPS_STG.CMC_BLCO_COMM_ITEM.BLEI_CK is NULL then set WPS_IDS.BILL_ENTY_SK = -1 ELSE IF
WPS_STG.CMC_BLCO_COMM_ITEM.BLEI_CK is NOT NULL THEN Perform a LOOKUP on
WPS_IDS.BILL_ENTY WHERE WPS_IDS.BILL_ENTY.SRC_UNQ_BILL_ENTY_ID = WPS_STG.CMC_BLCO_COMM_ITEM.BLEI_CK and
and RETURN WPS_IDS.BILL_ENTY.BILL_ENTY_SK ELSE IF
Lookup fails then set WPS_IDS.BILL_ENTY_SK = -1
I need some help to create a SQL statement which will validate if the data from the source column has populated correctly on the table column based on the above condition.
October 1, 2012 at 3:13 pm
Your request doesnt quite make sense, your looking at CMC_BLCO_COMM_ITEM.BLEI_CK to see if its NULL, but your create table statement declares "NOT NULL" So it cannot be null, why check for Null?
Seems simple to me. Left Join to your lookup table, If its found, the BILL_ENTY.BILL_ENTY_SK will not be null and use it.
Otherwise use -1
SELECT ISNULL(BILL_ENTY.BILL_ENTY_SK, -1)
FROM dbo.CMC_BLCO_COMM_ITEM
LEFT OUTER JOIN [dbo].[BILL_ENTY]
ON WPS_IDS.BILL_ENTY.SRC_UNQ_BILL_ENTY_ID = WPS_STG.CMC_BLCO_COMM_ITEM.BLEI_CK
Am I missing something?
October 2, 2012 at 8:02 am
If CMC_BLCO_COMM_ITEM.BLEI_CK is NULL then set BILL_ENTY_SK = -1
What's the link between the two tables? You need to know which record to update.
October 2, 2012 at 8:38 am
This might be what you want, but I'm guessing on the join:
--=========== TEST DATA ===========================
-- Reset all NOT NULL to NULL...
--Source Table:
if object_id('[dbo].[CMC_BLCO_COMM_ITEM]') is not null drop table [dbo].[CMC_BLCO_COMM_ITEM]
CREATE TABLE [dbo].[CMC_BLCO_COMM_ITEM](
[BLEI_CK] [int] NULL,
[CSPI_ID] [char](8) NULL,
[PMFA_ID] [char](2) NULL,
[BLCO_SEQ_NO] [smallint] NULL,
[COAR_ID] [char](12) NULL,
[BLCO_EFF_DT] [datetime] NULL,
[BLCO_TERM_DT] [datetime] NULL,
[BLCO_PCT] [int] NULL,
[ROW_MODIFICATION_TYPE] [char](1) NULL,
[CR_RUN_CYC_EXCTN_SK] [int] NULL,
[LST_UPDT_RUN_CYC_EXCTN_SK] [int] NULL
) ON [PRIMARY];
insert [dbo].[CMC_BLCO_COMM_ITEM] ([BLEI_CK], [CR_RUN_CYC_EXCTN_SK]) values ( null, 1 );
insert [dbo].[CMC_BLCO_COMM_ITEM] ([BLEI_CK], [CR_RUN_CYC_EXCTN_SK]) values ( 96, 2 );
insert [dbo].[CMC_BLCO_COMM_ITEM] ([BLEI_CK], [CR_RUN_CYC_EXCTN_SK]) values ( 97, 3 );
--Target Table:
if object_id('[dbo].[BILL_CMSN]') is not null drop table [dbo].[BILL_CMSN]
CREATE TABLE [dbo].[BILL_CMSN](
[BILL_CMSN_SK] [int] NULL,
[SRC_UNQ_BILL_ENTY_ID] [int] NULL,
[PDT_PLAN_ID] [varchar](50) NULL,
[FEE_DISC_ID] [varchar](50) NULL,
[BILL_CMSN_SEQ_NUM] [smallint] NULL,
[SRC_SYS_CD] [varchar](30) NULL,
[CR_RUN_CYC_EXCTN_SK] [int] NULL,
[LST_UPDT_RUN_CYC_EXCTN_SK] [int] NULL,
[CRD_DTTM] [datetime] NULL,
[UPDT_DTTM] [datetime] NULL,
[SRC_SYS_CD_SK] [int] NULL,
[BILL_ENTY_SK] [int] NULL,
[PDT_PLAN_SK] [int] NULL,
[CMSN_ARGT_SK] [int] NULL,
[BILL_CMSN_EFF_DT_SK] [int] NULL,
[BILL_CMSN_TRMN_DT_SK] [int] NULL,
[BILL_CMSN_PREM_PCT] [int] NULL,
) ON [PRIMARY]
insert [dbo].[BILL_CMSN] ([BILL_ENTY_SK], [CR_RUN_CYC_EXCTN_SK]) values ( 0, 1 );
insert [dbo].[BILL_CMSN] ([BILL_ENTY_SK], [CR_RUN_CYC_EXCTN_SK]) values ( 0, 2 );
insert [dbo].[BILL_CMSN] ([BILL_ENTY_SK], [CR_RUN_CYC_EXCTN_SK]) values ( 0, 3 );
--Look Up Table:
if object_id('[dbo].[BILL_ENTY]') is not null drop table [dbo].[BILL_ENTY]
CREATE TABLE [dbo].[BILL_ENTY](
[BILL_ENTY_SK] [int] NULL,
[SRC_UNQ_BILL_ENTY_ID] [int] NULL,
[SRC_SYS_CD] [varchar](30) NULL,
[CR_RUN_CYC_EXCTN_SK] [int] NULL,
[LST_UPDT_RUN_CYC_EXCTN_SK] [int] NULL,
[CRD_DTTM] [datetime] NULL,
[UPDT_DTTM] [datetime] NULL,
[SRC_SYS_CD_SK] [int] NULL,
[BILL_GRP_SK] [int] NULL,
[GRP_SK] [int] NULL,
[SUB_GRP_SK] [int] NULL,
[SUBS_SK] [int] NULL,
[ALT_FUNDG_AGRE_SK] [int] NULL,
[BILL_LVL_CD_SK] [int] NULL,
[BILL_ENTY_LST_BILL_DUE_DT_SK] [int] NULL,
[BILL_ENTY_LST_BILL_END_DT_SK] [int] NULL,
[BILL_ENTY_LST_APV_BILL_DUE_DT_SK] [int] NULL,
[BILL_ENTY_LST_BILL_PMT_DUE_DT_SK] [int] NULL,
[BILL_ENTY_LST_TOLRNC_PAID_BILL_DUE_DT_SK] [int] NULL,
[BILL_ENTY_LST_RCONCL_BILL_DUE_DT_SK] [int] NULL,
[BILL_ENTY_LST_BILL_GNT_DT_SK] [int] NULL,
[BILL_ENTY_LST_MLTP_MODE_DUE_DT_SK] [int] NULL,
[BILL_ENTY_LST_MLTP_MODE_END_DT_SK] [int] NULL,
[BILL_ENTY_BILL_BAT_STU_CD_SK] [int] NULL,
[BILL_ENTY_LST_BILL_MNL_ALLOCS_DUE_DT_SK] [int] NULL,
[BILL_ENTY_PRCS_STU_CD_SK] [int] NULL,
[BILL_ENTY_SSPND_BILL_CD_SK] [int] NULL,
[BILL_ENTY_LST_ARCV_DT_SK] [int] NULL,
[BILL_ENTY_ELIG_THRU_DT_UPDT_CD_SK] [int] NULL,
[BILL_ENTY_TOLRNC_METH_CD_SK] [int] NULL,
[BILL_ENTY_NET_DUE_AMT] [money] NULL,
[BILL_ENTY_NET_CURR_OUTSTND_SUSP_AMT] [money] NULL,
[BILL_ENTY_RCONCL_IND] [char](1) NULL,
[BILL_ENTY_SUBS_RATNG_IND] [char](1) NULL,
[BILL_ENTY_RMTC_TOLRNC_AMT] [money] NULL,
[BILL_ENTY_RMTC_TOLRNC_PCT] [int] NULL,
[SRC_UNQ_BILL_LVL_ID] [int] NULL
) ON [PRIMARY]
insert [dbo].[BILL_ENTY] ([BILL_ENTY_SK], [SRC_UNQ_BILL_ENTY_ID]) values ( 33, 96 );
--Condition:
--I am working on DW project where I need to validate if the data from the Source Column
-- has populated correctly on the Target Column based on a lookup condition. I have given the
-- create table statements for the Source table,Target table and the Lookup table above.
-- THe actual columns used in this condition are highlighted. The condition is given below,
--If CMC_BLCO_COMM_ITEM.BLEI_CK is NULL then set BILL_ENTY_SK = -1
--ELSE IF CMC_BLCO_COMM_ITEM.BLEI_CK is NULL THEN Perform a LOOKUP on
--BILL_ENTY WHERE BILL_ENTY.SRC_UNQ_BILL_ENTY_ID = CMC_BLCO_COMM_ITEM.BLEI_CK and
--RETURN BILL_ENTY.BILL_ENTY_SK
--ELSE IF Lookup fails then set BILL_ENTY_SK = -1
--I need some help to create a SQL statement which will validate if the data from the source column has
-- populated correctly on the table column based on the above condition.
--========= CHECK BEFORE UPDATES =============
select [CR_RUN_CYC_EXCTN_SK], [BILL_ENTY_SK] from [dbo].[BILL_CMSN];
--========= SUGGESTED SOLUTION ================
UPDATE [dbo].[BILL_CMSN]
SET BILL_ENTY_SK = CASE WHEN CI.BLEI_CK IS NULL THEN -1
WHEN BE.BILL_ENTY_SK IS NULL THEN -1
ELSE BE.BILL_ENTY_SK END
FROM [dbo].[BILL_CMSN] BC
INNER JOIN [dbo].[CMC_BLCO_COMM_ITEM] CI
ON BC.[CR_RUN_CYC_EXCTN_SK] = CI.[CR_RUN_CYC_EXCTN_SK] -- ????? WHAT'S THE JOIN ???????
LEFT OUTER JOIN dbo.BILL_ENTY BE
ON BE.SRC_UNQ_BILL_ENTY_ID = CI.BLEI_CK;
--========= CHECK AFTER UPDATES =============
select [CR_RUN_CYC_EXCTN_SK], [BILL_ENTY_SK] from [dbo].[BILL_CMSN];
October 2, 2012 at 10:51 am
Hi,
Thanks for the response and all those responses should certainly help. I apologize for the mistakes as this is my first post :(.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply