Mike, Several items come to mind. syntax may be incorrect "IN ( &vac )". You define vac as: "SET VAR vac TEXT = 'A,C' "
I seem to remember that you cannot use an ampersand variable within parenthesis using the IN syntax. Instead you must include the paranthesis withing the variable definition. thus you would do the following: SET VAR vac TEXT = '(A,C)' " IN &vac " Also you did not list the source for "prto_trkg_view" which I am assuming is a view. If so do you do and manipulation of date columns in this view. If you do then RBASE will have assigned the view column a data type of NOTE. Any time you use FUNCTIONS or manipulate underlying column info in the view definition RBASE assigns a datatype of NOTE instead of the expected data type assigned to the underlying column. -- Jim Bentley American Celiac Society 266 Midway Dr River Ridge LA 70123 email: [EMAIL PROTECTED] email: [EMAIL PROTECTED] Phone: 504-738-6165 -----Original Message----- From: Ramsour Mike <[EMAIL PROTECTED]> Sent: Thu, 22 Sep 2005 15:05:43 -0400 To: [email protected] (RBASE-L Mailing List) Subject: [RBASE-L] - RE: Invalid date value error 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 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
