Page 1 of 1

sql query not working

PostPosted: Mon May 16, 2011 11:51 pm
by gopal_NKP
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

Re: sql query not working

PostPosted: Tue May 17, 2011 1:23 am
by Akatsukami
gopal_NKP wrote:column5 contains numeric data only.

I do not believe this.

Re: sql query not working

PostPosted: Tue May 17, 2011 4:26 am
by BillyBoyo
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?

Re: sql query not working

PostPosted: Tue May 17, 2011 1:57 pm
by GuyC
select * from table_name
where translate(COLUMN5,'X          ',' 0123456789') <> ' '
and COLUMN1 <> 'VALUE'

Re: sql query not working

PostPosted: Thu May 19, 2011 3:55 pm
by gopal_NKP
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) .

Re: sql query not working

PostPosted: Thu May 19, 2011 6:31 pm
by GuyC
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

Re: sql query not working

PostPosted: Thu Jun 09, 2011 5:18 pm
by gopal_NKP
hi,

it worked for me. thanks for help.

thanks