Should be simple enough I thought...
I will provide the full scenario because I'm not sure if a solution would be simpler on the database side or the Webfocus side.
Data was exported out of a PIC database into .csv files which were pulled into SQL Server 7 via DTS. There is an ODBC connection to the Webfocus Server (the only way our local support people knew how to setup).
All the fields were set as varchar 255. I am a novice in means of database development but changed the field properties in design table mode making dates 'datetime' and dollar figures as 'money' leaving the rest varchar but altering field sizes.
The dates weren't viewable until I changed the .mas file Actual as A20 and I put the Usage as MDYY.
However, I cannot do any where/if statements on the date fields for any variation.
0 ERROR AT OR NEAR LINE 11 IN PROCEDURE MEMFEX FOCEXEC *
(FOC177) INVALID DATE CONSTANT: )
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
I also receive some errors on sum fields:
(FOC1400) SQLCODE IS 409 (HEX: 00000199)
: [22005] [Microsoft][ODBC SQL Server Driver][SQL Server]The sum or averag
: e aggregate operation cannot take a varchar data type as an argument. [3
: 7000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could
: not be prepared.
L (FOC1406) SQL OPEN CURSOR ERROR. : SALESANALYSIS
My intuition is that these issues could be alleviated more efficiently via altering the database, but the support staff are trying to create a webfocus workaround. I'll either at this point!
I would really appreciate if someone could provide with some suggestions to alleviate my issues.
Thank you in advance.
I will provide the full scenario because I'm not sure if a solution would be simpler on the database side or the Webfocus side.
Data was exported out of a PIC database into .csv files which were pulled into SQL Server 7 via DTS. There is an ODBC connection to the Webfocus Server (the only way our local support people knew how to setup).
All the fields were set as varchar 255. I am a novice in means of database development but changed the field properties in design table mode making dates 'datetime' and dollar figures as 'money' leaving the rest varchar but altering field sizes.
The dates weren't viewable until I changed the .mas file Actual as A20 and I put the Usage as MDYY.
However, I cannot do any where/if statements on the date fields for any variation.
0 ERROR AT OR NEAR LINE 11 IN PROCEDURE MEMFEX FOCEXEC *
(FOC177) INVALID DATE CONSTANT: )
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
I also receive some errors on sum fields:
(FOC1400) SQLCODE IS 409 (HEX: 00000199)
: [22005] [Microsoft][ODBC SQL Server Driver][SQL Server]The sum or averag
: e aggregate operation cannot take a varchar data type as an argument. [3
: 7000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could
: not be prepared.
L (FOC1406) SQL OPEN CURSOR ERROR. : SALESANALYSIS
My intuition is that these issues could be alleviated more efficiently via altering the database, but the support staff are trying to create a webfocus workaround. I'll either at this point!
I would really appreciate if someone could provide with some suggestions to alleviate my issues.
Thank you in advance.