Professional Web Applications Themes

with clause in sql procedure - IBM DB2

Hi, I'm trying to do this: (with temp1 (col_a) as (values char(I_DAY_FON,32)) select rtrim((case when ascii(substr(col_a,1,1)) between 48 and 57 then substr(col_a,1,1) else '' end) || (case when ascii(substr(col_a,2,1)) between 48 and 57 then substr(col_a,2,1) else '' end) || (case when ascii(substr(col_a,3,1)) between 48 and 57 then substr(col_a,3,1) else '' end) || (case when ascii(substr(col_a,4,1)) between 48 and 57 then substr(col_a,4,1) else '' end) || (case when ascii(substr(col_a,5,1)) between 48 and 57 then substr(col_a,5,1) else '' end) || (case when ascii(substr(col_a,6,1)) between 48 and 57 then substr(col_a,6,1) else '' end) || (case when ascii(substr(col_a,7,1)) between 48 and 57 then substr(col_a,7,1) ...

  1. #1

    Default with clause in sql procedure

    Hi,

    I'm trying to do this:
    (with temp1 (col_a) as (values char(I_DAY_FON,32))
    select
    rtrim((case when ascii(substr(col_a,1,1)) between 48 and 57
    then substr(col_a,1,1) else '' end) ||
    (case when ascii(substr(col_a,2,1)) between 48 and 57
    then substr(col_a,2,1) else '' end) ||
    (case when ascii(substr(col_a,3,1)) between 48 and 57
    then substr(col_a,3,1) else '' end) ||
    (case when ascii(substr(col_a,4,1)) between 48 and 57
    then substr(col_a,4,1) else '' end) ||
    (case when ascii(substr(col_a,5,1)) between 48 and 57
    then substr(col_a,5,1) else '' end) ||
    (case when ascii(substr(col_a,6,1)) between 48 and 57
    then substr(col_a,6,1) else '' end) ||
    (case when ascii(substr(col_a,7,1)) between 48 and 57
    then substr(col_a,7,1) else '' end) ||
    (case when ascii(substr(col_a,8,1)) between 48 and 57
    then substr(col_a,8,1) else '' end) ||
    (case when ascii(substr(col_a,9,1)) between 48 and 57
    then substr(col_a,9,1) else '' end) ||
    (case when ascii(substr(col_a,10,1)) between 48 and 57
    then substr(col_a,10,1) else '' end) ||
    (case when ascii(substr(col_a,11,1)) between 48 and 57
    then substr(col_a,11,1) else '' end) ||
    (case when ascii(substr(col_a,12,1)) between 48 and 57
    then substr(col_a,12,1) else '' end) ||
    (case when ascii(substr(col_a,13,1)) between 48 and 57
    then substr(col_a,13,1) else '' end) ||
    (case when ascii(substr(col_a,14,1)) between 48 and 57
    then substr(col_a,14,1) else '' end) ||
    (case when ascii(substr(col_a,15,1)) between 48 and 57
    then substr(col_a,15,1) else '' end) ||
    (case when ascii(substr(col_a,16,1)) between 48 and 57
    then substr(col_a,16,1) else '' end) ||
    (case when ascii(substr(col_a,17,1)) between 48 and 57
    then substr(col_a,17,1) else '' end) ||
    (case when ascii(substr(col_a,18,1)) between 48 and 57
    then substr(col_a,18,1) else '' end) ||
    (case when ascii(substr(col_a,19,1)) between 48 and 57
    then substr(col_a,19,1) else '' end) ||
    (case when ascii(substr(col_a,20,1)) between 48 and 57
    then substr(col_a,20,1) else '' end) ||
    (case when ascii(substr(col_a,21,1)) between 48 and 57
    then substr(col_a,21,1) else '' end) ||
    (case when ascii(substr(col_a,22,1)) between 48 and 57
    then substr(col_a,22,1) else '' end) ||
    (case when ascii(substr(col_a,23,1)) between 48 and 57
    then substr(col_a,23,1) else '' end) ||
    (case when ascii(substr(col_a,24,1)) between 48 and 57
    then substr(col_a,24,1) else '' end) ||
    (case when ascii(substr(col_a,25,1)) between 48 and 57
    then substr(col_a,25,1) else '' end) ||
    (case when ascii(substr(col_a,26,1)) between 48 and 57
    then substr(col_a,26,1) else '' end) ||
    (case when ascii(substr(col_a,27,1)) between 48 and 57
    then substr(col_a,27,1) else '' end) ||
    (case when ascii(substr(col_a,28,1)) between 48 and 57
    then substr(col_a,28,1) else '' end) ||
    (case when ascii(substr(col_a,29,1)) between 48 and 57
    then substr(col_a,29,1) else '' end) ||
    (case when ascii(substr(col_a,30,1)) between 48 and 57
    then substr(col_a,30,1) else '' end) ||
    (case when ascii(substr(col_a,31,1)) between 48 and 57
    then substr(col_a,31,1) else '' end) ||
    (case when ascii(substr(col_a,32,1)) between 48 and 57
    then substr(col_a,32,1) else '' end))
    into fmtPhoneNum
    from temp1 ;

    in a SQL procedure. The idea is to take any string that is contained
    in the variable I_DAY_FON and p out any numeric chars. The same
    statement in a file run from the CLP with the into clause and a sting
    rather than a variable works fine. I'm getting an error when I build
    the procedure that says:

    SQL0104N An unexpected token "with" was found following .....
    ". Expected tokens may include: "SELECT".

    Any ideas how to get around this problem?

    Thanks!
    Leo

    Example from CLP: String '234.4556\545' returns :
    1
    --------------------------------
    2344556545

    1 record(s) selected.
    Leo Guest

  2. #2

    Default Re: with clause in sql procedure

    I've never before seen syntax like you are using despite many years of
    writing SQL. You don't mention what version of DB2 and what OS you are using
    so perhaps this is something new in V8.1. (I haven't read the 8.1 docs yet.)
    However, the lines that contain

    else " end

    just don't look right to me. Are these pairs of consecutive apostrophes (')
    or single instances of quotes (")? In either case, I'm not sure what you are
    trying to accomplish. Check your SQL Reference carefully and verify that
    this syntax is valid. Maybe you should consider writing a UDF (user-defined
    function) to handle this part of the query.

    Rhino

    "Leo" <lpedronf-tech.net> wrote in message
    news:dc1c197e.0309101242.4be746d6posting.google.c om...
    > Hi,
    >
    > I'm trying to do this:
    > (with temp1 (col_a) as (values char(I_DAY_FON,32))
    > select
    > rtrim((case when ascii(substr(col_a,1,1)) between 48 and 57
    > then substr(col_a,1,1) else '' end) ||
    > (case when ascii(substr(col_a,2,1)) between 48 and 57
    > then substr(col_a,2,1) else '' end) ||
    > (case when ascii(substr(col_a,3,1)) between 48 and 57
    > then substr(col_a,3,1) else '' end) ||
    > (case when ascii(substr(col_a,4,1)) between 48 and 57
    > then substr(col_a,4,1) else '' end) ||
    > (case when ascii(substr(col_a,5,1)) between 48 and 57
    > then substr(col_a,5,1) else '' end) ||
    > (case when ascii(substr(col_a,6,1)) between 48 and 57
    > then substr(col_a,6,1) else '' end) ||
    > (case when ascii(substr(col_a,7,1)) between 48 and 57
    > then substr(col_a,7,1) else '' end) ||
    > (case when ascii(substr(col_a,8,1)) between 48 and 57
    > then substr(col_a,8,1) else '' end) ||
    > (case when ascii(substr(col_a,9,1)) between 48 and 57
    > then substr(col_a,9,1) else '' end) ||
    > (case when ascii(substr(col_a,10,1)) between 48 and 57
    > then substr(col_a,10,1) else '' end) ||
    > (case when ascii(substr(col_a,11,1)) between 48 and 57
    > then substr(col_a,11,1) else '' end) ||
    > (case when ascii(substr(col_a,12,1)) between 48 and 57
    > then substr(col_a,12,1) else '' end) ||
    > (case when ascii(substr(col_a,13,1)) between 48 and 57
    > then substr(col_a,13,1) else '' end) ||
    > (case when ascii(substr(col_a,14,1)) between 48 and 57
    > then substr(col_a,14,1) else '' end) ||
    > (case when ascii(substr(col_a,15,1)) between 48 and 57
    > then substr(col_a,15,1) else '' end) ||
    > (case when ascii(substr(col_a,16,1)) between 48 and 57
    > then substr(col_a,16,1) else '' end) ||
    > (case when ascii(substr(col_a,17,1)) between 48 and 57
    > then substr(col_a,17,1) else '' end) ||
    > (case when ascii(substr(col_a,18,1)) between 48 and 57
    > then substr(col_a,18,1) else '' end) ||
    > (case when ascii(substr(col_a,19,1)) between 48 and 57
    > then substr(col_a,19,1) else '' end) ||
    > (case when ascii(substr(col_a,20,1)) between 48 and 57
    > then substr(col_a,20,1) else '' end) ||
    > (case when ascii(substr(col_a,21,1)) between 48 and 57
    > then substr(col_a,21,1) else '' end) ||
    > (case when ascii(substr(col_a,22,1)) between 48 and 57
    > then substr(col_a,22,1) else '' end) ||
    > (case when ascii(substr(col_a,23,1)) between 48 and 57
    > then substr(col_a,23,1) else '' end) ||
    > (case when ascii(substr(col_a,24,1)) between 48 and 57
    > then substr(col_a,24,1) else '' end) ||
    > (case when ascii(substr(col_a,25,1)) between 48 and 57
    > then substr(col_a,25,1) else '' end) ||
    > (case when ascii(substr(col_a,26,1)) between 48 and 57
    > then substr(col_a,26,1) else '' end) ||
    > (case when ascii(substr(col_a,27,1)) between 48 and 57
    > then substr(col_a,27,1) else '' end) ||
    > (case when ascii(substr(col_a,28,1)) between 48 and 57
    > then substr(col_a,28,1) else '' end) ||
    > (case when ascii(substr(col_a,29,1)) between 48 and 57
    > then substr(col_a,29,1) else '' end) ||
    > (case when ascii(substr(col_a,30,1)) between 48 and 57
    > then substr(col_a,30,1) else '' end) ||
    > (case when ascii(substr(col_a,31,1)) between 48 and 57
    > then substr(col_a,31,1) else '' end) ||
    > (case when ascii(substr(col_a,32,1)) between 48 and 57
    > then substr(col_a,32,1) else '' end))
    > into fmtPhoneNum
    > from temp1 ;
    >
    > in a SQL procedure. The idea is to take any string that is contained
    > in the variable I_DAY_FON and p out any numeric chars. The same
    > statement in a file run from the CLP with the into clause and a sting
    > rather than a variable works fine. I'm getting an error when I build
    > the procedure that says:
    >
    > SQL0104N An unexpected token "with" was found following .....
    > ". Expected tokens may include: "SELECT".
    >
    > Any ideas how to get around this problem?
    >
    > Thanks!
    > Leo
    >
    > Example from CLP: String '234.4556\545' returns :
    > 1
    > --------------------------------
    > 2344556545
    >
    > 1 record(s) selected.

    Rhino Guest

  3. #3

    Default Re: with clause in sql procedure

    Leo <lpedronf-tech.net> wrote:
    > Hi,
    >
    > I'm trying to do this:
    > (with temp1 (col_a) as (values char(I_DAY_FON,32))
    > select
    > rtrim((case when ascii(substr(col_a,1,1)) between 48 and 57
    [...]
    > then substr(col_a,32,1) else '' end))
    > into fmtPhoneNum
    > from temp1 ;
    Stupid question maybe, but why do you want to use a common table expression
    in the first place? Just do a

    SELECT ...
    INTO ...
    FROM TABLE ( VALUES ( CHAR(i_day_fon, 32) ) ) AS temp1(col_a)

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

  4. #4

    Default Re: with clause in sql procedure

    DB2 Version 7.2 fixpack 10 on Solaris.

    What I'm doing is looking at the contents of the variable I_DAY_FON,
    which could be any char string, and p out all the numeric chars in
    the sting. For example, the string '123-456,789' would select into
    the variable fmtPhoneNum the string '123456789'. It works fine when I
    run it from the CLP.

    So what "case when ascii(substr(col_a,4,1)) between 48 and 57 then
    substr (col_a,4,1) else '' end)" is doing is, comparing the fourth
    char in the string ascii value to see if it's between 48 and 57. (48
    is zero and 57 is nine). If it's between 48 and 57, the concatenate
    it to the string, else concatenate nothing or "tick-tick" (two single
    ticks).

    The SQL developers guide says that SQL procedures are supposed to
    fully support select statments. So, the SQL reference says that use
    of "with" is valid in a select statement, so why does the SQL
    procedure error when it hits the "with" portion of my select? It says
    that it's looking for select and select is on the next line, so why
    doesn't it build? I have other select statements in the same
    procedure that are doing select into with the common table expression
    fine, but since I introduced this, it hasn't built yet.

    I'm currently looking into setting the whole string equal to a
    variable and preparing, and executing it dynamicly with the procedure,
    but I would rather the sql be static, but I guess that's just not
    possible.

    Thanks for looking at my problem though!
    Leo



    "Rhino" <rhino1NOSPAM.sympatico.ca> wrote in message news:<wXZ7b.4093$fC5.1046295news20.bellglobal.com >...
    > I've never before seen syntax like you are using despite many years of
    > writing SQL. You don't mention what version of DB2 and what OS you are using
    > so perhaps this is something new in V8.1. (I haven't read the 8.1 docs yet.)
    > However, the lines that contain
    >
    > else " end
    >
    > just don't look right to me. Are these pairs of consecutive apostrophes (')
    > or single instances of quotes (")? In either case, I'm not sure what you are
    > trying to accomplish. Check your SQL Reference carefully and verify that
    > this syntax is valid. Maybe you should consider writing a UDF (user-defined
    > function) to handle this part of the query.
    >
    > Rhino
    >
    > "Leo" <lpedronf-tech.net> wrote in message
    > news:dc1c197e.0309101242.4be746d6posting.google.c om...
    > > Hi,
    > >
    > > I'm trying to do this:
    > > (with temp1 (col_a) as (values char(I_DAY_FON,32))
    > > select
    > > rtrim((case when ascii(substr(col_a,1,1)) between 48 and 57
    > > then substr(col_a,1,1) else '' end) ||
    > > (case when ascii(substr(col_a,2,1)) between 48 and 57
    > > then substr(col_a,2,1) else '' end) ||
    > > (case when ascii(substr(col_a,3,1)) between 48 and 57
    > > then substr(col_a,3,1) else '' end) ||
    > > (case when ascii(substr(col_a,4,1)) between 48 and 57
    > > then substr(col_a,4,1) else '' end) ||
    > > (case when ascii(substr(col_a,5,1)) between 48 and 57
    > > then substr(col_a,5,1) else '' end) ||
    > > (case when ascii(substr(col_a,6,1)) between 48 and 57
    > > then substr(col_a,6,1) else '' end) ||
    > > (case when ascii(substr(col_a,7,1)) between 48 and 57
    > > then substr(col_a,7,1) else '' end) ||
    > > (case when ascii(substr(col_a,8,1)) between 48 and 57
    > > then substr(col_a,8,1) else '' end) ||
    > > (case when ascii(substr(col_a,9,1)) between 48 and 57
    > > then substr(col_a,9,1) else '' end) ||
    > > (case when ascii(substr(col_a,10,1)) between 48 and 57
    > > then substr(col_a,10,1) else '' end) ||
    > > (case when ascii(substr(col_a,11,1)) between 48 and 57
    > > then substr(col_a,11,1) else '' end) ||
    > > (case when ascii(substr(col_a,12,1)) between 48 and 57
    > > then substr(col_a,12,1) else '' end) ||
    > > (case when ascii(substr(col_a,13,1)) between 48 and 57
    > > then substr(col_a,13,1) else '' end) ||
    > > (case when ascii(substr(col_a,14,1)) between 48 and 57
    > > then substr(col_a,14,1) else '' end) ||
    > > (case when ascii(substr(col_a,15,1)) between 48 and 57
    > > then substr(col_a,15,1) else '' end) ||
    > > (case when ascii(substr(col_a,16,1)) between 48 and 57
    > > then substr(col_a,16,1) else '' end) ||
    > > (case when ascii(substr(col_a,17,1)) between 48 and 57
    > > then substr(col_a,17,1) else '' end) ||
    > > (case when ascii(substr(col_a,18,1)) between 48 and 57
    > > then substr(col_a,18,1) else '' end) ||
    > > (case when ascii(substr(col_a,19,1)) between 48 and 57
    > > then substr(col_a,19,1) else '' end) ||
    > > (case when ascii(substr(col_a,20,1)) between 48 and 57
    > > then substr(col_a,20,1) else '' end) ||
    > > (case when ascii(substr(col_a,21,1)) between 48 and 57
    > > then substr(col_a,21,1) else '' end) ||
    > > (case when ascii(substr(col_a,22,1)) between 48 and 57
    > > then substr(col_a,22,1) else '' end) ||
    > > (case when ascii(substr(col_a,23,1)) between 48 and 57
    > > then substr(col_a,23,1) else '' end) ||
    > > (case when ascii(substr(col_a,24,1)) between 48 and 57
    > > then substr(col_a,24,1) else '' end) ||
    > > (case when ascii(substr(col_a,25,1)) between 48 and 57
    > > then substr(col_a,25,1) else '' end) ||
    > > (case when ascii(substr(col_a,26,1)) between 48 and 57
    > > then substr(col_a,26,1) else '' end) ||
    > > (case when ascii(substr(col_a,27,1)) between 48 and 57
    > > then substr(col_a,27,1) else '' end) ||
    > > (case when ascii(substr(col_a,28,1)) between 48 and 57
    > > then substr(col_a,28,1) else '' end) ||
    > > (case when ascii(substr(col_a,29,1)) between 48 and 57
    > > then substr(col_a,29,1) else '' end) ||
    > > (case when ascii(substr(col_a,30,1)) between 48 and 57
    > > then substr(col_a,30,1) else '' end) ||
    > > (case when ascii(substr(col_a,31,1)) between 48 and 57
    > > then substr(col_a,31,1) else '' end) ||
    > > (case when ascii(substr(col_a,32,1)) between 48 and 57
    > > then substr(col_a,32,1) else '' end))
    > > into fmtPhoneNum
    > > from temp1 ;
    > >
    > > in a SQL procedure. The idea is to take any string that is contained
    > > in the variable I_DAY_FON and p out any numeric chars. The same
    > > statement in a file run from the CLP with the into clause and a sting
    > > rather than a variable works fine. I'm getting an error when I build
    > > the procedure that says:
    > >
    > > SQL0104N An unexpected token "with" was found following .....
    > > ". Expected tokens may include: "SELECT".
    > >
    > > Any ideas how to get around this problem?
    > >
    > > Thanks!
    > > Leo
    > >
    > > Example from CLP: String '234.4556\545' returns :
    > > 1
    > > --------------------------------
    > > 2344556545
    > >
    > > 1 record(s) selected.
    Leo Guest

  5. #5

    Default Re: with clause in sql procedure

    >
    > The SQL developers guide says that SQL procedures are supposed to
    > fully support select statments. So, the SQL reference says that use
    > of "with" is valid in a select statement, so why does the SQL
    > procedure error when it hits the "with" portion of my select?
    SELECT statement allow "WITH common-table-expression".
    SELECT INTO statement does not allow "WITH common-table-expression".

    Please see .....
    "SQL Reference Volume 1, Chapter 4. Queries, Select-statement" for
    "SELECT statement.
    "SQL Reference Volume 2, Chapter 1. Statements, SELECT INTO" for
    "SELECT INTO statement"
    > I have other select statements in the same
    > procedure that are doing select into with the common table expression
    > fine, but since I introduced this, it hasn't built yet.
    I wonder why you could do select into with the common table expression
    file.
    Would you show me your CREATE PROCEDURE, if it's not too big?
    > I'm currently looking into setting the whole string equal to a
    > variable and preparing, and executing it dynamicly with the procedure,
    > but I would rather the sql be static, but I guess that's just not
    > possible.
    How about using Nested table expression?
    Like this:
    SELECT rtrim((case ... end) || ... || (case ... end))
    into fmtPhoneNum
    FROM (values char(I_DAY_FON,32)) AS temp1 (col_a);
    Tokunaga T. Guest

  6. #6

    Default Re: with clause in sql procedure

    Leo <lpedronf-tech.net> wrote:
    > DB2 Version 7.2 fixpack 10 on Solaris.
    >
    > What I'm doing is looking at the contents of the variable I_DAY_FON,
    > which could be any char string, and p out all the numeric chars in
    > the sting. For example, the string '123-456,789' would select into
    > the variable fmtPhoneNum the string '123456789'. It works fine when I
    > run it from the CLP.
    >
    > So what "case when ascii(substr(col_a,4,1)) between 48 and 57 then
    > substr (col_a,4,1) else '' end)" is doing is, comparing the fourth
    > char in the string ascii value to see if it's between 48 and 57. (48
    > is zero and 57 is nine). If it's between 48 and 57, the concatenate
    > it to the string, else concatenate nothing or "tick-tick" (two single
    > ticks).
    Maybe this is an even more simple solution for your problem:

    SELECT REPLACE(TRANSLATE(UPPER(CHAR(i_day_fon, 32)), '',
    'ABCDEFGHIJKLMNOPQRSTUVWXYZ!#$%^&*(){}[]''",.<>-\_|;:'), ' ', '')
    INTO ...
    FROM ...

    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

Similar Threads

  1. Using IN in a WHERE clause
    By drmaves in forum Coldfusion Database Access
    Replies: 15
    Last Post: September 5th, 05:05 PM
  2. CF MX 6 WHERE Clause
    By Jeremy5431 in forum Macromedia ColdFusion
    Replies: 13
    Last Post: May 2nd, 02:26 PM
  3. help with GROUP BY clause?
    By Nathon Jones in forum Dreamweaver AppDev
    Replies: 10
    Last Post: February 25th, 07:06 PM
  4. Santa Clause like you have never seen him before
    By Gary Eickmeier in forum Photography
    Replies: 3
    Last Post: December 30th, 10:59 PM
  5. if-clause not working
    By M. Görg in forum PHP Development
    Replies: 2
    Last Post: July 14th, 03:46 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139