Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    2

    Unanswered: Joined two tables

    Hello ,
    I use Data transfer from system iseries . Throgh sql i downloaded the data. By default the date format is julian date when i download , if i want the date format to calender date one standards table F00365 is there , through this table join we convert the juilan format to calendar format. The issue is if one date field is there , no issue . if more than one date fields is how we write the query to convert the both date field as calendar date format. My query mention below ,kindly help on this issue

    SELECT F0311.RPAN8"CUST.NO",F0311.RPALPH"CUST NAME",F0311.RPSDOC"ORDER NO",F0311.RPSDCT"ORDER TY",F58421A.CCLGL1"CUST.PONO",F0311.RPDOC"INVOIC E NO" ,F0311.RPDCT"TYPE",F00365.ONDATE "INVOICE DATE",F0311.RPDDJ"DUE DATE",F0311.RPAG/1000"ORIGINAL AMT",F0311.RPAAP/100 "OPEN AMT"FROM P2DTAA/F0311 LEFT OUTER JOIN F58421A ON F58421A.CCDOCO = F0311.RPSDOC AND F58421A.CCDCTO = F0311.RPSDCT LEFT OUTER JOIN F00365 ON F00365. ONDTEJ = F0311.RPDIVJ WHERE AND (F0311.RPAAP > 0) AND (F0311.RPTRTC <> 'G')

    Here i joined the table F0311 & F00365 joined fields are
    F00365.ONDTEJ = F0311.RPSDCT and in the selection i put calender fleld is F00365.ONDATE.
    The same function i want to use for F0311.RPDDJ also


    The tables are used is F0311,F58421A,F00365
    Help me

    Suresh

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can convert julian date to calendar date by an expression, like this:

    DATE('0001-01-01') + (julian_date - 1721426) DAYS

  3. #3
    Join Date
    Sep 2010
    Posts
    2

    Red face

    Hi ,

    Thanks for valuable information, But i shows some error , the error message & SQL query was follows for your information

    QUERY
    SELECT ILLITM,ILMCU,ILDOC,ILDCT,(DATE('0001-01-01') + (ILDGL-1721426)DAYS),ILTRUM,ILTRQT FROM P2DTAA/F4111 WHERE ILMCU LIKE '%ITR' AND ILDCT = 'II' AND ILDGL >110266

    Error Message

    WBDB0099 - No more data is available for the stream fetch request

    SQL0183 - Result of date or timestamp expression not valid.

    Cause . . . . . : The result of an arithmetic operation is a date or timestamp that is not within the valid range of dates which are between 0001-01-01 and 9999-12-31. If the result is a date in the format YMD, MDY, DMY, or JUL then the year must be between 1940 and 2039. If this is a FETCH, embedded SELECT, SET or VALUES INTO, then the relative position of the host variable in the INTO clause is 5 and the host variable name is *N. Recovery . . . : Correct the arithmetic expression or the data that was being processed at the time the error occurred. If the date format is YMD, MDY, DMY or JUL and the result is not between 1940 and 2039, then specify USA, ISO, EUR, or JIS for the date format. The date format can be specified on the STRSQL or CRTSQLxxx commands or can be changed for the job by using the CHGJOB command. Try the request again.

    KINDLY HELP ME

    THanks in advance
    Suresh

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What is the value of ILDGL?
    Is it really Julian date?

    In your code, there is a phrase "AND ILDGL >110266".

    If the value of ILDGL is Julian date,
    ILDGL = 110267(lowest number which satisfy the condition) may be about February 7 4411 B.C.
    (110267 days from January 1, 4713 B.C.(the start of the Julian date calendar)).

    See JULIAN_DAY built-in function in SQL References(e.g. "DB2 9.7 for LUW SQL Reference, Volume 1" or "DB2 for i SQL Reference 7.1").

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •