Workaround !
It seems that the sql processor wait external date format to evaluate the
where condition. And I understand the subquerires return the date in
internal format, sql-processor don't execute the o-convertion.
I define a second I-Type like this :
Name.......... Number
TODAY I DATE()
TODAY_OCONV I OCONV(DATE(),'D')
And then,
SELECT MYDATEFILE FROM MYTABLE
WHERE MYDATEFIELD operator (SELECT TODAY_OCONV FROM CALENDAR) ;
Optimizing subquery block 1
Driver source: CALENDAR
Access method: file scan
UniVerse/SQL: Executing non-correlated subquery block 1
Optimizing query block 0
Table restriction: MYTABLE.MYDATEFIELD operator '17 JUN 2006'
And it works good. !
Thank you very much.
Manu
----- Original Message -----
From: "john reid" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Friday, June 16, 2006 8:45 PM
Subject: Re: [U2] [UV][uvodbc]where date_field <= (subqueries)
Must have something to do with the conversion... 'cause this seems to
work.
j
SELECT DTTEST FROM VOC WHERE VOC.DTTEST EQ (SELECT TODAY FROM TEST )
EXPLAIN
SQL+;
Optimizing subquery block 1
Driver source: TEST
Access method: file scan
UniVerse/SQL: Executing non-correlated subquery block 1
Optimizing query block 0
Table restriction: VOC.DTTEST = 14047
Driver source: VOC
Access method: file scan
UniVerse/SQL: Press any key to continue or 'Q' to quit
DT..........
14047
On 6/16/06, Manu Fernandes <[EMAIL PROTECTED]> wrote:
Hello all,
(on Universe, with UVODBC connector)
One bottle in the see !
How to create a odbc sql queries with WHERE clause test DATEFIELD with
subqueries DATE result value.
exemple :
I have a generic table where I define "CALENDAR" values from DATE()
computing
>SELECT * FROM CALENDAR;
ID.............. DATES
TODAY........... 16 JUN 2006
TOMORROW........ 17 JUN 2006
YESTERDAY....... 15 JUN 2006
CURRENTYEAR..... 2006
PREVIOUSYEAR.... 2005
CURRENTMONTH.... 06
PREVIOUSMONTH... 05
DAYOFWEEK....... 5
CURRENTQUARTER.. 2
PREVIOUSQUARTER. 1
NEXTQUARTER..... 3
etc...
From SQL stmt I want to Select all record where datefield check TODAY, I
write like this :
SELECT MYDATEFIELD FROM MYTABLE WHERE MYTABLE.MYDATEFIELD operator
(SELECT
TODAY FROM CALENDAR);
with =, <=, >= operators the system reply nothing found !
with IN operator, the system found all record "=" TODAY
All idea are welcome to test greather/less than today... .
Thanks
Manu
-------
u2-users mailing list
[email protected]
To unsubscribe please visit http://listserver.u2ug.org/
--
john
-------
u2-users mailing list
[email protected]
To unsubscribe please visit http://listserver.u2ug.org/
-------
u2-users mailing list
[email protected]
To unsubscribe please visit http://listserver.u2ug.org/
