Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2017
    Posts
    12

    Question Unanswered: How to get duplicate substring in DB2 sql

    How to get duplicate sub string in DB2 sql my scenario is that, I have one column

    col1

    | ABC_1 |

    | ABC_3 |

    | WXY_5 |

    I want to get only Duplicate sub string data with there addition of remaining sub values

    COLUMNS->
    String addition of sub string values

    | ABC | | 4 |

    | WXY | | 5 |

    4 is the addition of duplicate sub string value for that I have used query is that SUM(CAST(SUBSTR(a.ref1, LOCATE('-',a.ref1)+1) as DECIMAL)) as output

    but how to do it ?

    Please give me a any suggestion ?

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    306
    Provided Answers: 46
    Try this:
    Code:
    select 
      substr(ref, 1, locate('_', ref)-1) col 
    , sum(int(substr(ref, locate('_', ref)+1))) s
    from table(values
      'ABC_1'
    , 'ABC_3'
    , 'WXY_5'
    ) a(ref)
    group by substr(ref, 1, locate('_', ref)-1);
    Regards,
    Mark.

  3. #3
    Join Date
    Oct 2017
    Posts
    12

    Question

    select
    substr(a.ref1, 1, locate('_', a.ref1)-1) as col ,
    sum(int(substr(a.ref1, locate('_', a.ref1)+1))) as s

    from



    (SELECT DISTINCT T1.PROJECT_NAME,
    T1.REFERENCE_ID,
    T1.NAME AS URL1_title,
    T1.URL AS URL1,
    T1.REQUEST_TYPE,

    T2.VAL AS owner1,

    T2.VAL ||'-'|| cast(T3.VAL AS CHAR(10)) AS ref1 ,
    T4.VAL AS owner2,
    T4.VAL ||'-'|| cast(T5.VAL AS CHAR(10)) AS ref2 ,
    T6.VAL AS owner3,
    T6.VAL ||'-'|| cast(T7.VAL AS CHAR(10)) AS ref3
    FROM RIDW.VW_REQUEST T1
    LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T2
    ON T2.REQUEST_ID=T1.REQUEST_ID AND T2.NAME='owner_1'
    LEFT OUTER JOIN RICALM.VW_RQST_DECIMAL_EXT T3
    ON T3.REQUEST_ID=T1.REQUEST_ID AND T3.NAME='resource1'
    LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T4
    ON T4.REQUEST_ID=T1.REQUEST_ID AND T4.NAME='owner_2'
    LEFT OUTER JOIN RICALM.VW_RQST_DECIMAL_EXT T5
    ON T5.REQUEST_ID=T1.REQUEST_ID AND T5.NAME='resource2'
    LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T6
    ON T6.REQUEST_ID=T1.REQUEST_ID AND T6.NAME='owner_3'
    LEFT OUTER JOIN RICALM.VW_RQST_DECIMAL_EXT T7
    ON T7.REQUEST_ID=T1.REQUEST_ID AND T7.NAME='resource'
    WHERE T1.PROJECT_ID = 0 AND
    ( T1.REQUEST_TYPE = 'Task'
    ) AND
    (T1.ISSOFTDELETED = 0) AND
    (T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL) ORDER BY T2.VAL asc,
    T4.VAL asc,
    T6.VAL asc)a

    group by substr(a.ref1, 1, locate('_', a.ref1)-1)

    Here is my modified query

    I got an error as An unexpected response was received from the data source: The second or third argument of the SUBSTR function is out of range.
    Last edited by amrutaraut; 10-04-17 at 06:11.

  4. #4
    Join Date
    Oct 2017
    Posts
    12

    Red face

    error solved .Thank you so much

  5. #5
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    306
    Provided Answers: 46
    Your initial example contains some discrepancy: the symbol-separator is '_' in the data example, but you try to locate '-' using function LOCATE.
    As far as I see, your data contain '-' as a separator. So, try to locate '-' instead of '_' in all locate function invocations.
    Regards,
    Mark.

  6. #6
    Join Date
    Oct 2017
    Posts
    12

    Question

    Hi Mark,
    I have one more query,how to add same condition on two or more another columns,
    i.e how to add two or more GROUP BY for two or more columns in above DB2 SQL query?

  7. #7
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    306
    Provided Answers: 46
    This depends on what you want to get, for example, for this query:
    Code:
    select * 
    from table(values
      ('AAA-1', 'CCC-4')
    , ('AAA-3', 'AAA-2')
    , ('CCC-5', 'DDD-3')
    ) a(ref1, ref2);
    Do you want to get the following?
    'AAA', 6
    'CCC', 9
    'DDD', 3
    If not, then provide desired output for this example,
    Regards,
    Mark.

  8. #8
    Join Date
    Oct 2017
    Posts
    12

    Question

    Hi Mark,
    Yes i want exactly same
    actually there are three column are there owner 1 ,owner 2, owner 3 with duplicates owner name,
    I want unique owners name in separate column and addition of there sub string values in another column

    Regards,
    Amruta

  9. #9
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    306
    Provided Answers: 46
    Hi Amruta,

    Try this:
    Code:
    select 
      substr(ref, 1, locate('-', ref)-1) col 
    , sum(int(substr(ref, locate('-', ref)+1))) s
    from (
    select 
    case c.n 
      when 1 then a.ref1 
      when 2 then a.ref2 
      when 3 then a.ref3 
    end ref
    from table(values
      ('AAA-1', 'CCC-4', 'DDD-4')
    , ('AAA-3', 'AAA-2', 'CCC-1')
    , ('CCC-5', 'DDD-3', 'EEE-1')
    ) a(ref1, ref2, ref3)
    , table (values 1, 2, 3) c(n)
    )
    group by substr(ref, 1, locate('-', ref)-1);
    Regards,
    Mark.

  10. #10
    Join Date
    Oct 2017
    Posts
    12

    Question

    Hi Mark
    thanks for correct me
    basically i am not using any table for query,this query is automatically generated in IBM jazz reporting service,and I am trying to customize it,So i am confuse to how can I do it in created query
    Last edited by amrutaraut; 10-05-17 at 04:46.

  11. #11
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    306
    Provided Answers: 46
    I thought it should be obvious...
    Code:
    select 
      substr(ref, 1, locate('-', ref)-1) col 
    , sum(int(substr(ref, locate('-', ref)+1))) s
    from 
    (
    select
    case c.n 
      when 1 then a.ref1 
      when 2 then a.ref2 
      when 3 then a.ref3 
    end ref
    from 
    (
    -- Start of your original query
    SELECT DISTINCT T1.PROJECT_NAME,
    ...
    T2.VAL ||'-'|| cast(T3.VAL AS CHAR(10)) AS ref1,
    T4.VAL ||'-'|| cast(T5.VAL AS CHAR(10)) AS ref2,
    T6.VAL ||'-'|| cast(T7.VAL AS CHAR(10)) AS ref3,
    ...
    FROM ...
    -- End of your original query (exclude ORDER BY)
    ) a
    , table (values 1, 2, 3) c(n)
    )
    group by substr(ref, 1, locate('-', ref)-1);
    Regards,
    Mark.

  12. #12
    Join Date
    Oct 2017
    Posts
    12

    Unhappy

    Hi Mark

    It gives me again error on case c.n as like(CRRGW5628E An java.sql.SQLSyntaxErrorException error occurred when validating the input SQL string, caused by Syntax error: Encountered "c" at line 7, column 6)

    I really stuck in this query

    Thanks and Regards,
    Amruta
    Last edited by amrutaraut; 10-05-17 at 06:15.

  13. #13
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    306
    Provided Answers: 46
    What's the full text of your query?
    Regards,
    Mark.

  14. #14
    Join Date
    Oct 2017
    Posts
    12

    Unhappy

    select
    substr(d.ref, 1, locate('_', d.ref)-1) col
    , sum(int(substr(d.ref, locate('_', d.ref)+1))) s
    from
    (
    select
    case c.n
    when 1 then a.ref1
    when 2 then a.ref2
    when 3 then a.ref3
    end ref
    from
    (
    SELECT DISTINCT T1.PROJECT_NAME,
    T1.REFERENCE_ID AS REF,
    T1.NAME AS URL1_title,
    T1.URL AS URL1,
    T1.REQUEST_TYPE,

    T2.VAL AS owner1,

    T2.VAL ||'_'|| cast(T3.VAL AS CHAR(10)) AS ref1 ,
    T4.VAL AS owner2,
    T4.VAL ||'_'|| cast(T5.VAL AS CHAR(10)) AS ref2 ,
    T6.VAL AS owner3,
    T6.VAL ||'_'|| cast(T7.VAL AS CHAR(10)) AS ref3
    FROM RIDW.VW_REQUEST T1
    LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T2
    ON T2.REQUEST_ID=T1.REQUEST_ID AND T2.NAME='owner_1'
    LEFT OUTER JOIN RICALM.VW_RQST_DECIMAL_EXT T3
    ON T3.REQUEST_ID=T1.REQUEST_ID AND T3.NAME='resource1'
    LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T4
    ON T4.REQUEST_ID=T1.REQUEST_ID AND T4.NAME='owner_2'
    LEFT OUTER JOIN RICALM.VW_RQST_DECIMAL_EXT T5
    ON T5.REQUEST_ID=T1.REQUEST_ID AND T5.NAME='resource2'
    LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T6
    ON T6.REQUEST_ID=T1.REQUEST_ID AND T6.NAME='owner_3'
    LEFT OUTER JOIN RICALM.VW_RQST_DECIMAL_EXT T7
    ON T7.REQUEST_ID=T1.REQUEST_ID AND T7.NAME='resource'
    WHERE T1.PROJECT_ID = 0 AND
    ( T1.REQUEST_TYPE = 'Task'
    ) AND
    (T1.ISSOFTDELETED = 0) AND
    (T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL)
    ) a, table (values 1, 2, 3) c(n)
    )d
    group by substr(d.ref, 1, locate('_', d.ref)-1)

    I done just you said,then it gives me an error on group by statement then I have added d reference variable then it gives me error on case c.n

    without d reference i dot error like(CRRGW5628E An com.foundationdb.sql.parser.SQLParserException error occurred when validating the input SQL string, caused by Encountered " "group" "group "" at line 47, column 1. Was expecting one of: "as")
    Last edited by amrutaraut; 10-05-17 at 07:21.

  15. #15
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    306
    Provided Answers: 46
    What's your db2 version?
    Is it possible to run this statement with db2 CLP?
    Or at least get exact db2 SQLCODE and full error message text?
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •