Mike,
Use the debugger to make sure all your date variables used for
comparisons are set the way you expect.
Ben Petersen
On 22 Sep 2005 at 15:05, Ramsour Mike wrote:
> Coincidentally, I was working on a program using the following code. I was
> TRACEing it and when it got to the point of creating "TEMPVIEW" I got the
> INVALID DATE VALUE (2418) error.
>
> The columns REJDATE and PRODDATE are date columns from the tables.
> PRTO_TRKG_VIEW and DALY_PROD_WTS are views based on underlying tables.
>
> The only thing that may be remotely common to this problem is that I create
> the views with one or more UNION statements. There's no reason that should
> be a problem.
>
> If someone can see where I should be doing this differently I would welcome
> any suggestions. Interestingly, without changing a thing, I re-TRACEd the
> program and it ran fine!
>
> Sign me Frustrated -- Mike
>
> SET VAR vwb_sunday DATE = ( .#DATE - ( 7 + (IDWK( .#DATE )))) -- Determines
> the Sunday date for the previous week
> --
> SET VAR vwe_saturday DATE = ( .vwb_sunday + 6) -- Determines
> the Saturday date for the previous week
> --
> SET VAR vstart DATE = (RDATE(1,2,2005)) -- Sets the
> starting date for fiscal year 2005
> --
> -- Several variables used in the view
> --
> SET VAR vmfg TEXT = 'MFG'
> --
> SET VAR vint TEXT = 'INT'
> --
> SET VAR vsup TEXT = 'SUP'
> --
> SET VAR vst TEXT = 'ST'
> --
> SET VAR vss TEXT = 'SS'
> --
> SET VAR vrt TEXT = 'RT'
> --
> SET VAR vm TEXT = (CHAR(77))
> --
> SET VAR vo TEXT = (CHAR(79))
> --
> SET VAR vac TEXT = 'A,C'
> --
> SET VAR v9999 TEXT = '9999'
> --
> SET VAR vstd_scrap TEXT = 'STANDARD SCRAP'
> --
> CREATE TEMPORARY VIEW tempview (wb_sunday,aor,rej_type,dfct_code,dfct_desc,
> +
> pounds,var_cost) +
> AS SELECT wb_sunday,aor,rej_type,dfct_code,dfct_desc,pounds,var_cost +
> FROM prto_trkg_view +
> WHERE rejdate BETWEEN .vstart AND .vwe_saturday AND rej_type = .vst +
> AND iql_smry = .vm AND rej_srce IN ( &vac ) AND rej_area = .vint +
> AND aor IN ('CBM','JHW','JLF') AND cur_cc_flag IN ( .vm, .vo) +
> UNION ALL SELECT
> wb_sunday,.vsup,rej_type,dfct_code,dfct_desc,pounds,var_cost +
> FROM prto_trkg_view +
> WHERE rejdate BETWEEN .vstart AND .vwe_saturday AND rej_type = .vst +
> AND iql_smry = .vm AND rej_srce IN ( &vac ) AND rej_area = .vint +
> AND aor IN ('CRD','TPR') AND cur_cc_flag IN ( .vm, .vo) +
> UNION ALL SELECT wb_sunday,aor,.vss,.v9999,.vstd_scrap,prod_scrap, +
> prod_scrap_cost FROM daly_prod_wts +
> WHERE proddate BETWEEN .vstart AND .vwe_saturday +
> AND smryflag = .vm AND depttype = .vmfg
>
>
> -----Original Message-----
> From: Javier Valencia [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 22, 2005 2:24 PM
> To: [email protected]
> Subject: [RBASE-L] - RE: Invalid date value error
>
>
> Mike:
> It has always been my understanding that you compare DATE columns against
> DATE variables. It is possible that you could get away comparing DATE
> columns against TEXT variables as R:Base used to be very forgiving on this
> type of thing, but it is likely that as the rules are tighten, it might not
> work in the future.
> 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: Thursday, September 22, 2005 12:42 PM
> To: RBASE-L Mailing List
> Subject: [RBASE-L] - RE: Invalid date value error
>
> 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
>
