I am composing a SEQUEL ViewPoint query joining 2 tables. The first table has the following fields: Action, SSAN, MEMNO, RSCODE, USERID, and TIMESTAMP which is in the format of (Ex. 2005-03-11-09.54.18.296000). The other tables is simply used to get the Member NAME by joining the two tables on SSAN.
Why my query starts, the user is presented with a dialog for a date which I have designated as StartDate. The idea is to return all desired fields where table1.TIMESTAMP value is greater than the user selected StartDate.
My current query looks like so:
SELECT memno.1 EDTCDE(L), name.2, 'SYS1' NAME(SYS), "&&startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
FROM library1/table1, library1/table2
JOIN SSAN.1=SSAN.2
WHERE TIMESTAMP>StartDate AND RSCODE='STP'
UNION
SELECT memno.1 EDTCDE(L), name.2, 'SYS2' NAME(SYS), "&&startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
FROM library2/table1, library2/table1
JOIN SSAN.1=SSAN.2
WHERE TIMESTAMP>StartDate AND RSCODE='STP'
ORDER BY sys ASC, memno ASC
Which results in:
Fields TIMESTAMP and STARTDATE in WHERE clause are not compatible.
Cause. . . . .: You are trying to compare two fields that not compatible. One of the following is true:
1 -- One field is numeric and the other is not numeric (character or date/time)
2 -- One field is character and the other is not character (numeric or date/time)
3 -- One field is double-byte and the other is single byte.
This is clearly due to my table1.TIMESTAMP filed being in (Ex. 2005-03-11-09.54.18.296000) format and my query value StartDate being in (ex. 01/01/14).
How can I format my TIMESTAMP value in the WHERE clause in order to compare it with my StartDate value? (Or is there a better way to go about this? I'm still pretty green when it comes to SQL and Database QWerying)
Using Roopesh' suggestion of timestamp > cast(StartDate as datetime) results in:
SELECT memno.1 EDTCDE(L), name.2, 'SYS1' NAME(SYS), "&&startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
FROM library1/table1, library1/table2
JOIN SSAN.1=SSAN.2
WHERE TIMESTAMP>Cast(StartDate as datetime) AND RSCODE='STP'
UNION
SELECT memno.1 EDTCDE(L), name.2, 'SYS2' NAME(SYS), "&&startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
FROM library2/table1, library2/table1
JOIN SSAN.1=SSAN.2
WHERE TIMESTAMP>Cast(StartDate as datetime) AND RSCODE='STP'
ORDER BY sys ASC, memno ASC
There is an error in the WHERE clause. Parser expected ")". Continue Anyway? [YES selected]
I select date 01/01/14 which shows in the sql as "01/01/14 NAME(StartDate) and receive:
Identifier 'AS' preceding ' datetime)' is used incorrectly.
Proper SQL syntax rules have been viloated. The identifier cannot occur where it has been found in the statement. Instad of 'AS', SQL syntax rules allow only: ) ,. If you are using *SEQUEL object authority checking, you cannot use runtime variables in place of the allowed values.
Using Notulysses' suggestion:
Variable StartDate is of Type Date with a length of 10, defaulted to 01/01/2014.
SELECT memno.1 EDTCDE(L), name.2, 'SYS1' NAME(SYS), "&&startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
FROM library1/table1, library1/table2
JOIN SSAN.1=SSAN.2
WHERE CAST(TIMESTAMP as Date)>StartDate AND RSCODE='STP
UNION
SELECT memno.1 EDTCDE(L), name.2, 'SYS2' NAME(SYS), "&&startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
FROM library2/table1, library2/table1
JOIN SSAN.1=SSAN.2
WHERE CAST(TIMESTAMP as Date)>StartDate AND RSCODE='STP
ORDER BY sys ASC, memno ASC
There is an error in the WHERE clause. Parser expected ")". Continue Anyway? [YES selected]
I select date 01/01/14 which shows in the sql as "01/01/14 NAME(StartDate) and receive:
Identifier 'AS' preceding ' Date)>Sta' is used incorrectly.
Proper SQL syntax rules have been viloated. The identifier cannot occur where it has been found in the statement. Instad of 'AS', SQL syntax rules allow only: ) ,. If you are using *SEQUEL object authority checking, you cannot use runtime variables in place of the allowed values.
ViewPoint Syntax can be strange compared to regular SQL. Very glad we will be rid of it as a standard tool in our shop before long.......
EDIT2 (SOLUTION):
As set forth by JamesA the trick was to use the DATE function: DATE(TIMESTAMP).
Use the DATE function to convert the timestamp to just a date portion:
SELECT memno.1 EDTCDE(L), name.2, 'SYS1' NAME(SYS), "&&startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
FROM library1/table1, library1/table2
JOIN SSAN.1=SSAN.2
WHERE DATE(TIMESTAMP)>StartDate AND RSCODE='STP'
UNION
SELECT memno.1 EDTCDE(L), name.2, 'SYS2' NAME(SYS), "&&startdate" NAME(StartDate), CURRENT DATE NAME(CurDate)
FROM library2/table1, library2/table1
JOIN SSAN.1=SSAN.2
WHERE DATE(TIMESTAMP)>StartDate AND RSCODE='STP'
ORDER BY sys ASC, memno ASC