Conceptual ideas - 2 tables one changes other complete Cursors?

[Erland Sommarskog]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?

rcamarda (rcamarda@cable speed.com) writes:[color=blue]
> David, this is pretty cool (although I'm not sure whats going on...Ill
> have to read up on coalesce).
> It seems that eh coalesce is returning the first non-null field that
> it's given in the argument list.[/color]

That's it!

As for where to read about coalesce, CASE etc, see my signature.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?

rcamarda (rcamarda@cable speed.com) writes:[color=blue]
> David, this is pretty cool (although I'm not sure whats going on...Ill
> have to read up on coalesce).
> It seems that eh coalesce is returning the first non-null field that
> it's given in the argument list.[/color]

That's it!

As for where to read about coalesce, CASE etc, see my signature.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[rcamarda]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?

Follow up:
This works like a champ! A generalize form:

SELECT
<< current student fields. >>
-- brings in all students records
FROM current_records
UNION
SELECT
-- bring in all the student changes
<< 'static' fields>>,
COALESCE(change d_records.<fiel d>, current_records .<field>) AS <FIELD>
<<n fields>>
FROM changed_records
WHERE current_records .business_id=ch anged_records.b usiness_id

actual SQL I created: (I may need to look this up some day *grin*)

SELECT
"STUDENT_ID ",
"STUDENT_APPLIC ATION_DT",
"STUDENT_ETHNIC _ID",
"STUDENT_VISA_T YPE",
"STUDENT_GENDER ",
"STUDENT_MARITA L",
"STUDENT_BIRTH_ DT",
"STUDENT_BIRTH_ PLACE",
"STUDENT_LEAD_I D",
"STUDENT_INPUT_ DT",
"STUDENT_FINAID _REQ",
"STUDENT_VA_STA TUS",
"STUDENT_VA_DT" ,
"STUDENT_EMAIL" ,
"STUDENT_FA X",
"STUDENT_COUNTR Y_ID",
"STUDENT_COUNTR Y_CAPTION",
"RECORD_DT" ,
"STUDENT_LASTNA ME",
"STUDENT_FIRSTN AME",
"STUDENT_MI ",
"STUDENT_ADDRES S1",
"STUDENT_ADDRES S2",
"STUDENT_CI TY",
"STUDENT_STATE" ,
"STUDENT_ZI P",
"STUDENT_HOME_P HONE",
"STUDENT_WORK_P HONE",
"STUDENT_HS_NAM E",
"STUDENT_EXTERN AL_ID",
"STUDENT_HS_GRA D_DT",
"STUDENT_FINANC IAL_AID",
"STUDENT_COMPAN Y_ID",
"STUDENT_LPROGR AM_ID",
"STUDENT_OTHER_ COMPANY_CAPTION ",
"STUDENT_CAMPUS _ID",
"STUDENT_ADVISO R_ID",
"STUDENT_PIN_ID ",
"STUDENT_WORK_E XTENSION",
"STUDENT_EXPECT ED_START_DT",
"STUDENT_SPONSO R_ID",
"STUDENT_LOAD_D T",
"STUDENT_DO_NOT _CALL",
"STUDENT_DO_NOT _MAIL",
"STUDENT_DO_NOT _EMAIL",
"STUDENT_VISA_E XCPT_SESSION_ID ",
"STUDENT_CREATE _DT",
"STUDENT_CREATE _TIME",
"STUDENT_TALISM A_ID",
"STUDENT_TALISM A_STATUS",
"STUDENT_TALISM A_SUBSTATUS",
"STUDENT_PEP_DT ",
"STUDENT_VOC_RE HAB",
"STUDENT_ADMREP _ID",
"STUDENT_MARKET CODE_ID"
FROM "dbo"."STUD ENT"
UNION
SELECT
STUDENT."STUDEN T_ID",
STUDENT."STUDEN T_APPLICATION_D T",
STUDENT."STUDEN T_ETHNIC_ID",
STUDENT."STUDEN T_VISA_TYPE",
STUDENT."STUDEN T_GENDER",
STUDENT."STUDEN T_MARITAL",
STUDENT."STUDEN T_BIRTH_DT",
STUDENT."STUDEN T_BIRTH_PLACE",
STUDENT."STUDEN T_LEAD_ID",
STUDENT."STUDEN T_INPUT_DT",
STUDENT."STUDEN T_FINAID_REQ",
STUDENT."STUDEN T_VA_STATUS",
STUDENT."STUDEN T_VA_DT",
STUDENT."STUDEN T_EMAIL",
STUDENT."STUDEN T_FAX",
STUDENT."STUDEN T_COUNTRY_ID",
STUDENT."STUDEN T_COUNTRY_CAPTI ON",
STUDENT_CHANGES ."RECORD_DT" ,
STUDENT."STUDEN T_LASTNAME",
STUDENT."STUDEN T_FIRSTNAME",
STUDENT."STUDEN T_MI",
STUDENT."STUDEN T_ADDRESS1",
STUDENT."STUDEN T_ADDRESS2",
STUDENT."STUDEN T_CITY",
STUDENT."STUDEN T_STATE",
STUDENT."STUDEN T_ZIP",
STUDENT."STUDEN T_HOME_PHONE",
STUDENT."STUDEN T_WORK_PHONE",
STUDENT."STUDEN T_HS_NAME",
STUDENT."STUDEN T_EXTERNAL_ID",
STUDENT."STUDEN T_HS_GRAD_DT",
STUDENT."STUDEN T_FINANCIAL_AID ",
STUDENT."STUDEN T_COMPANY_ID",
STUDENT."STUDEN T_LPROGRAM_ID",
STUDENT."STUDEN T_OTHER_COMPANY _CAPTION",
COALESCE(studen t_changes.stude nt_campus_id,st udent.student_c ampus_id)
AS STUDENT_CAMPUS_ ID,
STUDENT."STUDEN T_ADVISOR_ID",
STUDENT."STUDEN T_PIN_ID",
STUDENT."STUDEN T_WORK_EXTENSIO N",
STUDENT."STUDEN T_EXPECTED_STAR T_DT",
STUDENT."STUDEN T_SPONSOR_ID",
STUDENT."STUDEN T_LOAD_DT",
STUDENT."STUDEN T_DO_NOT_CALL",
STUDENT."STUDEN T_DO_NOT_MAIL",
STUDENT."STUDEN T_DO_NOT_EMAIL" ,
STUDENT."STUDEN T_VISA_EXCPT_SE SSION_ID",
STUDENT."STUDEN T_CREATE_DT",
STUDENT."STUDEN T_CREATE_TIME",
STUDENT."STUDEN T_TALISMA_ID",
STUDENT."STUDEN T_TALISMA_STATU S",
STUDENT."STUDEN T_TALISMA_SUBST ATUS",
STUDENT."STUDEN T_PEP_DT",
STUDENT."STUDEN T_VOC_REHAB",

COALESCE(studen t_changes.stude nt_ADMREP_id,st udent.student_A DMREP_id)
AS STUDENT_ADMREP_ ID,
STUDENT."STUDEN T_MARKETCODE_ID "
FROM
"dbo"."STUDENT" ,
"dbo"."STUDENT_ CHANGES"
WHERE
STUDENT.STUDENT _ID = STUDENT_CHANGES .STUDENT_ID

ref: DecisionStream Fact build Cognos SCD slowly changing dimensions

[rcamarda]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?

Follow up:
This works like a champ! A generalize form:

SELECT
<< current student fields. >>
-- brings in all students records
FROM current_records
UNION
SELECT
-- bring in all the student changes
<< 'static' fields>>,
COALESCE(change d_records.<fiel d>, current_records .<field>) AS <FIELD>
<<n fields>>
FROM changed_records
WHERE current_records .business_id=ch anged_records.b usiness_id

actual SQL I created: (I may need to look this up some day *grin*)

SELECT
"STUDENT_ID ",
"STUDENT_APPLIC ATION_DT",
"STUDENT_ETHNIC _ID",
"STUDENT_VISA_T YPE",
"STUDENT_GENDER ",
"STUDENT_MARITA L",
"STUDENT_BIRTH_ DT",
"STUDENT_BIRTH_ PLACE",
"STUDENT_LEAD_I D",
"STUDENT_INPUT_ DT",
"STUDENT_FINAID _REQ",
"STUDENT_VA_STA TUS",
"STUDENT_VA_DT" ,
"STUDENT_EMAIL" ,
"STUDENT_FA X",
"STUDENT_COUNTR Y_ID",
"STUDENT_COUNTR Y_CAPTION",
"RECORD_DT" ,
"STUDENT_LASTNA ME",
"STUDENT_FIRSTN AME",
"STUDENT_MI ",
"STUDENT_ADDRES S1",
"STUDENT_ADDRES S2",
"STUDENT_CI TY",
"STUDENT_STATE" ,
"STUDENT_ZI P",
"STUDENT_HOME_P HONE",
"STUDENT_WORK_P HONE",
"STUDENT_HS_NAM E",
"STUDENT_EXTERN AL_ID",
"STUDENT_HS_GRA D_DT",
"STUDENT_FINANC IAL_AID",
"STUDENT_COMPAN Y_ID",
"STUDENT_LPROGR AM_ID",
"STUDENT_OTHER_ COMPANY_CAPTION ",
"STUDENT_CAMPUS _ID",
"STUDENT_ADVISO R_ID",
"STUDENT_PIN_ID ",
"STUDENT_WORK_E XTENSION",
"STUDENT_EXPECT ED_START_DT",
"STUDENT_SPONSO R_ID",
"STUDENT_LOAD_D T",
"STUDENT_DO_NOT _CALL",
"STUDENT_DO_NOT _MAIL",
"STUDENT_DO_NOT _EMAIL",
"STUDENT_VISA_E XCPT_SESSION_ID ",
"STUDENT_CREATE _DT",
"STUDENT_CREATE _TIME",
"STUDENT_TALISM A_ID",
"STUDENT_TALISM A_STATUS",
"STUDENT_TALISM A_SUBSTATUS",
"STUDENT_PEP_DT ",
"STUDENT_VOC_RE HAB",
"STUDENT_ADMREP _ID",
"STUDENT_MARKET CODE_ID"
FROM "dbo"."STUD ENT"
UNION
SELECT
STUDENT."STUDEN T_ID",
STUDENT."STUDEN T_APPLICATION_D T",
STUDENT."STUDEN T_ETHNIC_ID",
STUDENT."STUDEN T_VISA_TYPE",
STUDENT."STUDEN T_GENDER",
STUDENT."STUDEN T_MARITAL",
STUDENT."STUDEN T_BIRTH_DT",
STUDENT."STUDEN T_BIRTH_PLACE",
STUDENT."STUDEN T_LEAD_ID",
STUDENT."STUDEN T_INPUT_DT",
STUDENT."STUDEN T_FINAID_REQ",
STUDENT."STUDEN T_VA_STATUS",
STUDENT."STUDEN T_VA_DT",
STUDENT."STUDEN T_EMAIL",
STUDENT."STUDEN T_FAX",
STUDENT."STUDEN T_COUNTRY_ID",
STUDENT."STUDEN T_COUNTRY_CAPTI ON",
STUDENT_CHANGES ."RECORD_DT" ,
STUDENT."STUDEN T_LASTNAME",
STUDENT."STUDEN T_FIRSTNAME",
STUDENT."STUDEN T_MI",
STUDENT."STUDEN T_ADDRESS1",
STUDENT."STUDEN T_ADDRESS2",
STUDENT."STUDEN T_CITY",
STUDENT."STUDEN T_STATE",
STUDENT."STUDEN T_ZIP",
STUDENT."STUDEN T_HOME_PHONE",
STUDENT."STUDEN T_WORK_PHONE",
STUDENT."STUDEN T_HS_NAME",
STUDENT."STUDEN T_EXTERNAL_ID",
STUDENT."STUDEN T_HS_GRAD_DT",
STUDENT."STUDEN T_FINANCIAL_AID ",
STUDENT."STUDEN T_COMPANY_ID",
STUDENT."STUDEN T_LPROGRAM_ID",
STUDENT."STUDEN T_OTHER_COMPANY _CAPTION",
COALESCE(studen t_changes.stude nt_campus_id,st udent.student_c ampus_id)
AS STUDENT_CAMPUS_ ID,
STUDENT."STUDEN T_ADVISOR_ID",
STUDENT."STUDEN T_PIN_ID",
STUDENT."STUDEN T_WORK_EXTENSIO N",
STUDENT."STUDEN T_EXPECTED_STAR T_DT",
STUDENT."STUDEN T_SPONSOR_ID",
STUDENT."STUDEN T_LOAD_DT",
STUDENT."STUDEN T_DO_NOT_CALL",
STUDENT."STUDEN T_DO_NOT_MAIL",
STUDENT."STUDEN T_DO_NOT_EMAIL" ,
STUDENT."STUDEN T_VISA_EXCPT_SE SSION_ID",
STUDENT."STUDEN T_CREATE_DT",
STUDENT."STUDEN T_CREATE_TIME",
STUDENT."STUDEN T_TALISMA_ID",
STUDENT."STUDEN T_TALISMA_STATU S",
STUDENT."STUDEN T_TALISMA_SUBST ATUS",
STUDENT."STUDEN T_PEP_DT",
STUDENT."STUDEN T_VOC_REHAB",

COALESCE(studen t_changes.stude nt_ADMREP_id,st udent.student_A DMREP_id)
AS STUDENT_ADMREP_ ID,
STUDENT."STUDEN T_MARKETCODE_ID "
FROM
"dbo"."STUDENT" ,
"dbo"."STUDENT_ CHANGES"
WHERE
STUDENT.STUDENT _ID = STUDENT_CHANGES .STUDENT_ID

ref: DecisionStream Fact build Cognos SCD slowly changing dimensions