Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Help Expand / Collapse
Author
Message
Posted Monday, October 1, 2012 12:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 3:39 PM
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
Post #1366697
Posted Monday, October 1, 2012 1:04 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1366718
Posted Monday, October 1, 2012 1:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 3:39 PM
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.
Post #1366738
Posted Monday, October 1, 2012 3:13 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:03 PM
Points: 1,480, Visits: 1,028
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?
Post #1366786
Posted Tuesday, October 2, 2012 7:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 1,945, Visits: 2,915
Please 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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1367042
Posted Tuesday, October 2, 2012 8:02 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
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.
Post #1367056
Posted Tuesday, October 2, 2012 8:38 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
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];



Post #1367078
Posted Tuesday, October 2, 2012 10:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 3:39 PM
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 .

Post #1367176
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse