Post reply

code help needed

  • March 5, 2011 at 8:31 am

    #236057

    Below is the result i get for the query below assuming a matrix report. Only the codes that have counts are displayed below. "+"signs are only to give some spaces they don't mean anything.

    ++++ E 3 w w5 ss TS 3S

    BCST 4 5 6

    INST 3 4 6 5 8

    MAIL 6

    SELECT count(B.TRANS_TYPE) STARTS, B.PROD_CODE pcode, C.GROUP_CODE DMcode

    FROM UNICA.DSI_SUBS_STOP_START_DRW B, UNICA.DSI_OFFER_SOURCE C

    WHERE B.OFFER_SOURCE = C.CODE AND B.PROD_CODE is not null AND C.GROUP_CODE is not null

    AND (B.TRANS_TYPE IN ('3')) AND C.GROUP_CODE IN ('BCST','FLYR','INST','MAIL')

    AND B.PROD_CODE in ('E','3','W','W5','SS','S','4','TS','W2','WS','3S')

    GROUP BY B.PROD_CODE , C.GROUP_CODE

    I want my result like this below. I want all of the DMcode and Pcode to print by force. even though their count is zero.

    ++++ E 3 w w5 ss 4 TS W2 WS 3S

    BCST 4 5 6

    FLYR

    INST 3 4 6 5 8

    MAIL 6

    I need your help with the sql. Some one please help me thanks. I really need a good idea. Thanks.

    Edited by: 831075 on Mar 4, 2011 3:20 PM

  • March 6, 2011 at 10:35 pm

    #1295180

    Someone help. Thank you.

  • March 7, 2011 at 3:39 am

    #1295233

    One reason you might not have an answer by now is the missing information.

    Please provide table def and ready to sample data as described in the first link in my signature. Also, please include your expected result based on the sample.


    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]

  • March 7, 2011 at 9:20 am

    #1295395

    http://imageupload.org/?di=1312995099649

    Please see the image above i want the output like this. I hope i made myself clear. I just need an idea to print all the codes even if there is no count. Thank you.

  • March 7, 2011 at 9:49 am

    #1295424

    varunkum (3/7/2011)

    http://imageupload.org/?di=1312995099649

    Please see the image above i want the output like this. I hope i made myself clear. I just need an idea to print all the codes even if there is no count. Thank you.

    Most people like to test their suggestions in the form of code. Such images are of no real help.

    Be that as it may, you need to create an aux table that contains each Group code, perhaps as a CTE, and then do an outer join to that aux table.

    --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)

Viewing 5 posts - 1 through 4 (of 4 total)

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