Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: string function locate

    Hi,

    DB2 V9.1 Z/OS

    Please find the query and help me out to get expected result set

    I was trying below query but giving wrong result set
    Code:
    SELECT
     T1.PL_PLA
    ,T1.TER_KEYS
    ,T1.DES_DET
    
    FROM BASE_TABLE T1
    INNER JOIN
    FIRST TABLE T2
    ON
    T1.PL_PLA=T2.PL_PLA
    AND LOCATE(T1.TER_KEYS || ',' , T2.TER_KEY || ',')>0
    
    WHERE
    
    T2.PL_PLA='AAA'
    
    
    
    
    BASE TABLE
    
    PL_PLA TER_KEYS         DES_DET
    AAA     1234,2345,5432    VAL
    AAA      1234             DEN
    AAA      3457,1234,6874   UNI
    AAA      6789,5744        XXX
    
    FIRST TABLE
    
    PL_PLA FIRST_NO TER_KEY
    AAA     111       1234
    
    EXPECTED RESULT SET
    
    PL_PLA TER_KEYS         DES_DET
    AAA     1234,2345,5432    VAL
    AAA      1234             DEN
    AAA      3457,1234,6874   UNI

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    why do you put the condition in join condition ?
    it can be placed in where predicate
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    I was tried to put in WHERE condition but giving below error

    SQLSTATE=42601

    SQL0104N An unexpected token "<END-OF-STATEMENT>" was found following "". Expected tokens may include: "AT MICROSECONDS MICROSECOND SECONDS SECOND MINUTES".

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    FROM BASE_TABLE T1
    INNER JOIN
    FIRST TABLE T2
    if this is a copyscreen : is first table not connected with _
    always publish the complete executed query (no re-typing) with error message
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Sep 2011
    Posts
    220
    sorry,it is a typo mistake

    the table name would be FIRST_TABLE

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    always publish the complete executed query (no re-typing) with error message
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Sep 2011
    Posts
    220
    SELECT
    T1.PL_PLA
    ,T1.TER_KEYS
    ,T1.DES_DET

    FROM DSNDEV.BASE_TABLE T1

    INNER JOIN

    DSNDEV.FIRST_TABLE T2
    ON
    T1.PL_PLA=T2.PL_PLA

    WHERE

    T1.PL_PLA = 'AAA'
    AND (LOCATE(T2.TER_KEY || ',' , T1.TER_KEYS || ',')>0;
    ------------------------------------------------------------------------------
    SQL0104N An unexpected token "<END-OF-STATEMENT>" was found following "".
    Expected tokens may include: "AT MICROSECONDS MICROSECOND SECONDS SECOND
    MINUTES". SQLSTATE=42601

    SQL0104N An unexpected token "<END-OF-STATEMENT>" was found following "". Expected tokens may include: "AT MICROSECONDS MICROSECOND SECONDS SECOND MINUTES".

    Explanation:

    A syntax error in the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure was detected at the specified token
    following the text "<text>". The "<text>" field indicates the 20
    characters of the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

    As an aid, a partial list of valid tokens is provided in the SQLERRM
    field of the SQLCA as "<token-list>". This list assumes the statement is
    correct to that point.

    This message can be returned when text is passed to the command line
    processor (CLP) in command mode and the text contains special characters
    that are interpreted by the operating system shell, such as single or
    double quotes, which are not identified with an escape character.

    The statement cannot be processed.

    User response:

    Respond to this error in one of the following ways:

    * Examine and correct the statement in the area of the specified token.
    * If you are using the CLP in command mode and there are any special
    characters, such as quotes, in the command, use an escape character,
    such as the backslash character, to cause the operating system shell
    to not take any special action for those special characters. You
    could also issue the statement using CLP in interactive mode or batch
    mode to avoid any processing of special characters by the operating
    system shell.

    sqlcode: -104

    sqlstate: 42601


    Related information
    Command line processor features

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You don't say what your unexpected results are, so its hard to tell you exactly what's wrong. The placement will not affect resultset, but it should be in the ON clause. Its just best habit, as if you get in the habit of putting some conditions in the ON clause and others in the WHERE, you will do it with a left outer join, which causes performance issues and incorrect results. I question why you are concatenating a ',' into the clause as it is superfluous. As for your -104, looks like you are missing a closed paren. The entire SQLCA helps as it puts the position number of the syntax error. This was just mentioned recently on the DB2-L Listserv, you can look it up on idug.org.
    Dave

  9. #9
    Join Date
    Sep 2011
    Posts
    220
    below is the result set which i was getting after executing the query

    Code:
    ACTUAL RESULT SET GETTING
    
    
    PL_PLA  TER_KEYS         DES_DET
    AAA     1234,2345,5432    VAL
    AAA      3457,1234,6874   UNI 
    
    
    
    BUT EXPECTED RESULT SET IS
    
    EXPECTED RESULT SET
    
    PL_PLA TER_KEYS         DES_DET
    AAA     1234,2345,5432    VAL
    AAA      1234             DEN
    AAA      3457,1234,6874   UNI

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Billa007 View Post
    SELECT
    T1.PL_PLA
    ,T1.TER_KEYS
    ,T1.DES_DET

    FROM DSNDEV.BASE_TABLE T1

    INNER JOIN

    DSNDEV.FIRST_TABLE T2
    ON
    T1.PL_PLA=T2.PL_PLA

    WHERE

    T1.PL_PLA = 'AAA'
    AND (LOCATE(T2.TER_KEY || ',' , T1.TER_KEYS || ',')>0;
    ------------------------------------------------------------------------------
    SQL0104N An unexpected token "<END-OF-STATEMENT>" was found following "".
    Expected tokens may include: "AT MICROSECONDS MICROSECOND SECONDS SECOND
    MINUTES". SQLSTATE=42601

    ...
    There was not a corresponding right parenthesis with the left parenthesis (

    And, what was the datatype of TER_KEY in FIRST TABLE?
    Aren't there trailing blanks?
    Last edited by tonkuma; 09-25-13 at 04:06.

  11. #11
    Join Date
    Sep 2011
    Posts
    220
    FIRST_TABLE

    TER_KEY CHAR(4)

    BASE_TABLE

    TER_KEYS CHAR(77)

    Code:
    SELECT 
    T1.PL_PLA
    ,T1.TER_KEYS
    ,T1.DES_DET
    
    FROM DSNDEV.BASE_TABLE T1
    
    INNER JOIN
    
    DSNDEV.FIRST_TABLE T2
    ON
    T1.PL_PLA=T2.PL_PLA 
    
    WHERE 
    
    T1.PL_PLA = 'AAA'
    AND LOCATE(T2.TER_KEY || ',' , T1.TER_KEYS || ',')>0;
    
    
    ACTUAL RESULT SET GETTING
    
    
    PL_PLA  TER_KEYS         DES_DET
    AAA     1234,2345,5432    VAL
    AAA      3457,1234,6874   UNI 
    
    
    
    BUT EXPECTED RESULT SET IS
    
    EXPECTED RESULT SET
    
    PL_PLA TER_KEYS         DES_DET
    AAA     1234,2345,5432    VAL
    AAA      1234             DEN
    AAA      3457,1234,6874   UNI
    Thanks,

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    ...

    And, what was the datatype of TER_KEY in FIRST TABLE?
    Aren't there trailing blanks?
    Please try by removing trailing blanks of TER_KEYS in BASE_TABLE, like
    Code:
    ...
    AND LOCATE(T2.TER_KEY || ',' , RTRIM(T1.TER_KEYS) || ',')>0;

  13. #13
    Join Date
    Sep 2011
    Posts
    220
    Thanks.,working fine

Posting Permissions

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