Question
Josefien Janssen · Mar 23, 2021

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?

 


 

 

Product version: Caché 2016.1
$ZV: 2016.1.3
00
1 0 8 79
Log in or sign up to continue

Replies

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),DateTijdSec
from  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