Post reply

Stored procedure Logic

  • April 16, 2010 at 12:34 pm

    #386723

    Hi All,

    I am not so good at SQL coding and need your help in writing a SP which does the following,

    I have to copy values from staging table A to Target table B and the sample format is as follows,

    I have staging table with the following format along with sample values,

    Table A:

    ColumnNames Account, SeqNo, ColA1, ColA2, ColA3, ColA11 ..... ColB1, ColB1, ColB2, ColB3 .....ColB11, ColZ1, ColZ2, ColZ3 ....ColZ11

    Values 8888, 1, R, E, R, NULL ......... 5, 10, 15, 20

    Values 8888, 2, P, NULL ..... .............. 15, 50, 5, 02

    Values 9999, 1, E, R, E, E , NULL... 25, 20, 10, 20

    Table B:

    ColumnNames: Account, A1, A2, A3, A4, A5, B1, B2, B3, B4, B5 .........Z1, Z2, Z3, Z4 , Z5

    I have to copy rows from staging table to target with the following transformation,

    For a combination of acount and Seqno,

    If ColA1 = R or P, I have to insert target table columns as follows,

    Account, A1 = ColA1 , A2 = ColB1, A3 = ColC1 ..... A5 = ColE1

    Then

    If ColA2 = E, I have to skip that set of columns and go to next column like ColA3 and if it is R or P I have to insert values as follows,

    Account, B1 = ColA3, B2= ColB3, B3= COlC3 .. B5 = ColE5

    On the same lines I have to verify each column until I reaches col11 for a combinattion of Account and seqno and then have to go for the same account but next seq number on the table.

    The bottom line is,

    If Col1 = R or P values, I have to insert values accordingly or col1 is E, I have to check the next column until I processed all the columns i.e., upto col 11 and when any col = Null I have to move on to next account number and continue the same process.

    If you have any questions, please let me know.

    I know description is not straight forward. Apologies:)

    Thanks,

    -Mohsin

  • April 16, 2010 at 12:37 pm

    #1153065

    u saying u need table created? record inserted? A select from a table?

  • April 16, 2010 at 1:13 pm

    #1153101

    Copying from stage table to target table with conditions as specified above.

    Basically select from stage and insert into target.

    -Mohsin

  • April 16, 2010 at 1:25 pm

    #1153114

    Would you mind providing a sample Excel file we can use (including the OPENROWSET statement you're using) and your expected output based on that sample data?

    Also, would you mind sharing waht you've tried so far and where you get stuck?


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • April 16, 2010 at 1:42 pm

    #1153123

    Mohammed Mohsin-392707 (4/16/2010)I am not so good at SQL coding and need your help in writing a SP

    Are you familiar with "insert into ... select ... from ... where ..." syntax?

    if not, check here for details http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/

    by the way, how did you manage to put yourself in such an unconfortable position?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • April 16, 2010 at 1:54 pm

    #1153127

    I have uploaded source table script (changeInfo.txt) and source table data in (changeInfo_text.txt) along with target table structures.

    I am not understanding logic to start from.

    Thanks,

    -Mohsin

  • April 16, 2010 at 2:02 pm

    #1153132

    Hi Paul,

    Its not straight forward to use insert into ... Select clause statements.

    I think you are not clear with the requirement. I have uploaded source and target table scripts along with source data.

    For each account, and for each column CHGF1, CHGF2, CHGF3 ...CHGF11 I have to check if the value,

    1) R/P insert combination of columns into target OR

    2) E, skil this column, and go to next column until you are done with CHGF11, OR

    3) NULL, move on to next account.

    Thanks,

    -Mohsin

  • April 16, 2010 at 2:09 pm

    #1153135

    So the source and target column mapping would be as follows,

    CHGF1 = R or P

    Then mapping is

    ACT03_ARM_Change_Type_1 = CHGF1

    ACT03_ARM_Change_Code_1 = 1

    ACT03_ARM_Change_Effective_Dt_1 = CHGD1

    ACT03_ARM_Change_Amount_1 = CHGA1

    ACT03_ARM_Change_Rate_1 = CHGA1/10

    and IF CHGF2 = R or P

    ACT03_ARM_Change_Type_2 = CHGF2

    ACT03_ARM_Change_Code_2 = 1

    ACT03_ARM_Change_Effective_Dt_2 = CHGD2

    ACT03_ARM_Change_Amount_2 = CHGA2

    ACT03_ARM_Change_Rate_2 = CHGA2/10

    And CHGF3 = E

    Skip this columna and go to CHGF4

    If CHGF4 = R or P

    IF ACT03_ARM_Change_Type_3 = CHGF4

    ACT03_ARM_Change_Code_3 = 1

    ACT03_ARM_Change_Effective_Dt_3 = CHGD4

    ACT03_ARM_Change_Amount_3 = CHGA4

    ACT03_ARM_Change_Rate_3 = CHGA4/10

    and so on until you processed all 11 columns CHGF11 or you get NULL value in which case you will go to next account.

    Hope this clarifies the requirement.

    Thanks.

  • April 16, 2010 at 2:49 pm

    #1153149

    I would like to suggest that you read the first article I reference in my signature block regarding asking for assistance. It will walk you through the steps you need to follow to create the necessary information that will allow individuals to quickly help you. In addtion to the DDL for the tables and sample data, you shold alos post the expected results based on the sample data you provide. This will give us something to compare our output to and see if we have met your requirements.

    The benefit of doing this is that in return you get tested code.

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

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