Results 1 to 4 of 4

Thread: DB2 Queries

  1. #1
    Join Date
    Oct 2009
    Posts
    1

    Unanswered: DB2 Queries

    1) How can i see SQLERRD(3) ???

    2) Can you please suggest the easiest way to fetch records from a table with a column having characters other than non-specified values ? For Example : I need a row which is having characters other than A, B and C ???
    Input : ABC, AMC, BBC, TTG, KKK, LLL
    Output should be : TTG, KKK, LLL

    3) I need to update the values of a column in DB2 if the value is numeric ???
    Ex : Input value is : 123, 145
    Output should be : 00123 , 00145

    4) There are three tables Current, History and archive. The contents of these tables are consolidated in one table called Full. I need to write a query in which i need to sum up the record count of Current, History and archive

    For example
    SELECT COUNT(*) AS C
    FROM CURRENT
    UNION
    SELECT COUNT(*) AS H
    FROM HISTORY
    UNION
    SELECT COUNT(*) AS A
    FROM ARCHIVE;

    Now i need to sum up the count values(c,h,a) in the same query.
    Can anyone help me on this?

    5) I need to fetch all the rows where spaces is a part of column value with some restriction ???

    I/P is : UNITED STATES, INDIA, UK
    O/P should be : UNITED STATES (as it is having spaces)

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    2), 5)
    Please see LOCATE function
    LOCATE - IBM DB2 9.7 for Linux, UNIX, and Windows

    For example, to find a string having 'A'.
    LOCATE('A' , string) > 0


    3)
    3-1) What is the datatype of the column?

    3-2) How to do for those data?
    '01 23'
    '012 '


    4)
    Try...
    Code:
    SELECT c
         , h
         , a
         , c + h + a AS sum
     FROM  (
           SELECT COUNT(*) AS C 
            FROM  CURRENT
           ) c
         , (
           SELECT COUNT(*) AS H 
            FROM  HISTORY 
           ) h
         , (
           SELECT COUNT(*) AS A 
            FROM  ARCHIVE
           ) a
    ;
    Last edited by tonkuma; 02-01-12 at 07:39.

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    SQLERRD(3) : see get diagnostics
    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

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    2)
    ... a row which is having characters other than A, B and C ???
    Input : ABC, AMC, BBC, TTG, KKK, LLL
    Output should be : TTG, KKK, LLL
    If specified characters supplied as a string like 'ABC',
    then try...
    Code:
    ...
     WHERE LENGTH( TRANSLATE(string , '' , 'ABC' , '') )
           = LENGTH(string)
    ...

Posting Permissions

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