RE: Witango-Talk: Probably SQL ?
I tried the following with the SGET function. <@assign local$DateSelect "( (SGET(o1.Ord_OrderNumber,8,1) >= '<@arg StartYear encoding=sql><@arg StartMonth encoding=sql><@arg StartDay encoding=sql>') AND ('<@arg EndYear encoding=sql><@arg EndMonth encoding=sql><@arg EndDay encoding=sql>' <= SGET(o1.Ord_OrderNumber,8,1)) )"> Got the following error: Syntax is incorrect for the command TEXT Any ideas? Steve Fogelson -Original Message- From: Fogelson, Steve [mailto:[EMAIL PROTECTED] Sent: Saturday, August 09, 2003 10:57 AM To: Witango User Group (E-mail) Subject: Witango-Talk: Probably SQL ? Win2K Server, R:Tango 2000, R:Base 6.5 I create a field in a table with the following witango code: <@ASSIGN user$OrderNumber "<@currentdate format=datetime:%Y%m%d><@tstosecs <@currenttimestamp>>"> Later I want to do a search based strictly on the first 8 characters of the field. I use the following code to set up the Where variable for DBMS <@assign local$DateSelect "( (o1.Ord_OrderNumber >= '<@arg StartYear encoding=sql><@arg StartMonth encoding=sql><@arg StartDay encoding=sql>') AND ('<@arg EndYear encoding=sql><@arg EndMonth encoding=sql><@arg EndDay encoding=sql>' <= o1.Ord_OrderNumber) )"> I then use the following in the DBMS: Where <@var local$Select encoding=none> Some how I have to do something with o1.Ord_OrderNumber Maybe I am going to have to create a field with just the date Thanks Steve Fogelson Internet Commerce Solutions TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
RE: Witango-Talk: Probably SQL ?
Atrix and Ben, Thanks for the reply. I got rid of the text error. It is working now. But I will tell you what I am doing. In case you are interested. The SGET function appears to be what will do this. I have a field in my OrderSummary table called Ord_OrderNumber. I assign a unique value to the field with the following: <@ASSIGN user$OrderNumber "<@currentdate format=datetime:%Y%m%d><@tstosecs <@currenttimestamp>>"> This tag yields a 18 digit value. Later on I want to print orders for today or within a range of dates. I thought I should be able select rows from the table based on the first 8 characters of this field. I create a "Where" clause for the DBMS with the following when looking for todays orders. <@assign local$Select "( ((SGET(o1.Ord_OrderNumber,8,1))='<@currentdate format="%Y%m%d" encoding=sql>') ) "> Thanks for the help. Steve -Original Message- From: Ben Johansen [mailto:[EMAIL PROTECTED] Sent: Saturday, August 09, 2003 10:00 PM To: [EMAIL PROTECTED] Subject: RE: Witango-Talk: Probably SQL ? Hey, Steve can you throw me a bone here :-) I still don't see what you are trying to do :-P R:Base has some function you can use to deal with datetime data type (DEXTRACT(datetime)) Returns the date portion of a value that has a DATETIME data type. In the following example, the value of vdextract is 06/12/93. SET VAR vdextract = (DEXTRACT('06/12/93 12:15:30.123')) (TEXTRACT(datetime)) Returns the time portion of DATETIME. In the following example, the value of vtextract is 12:15:30.123. SET VAR vtextract = (TEXTRACT('08/09/95 12:15:30.123')) select * from table where (DEXTRACT(DateTimeColumn)) = 'MM/DD/YY' --- also in my experience with R:Tango you need to treat dates like text and quote them. Ben Johansen - http://www.pcforge.com -Authorized WiTango Reseller http://www.pcforge.com/WitangoGoodies.htm -Authorized Alt-N Reseller http://www.pcforge.com/AltN.htm -Original Message- From: Fogelson, Steve [mailto:[EMAIL PROTECTED] Sent: Saturday, August 09, 2003 7:44 PM To: '[EMAIL PROTECTED]' Subject: RE: Witango-Talk: Probably SQL ? I tried the following with the SGET function. <@assign local$DateSelect "( (SGET(o1.Ord_OrderNumber,8,1) >= '<@arg StartYear encoding=sql><@arg StartMonth encoding=sql><@arg StartDay encoding=sql>') AND ('<@arg EndYear encoding=sql><@arg EndMonth encoding=sql><@arg EndDay encoding=sql>' <= SGET(o1.Ord_OrderNumber,8,1)) )"> Got the following error: Syntax is incorrect for the command TEXT Any ideas? Steve Fogelson -Original Message- From: Fogelson, Steve [mailto:[EMAIL PROTECTED] Sent: Saturday, August 09, 2003 10:57 AM To: Witango User Group (E-mail) Subject: Witango-Talk: Probably SQL ? Win2K Server, R:Tango 2000, R:Base 6.5 I create a field in a table with the following witango code: <@ASSIGN user$OrderNumber "<@currentdate format=datetime:%Y%m%d><@tstosecs <@currenttimestamp>>"> Later I want to do a search based strictly on the first 8 characters of the field. I use the following code to set up the Where variable for DBMS <@assign local$DateSelect "( (o1.Ord_OrderNumber >= '<@arg StartYear encoding=sql><@arg StartMonth encoding=sql><@arg StartDay encoding=sql>') AND ('<@arg EndYear encoding=sql><@arg EndMonth encoding=sql><@arg EndDay encoding=sql>' <= o1.Ord_OrderNumber) )"> I then use the following in the DBMS: Where <@var local$Select encoding=none> Some how I have to do something with o1.Ord_OrderNumber Maybe I am going to have to create a field with just the date Thanks Steve Fogelson Internet Commerce Solutions TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
RE: Witango-Talk: Probably SQL ?
Hey, Steve can you throw me a bone here :-) I still don't see what you are trying to do :-P R:Base has some function you can use to deal with datetime data type (DEXTRACT(datetime)) Returns the date portion of a value that has a DATETIME data type. In the following example, the value of vdextract is 06/12/93. SET VAR vdextract = (DEXTRACT('06/12/93 12:15:30.123')) (TEXTRACT(datetime)) Returns the time portion of DATETIME. In the following example, the value of vtextract is 12:15:30.123. SET VAR vtextract = (TEXTRACT('08/09/95 12:15:30.123')) select * from table where (DEXTRACT(DateTimeColumn)) = 'MM/DD/YY' --- also in my experience with R:Tango you need to treat dates like text and quote them. Ben Johansen - http://www.pcforge.com -Authorized WiTango Reseller http://www.pcforge.com/WitangoGoodies.htm -Authorized Alt-N Reseller http://www.pcforge.com/AltN.htm -Original Message- From: Fogelson, Steve [mailto:[EMAIL PROTECTED] Sent: Saturday, August 09, 2003 7:44 PM To: '[EMAIL PROTECTED]' Subject: RE: Witango-Talk: Probably SQL ? I tried the following with the SGET function. <@assign local$DateSelect "( (SGET(o1.Ord_OrderNumber,8,1) >= '<@arg StartYear encoding=sql><@arg StartMonth encoding=sql><@arg StartDay encoding=sql>') AND ('<@arg EndYear encoding=sql><@arg EndMonth encoding=sql><@arg EndDay encoding=sql>' <= SGET(o1.Ord_OrderNumber,8,1)) )"> Got the following error: Syntax is incorrect for the command TEXT Any ideas? Steve Fogelson -Original Message- From: Fogelson, Steve [mailto:[EMAIL PROTECTED] Sent: Saturday, August 09, 2003 10:57 AM To: Witango User Group (E-mail) Subject: Witango-Talk: Probably SQL ? Win2K Server, R:Tango 2000, R:Base 6.5 I create a field in a table with the following witango code: <@ASSIGN user$OrderNumber "<@currentdate format=datetime:%Y%m%d><@tstosecs <@currenttimestamp>>"> Later I want to do a search based strictly on the first 8 characters of the field. I use the following code to set up the Where variable for DBMS <@assign local$DateSelect "( (o1.Ord_OrderNumber >= '<@arg StartYear encoding=sql><@arg StartMonth encoding=sql><@arg StartDay encoding=sql>') AND ('<@arg EndYear encoding=sql><@arg EndMonth encoding=sql><@arg EndDay encoding=sql>' <= o1.Ord_OrderNumber) )"> I then use the following in the DBMS: Where <@var local$Select encoding=none> Some how I have to do something with o1.Ord_OrderNumber Maybe I am going to have to create a field with just the date Thanks Steve Fogelson Internet Commerce Solutions TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf
Re: Witango-Talk: Probably SQL ?
if you turn on debug you should be able to actualy see the sql its spitting out thats giving the error. if you paste that too that would help us figure out what the problem is (: - Original Message - From: "Fogelson, Steve" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, August 09, 2003 7:43 PM Subject: RE: Witango-Talk: Probably SQL ? > I tried the following with the SGET function. > > <@assign local$DateSelect "( (SGET(o1.Ord_OrderNumber,8,1) >= '<@arg > StartYear encoding=sql><@arg StartMonth encoding=sql><@arg StartDay > encoding=sql>') AND ('<@arg EndYear encoding=sql><@arg EndMonth > encoding=sql><@arg EndDay encoding=sql>' <= SGET(o1.Ord_OrderNumber,8,1)) > )"> > > Got the following error: > > Syntax is incorrect for the command TEXT > > Any ideas? > > Steve Fogelson > > -Original Message- > From: Fogelson, Steve [mailto:[EMAIL PROTECTED] > Sent: Saturday, August 09, 2003 10:57 AM > To: Witango User Group (E-mail) > Subject: Witango-Talk: Probably SQL ? > > > Win2K Server, R:Tango 2000, R:Base 6.5 > > I create a field in a table with the following witango code: > > <@ASSIGN user$OrderNumber "<@currentdate format=datetime:%Y%m%d><@tstosecs > <@currenttimestamp>>"> > > Later I want to do a search based strictly on the first 8 characters of the > field. I use the following code to set up the Where variable for DBMS > > <@assign local$DateSelect "( (o1.Ord_OrderNumber >= '<@arg StartYear > encoding=sql><@arg StartMonth encoding=sql><@arg StartDay encoding=sql>') > AND ('<@arg EndYear encoding=sql><@arg EndMonth encoding=sql><@arg EndDay > encoding=sql>' <= o1.Ord_OrderNumber) )"> > > I then use the following in the DBMS: > > Where > <@var local$Select encoding=none> > > Some how I have to do something with o1.Ord_OrderNumber > > Maybe I am going to have to create a field with just the date > > Thanks > > Steve Fogelson > Internet Commerce Solutions > > TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf > > TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf TO UNSUBSCRIBE: Go to http://www.witango.com/maillist.taf