+ Reply to Thread
Page 1 of 6 1 2 3 ... LastLast
Results 1 to 10 of 55

newbie 'len' function sql command question

  1. newbie 'len' function sql command question

    hello,

    I am new to db2. In sql, I was able to execute the following commands
    succesufully:

    Select A.Phone, B.*
    From A, B
    Where Len(A.zip, 5) = Len(B.zip, 5) and
    Len(A.last, 5) = Len(B.last, 5) and
    Len(A.address, 10) = Len(B.address, 10)

    What would the equivalent be in DB2?

    Thank you.


    tia

    ja

  2. Re: newbie 'len' function sql command question


    "ja" wrote in message
    news:3io0e016gito9eut7vpbi0i117agj797u6@4ax.com...
    > hello,
    >
    > I am new to db2. In sql, I was able to execute the following commands
    > succesufully:
    >
    > Select A.Phone, B.*
    > From A, B
    > Where Len(A.zip, 5) = Len(B.zip, 5) and
    > Len(A.last, 5) = Len(B.last, 5) and
    > Len(A.address, 10) = Len(B.address, 10)
    >
    > What would the equivalent be in DB2?
    >

    I don't understand your question. DB2 *uses* SQL; DB2 is not a substitute
    for SQL. I'm *guessing* that you mean that the query in the example is one
    you have used in some other dialect of SQL, like the one used by Oracle or
    SQL Server.

    DB2's dialect of SQL has a length() function. It is documented in the SQL
    Reference, which should have been installed on your machine when you
    installed DB2, assuming you are using DB2 for Windows/Unix/Linux. (I'm not
    sure what manuals get installed if you are using DB2 on AS/400 or mainframe
    and the location will probably vary depending on where your installer puts
    things.)

    The DB2 length() function for DB2 V7.2 on Windows/Unix/Linux has only a
    single argument, which is normally a column name or the name of a host
    variable from an application. The function calculates the length of the
    value in the column or host variable. For example:

    Select length(lastname) from employee

    should display the length of the lastname for each row in the employee
    table. CAUTION: if the column is fixed length, the function will return the
    maximum possible length of the values in the column, not the actual length
    of the values in each row. If the column is variable length, the function
    returns the actual length of the values. There are other "gotchas" listed in
    the manual in the article for the length function.

    I'm not sure what the second parameter of the len() function in your example
    means. Does len(A.zip, 5) mean "calculate the length of the zip code
    starting at the 5th character and continuing to the end of the field"? If
    yes, you'll need to combine DB2's length() and substr() functions, as
    follows:

    length(substr(A.zip, 5))

    Again, watch out for "gotchas", all of which are specified in the manual.
    Otherwise, you may get an answer you aren't expecting.

    Rhino



  3. Re: newbie 'len' function sql command question


    "ja" wrote in message
    news:3io0e016gito9eut7vpbi0i117agj797u6@4ax.com...
    > hello,
    >
    > I am new to db2. In sql, I was able to execute the following commands
    > succesufully:
    >
    > Select A.Phone, B.*
    > From A, B
    > Where Len(A.zip, 5) = Len(B.zip, 5) and
    > Len(A.last, 5) = Len(B.last, 5) and
    > Len(A.address, 10) = Len(B.address, 10)
    >
    > What would the equivalent be in DB2?
    >

    I don't understand your question. DB2 *uses* SQL; DB2 is not a substitute
    for SQL. I'm *guessing* that you mean that the query in the example is one
    you have used in some other dialect of SQL, like the one used by Oracle or
    SQL Server.

    DB2's dialect of SQL has a length() function. It is documented in the SQL
    Reference, which should have been installed on your machine when you
    installed DB2, assuming you are using DB2 for Windows/Unix/Linux. (I'm not
    sure what manuals get installed if you are using DB2 on AS/400 or mainframe
    and the location will probably vary depending on where your installer puts
    things.)

    The DB2 length() function for DB2 V7.2 on Windows/Unix/Linux has only a
    single argument, which is normally a column name or the name of a host
    variable from an application. The function calculates the length of the
    value in the column or host variable. For example:

    Select length(lastname) from employee

    should display the length of the lastname for each row in the employee
    table. CAUTION: if the column is fixed length, the function will return the
    maximum possible length of the values in the column, not the actual length
    of the values in each row. If the column is variable length, the function
    returns the actual length of the values. There are other "gotchas" listed in
    the manual in the article for the length function.

    I'm not sure what the second parameter of the len() function in your example
    means. Does len(A.zip, 5) mean "calculate the length of the zip code
    starting at the 5th character and continuing to the end of the field"? If
    yes, you'll need to combine DB2's length() and substr() functions, as
    follows:

    length(substr(A.zip, 5))

    Again, watch out for "gotchas", all of which are specified in the manual.
    Otherwise, you may get an answer you aren't expecting.

    Rhino



  4. Re: newbie 'len' function sql command question


    "ja" wrote in message
    news:3io0e016gito9eut7vpbi0i117agj797u6@4ax.com...
    > hello,
    >
    > I am new to db2. In sql, I was able to execute the following commands
    > succesufully:
    >
    > Select A.Phone, B.*
    > From A, B
    > Where Len(A.zip, 5) = Len(B.zip, 5) and
    > Len(A.last, 5) = Len(B.last, 5) and
    > Len(A.address, 10) = Len(B.address, 10)
    >
    > What would the equivalent be in DB2?
    >

    I don't understand your question. DB2 *uses* SQL; DB2 is not a substitute
    for SQL. I'm *guessing* that you mean that the query in the example is one
    you have used in some other dialect of SQL, like the one used by Oracle or
    SQL Server.

    DB2's dialect of SQL has a length() function. It is documented in the SQL
    Reference, which should have been installed on your machine when you
    installed DB2, assuming you are using DB2 for Windows/Unix/Linux. (I'm not
    sure what manuals get installed if you are using DB2 on AS/400 or mainframe
    and the location will probably vary depending on where your installer puts
    things.)

    The DB2 length() function for DB2 V7.2 on Windows/Unix/Linux has only a
    single argument, which is normally a column name or the name of a host
    variable from an application. The function calculates the length of the
    value in the column or host variable. For example:

    Select length(lastname) from employee

    should display the length of the lastname for each row in the employee
    table. CAUTION: if the column is fixed length, the function will return the
    maximum possible length of the values in the column, not the actual length
    of the values in each row. If the column is variable length, the function
    returns the actual length of the values. There are other "gotchas" listed in
    the manual in the article for the length function.

    I'm not sure what the second parameter of the len() function in your example
    means. Does len(A.zip, 5) mean "calculate the length of the zip code
    starting at the 5th character and continuing to the end of the field"? If
    yes, you'll need to combine DB2's length() and substr() functions, as
    follows:

    length(substr(A.zip, 5))

    Again, watch out for "gotchas", all of which are specified in the manual.
    Otherwise, you may get an answer you aren't expecting.

    Rhino



  5. Re: newbie 'len' function sql command question


    "ja" wrote in message
    news:3io0e016gito9eut7vpbi0i117agj797u6@4ax.com...
    > hello,
    >
    > I am new to db2. In sql, I was able to execute the following commands
    > succesufully:
    >
    > Select A.Phone, B.*
    > From A, B
    > Where Len(A.zip, 5) = Len(B.zip, 5) and
    > Len(A.last, 5) = Len(B.last, 5) and
    > Len(A.address, 10) = Len(B.address, 10)
    >
    > What would the equivalent be in DB2?
    >

    I don't understand your question. DB2 *uses* SQL; DB2 is not a substitute
    for SQL. I'm *guessing* that you mean that the query in the example is one
    you have used in some other dialect of SQL, like the one used by Oracle or
    SQL Server.

    DB2's dialect of SQL has a length() function. It is documented in the SQL
    Reference, which should have been installed on your machine when you
    installed DB2, assuming you are using DB2 for Windows/Unix/Linux. (I'm not
    sure what manuals get installed if you are using DB2 on AS/400 or mainframe
    and the location will probably vary depending on where your installer puts
    things.)

    The DB2 length() function for DB2 V7.2 on Windows/Unix/Linux has only a
    single argument, which is normally a column name or the name of a host
    variable from an application. The function calculates the length of the
    value in the column or host variable. For example:

    Select length(lastname) from employee

    should display the length of the lastname for each row in the employee
    table. CAUTION: if the column is fixed length, the function will return the
    maximum possible length of the values in the column, not the actual length
    of the values in each row. If the column is variable length, the function
    returns the actual length of the values. There are other "gotchas" listed in
    the manual in the article for the length function.

    I'm not sure what the second parameter of the len() function in your example
    means. Does len(A.zip, 5) mean "calculate the length of the zip code
    starting at the 5th character and continuing to the end of the field"? If
    yes, you'll need to combine DB2's length() and substr() functions, as
    follows:

    length(substr(A.zip, 5))

    Again, watch out for "gotchas", all of which are specified in the manual.
    Otherwise, you may get an answer you aren't expecting.

    Rhino



  6. Re: newbie 'len' function sql command question


    "ja" wrote in message
    news:3io0e016gito9eut7vpbi0i117agj797u6@4ax.com...
    > hello,
    >
    > I am new to db2. In sql, I was able to execute the following commands
    > succesufully:
    >
    > Select A.Phone, B.*
    > From A, B
    > Where Len(A.zip, 5) = Len(B.zip, 5) and
    > Len(A.last, 5) = Len(B.last, 5) and
    > Len(A.address, 10) = Len(B.address, 10)
    >
    > What would the equivalent be in DB2?
    >

    I don't understand your question. DB2 *uses* SQL; DB2 is not a substitute
    for SQL. I'm *guessing* that you mean that the query in the example is one
    you have used in some other dialect of SQL, like the one used by Oracle or
    SQL Server.

    DB2's dialect of SQL has a length() function. It is documented in the SQL
    Reference, which should have been installed on your machine when you
    installed DB2, assuming you are using DB2 for Windows/Unix/Linux. (I'm not
    sure what manuals get installed if you are using DB2 on AS/400 or mainframe
    and the location will probably vary depending on where your installer puts
    things.)

    The DB2 length() function for DB2 V7.2 on Windows/Unix/Linux has only a
    single argument, which is normally a column name or the name of a host
    variable from an application. The function calculates the length of the
    value in the column or host variable. For example:

    Select length(lastname) from employee

    should display the length of the lastname for each row in the employee
    table. CAUTION: if the column is fixed length, the function will return the
    maximum possible length of the values in the column, not the actual length
    of the values in each row. If the column is variable length, the function
    returns the actual length of the values. There are other "gotchas" listed in
    the manual in the article for the length function.

    I'm not sure what the second parameter of the len() function in your example
    means. Does len(A.zip, 5) mean "calculate the length of the zip code
    starting at the 5th character and continuing to the end of the field"? If
    yes, you'll need to combine DB2's length() and substr() functions, as
    follows:

    length(substr(A.zip, 5))

    Again, watch out for "gotchas", all of which are specified in the manual.
    Otherwise, you may get an answer you aren't expecting.

    Rhino



  7. Re: newbie 'len' function sql command question


    "ja" wrote in message
    news:3io0e016gito9eut7vpbi0i117agj797u6@4ax.com...
    > hello,
    >
    > I am new to db2. In sql, I was able to execute the following commands
    > succesufully:
    >
    > Select A.Phone, B.*
    > From A, B
    > Where Len(A.zip, 5) = Len(B.zip, 5) and
    > Len(A.last, 5) = Len(B.last, 5) and
    > Len(A.address, 10) = Len(B.address, 10)
    >
    > What would the equivalent be in DB2?
    >

    I don't understand your question. DB2 *uses* SQL; DB2 is not a substitute
    for SQL. I'm *guessing* that you mean that the query in the example is one
    you have used in some other dialect of SQL, like the one used by Oracle or
    SQL Server.

    DB2's dialect of SQL has a length() function. It is documented in the SQL
    Reference, which should have been installed on your machine when you
    installed DB2, assuming you are using DB2 for Windows/Unix/Linux. (I'm not
    sure what manuals get installed if you are using DB2 on AS/400 or mainframe
    and the location will probably vary depending on where your installer puts
    things.)

    The DB2 length() function for DB2 V7.2 on Windows/Unix/Linux has only a
    single argument, which is normally a column name or the name of a host
    variable from an application. The function calculates the length of the
    value in the column or host variable. For example:

    Select length(lastname) from employee

    should display the length of the lastname for each row in the employee
    table. CAUTION: if the column is fixed length, the function will return the
    maximum possible length of the values in the column, not the actual length
    of the values in each row. If the column is variable length, the function
    returns the actual length of the values. There are other "gotchas" listed in
    the manual in the article for the length function.

    I'm not sure what the second parameter of the len() function in your example
    means. Does len(A.zip, 5) mean "calculate the length of the zip code
    starting at the 5th character and continuing to the end of the field"? If
    yes, you'll need to combine DB2's length() and substr() functions, as
    follows:

    length(substr(A.zip, 5))

    Again, watch out for "gotchas", all of which are specified in the manual.
    Otherwise, you may get an answer you aren't expecting.

    Rhino



  8. Re: newbie 'len' function sql command question

    Sorry, wrong question. Here is what I'd like to know about db2:

    I have the following query:

    select b.*, a.t1, a.t2, a.t3
    into SVRAC
    from b1 as b inner join SRACode as a
    on left(b.phone, 3) = a.areacode

    Basically, I got two tables. I want to select all of the fields in
    table 'b' and 3 fields from table 'a' by using the area code from
    table 'a' and the 1st 3 character of the field 'phone' in table 'b.'
    Once I get this result, I want to put it in a separate table called
    SVRAC.

    What would the translation be in db2, if there's any?




    On Mon, 28 Jun 2004 18:17:52 -0400, "Rhino"
    wrote:

    >
    >"ja" wrote in message
    >news:3io0e016gito9eut7vpbi0i117agj797u6@4ax.com...
    >> hello,
    >>
    >> I am new to db2. In sql, I was able to execute the following commands
    >> succesufully:
    >>
    >> Select A.Phone, B.*
    >> From A, B
    >> Where Len(A.zip, 5) = Len(B.zip, 5) and
    >> Len(A.last, 5) = Len(B.last, 5) and
    >> Len(A.address, 10) = Len(B.address, 10)
    >>
    >> What would the equivalent be in DB2?
    >>

    >I don't understand your question. DB2 *uses* SQL; DB2 is not a substitute
    >for SQL. I'm *guessing* that you mean that the query in the example is one
    >you have used in some other dialect of SQL, like the one used by Oracle or
    >SQL Server.
    >
    >DB2's dialect of SQL has a length() function. It is documented in the SQL
    >Reference, which should have been installed on your machine when you
    >installed DB2, assuming you are using DB2 for Windows/Unix/Linux. (I'm not
    >sure what manuals get installed if you are using DB2 on AS/400 or mainframe
    >and the location will probably vary depending on where your installer puts
    >things.)
    >
    >The DB2 length() function for DB2 V7.2 on Windows/Unix/Linux has only a
    >single argument, which is normally a column name or the name of a host
    >variable from an application. The function calculates the length of the
    >value in the column or host variable. For example:
    >
    >Select length(lastname) from employee
    >
    >should display the length of the lastname for each row in the employee
    >table. CAUTION: if the column is fixed length, the function will return the
    >maximum possible length of the values in the column, not the actual length
    >of the values in each row. If the column is variable length, the function
    >returns the actual length of the values. There are other "gotchas" listed in
    >the manual in the article for the length function.
    >
    >I'm not sure what the second parameter of the len() function in your example
    >means. Does len(A.zip, 5) mean "calculate the length of the zip code
    >starting at the 5th character and continuing to the end of the field"? If
    >yes, you'll need to combine DB2's length() and substr() functions, as
    >follows:
    >
    >length(substr(A.zip, 5))
    >
    >Again, watch out for "gotchas", all of which are specified in the manual.
    >Otherwise, you may get an answer you aren't expecting.
    >
    >Rhino
    >



  9. Re: newbie 'len' function sql command question

    Sorry, wrong question. Here is what I'd like to know about db2:

    I have the following query:

    select b.*, a.t1, a.t2, a.t3
    into SVRAC
    from b1 as b inner join SRACode as a
    on left(b.phone, 3) = a.areacode

    Basically, I got two tables. I want to select all of the fields in
    table 'b' and 3 fields from table 'a' by using the area code from
    table 'a' and the 1st 3 character of the field 'phone' in table 'b.'
    Once I get this result, I want to put it in a separate table called
    SVRAC.

    What would the translation be in db2, if there's any?




    On Mon, 28 Jun 2004 18:17:52 -0400, "Rhino"
    wrote:

    >
    >"ja" wrote in message
    >news:3io0e016gito9eut7vpbi0i117agj797u6@4ax.com...
    >> hello,
    >>
    >> I am new to db2. In sql, I was able to execute the following commands
    >> succesufully:
    >>
    >> Select A.Phone, B.*
    >> From A, B
    >> Where Len(A.zip, 5) = Len(B.zip, 5) and
    >> Len(A.last, 5) = Len(B.last, 5) and
    >> Len(A.address, 10) = Len(B.address, 10)
    >>
    >> What would the equivalent be in DB2?
    >>

    >I don't understand your question. DB2 *uses* SQL; DB2 is not a substitute
    >for SQL. I'm *guessing* that you mean that the query in the example is one
    >you have used in some other dialect of SQL, like the one used by Oracle or
    >SQL Server.
    >
    >DB2's dialect of SQL has a length() function. It is documented in the SQL
    >Reference, which should have been installed on your machine when you
    >installed DB2, assuming you are using DB2 for Windows/Unix/Linux. (I'm not
    >sure what manuals get installed if you are using DB2 on AS/400 or mainframe
    >and the location will probably vary depending on where your installer puts
    >things.)
    >
    >The DB2 length() function for DB2 V7.2 on Windows/Unix/Linux has only a
    >single argument, which is normally a column name or the name of a host
    >variable from an application. The function calculates the length of the
    >value in the column or host variable. For example:
    >
    >Select length(lastname) from employee
    >
    >should display the length of the lastname for each row in the employee
    >table. CAUTION: if the column is fixed length, the function will return the
    >maximum possible length of the values in the column, not the actual length
    >of the values in each row. If the column is variable length, the function
    >returns the actual length of the values. There are other "gotchas" listed in
    >the manual in the article for the length function.
    >
    >I'm not sure what the second parameter of the len() function in your example
    >means. Does len(A.zip, 5) mean "calculate the length of the zip code
    >starting at the 5th character and continuing to the end of the field"? If
    >yes, you'll need to combine DB2's length() and substr() functions, as
    >follows:
    >
    >length(substr(A.zip, 5))
    >
    >Again, watch out for "gotchas", all of which are specified in the manual.
    >Otherwise, you may get an answer you aren't expecting.
    >
    >Rhino
    >



  10. Re: newbie 'len' function sql command question

    Sorry, wrong question. Here is what I'd like to know about db2:

    I have the following query:

    select b.*, a.t1, a.t2, a.t3
    into SVRAC
    from b1 as b inner join SRACode as a
    on left(b.phone, 3) = a.areacode

    Basically, I got two tables. I want to select all of the fields in
    table 'b' and 3 fields from table 'a' by using the area code from
    table 'a' and the 1st 3 character of the field 'phone' in table 'b.'
    Once I get this result, I want to put it in a separate table called
    SVRAC.

    What would the translation be in db2, if there's any?




    On Mon, 28 Jun 2004 18:17:52 -0400, "Rhino"
    wrote:

    >
    >"ja" wrote in message
    >news:3io0e016gito9eut7vpbi0i117agj797u6@4ax.com...
    >> hello,
    >>
    >> I am new to db2. In sql, I was able to execute the following commands
    >> succesufully:
    >>
    >> Select A.Phone, B.*
    >> From A, B
    >> Where Len(A.zip, 5) = Len(B.zip, 5) and
    >> Len(A.last, 5) = Len(B.last, 5) and
    >> Len(A.address, 10) = Len(B.address, 10)
    >>
    >> What would the equivalent be in DB2?
    >>

    >I don't understand your question. DB2 *uses* SQL; DB2 is not a substitute
    >for SQL. I'm *guessing* that you mean that the query in the example is one
    >you have used in some other dialect of SQL, like the one used by Oracle or
    >SQL Server.
    >
    >DB2's dialect of SQL has a length() function. It is documented in the SQL
    >Reference, which should have been installed on your machine when you
    >installed DB2, assuming you are using DB2 for Windows/Unix/Linux. (I'm not
    >sure what manuals get installed if you are using DB2 on AS/400 or mainframe
    >and the location will probably vary depending on where your installer puts
    >things.)
    >
    >The DB2 length() function for DB2 V7.2 on Windows/Unix/Linux has only a
    >single argument, which is normally a column name or the name of a host
    >variable from an application. The function calculates the length of the
    >value in the column or host variable. For example:
    >
    >Select length(lastname) from employee
    >
    >should display the length of the lastname for each row in the employee
    >table. CAUTION: if the column is fixed length, the function will return the
    >maximum possible length of the values in the column, not the actual length
    >of the values in each row. If the column is variable length, the function
    >returns the actual length of the values. There are other "gotchas" listed in
    >the manual in the article for the length function.
    >
    >I'm not sure what the second parameter of the len() function in your example
    >means. Does len(A.zip, 5) mean "calculate the length of the zip code
    >starting at the 5th character and continuing to the end of the field"? If
    >yes, you'll need to combine DB2's length() and substr() functions, as
    >follows:
    >
    >length(substr(A.zip, 5))
    >
    >Again, watch out for "gotchas", all of which are specified in the manual.
    >Otherwise, you may get an answer you aren't expecting.
    >
    >Rhino
    >



+ Reply to Thread
Page 1 of 6 1 2 3 ... LastLast