Post reply

Stored Procedure

  • November 4, 2009 at 11:30 am

    #156374

    This is the issue i need to solve

    Can ayone one explain what this means and how to change the code

    I attached the code also

    1. Fix DMS process to update Derived ODS table for LAST_SPONSOR_DATE and FIRST_SPONSOR_DATE

    --Our ETL calls a stored procedure DWSODS01.DWP11200_IMC_DMS_MAIN

    You will need to update the second merge statement into DWSODS01.DWT00102_DERV_IMC_MISC.

    FIRST_SPON_DT_KEY_NO = CASE WHEN (load.FIRST_SPON_DT_KEY_NO = 19000101 or (load.INMKT_FIRST_SPON_DT_KEY_NO is null)) then stg.INMKT_FIRST_SPON_DT_KEY_NO else load.FIRST_SPON_DT_KEY_NO END

    LAST_SPON_DT_KEY_NO = stg.INMKT_LAST_SPON_DT_KEY_NO

    --We will need you to also write a script that will fix/update the data in DWSODS01.DWT00102_DERV_IMC_MISC. I believe our initial load scripts loaded this table correctly back in June when we created it, but our new etl wasn't maintaining it for legacy (DMS) data feeds. The data is correct in the DWSODS01.DWT00002_IMC_DMS_MAIN table. You can use the following logic in your update statement:

    LAST_SPON_DT_KEY_NO =DWT00002_IMC_DMS_MAIN.INMKT_LAST_SPON_DT_KEY_NO

    FIRST_SPON_DT_KEY_NO =CASE WHEN (DWT00102_DERV_IMC_MISC.INMKT_FIRST_SPON_DT_KEY_NO = 19000101 or (DWT00102_DERV_IMC_MISC.INMKT_FIRST_SPON_DT_KEY_NO is null)) then DWT00002_IMC_DMS_MAIN.INMKT_FIRST_SPON_DT_KEY_NO else DWT00102_DERV_IMC_MISC.INMKT_FIRST_SPON_DT_KEY_NO END

    All of this occurs in our DWSODS01 schema on our Oracle database.

  • November 4, 2009 at 11:40 am

    #1074954

    Oracle database? Is this an Oracle or SQL Server stored procedure?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 4, 2009 at 11:44 am

    #1074956

    I am not sure as i am new to both.

    I think it is oracle.

    If you know can you help me?

  • November 4, 2009 at 1:11 pm

    #1075021

    You are new to both Oracle and SQL Server yet someone expects you to be responsible for these types of changes? Do you have a background in RDBMS's or in ANSI SQL at all?

    This is a SQL Server forum so you'll likely not get help on your Oracle procedure here.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • November 4, 2009 at 7:29 pm

    #1075114

    rahulsony111 (11/4/2009)

    I think it is oracle.

    You think? Which database engine is the stored procedure part of? Shouldn't be a hard thing to tell.

    If you know can you help me?

    If I do your work do I get your salary? We're happy to help, but the emphasis there is 'help', not to do the entire thing for you.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 5, 2009 at 12:22 am

    #1075187

    Looking at the attached SP this is a Oracle Db

    ref SYSDATE and RAISE_APPLICATION_ERROR

    are not SQL functions

  • November 5, 2009 at 7:47 am

    #1075341

    yes it is oracle DB

    Can anyone help me with this?

  • November 5, 2009 at 8:57 am

    #1075417

    This is a SQL Server forum. This is probably not the best place to ask. Try www.dbforums.com

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 5, 2009 at 11:30 am

    #1075529

    Thanks for that forum man.

  • November 5, 2009 at 9:20 pm

    #1075699

    AnzioBake (11/5/2009)

    Looking at the attached SP this is a Oracle Db

    ref SYSDATE and RAISE_APPLICATION_ERROR

    are not SQL functions

    Heh... AAAAAAHHHHHHHGGGGGG!!!!! I LOOKED, I LOOKED. IT IS ORACLE!!!!! MY EYES!!! MY EYES!!!! :-P:-D:-);-):hehe:

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply