... but I still don't know exactly why ... Apparently, if I alias all the tables in the FROM-clause : FROM + R_AWARD_HEADER rah, + R_AWARD_AMOUNT_INFO raai +
and use those aliases in the WHERE-clause: WHERE + rah.MIT_AWARD_NUMBER = rah.MIT_AWARD_NUMBER + AND + raai.SEQUENCE_NUMBER = rah.SEQUENCE_NUMBER + AND + raai.CURRENT_FUND_EFFECTIVE_DATE < .#NOW + AND + raai.OBLIGATION_EXPIRATION_DATE >= .#NOW IT WORKS! On the ORACLE side, I think it works either way. I'm not saying anything pro or con and I confess an R-Bias, it's just an observation on the seemingly subtle but hugely discreet nuances of different engines ... L8R, Steve in Memphis -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Wills, Steve Sent: Thursday, February 07, 2008 3:33 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: DATETIME Comparison Q Emmit, thanks ... 1) Do you mean, are they typed in RBv8 as DATETIME or do you mean are they displayed like DATETIME? In either case, the answer is yes. 2) Yep', I forgot about the dotted-var on the left, despite Bill_D helping me with that sometime way back in the pre-carbon-footprint era - no insult intended, Bill. Still, when I either "un-dotted" the var's or flipped 'em back on the right-hand side, no joy, even though it then says "No rows found". (Yep', there are rows that meet the spec's of the WHERE-clause. 3) I think that was just the wrapping, as the original looks fine, even though I was really hoping that it was this one easy-to-miss, easy-to-fix problem. Steve -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Emmitt Dove Sent: Thursday, February 07, 2008 3:11 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: DATETIME Comparison Q Steve, 1) Are the Oracle columns actually being seen as DATETIME datatypes? 2) I would recommend putting the ".#NOW" on the right side of the comparison. Dotted variables always belong on the right. 3) In your posting, there is an extra plus sign in the WHERE clause of the BROWSE. Maybe, though, that was just the way your message wrapped here. I see ".#NOW > R_AWARD_AMOUNT_INFO.CURRENT_FUND_EFFECTIVE_DATE + AND +" but when it is quoted here it wraps differently. Emmitt Dove Manager, DairyPak Business Systems Evergreen Packaging, Inc. [EMAIL PROTECTED] [EMAIL PROTECTED] (203) 643-8022 -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Wills, Steve Sent: Thursday, February 07, 2008 3:51 PM To: RBASE-L Mailing List Subject: [RBASE-L] - DATETIME Comparison Q I am having troubles making comparisons with values in a couple of DATETIME fields from a table whose structure is shown below. It is a copy of an ORACLE-based table, copied while SATTACHed to that DB (but I don't know if that has any bearing on my problem). This statement works: SELECT * FROM R_AWARD_AMOUNT_INFO WHERE .#NOW > CURRENT_FUND_EFFECTIVE_DATE AND .#NOW <= OBLIGATION_EXPIRATION_DATE This one does not, run either from command line (w/o continuator) or from command file : BROWSE + R_AWARD_AMOUNT_INFO.OBLIGATED_CHANGE AS AMOUNT, + R_AWARD_HEADER.RH_TITLE AS PROJECT_TITLE, + R_AWARD_AMOUNT_INFO.CURRENT_FUND_EFFECTIVE_DATE AS CURR_FND_EFF_DT, + R_AWARD_AMOUNT_INFO.OBLIGATION_EXPIRATION_DATE AS OBLG_EXPIRE_DT + FROM + R_AWARD_HEADER, + R_AWARD_AMOUNT_INFO + WHERE + R_AWARD_AMOUNT_INFO.MIT_AWARD_NUMBER = R_AWARD_HEADER.MIT_AWARD_NUMBER + AND + R_AWARD_AMOUNT_INFO.SEQUENCE_NUMBER = R_AWARD_HEADER.SEQUENCE_NUMBER + AND + .#NOW > R_AWARD_AMOUNT_INFO.CURRENT_FUND_EFFECTIVE_DATE + AND + .#NOW <= R_AWARD_AMOUNT_INFO.OBLIGATION_EXPIRATION_DATE Throwing this error: -ERROR- Invalid DATETIME value (2418) I have tried using various functions, "flip-flopping" the operands and operators, and I just can't figure out the problem. Any ideas? Thanks, Steve in Memphis -- *** TABLE INFO *** R>SET NAMEWIDTH 30 R>list table R_AWARD_AMOUNT_INFO Table: R_AWARD_AMOUNT_INFO No Lock(s) Descr: Server table - COEUS.OSP$AWARD_AMOUNT_INFO COEUS_ORACE No. Column Name Attributes --- ------------------ ------------------------------------------------------ 1 MIT_AWARD_NUMBER Type : TEXT 10 NOT NULL Consrnt: PRIMARY KEY 2 SEQUENCE_NUMBER Type : NUMERIC ( 4, 0) NOT NULL Consrnt: PRIMARY KEY 3 AMOUNT_SEQUENCE_NUMBER Type : NUMERIC ( 4, 0) NOT NULL Consrnt: PRIMARY KEY 4 ANTICIPATED_TOTAL_AMOUNT Type : NUMERIC (12, 2) 5 ANT_DISTRIBUTABLE_AMOUNT Type : NUMERIC (12, 2) 6 FINAL_EXPIRATION_DATE Type : DATETIME 7 CURRENT_FUND_EFFECTIVE_DATE Type : DATETIME 8 AMOUNT_OBLIGATED_TO_DATE Type : NUMERIC (12, 2) 9 OBLI_DISTRIBUTABLE_AMOUNT Type : NUMERIC (12, 2) 10 OBLIGATION_EXPIRATION_DATE Type : DATETIME 11 TRANSACTION_ID Type : TEXT 10 Index : SINGLE-COLUMN 12 ENTRY_TYPE Type : TEXT 1 13 EOM_PROCESS_FLAG Type : TEXT 1 14 UPDATE_TIMESTAMP Type : DATETIME 15 UPDATE_USER Type : TEXT 8 16 ANTICIPATED_CHANGE Type : NUMERIC (12, 2) 17 OBLIGATED_CHANGE Type : NUMERIC (12, 2) Current number of rows: 24596 J. Stephen Wills Program Manager, Research Informatics Office of the Vice Chancellor for Research University of Tennessee Health Science Center 62 S. Dunlap, Suite 400 Memphis, TN 38163 Office: 901-448-2389 FAX : 901-448-7133
