Re: Query problems

Ahh, thank you.  I didn't consider converting it to a string and then doing
the manipulation that way.  It makes my query a lot easier to code, read,
understand and maintain.

Also, since I could do it in 2 lines now instead of the 6+ required before,
I went ahead and made it a YYMMDD format.  Which just meant
going from INVDTE, 1, 4 instead of INVDTE, 1, 2

I do hope it will convert a 6 0 field value of 31299 to '031299' instead of
'31299'.  Guess I"ll find out.

I seem to recall trying to convert it to a string when I first wanted to do this
about 6 months ago, but I couldn't find the function to do it (DIGITS).  I
was looking for STR or STRING or such, as that's how most other langauges
call it.

Thank you for your help.  I still think there's a bug in Query though, but this
will work around it quite well.

Regards,

Jim Langston

Jon Erickson wrote:

> Check the result field definitions, eg. 4,2 & 6,0 etc.  You can also use the
> DIGITS to a 6 char field then use SUBSTR on the char field.
> INVDTE  DIGITS(INVDTE#)
> INVYYMM SUBSTR(INVDTE,5,2) ||
>                 SUBSTR(INVDTE,1,2)
>
> Then create a selection criteria something like:
> INVYYMM GE '9810'
> AND INVYYMM LE '9903'
>
> HTH,
> Jon A. Erickson
> Sr. Programmer Analyst
> 800.COM Inc.
> 1516 NW Thurman St
> Portland, OR  97209-2517
>
> Direct: 503.944.3613
> Fax: 503.944.3690
> Web: http://800.com
>
> -----Original Message-----
> From: Jim Langston [mailto:jlangston@conexfreight.com]
> Sent: Thursday, September 16, 1999 8:11 AM
> To: 'MIDRANGE-L@midrange.com'
> Subject: Query problems
>
> I am having a problem in a query, that I did a work around for, but
> don't' know why it happens.
>
> We have some dates in MMDDYY format stored as numbers.
>
> In a query I define some result fields as:
> Temp1      ARINDT / 100
> Temp2      Temp1 * 100
> InvYear     ARINDT - Temp2
> InvMonth   ARINDT / 10000
>
> Then, in my select records I have
>             InvYear      EQ     98
> AND    InvMonth   GE     10
> OR       InvYear      EQ     99
> AND    InvMonth    LE     03
>
> The whole idea being to get the range of records from 10/01/98 to
> 03/31/99
>
> Well, it works but... I do no have any data for 03/xx/99  I have January
> and February but not March, and I don't know why.
>
> To resolve this I finally made yet some more result fields:
>
> Temp3         InvYear * 100
> InvYYMM   Temp3 + InvMonth
>
> Then, I select for
>            InvYYMM   GE    9810
> AND  InvYYMM    LE    9903
>
> Which works, and gives me all the records.
>
> Is this a bug I'm looking at?  I'm on V3R7M0
>
> And do you know of an easier way to do this?
>
> Regards,
>
> Jim Langston
>
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator:
> david@midrange.com
> +---
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator: david@midrange.com
> +---

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---