In our OpenEdge 10.1B database we have a database table called ADDDATA which provides general storage for user-definable fields. The table contains information to identify what entity the data relates to, what type of data the row contains, and then a 32-extent character field called DATA where the actual field values are stored (up to 32 user definable field values can be stored in one row).
Each extent of the DATA field holds a character value, but the interpretation/presentation of these field values might be a different data type, e.g. integer, decimal or date.
What we are trying to do is create some ODBC-accessible SQL views in the Progress database, so we can present the data in the ADDDATA table in a more structured and user-friendly form, with meaningful column names and the character data converted to the appropriate type. For example DATA[7] might be defined as "MoneyOnAccount" and hold a value of "150.00" as character, and we want to create a SQL view where DATA[7] is converted from character to decimal and present it with a column name of "MoneyOnAccount".
But we are finding problems with conversion of character values to dates, attached is one of our view definitions where DATA[1] is being converted to a date value. The character values are all stored in dd/mm/yyyy format (as we are in the UK). The view is created OK, but when trying to execute queries over it we are getting errors, such as:
what is the definition of the view where you are converting from character string to date?
Hi Gus,
This is the full definition of the view, the date conversion part is in bold (following on from my previous post about pro_element(...) etc. you'll see that we're still using this function instead of the square-bracket syntax to access separate extents from the data field).
CREATE VIEW "CUSTOM"."INCDET1_MA"
(
"FAC-TAB-NUMBER",
"GENTYPE",
"INCABSTR",
"INCADD1",
"INCADD2",
"INCCNTRY",
"INCCOUNT",
"INCDATE",
"INCDESC",
"INCHS",
"INCPOSTC",
"INCSITE",
"INCTIME",
"INCTOWN",
"INCUPDAT",
"MOD-DATE",
"MOD-TIME",
"MOD-USER",
"ORIINCDT",
"REFERENCE",
"SPARE1",
"SPARE2",
"SPARE3",
"SPARE4",
"SPARE5",
"SPARE6",
"SPARE7",
"SPARE8",
"SPECTYPE",
"TAB-CODE",
"TYPE"
)
AS SELECT
"PUB"."ADDDATA"."FAC-TAB-NUMBER",
convert ('character', nullif( pro_element ("PUB"."ADDDATA"."DATA" ,12 ,12),'?')),
convert ('character', nullif( pro_element ("PUB"."ADDDATA"."DATA" ,15 ,15),'?')),
convert ('character', nullif( pro_element ("PUB"."ADDDATA"."DATA" ,3 ,3),'?')),
convert ('character', nullif( pro_element ("PUB"."ADDDATA"."DATA" ,4 ,4),'?')),
convert ('character', nullif( pro_element ("PUB"."ADDDATA"."DATA" ,8 ,8),'?')),
convert ('character', nullif( pro_element ("PUB"."ADDDATA"."DATA" ,6 ,6),'?')),
convert ('date', nullif(nullif( pro_element ("PUB"."ADDDATA"."DATA" ,1 ,1),'?'),'')),
convert ('character', nullif( pro_element ("PUB"."ADDDATA"."DATA" ,9 ,9),'?')),
convert ('character', nullif( pro_element ("PUB"."ADDDATA"."DATA" ,10 ,10),'?')),
convert ('character', nullif( pro_element ("PUB"."ADDDATA"."DATA" ,7 ,7),'?')),
convert ('character', nullif( pro_element ("PUB"."ADDDATA"."DATA" ,11 ,11),'?')),
convert ('character', nullif( pro_element ("PUB"."ADDDATA"."DATA" ,2 ,2),'?')),
convert ('character', nullif( pro_element ("PUB"."ADDDATA"."DATA" ,5 ,5),'?')),
convert ('character', nullif( pro_element ("PUB"."ADDDATA"."DATA" ,16 ,16),'?')),
"PUB"."ADDDATA"."MOD-DATE",
"PUB"."ADDDATA"."MOD-TIME",
"PUB"."ADDDATA"."MOD-USER",
convert ('character', nullif( pro_element ("PUB"."ADDDATA"."DATA" ,14 ,14),'?')),
"PUB"."ADDDATA"."REFERENCE",
"PUB"."ADDDATA"."SPARE1",
"PUB"."ADDDATA"."SPARE2",
"PUB"."ADDDATA"."SPARE3",
"PUB"."ADDDATA"."SPARE4",
"PUB"."ADDDATA"."SPARE5",
"PUB"."ADDDATA"."SPARE6",
"PUB"."ADDDATA"."SPARE7",
"PUB"."ADDDATA"."SPARE8",
convert ('character', nullif( pro_element ("PUB"."ADDDATA"."DATA" ,13 ,13),'?')),
"PUB"."ADDDATA"."TAB-CODE",
"PUB"."ADDDATA"."TYPE"
FROM "PUB"."ADDDATA" WHERE "PUB"."ADDDATA"."TYPE" = 'MA'
AND "PUB"."ADDDATA"."TAB-CODE" = 'INCDET1';
That looks reasonable to me. I can't see a reason for why it does not work. But why do you have nested nullif()'s ?
Hi Gus,
Thanks for looking, yes good point about the nullifs(), I've been trying to get a much simpler example together to see if I can shed any more light on it, with a much smaller data set and a simpler view definition I can reproduce the problem more easily but I still don't know what's causing it.
Here's a simpler view definition, the definition of the XDATE field converts the string values from dd/mm/yyyy format to mm/yy/dddd format before executing the to_date() function (with a test on empty string to make sure that null dates come through correctly).
CREATE VIEW "CUSTOM"."SRM1"
(
"FAC-TAB-NUMBER",
"XDATE" ,
"REFERENCE",
"TAB-CODE",
"TYPE"
)
AS SELECT
"PUB"."ADDDATA"."FAC-TAB-NUMBER",
case when pub.adddata.data[1] > '' then to_date(substr(pub.adddata.data[1], 4, 3) + substr(pub.adddata.data[1], 1, 3) + substr(pub.adddata.data[1], 7, 4)) else null end,
"PUB"."ADDDATA"."REFERENCE",
"PUB"."ADDDATA"."TAB-CODE",
"PUB"."ADDDATA"."TYPE"
FROM "PUB"."ADDDATA" WHERE "PUB"."ADDDATA"."TYPE" = 'MA'
AND "PUB"."ADDDATA"."TAB-CODE" = 'SRM';
GRANT SELECT ON "CUSTOM"."SRM1" TO PUBLIC;
COMMIT WORK;
I'm out of my depth here and have no idea what is wrong. I'll have to get professional help.