DB2 - L

 View Only

  • 1.  SQL query issue

    Posted Nov 12, 2021 07:58 AM
    Hi -

    We have a temp table that is loaded with below sample data and if we see data is duplicated .


    We need the output as below as 


    is there any way we can get that output ?  if there is a different product group then the rows should not get mixed up 

    Regards
    Ron T


    ------------------------------
    RonThomasXYZ Corp
    ------------------------------


  • 2.  RE: SQL query issue

    Posted Nov 12, 2021 09:29 AM
    Not a SQL guru, but it seems like SELECT DISTINCT would be your friend in this endeavor.



    ------------------------------
    MarkWieczorkowski...
    ------------------------------

    Original Message


  • 3.  RE: SQL query issue

    Posted Nov 12, 2021 09:45 AM
    mark - In that case we will get as below


    that has  disturbed the orginal product structure . I am looking at a way to pull the unique product groups with out disturbing the product structure.

    Thanks
    Ron T

    ------------------------------
    RonThomasXYZ Corp
    ------------------------------

    Original Message


  • 4.  RE: SQL query issue

    Posted Nov 12, 2021 11:04 AM
    Wet fish, ladies' nightwear, and a Sony PSP...I'm a little disturbed too. :) (joking)

    I'm not sure I completely understand your requirements, unfortunately. Sorry I can't be of more help.


    ------------------------------
    MarkWieczorkowski...
    ------------------------------

    Original Message


  • 5.  RE: SQL query issue

    Posted Nov 12, 2021 11:30 AM
    Ha ha .. Mark

    Here is data , on the left side is the input . The Right side is how the data is to be expected . Need to keep the sequence in order .

    In the input there is 4 groups , output should only have 2 groups as the other 2 are duplciates 



    Regards
    Ron T

    ------------------------------
    RonThomasXYZ Corp
    ------------------------------

    Original Message


  • 6.  RE: SQL query issue

    Posted Nov 12, 2021 06:23 PM
    So you want to know if something is duplicated:

    SELECT ... COUNT(*)
    GROUP BY ...
    HAVING COUNT(*) > 1
    .

    However, if you have two items:
    1_2_3_4_5 item 1
    6_2_3_4_5 item2

    then it appears that you will also get two of each of:
    2_3_4_5
    3_4_5
    4_5
    5

    Are these duplicates - of which you want only one?

    It appears that your records have some inherent ordering / grouping. Which isn't displayed.

    James Campbell


    On 12 Nov 2021 at 16:30, Ron Thomas via International wrote:

    > Ha ha .. Mark
    >
    > Here is data , on the left side is the input . The Right side is how the data is to be expected . Need to keep the sequence in order .
    >
    > In the input there is 4 groups , output should only have 2 groups as the other 2 are duplciates
    >
    >
    >
    > Regards
    > Ron T
    >
    > ------------------------------
    > RonThomasXYZ Corp
    > ------------------------------
    > -------------------------------------------
    > Original Message:
    > Sent: Nov 12, 2021 11:03 AM
    > From: Mark Wieczorkowski
    > Subject: SQL query issue
    >
    > Wet fish, ladies' nightwear, and a Sony PSP...I'm a little disturbed too. :) (joking)
    >
    > I'm not sure I completely understand your requirements, unfortunately. Sorry I can't be of more help.
    >
    >
    > ------------------------------
    > MarkWieczorkowski...
    > ------------------------------
    >
    > Original Message:
    > Sent: Nov 12, 2021 09:44 AM
    > From: Ron Thomas
    > Subject: SQL query issue
    >
    > mark - In that case we will get as below
    >
    >
    > that has disturbed the orginal product structure . I am looking at a way to pull the unique product groups with out disturbing the product structure.
    >
    > Thanks
    > Ron T
    >
    > ------------------------------
    > RonThomasXYZ Corp
    >
    > Original Message:
    > Sent: Nov 12, 2021 09:29 AM
    > From: Mark Wieczorkowski
    > Subject: SQL query issue
    >
    > Not a SQL guru, but it seems like SELECT DISTINCT would be your friend in this endeavor.
    >
    >
    >
    > ------------------------------
    > MarkWieczorkowski...
    >
    > Original Message:
    > Sent: Nov 12, 2021 07:57 AM
    > From: Ron Thomas
    > Subject: SQL query issue
    >
    > Hi -
    >
    > We have a temp table that is loaded with below sample data and if we see data is duplicated .
    >
    >
    > We need the output as below as
    >
    >
    > is there any way we can get that output ? if there is a different product group then the rows should not get mixed up
    >
    > Regards
    > Ron T
    >
    >
    > ------------------------------
    > RonThomasXYZ Corp
    > ------------------------------
    >
    >
    > Reply to Sender : https://www.idug.org/eGroups/PostReply/?GroupId=253&MID=172949&SenderKey=bca95c2c-5578-49a7-9cc6-d2dbe3896457
    >
    > Reply to Discussion : https://www.idug.org/eGroups/PostReply/?GroupId=253&MID=172949
    >
    >
    >
    > You are subscribed to "DB2 - L" as jacampbellaus@gmail.com. To change your subscriptions, go to http://www.idug.org/preferences?section=Subscriptions. To unsubscribe from this community discussion, go to http://www.idug.org/HigherLogic/eGroups/Unsubscribe.aspx?UserKey=0e114382-b62d-4df5-8bae-216292fc0091&sKey=KeyRemoved&GroupKey=37484667-8556-4529-8eb2-a1404f7c5c5f.



    --
    This email has been checked for viruses by AVG.
    https://www.avg.com


    Original Message


  • 7.  RE: SQL query issue

    Posted Nov 12, 2021 01:01 PM
    "ORIENTAL INGREDIENTS" is not in your original post, so why do they show up in your second post?

    ------------------------------
    Jørn Thyssen
    Rocket Software
    2021 IBM Champion
    ------------------------------

    Original Message


  • 8.  RE: SQL query issue

    Posted Nov 13, 2021 06:15 AM
    Hi Ron
    I am also not quite sure, if I understand everything. Which order do you want to preserve? The physical one? That would be very access path dependent. At least you need a tablespace scan, but even then, it might vary.
    If you don't want to touch the ordering a subquery might help, something like:
    SELECT ... FROM TEMP TABLE A
    WHERE NOT EXISTS
    (SELECT 1 FROM TEMP TABLE B
    WHERE RID(A) less than (I can't find the less than Symbol on my mobile phon Keyboard) 
    RID(B) AND other columns equal)
    If there is no duplicate the NOT EXISTS is true and for the First of any duplicate it will also be true, but for others it will be false.

    ------------------------------
    WalterJanißenRetired
    ------------------------------

    Original Message


  • 9.  RE: SQL query issue

    Posted Nov 15, 2021 07:34 AM
    SELECT DISTINCT PRODUCTGROUPID, PARENTGROUPID, DESCRIPTION
    FROM TABLE
    ORDER BY PRODUCTGROUPID, PARENTGROUPID, DESCRIPTION
    ;

    Does this work? You can use SELECT DISTINCT with multiple columns to get unique values across columns.

    ------------------------------
    ChrisLoudenSocial Security Administration
    ------------------------------

    Original Message


  • 10.  RE: SQL query issue

    Posted Nov 17, 2021 07:35 AM
    Thanks all for help .  we decided to build this structure using a job 

    Regards
    Ron T

    ------------------------------
    RonThomasXYZ Corp
    ------------------------------

    Original Message