InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 09:48:20 GMT20http://www.sqlservercentral.com/Forums/Topic1366697-1292-1.aspxHi,Thanks for the response and all those responses should certainly help. I apologize for the mistakes as this is my first post :(.Tue, 02 Oct 2012 10:51:24 GMTkrishna30http://www.sqlservercentral.com/Forums/Topic1366697-1292-1.aspxThis might be what you want, but I'm guessing on the join:[code="sql"]--=========== 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 ENDFROM [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];[/code]Tue, 02 Oct 2012 08:38:38 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1366697-1292-1.aspx[quote]If CMC_BLCO_COMM_ITEM.BLEI_CK is NULL then set BILL_ENTY_SK = -1[/quote]What's the link between the two tables? You need to know which record to update.Tue, 02 Oct 2012 08:02:26 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1366697-1292-1.aspxPlease post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etC. in your schema are. IF you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. This is minimal polite behavior on SQL forums. The useless narrative you posted seems to be COBOL, as shown by the PERFORM verb. And it looks like you are using the single letter tape drive names instead of valid table names. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. Try again and follow minimum Netiquette.Tue, 02 Oct 2012 07:48:34 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1366697-1292-1.aspxYour 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 -1SELECT ISNULL(BILL_ENTY.BILL_ENTY_SK, -1)FROM dbo.CMC_BLCO_COMM_ITEMLEFT OUTER JOIN [dbo].[BILL_ENTY] ON WPS_IDS.BILL_ENTY.SRC_UNQ_BILL_ENTY_ID = WPS_STG.CMC_BLCO_COMM_ITEM.BLEI_CKAm I missing something?Mon, 01 Oct 2012 15:13:40 GMTRay Mhttp://www.sqlservercentral.com/Forums/Topic1366697-1292-1.aspxHi,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]( [b][BLEI_CK] [int] NOT NULL,[/b] [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, [b][BILL_ENTY_SK] [int] NOT NULL,[/b] [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_IDSLook Up Table:CREATE TABLE [dbo].[BILL_ENTY]( [b][BILL_ENTY_SK] [int] NOT NULL,[/b] [b][SRC_UNQ_BILL_ENTY_ID] [int] NOT NULL,[/b] [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_IDSCondition: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 = -1I 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.Mon, 01 Oct 2012 13:30:34 GMTkrishna30http://www.sqlservercentral.com/Forums/Topic1366697-1292-1.aspxCan'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.Mon, 01 Oct 2012 13:04:01 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1366697-1292-1.aspxHi,I need some help in writing a query for this below condition. Source Column: A.BLEI_CK Target Column:B.BILL_ENTY_SKIF 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 IFLookup fails then set to -1 (Invalid) we have three conditions here. If the source column is null, then set the target column to -3If 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 -1Mon, 01 Oct 2012 12:17:01 GMTkrishna30