Javier, Bernie, Victor, et. al.
Thanks so much to all who have responded. I really appreciate the help
and
suggestions.
My bottom line question is: Which is the best practice for using defined
variables against date columns?
Is it best practice to use true date variables (which seems to make the
most
sense) or should I be using the text equivalent of a date variable? What
would Wayne or Razzak recommend? Is there a preferred date format that
may
be less problematic?
I frequently use the BETWEEN option in my SELECT statements. Would there
be
any difference in the way R:Base interprets "BETWEEN" versus using
"greater
than or equal" and "less than or equal to" syntax?
Thanks again.
Mike Ramsour
c/o AK Steel Coshocton Works
Phone: 740-829-4340
-----Original Message-----
From: Javier Valencia [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 20, 2005 11:43 AM
To: [email protected]
Subject: [RBASE-L] - RE: Invalid date value error
Mike:
I see your problem...you need to compare DATE type to DATE type; you
expression should be:
SET VAR vmaxdate DATE = ( .#date - 1)
SET VAR vmindate DATE = ( .vmaxdate - 7 )
-- VAR vmaxtext TEXT = .vmaxdate
-- VAR vmintext TEXT = .vmindate
SELECT dispdate, dfct_code, pounds FROM rej_view +
WHERE dispdate BETWEEN .vmindate AND .vmaxdate
-- (where dispdate is a date column)
Javier,
Javier Valencia, PE
President
Valencia Technology Group, L.L.C.
14315 S. Twilight Ln, Suite #14
Olathe, Kansas 66062-4578
Office (913)829-0888
Fax (913)649-2904
Cell (913)915-3137
================================================
Attention:
The information contained in this message and or attachments is intended
only for the person or entity to which it is addressed and may contain
confidential and/or privileged material. Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended recipient
is prohibited. If you received this in error, please contact the sender
and
delete the material from all system and destroy all copies.
======================================================
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] Behalf Of Ramsour
Mike
Sent: Tuesday, September 20, 2005 10:25 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - RE: Invalid date value error
Javier:
The place in my code where I get the error is when I use the variable
reference in a SELECT statement.
For example:
SET VAR vmaxdate DATE = ( .#date - 1)
--
SET VAR vmindate DATE = ( .vmaxdate - 7 )
--
SET VAR vmaxtext TEXT = .vmaxdate
--
SET VAR vmintext TEXT = .vmindate
--
SELECT dispdate, dfct_code, pounds FROM rej_view WHERE dispdate BETWEEN
.vmintext AND .vmaxtext (where dispdate is a date column)
It's something to do with how R:Base is interpreting the variables at a
particular moment in time. The same variables will be interpreted
correctly
at one point in a program and will err out in another part of my program.
I am going to go through my programs and use your suggestion about using
the
CTXT function. I'll see if that helps.
Thanks -- Mike
-----Original Message-----
From: Javier Valencia [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 20, 2005 10:51 AM
To: [email protected]
Subject: [RBASE-L] - RE: Invalid date value error
Mike:
I believe that your code should work correctly; however, I prefer the
following command to convert a DATE value to TEXT:
SET VAR vmintext TEXT = (CTXT(.vmindate))
I am not sure where in your code you get the error. I would think that it
would be if you try to compare/equate variables of different type. You
might
try TRACEing your code to determine where you get the error and then make
sure that your variables are properly defined and your expressions have
variables of like type.
Javier,
Javier Valencia, PE
President
Valencia Technology Group, L.L.C.
14315 S. Twilight Ln, Suite #14
Olathe, Kansas 66062-4578
Office (913)829-0888
Fax (913)649-2904
Cell (913)915-3137
================================================
Attention:
The information contained in this message and or attachments is intended
only for the person or entity to which it is addressed and may contain
confidential and/or privileged material. Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended recipient
is prohibited. If you received this in error, please contact the sender
and
delete the material from all system and destroy all copies.
======================================================
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] Behalf Of Ramsour
Mike
Sent: Tuesday, September 20, 2005 9:11 AM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Invalid date value error
Good morning:
I frequently get an "Invalid date value" error in my programs. Based on
advice from the list, I typically create a date variable and then assign
the
value to a text variable.
For example:
SET VAR vmindate DATE = ( .#date - 1)
--
SET VAR vmintext TEXT = .vmindate
I then use the text equivalent of the variable in my program using
<column>
= .vmintext.
What is perplexing is that I can run a program and get the error message
and
then IMMEDIATELY rerun the program and it will run fine with no errors. I
have confirmed the value of the variables using the SHOW VAR command. My
database gets RELOADed daily and I run R:SCOPE against to verify the
database integrity.
Why is this happening? Is there a better way to deal with date values?
I am using R:Base 6.5++ (sorry, my company hasn't given me the money yet
to
upgrade to 7.1 -- drat!)
Please help before I go completely crazy!
Thanks
Mike Ramsour
c/o AK Steel
Voice 740-829-4340
