sql query not working



IBM's flagship relational database management system

sql query not working

Postby gopal_NKP » Mon May 16, 2011 11:51 pm

hi,

i have the requirement as following.


SELECT COLUMN1,
COUNT(DISTINCT((COLUMN2||COLUMN3||COLUMN4))),
SUM(CAST(COLUMN5 AS INTEGER)))
FROM TABLE_NAME
GROUP BY COLUMN1
HAVING COLUMN1 <> 'VALUE'.

all columns are of char type. column5 length is 8. column5 contains numeric data only.

The query is not working. error is as follows.

QUERY MESSAGES:
An invalid character string argument was used with the INTEGER function.


if HAVING COLUMN1 = 'VALUE' is given it is working

for having column1 not equal to value, the query is not working. please suggest me on this.


thanks in advance
gopal_NKP
 
Posts: 22
Joined: Fri Feb 25, 2011 11:47 am
Has thanked: 0 time
Been thanked: 0 time

Re: sql query not working

Postby Akatsukami » Tue May 17, 2011 1:23 am

gopal_NKP wrote:column5 contains numeric data only.

I do not believe this.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: sql query not working

Postby BillyBoyo » Tue May 17, 2011 4:26 am

gopal_NKP wrote:[...]

if HAVING COLUMN1 = 'VALUE' is given it is working

for having column1 not equal to value, the query is not working.
[...]


Perhaps when COLUMN1 is not VALUE, COLUMN5 contains non-numerics?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: sql query not working

Postby GuyC » Tue May 17, 2011 1:57 pm

select * from table_name
where translate(COLUMN5,'X          ',' 0123456789') <> ' '
and COLUMN1 <> 'VALUE'
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: sql query not working

Postby gopal_NKP » Thu May 19, 2011 3:55 pm

hI,

PLEASE find some more info about my query.

SELECT COLUMN1,
COUNT(COLUMN2||COLUMN3||COLUMN4),
SUM(INTEGER(COLUMN5))
FROM TABLE_NAME
GROUP BY COLUMN1
HAVING COLUMN1 <> 'VALUE1'


COLUMN5 is of char(2) type and not null.
problem is for some rows, it does not the value.
please tell, how to handle the rows which do not have the value.
the rows which do not have the value for the column COLUMN5 should not be skipped, because it may affect the COUNT(COLUMN2||COLUMN3||COLUMN4) .
gopal_NKP
 
Posts: 22
Joined: Fri Feb 25, 2011 11:47 am
Has thanked: 0 time
Been thanked: 0 time

Re: sql query not working

Postby GuyC » Thu May 19, 2011 6:31 pm

there a lot wrong with your query :
1) Do you think COUNT(COLUMN2||COLUMN3||COLUMN4) will return something else than COUNT(*) ? you probably should have kept the DISTINCT from your first post.
2) HAVING-clause should only be used when evaluating aggregate functions result (like count() or sum() ). Normal column predicates should be in the where clause. To put it simply : Where clause is evaluated during data-reading, Having-clause is evaluated afterwards on the result set.

Now for an answer to your question. There are a few ways to solve your problem, none very performant.

Combining my previous post on how to find non-numeric data and your query, with a CASE-clause you could solve it this way :

SELECT COLUMN1
       ,COUNT(distinct(COLUMN2||COLUMN3||COLUMN4))
       ,SUM(case when translate(COLUMN5,'X          ',' 0123456789') <> ' ' then 0
                 else INTEGER(COLUMN5) end)
  FROM TABLE_NAME
 WHERE COLUMN1 <> 'VALUE1'
 GROUP BY COLUMN1
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: sql query not working

Postby gopal_NKP » Thu Jun 09, 2011 5:18 pm

hi,

it worked for me. thanks for help.

thanks
gopal_NKP
 
Posts: 22
Joined: Fri Feb 25, 2011 11:47 am
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post