DB2 Queries

thiru_siva · #1 (**permalink**) 02-01-12, 05:33

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)

tonkuma · #2 (**permalink**) 02-01-12, 06:35

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
;

przytula_guy · #3 (**permalink**) 02-01-12, 07:11

SQLERRD(3) : see get diagnostics

tonkuma · #4 (**permalink**) 02-01-12, 07:42

2)

Quote:

... 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)
...

Internet Services

Web Development

Digital Marketing

Consumer Tech