May 9, 2013 at 9:06 am
Hi,
I have Table A:with 419430 records, Table B with 19372 Records.
I am doing join on these 2 tables in order to calculate 2 new columns
BDGT_UTIL_AMT and BDGT_MBRSHIP_MO_CNT.
When I am joining the 2 tables records are drop, I am expecting 419430 records as result instead I am getting result of 214868 records, 204,562 records are dropped.
I have attach the data of Table 1:dev1_metadata_etl.DLDR_BDGT_UTIL_O and Table 2:PROD_DEPT_DLDR_TBL.DLDR_BDGT_MBRSHIP_O and the data I getting as Result of 214868 records.I have them in text file.
select DLDR_RDM.FILE_CTRL_ID,DLDR_RDM.ROW_NUM, DLDR_RDM.BDGT_SCEN_CD, DLDR_RDM.HLTH_PLAN_BDGT_CD, DLDR_RDM.PDCT_BDGT_CD,
DLDR_RDM.GL_ACCT_CD, DLDR_RDM.EXPRNC_COHORT_CD, DLDR_RDM.BDGT_UTIL_MEAS_CD, DLDR_RDM.BDGT_YR_MO_NUM,
DLDR_RDM.SRC_METRIC_VALUE,CAST(((DLDR_RDM.BDGT_AMT/12000) * DLDR_MEM.BDGT_AMT) AS DECIMAL(18,8)) AS BDGT_UTIL_AMT,
DLDR_MEM.BDGT_AMT AS BDGT_MBRSHIP_MO_CNT from dev1_metadata_etl.DLDR_BDGT_UTIL_O DLDR_RDM
Inner join PROD_DEPT_DLDR_TBL.DLDR_BDGT_MBRSHIP_O DLDR_MEM
on DLDR_RDM.BDGT_SCEN_CD=DLDR_MEM.BDGT_SCEN_CD
AND DLDR_RDM.HLTH_PLAN_BDGT_CD=DLDR_MEM.HLTH_PLAN_BDGT_CD
AND DLDR_RDM.PDCT_BDGT_CD=DLDR_MEM.PDCT_BDGT_CD
AND DLDR_RDM.BDGT_YR_MO_NUM=DLDR_MEM.BDGT_YR_MO_NUM
AND DLDR_RDM.EXPRNC_COHORT_CD=DLDR_MEM.EXPRNC_COHORT_CD
WHERE DLDR_MEM.FILE_CTRL_ID = 1502
Columns of Table 1:
FILE_CTRL_ID ROW_NUM BDGT_SCEN_CD HLTH_PLAN_BDGT_CD PDCT_BDGT_CD GL_ACCT_CD EXPRNC_COHORT_CD BDGT_UTIL_MEAS_CD BDGT_YR_MO_NUM SRC_METRIC_VALUE BDGT_AMT
Table 2:
FILE_CTRL_ID ROW_NUM BDGT_SCEN_CD HLTH_PLAN_BDGT_CD PDCT_BDGT_CD GL_ACCT_CD EXPRNC_COHORT_CD BDGT_YR_MO_NUM BDGT_AMT
My result Columns:
FILE_CTRL_ID ROW_NUM BDGT_SCEN_CD HLTH_PLAN_BDGT_CD PDCT_BDGT_CD GL_ACCT_CD EXPRNC_COHORT_CD BDGT_UTIL_MEAS_CD BDGT_YR_MO_NUM SRC_METRIC_VALUE BDGT_AMT,BDGT_UTIL_AMT ,BDGT_MBRSHIP_MO_CNT
I did full outer join and I am getting 215096 records
Left outer join: 214868
Inner join : 214868
Please help
Thanks
May 9, 2013 at 9:22 am
How many rows are returned if you comment out the WHERE clause?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 9, 2013 at 9:43 am
One suggestion, you may want to try formatting your code to make it easier to read, and please use white space as it also helps with readability. When posting your code use the IFCode shortcut [ code="sql" ] and [ /code ] (NO spaces inside the square brackets) as this will also maintain the formatting of the code, like this:
select
DLDR_RDM.FILE_CTRL_ID,
DLDR_RDM.ROW_NUM,
DLDR_RDM.BDGT_SCEN_CD,
DLDR_RDM.HLTH_PLAN_BDGT_CD,
DLDR_RDM.PDCT_BDGT_CD,
DLDR_RDM.GL_ACCT_CD,
DLDR_RDM.EXPRNC_COHORT_CD,
DLDR_RDM.BDGT_UTIL_MEAS_CD,
DLDR_RDM.BDGT_YR_MO_NUM,
DLDR_RDM.SRC_METRIC_VALUE,
CAST(((DLDR_RDM.BDGT_AMT/12000) * DLDR_MEM.BDGT_AMT) AS DECIMAL(18,8)) AS BDGT_UTIL_AMT,
DLDR_MEM.BDGT_AMT AS BDGT_MBRSHIP_MO_CNT
from
dev1_metadata_etl.DLDR_BDGT_UTIL_O DLDR_RDM
Inner join PROD_DEPT_DLDR_TBL.DLDR_BDGT_MBRSHIP_O DLDR_MEM
on (DLDR_RDM.BDGT_SCEN_CD = DLDR_MEM.BDGT_SCEN_CD
AND DLDR_RDM.HLTH_PLAN_BDGT_CD = DLDR_MEM.HLTH_PLAN_BDGT_CD
AND DLDR_RDM.PDCT_BDGT_CD = DLDR_MEM.PDCT_BDGT_CD
AND DLDR_RDM.BDGT_YR_MO_NUM = DLDR_MEM.BDGT_YR_MO_NUM
AND DLDR_RDM.EXPRNC_COHORT_CD = DLDR_MEM.EXPRNC_COHORT_CD)
WHERE
DLDR_MEM.FILE_CTRL_ID = 1502
May 9, 2013 at 11:09 am
Ahh got it, by removing where clause, thank you
May 9, 2013 at 11:10 am
Next time I will make sure to format the query 🙂
May 9, 2013 at 11:17 am
HiralChhaya (5/9/2013)
Next time I will make sure to format the query 🙂
Don't do it just for us, do it for yourself. Well formatted code is actually easier to debug and support.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply