RE: SQL Newbie question

For the numeric invoice number use DIGITS to convert to character with
leading zero's
or CHAR for character with no leading zero's





Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx


                                                                       
             steema@diskhaven.                                         
             com                                                       
             Sent by:                                                   To
             rpg400-l-bounces@         "RPG programming on the AS400 / 
             midrange.com              iSeries" <rpg400-l@xxxxxxxxxxxx>
                                                                        cc
                                                                       
             12/22/2005 12:13                                      Subject
             PM                        RE: SQL Newbie question         
                                                                       
                                                                       
             Please respond to                                         
              RPG programming                                          
              on the AS400 /                                           
                  iSeries                                              
             <rpg400-l@midrang                                         
                  e.com>                                               
                                                                       
                                                                       




The only problem is that the invoice number, which is the link between the
2 files, is numeric in one, and alpha in the other.
So all the joins, are failing.


 In SQL there is no native facility for mapping numeric date values into
> date
> types, but you can do what you want with a fairly simple expression...
>
> First, you need to turn the numeric value into a character string, in
such
> a
> way that you do not lose digits.  Use the DIGITS() finction for this.
> Then
> you substring out the bits you want...
>
> Select substr(digits(datemdy),5,2) as dtyear,
>             substr(digits(datemdy),1,2) as dtmonth,
>             substr(digits(datemdy),3,2) ad dtday
> *
> from freight join orderdtl on (join criteria)
> Order by 1,2
>
> Probably a more robust answer is to use a UDF to translate your numeric
> values into date values.  Then you can use SQLs built in date functions
to
> manipulate date element values (Year, Month, Day).
>
> There have been recent postings of date conversion UDFs to the list.
> Check
> the archives for more details.
>
> hth,
>
> Eric DeLong
> Sally Beauty Company
> MIS-Project Manager (BSG)
> 940-297-2863 or ext. 1863
>
>
>
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of steema@xxxxxxxxxxxxx
> Sent: Thursday, December 22, 2005 9:57 AM
> To: RPG programming on the AS400 / iSeries
> Cc: midrange-l@xxxxxxxxxxxx
> Subject: SQL Newbie question
>
>
> I need to take 2 files, one is the Frieght, and the other is Order
detail,
> which has the real amt paid for frt. To sort by year where the format is
> mmddyy. I would like to do this in SQL. I am not sure how to flex out the
> year and sort on it, how to total various warehouses and other codes.
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.