SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Help


Query Help

Author
Message
krishna30
krishna30
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 16
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
GSquared
GSquared
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59243 Visits: 9730
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
krishna30
krishna30
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 16
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.
Ray M
Ray M
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5211 Visits: 1076
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?
laurie-789651
laurie-789651
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1498 Visits: 1272
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.
laurie-789651
laurie-789651
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1498 Visits: 1272
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];




krishna30
krishna30
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 16
Hi,

Thanks for the response and all those responses should certainly help. I apologize for the mistakes as this is my first post Sad.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search