SQL Error 400 Illegal Value
Cache ODBC State S1000 Native Code 400 Illegal Value
This is my query:
select DateTijdSec from
GLPPatTcActie
where pnr = '27085070017' and LTestId->Makey='BLA' and VerzamelDatTijd < '2021-03-04-2021 09:04' and glpactieid->makey in ('TAV','TMA') order by DateTijdSec desc
Most likely there is a wrong date time in the table, how can i get the data?
Comments
what date time format would you expect this to be ????
'2021-03-04-2021 09:04'
YYYY-MM-DD-YYYY HH:mm
Not really standard! Rather a wrong DateTime in your Query !
This is the complete error:
<eb1>[C:\Program Files (x86)\George Poulose\QTODBC50\QTODBC50.exe]
[SQLCODE: <-400>:<Fatale fout >]
[Cache Error: <<ILLEGAL VALUE>zVerzamelDatTijdStorageToLogica+1^User.GLPPatTcActie.1>]
[Location: <ServerLoop - Query Fetch>]
[%msg: <Unexpected error occurred: <ILLEGAL VALUE>zVerzamelDatTijdStorageToLogica+1^User.GLPPatTcActie.1>]
State:S1000,Native:400,Origin:[Cache ODBC][State : S1000][Native Code 400]</eb1>
OK!
Now I see the problem. Each datatype has its specific conversion from internal format to ODBC format
So if VerzamelDatTijd is %Date or %Time or %Timestamp and the stored content doesn't fit
you get most likely this error.
It could be by principle := it fits never, a design issue
It could be just a glitch in some data record.
I suggest you start the SQL shell from terminal prompt. USER>do $system.SQL.Shell()
And then run your modified query:select ID,%INTERNAL(VerzamelDatTijd),%ODBCout(VerzamelDatTijd),DateTijdSecfrom GLPPatTcActie
where pnr = '27085070017' and LTestId->Makey='BLA'
-- and VerzamelDatTijd < '2021-03-04 09:04'
and glpactieid->makey in ('TAV','TMA')
-- order by DateTijdSec desc
ID shows the flaky record
%INTERNAL() shows the raw content
%ODBCOUT() should trigger the error
Once you know the content you should be able to fix it.
Hello Robert,
Thank you for the support. unfortunately I get the same error.
This is the first time that i run a query at the shell prompt.
These are my actions (the table GLPPatTcActie belongs to namespace LABSQL):
Namespace: LABSQL
You're in namespace LABSQL
Default directory is f:\databases\labsql\
LABSQL>do $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
LABSQL>>select ID,%INTERNAL(VerzamelDatTijd),%ODBCout(VerzamelDatTijd),DateTijdSec from GLPPatTcActie where pnr = '27085070017' and LTestId->Makey='BLA' and glpactieid->makey in ('TAV','TMA')
1. select ID,%INTERNAL(VerzamelDatTijd),%ODBCout(VerzamelDatTijd),DateTijdSec from GLPPatTcActie where pnr = '27085070017' and LTestId->Makey='BLA' and glpactieid->makey in ('TAV','TMA')
ID Expression_2 Expression_3 DateTijdSec
[SQLCODE: <-400>:<Fatale fout >]
[%msg: <Unexpected error occurred: <ILLEGAL VALUE>zVerzamelDatTijdStorageToLogica+1^User.GLPPatTcActie.1>]
0 Rows(s) Affected
statement prepare time: 0.8245s, elapsed execute time: 0.0167s.
---------------------------------------------------------------------------
LABSQL>> << entering multiline statement mode >>
1>>
OK. this looks like the first record you find is already in trouble.
I'd expect your data type is something else then stored.
Next omit the conversion %ODBCOUT() just to see what internal content is and where it starts
select ID,%INTERNAL(VerzamelDatTijd),
DateTijdSec from GLPPatTcActie
where pnr = '27085070017'
and LTestId->Makey='BLA'
and glpactieid->makey in ('TAV','TMA')
The next question is of course which program writes these values? and how? what's the source?
I get more and more the impression that some 'aged' program writes directly into the global.
Probably directly some $HOROLOG which is a combined %DATE,%TIME construct.
Hello Robert,
De Source is Labosys from Philips Medical Systems.
The query gives still the same error with pnr 27085070017
Another pnr 24084205019 gives this as result:
24084205019||2021-03-15 09:19:00||3422||2021-03-16 14:51:24||4 2021-03-15 09:19:00 2021-03-16 14:51:24
So you have to examine this special record in detail in the underlying Global.
Seems to be a broken record that you hit by accident.
Hallo Robert,
I made a mistake.
This is the query:
select top 1 DateTijdSec from
GLPPatTcActie
where pnr = '27085070017' and LTestId->Makey='BLA' and VerzamelDatTijd < '2021-01-29 14:48' and glpactieid->makey in ('TAV','TMA') order by DateTijdSec desc