Hello,

I am getting on error 215 on a select statement with a group by and having clause. My sql statement is:

select tsclm907.cnum:cnum.h, max(tsclm907.endt)
from tsclm907
where tsclm907.fini = tcyesno.yes
group by tsclm907.cnum
having max(tsclm907.endt) < :ardt
selectdo
...
endselect

A partial table definition is cnum, pono,..., stdt, endt. Similar sql against Oracle which works correctly is:

select t$cnum, max(t$endt) from baan.ttsclm907100
where t$fini = 1
having max(t$endt) < to_date('31-DEC-2003','DD-MON-YYYY')
group by t$cnum;

The error text from log.oracle8 is:

******* S T A R T of Error message *******
Log message called from /view/port.7.1d.11/vobs/tt/servers/ORACLE_2/ora_data.c: #270 keyword: Illegal condition
Pid 60282 Uid 389 Euid 389 Gid 125 Egid 125 Pset tab0529@devserv:58292
user_type S language 2 user_name tab0529 tty ote locale ISO88591/NULL
Errno 0 bdb_errno 110 (End of file reached)

dbs_errno = 215, Error 215 occurred:
Internal exception error :
Illegal condition, args [1] : [071545]
Flushed at /view/port.7.1d.11/vobs/tt/servers/ORACLE_2/ora_data.c : #270.
******* E N D of Error message *******

Does anyone have any ideas? Does anyone have a similar example of a group by with a having clause?

Thanks in advance for your help!

For enumareted type use integer value of enumareted field.
tcyesno.yes equals 1. Try followings
Good Luck !


select tsclm907.cnum:cnum.h, max(tsclm907.endt)
from tsclm907
where tsclm907.fini = 1
group by tsclm907.cnum
having max(tsclm907.endt) < :ardt
selectdo
...
endselect

mr_suleyman,

Thanks for your reply. However, after making the change from tsclm907.fini = tcyesno.yes to tsclm907.fini = 1, I still get the same error 215. I am fairly certain that the problem is in the having clause. If I change the sql removing "having max(endt) < :arco" and adding a second where clause of "and endt < :arco", the sql statement does not fail, but I don't get the correct results.

Does anyone have any other ideas?

Thanks

I think that It should work. I suggest check your sql structure with variable types.

Good Luck !

For grins and giggles instead of using ":ardt" try using a constant(or numeric value for the date). See if that works.

hello tab0529,

Mark you are right, this query can work if use a constant value instead of a variable for comparison in "having" clause. If the having clause has a date field and compare with the variable its not working but if comparison is with the constant then it works. If instead of date field, some numeric field is used in the having clause and compare with the variable, it work. I think that the problem is only with the date fields.

MRF

Have you tried using the "where bind" clause on the variable instead of directly assigning the variable in the select?

Have you tried using the "where bind" clause on the variable instead of directly assigning the variable in the select?

And this was what I was leading up to - using where bind to see if that would work.

MRF,

It looks like your assumption is correct about date fields and the having clause. I changed the sql to use "max(pono)" and "having max(pono) > 1" and did not have a problem with the code (pono is a numeric field). The date fields are domain tcccp.date (local dates - not UTC). Is there some easy way to convert the max(date) values into numeric values within the sql?

Thanks

mark_h or metbaan,

I have never used wherebind before. Can you help?

Thanks

Hi,

"Where bind" is also not working, giving the same problem.

MRF

I have used where bind but never with the having clause. But you can always use dynamic sql. Try doing a search on that on this forum. Then you can build you sql using a variable for ardt.


| 20051116.st Added for the last 4 characters.
wild = ".*"
last4 = wild & some.project(3;4)

sql.code = ""
found = true
sql.code = sql.code & "select tppdm600.cprj "
sql.code = sql.code & "from tppdm600 "
sql.code = sql.code & "where tppdm600.cprj like " & chr$(34) & last4 & chr$(34)
sql.code = sql.code & " as set with 1 rows"
sql_id = sql.parse(sql.code)
sql.exec(sql_id)
while true
on case sql.fetch(sql_id)
case eendfile:
found = false
break
case 0:
found = true
break
default:
found = false
endcase
break
endwhile
sql.break(sql_id)
sql.close(sql_id)
if found then
return(true)
endif


Granted this example only reads one record but you can get the idea.

I too had error 215 when using having clause in the SQL (Oracle & SQL 2000) . The same query worked on another database (Bisam) . Then we resolved it by changing our program logic as it was urgent to make it work . Prabably u can register a case with support center .