Date Formats, Oracle and a waiting user

I've posted on other topics with no luck so I'm going to start a new post. I've tried the following tips from the other posts�

Version 5.2.5 Dev Studio/Text Editor/Oracle database via ODBC

I updated my EDA prof with the following and then created the master file from an oracle table via ODBC

SQL SQLORA SET DATETIME OFF

My table generated with the following, which isn't any different than without the change to my EDA prof

FIELD=TERMINATION_DT, ALIAS=TERMINATION_DT, USAGE=HYYMDS, ACTUAL=HYYMDS, MISSING=ON, $

I then tried the following which I got from the documentation that was linked in another of the posts.�..

TABLE FILE TCONTRCT
PRINT CONTRACT_ID TERMINATION_DT
WHERE RECORDLIMIT EQ 10
WHERE TERMINATION_DT NE DT(2020/01/01 00:00:00) ;
END

I get the following oracle error
(FOC1400) SQLCODE IS 1756 (HEX: 000006DC)
: [S1000] [Oracle][ODBC][Ora]ORA-01756: quoted string not properly termina
: ted
L (FOC1406) SQL OPEN CURSOR ERROR. : TCONTRCT

Can anyone please help with this�� I'd really appreciate the help ?

Posts: 132 | Location: Kansas | Registered: November 12, 2003

posted

Here's an example of a WHERE statement on a Date-time field.

Meanwhile, I will look into the SQL SQLORA SET DATETIME OFF command. I have not used it before.

I think you can modify the date-time field in the Master that was generated incorrectly: Change it to something like this:

FIELD=ACTIVITY_DATE, ACTIVITY_DATE, YYMD, DATE, MISSING=ON, $

Example of WHERE statement on a Date-time field:

DEFINE FILE CAR
DATE1/HYYMDS = DT(19900101000000);
COUNTER/I2 WITH CAR = COUNTER + 1;
DATE2/HYYMDS = HADD(DATE1,'DAY',COUNTER, 8, DATE2);
END
TABLE FILE CAR
PRINT DATE1 DATE2
BY MODEL
WHERE DATE2 EQ DT(1990/01/10 00:00:00);
END

<JG>

posted

Pietro is absolutely correct manually changing the master as he describes will fix the problem.
This issue affects most of the adapters where dates are concerned and I've used the master edit method on Oracle, MSSQL, SAP R/3 and SAP BW. Worked everytime.

webfocuspgm

Platinum Member

posted

Hide Post

Pietro - THANK YOU .....

I used your code example and it works great..... As for the master that change dropped the time and I still need the time..... My user is very happy now.....

Posts: 132 | Location: Kansas | Registered: November 12, 2003

Ignored post by webfocuspgm posted

Show Post

posted

The SQL SQLORA SET DATETIME OFF command is used to create Masters where the Date-Time columns are generated as Date columns, which, in your case, is not what you require, since your user needs the time in his reports.

Glad to help.

posted

You may have figured this out already, but you can use a Dialog Manager variable in the WHERE statement:

-SET &ECHO=ALL;

-SET &DATETEST = EDIT(&YYMD,'9999/99/99');

DEFINE FILE CAR
DATE1/HYYMDS = DT(20050307000000);
COUNTER/I2 WITH CAR = COUNTER + 1;
DATE2/HYYMDS = HADD(DATE1,'DAY',COUNTER, 8, DATE2);
END
TABLE FILE CAR
PRINT DATE1 DATE2
BY MODEL
WHERE DATE2 EQ DT(&DATETEST 00:00:00);
END

webfocuspgm

Platinum Member

posted

Hide Post

I was so FOCUS'd (hehe) that I hadn't really looked at anything outside my immediate problem and DM doesn't come in to play for that ... but I will file that away for future reference..... THANKS

Posts: 132 | Location: Kansas | Registered: November 12, 2003

Ignored post by webfocuspgm posted

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

Date Formats, Oracle and a waiting user

Focal Point | Privacy Statement | Terms of Service