Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483

    Unanswered: Calendar of html format generated by DB2 SQL.

    1) Run following export command.
    (I'm using DB2 9.7.1 Express-C for Windows.)

    2) Look generated file(Calendar_2010.html) by your html browser.
    (I'm using Internet Explorer 6.0)

    Changed:
    , ( 1 , 13 , '<title>Calendar of year ' || target_year || '</title>' )

    , ( 10 , 10 , '<H1>' || target_year || '</H1>' )

    , ( 100 + mm , 4 , '<CAPTION>' || MONTHNAME( target_month ) || '</CAPTION>' )

    LTRIM( REPLACE( ' ' || SUBSTR( DIGITS( dd + 2 - DAYOFWEEK(target_month) ) , 9 , 2 )
    , ' 0' , '&nbsp;' ) )

    , (VALUES 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 ) mm(mm)

    "dd + 2" to "dd"
    "wk + 2" to "wk"

    Added:
    , ( 1 , 21 , '<style type="text/css">' )
    , ( 1 , 23 , 'td { /* font-family: monospace; */ text-align: center; }' )
    , ( 1 , 25 , 'th { font-family: monospace; }' )
    , ( 1 , 27 , 'caption { font-weight: bold; font-size: large; }' )
    , ( 1 , 29 , '</style>' )


    Code:
    ------------------------------ Commands Entered ------------------------------
    EXPORT TO Calendar_2010.html OF DEL MODIFIED BY nochardel 
    SELECT text
      FROM (VALUES 2010 ) p( target_year )
         , LATERAL
           (
            VALUES
              (     1  ,  1 , '<!DOCTYPE HTML PUBLIC "-//W3C/DTD HTML 4.0 Transitional//EN">' )
            , (     1  ,  2 , '<html>' )
            , (     1  , 10 , '<head>' )
            , (     1  , 11 , '<META NAME="Description" CONTENT="Generated by DB2 SQL and exported to DEL file">' )
            , (     1  , 12 , '<META HTTP-EQUIV="Content-Type" CONTENT="text/html">' )
            , (     1  , 13 , '<title>Calendar of year ' || target_year || '</title>' )
            , (     1  , 21 , '<style type="text/css">' )
            , (     1  , 23 , 'td { /* font-family: monospace; */ text-align: center; }' )
            , (     1  , 25 , 'th { font-family: monospace; }' )
            , (     1  , 27 , 'caption { font-weight: bold; font-size: large; }' )
            , (     1  , 29 , '</style>' )
            , (     1  , 99 , '</head>' )
            , (    10  ,  1 , '<body>' )
            , (    10  , 10 , '<H1>' || target_year || '</H1>' )
            , (  1000  , 10 , '</body>' )
            , (  1000  , 99 , '</html>' )
            UNION ALL
            SELECT j , k , text
              FROM
                   (VALUES 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 ) mm(mm) 
                 , LATERAL
                   (VALUES DATE('0001-01-01') + (target_year - 1) YEARs + mm MONTHs ) s( target_month )
                 , LATERAL
                   (VALUES
                      ( 100 + mm ,  3 , '<TABLE border="2" cellspacing="1" cellpadding="3">' )
                    , ( 100 + mm ,  4 , '<CAPTION>' || MONTHNAME( target_month ) || '</CAPTION>' )
                    , ( 100 + mm ,  5 , '<TR><TH><span style="color: red;">Sun</span></TH><TH>Mon</TH><TH>Tue</TH><TH>Wed</TH>'
                                        || '<TH>Thu</TH><TH>Fri</TH><TH><span style="color: blue;">Sat</span></TH></TR>' )
                    , ( 100 + mm , 91 , '</TABLE>' )
                    , ( 100 + mm , 97 , '<br><br>' )
                   ) s( j , k , text )
            UNION ALL
            SELECT 100 + mm
                 ,  10 + wk
                 , '<TR>' ||
                   XMLCAST(
                      XMLGROUP(  leading
                               , CASE
                                 WHEN dd - DAYOFWEEK(target_month)
                                      BETWEEN 1 AND DAY( LAST_DAY(target_month) ) THEN
                                      LTRIM( REPLACE( ' ' || SUBSTR( DIGITS( dd - DAYOFWEEK(target_month) ) , 9 , 2 )
                                                    , ' 0' , '&nbsp;' ) )
                                 ELSE '&nbsp;'
                                 END  AS dd
                               , trailing
                               ORDER BY dd )
                      AS VARCHAR(150) )
                   || '</TR>'
              FROM (VALUES ( '<TD>' , '</TD>' ) ) t( leading , trailing )
                 , (VALUES 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 ) mm(mm) 
                 , LATERAL
                   (VALUES DATE('0001-01-01') + (target_year - 1) YEARs + mm MONTHs ) s( target_month )
                 , (VALUES  2,  9, 16, 23, 30, 37 ) wk(wk)
                 , (VALUES          2,  3,  4,  5,  6,  7,  8,  9
                         , 10, 11, 12, 13, 14, 15, 16, 17, 18, 19
                         , 20, 21, 22, 23, 24, 25, 26, 27, 28, 29
                         , 30, 31, 32, 33, 34, 35, 36, 37, 38, 39
                         , 40, 41, 42, 43 ) dd(dd)
             WHERE dd BETWEEN wk AND wk + 6
               AND wk - DAYOFWEEK(target_month) <= DAY( LAST_DAY(target_month) )
             GROUP BY
                   mm , wk
           ) s( j , k , text )
     ORDER BY
           j , k
    ;
    ------------------------------------------------------------------------------
    SQL3104N  The Export utility is beginning to export data to file 
    "Calendar_2010.html".
    
    SQL3105N  The Export utility has finished exporting "139" rows.
    
    
    Number of rows exported: 139
    Last edited by tonkuma; 01-01-10 at 09:03.

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up New Year

    !!! Happy New Year !!!

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My original code will work only on DB2 9.7 for LUW.
    For earlier version or other platform, it may not work or some modifications would be neccesary to work.

    Following examples were not tested.

    DB2 9.5 for LUW:
    1) Concatenation of INTEGER was not supported.
    '<title>Calendar of year ' || target_year || '</title>'
    ===>
    '<title>Calendar of year ' || CHAR(target_year) || '</title>'

    '<H1>' || target_year || '</H1>'
    ===>
    '<H1>' || CHAR(target_year) || '</H1>'

    2) Function LAST_DAY was not supported.
    DAY( LAST_DAY(target_month) )
    ===>
    DAY(target_month + 1 MONTH - 1 DAY)

    DB2 9.1 for LUW:
    XMLGROUP was not supported.
    Replace XMLCAST( XMLGROUP( ... ) ... ) with XMLSERIALIZE( XMLAGG( XMLELEMENT( ... ) ... ) ... ), Like this...
    XMLCAST(
    XMLGROUP( leading
    , CASE
    WHEN dd - DAYOFWEEK(target_month)
    BETWEEN 1 AND DAY( LAST_DAY(target_month) ) THEN
    LTRIM( REPLACE( ' ' || SUBSTR( DIGITS( dd - DAYOFWEEK(target_month) ) , 9 , 2 )
    , ' 0' , '&nbsp;' ) )
    ELSE '&nbsp;'
    END AS dd
    , trailing
    ORDER BY dd )
    AS VARCHAR(150) )
    ===>
    REPLACE(
    XMLSERIALIZE(
    XMLAGG(
    XMLELEMENT
    ( NAME td,
    CASE
    WHEN dd - DAYOFWEEK(target_month)
    BETWEEN 1 AND DAY(target_month + 1 MONTH - 1 DAY) THEN
    LTRIM( REPLACE( ' ' || SUBSTR( DIGITS( dd - DAYOFWEEK(target_month) ) , 9 , 2 )
    , ' 0' , '&nbsp;' ) )
    ELSE '&nbsp;'
    END
    )
    ORDER BY dd )
    AS VARCHAR(150) )
    , '&amp;' , '&' )


    Other platforms(z/OS and iSeries):
    1) Multiple row constructor VALUES is not supported.
    Replace it with UNION ALL. like this...
    (VALUES 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 ) mm(mm)
    ===>
    (SELECT 0 FROM sysibm.sysdummy1 UNION ALL
    SELECT 1 FROM sysibm.sysdummy1 UNION ALL
    SELECT 2 FROM sysibm.sysdummy1 UNION ALL
    ...
    SELECT 11 FROM sysibm.sysdummy1
    ) mm(mm)

    2) Some functions and/or syntaxes may be not supported.
    Detailes were not investigated.
    Last edited by tonkuma; 01-01-10 at 09:42.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DATE('0001-01-01') + (target_year - 1) YEARs
    can be replaced by
    DATE(target_year || '001')
    on DB2 9.7 for LUW.

    or
    can be replaced by
    DATE( RTRIM( CHAR(target_year) ) || '001' )
    on DB2 9.5 or earlier for LUW.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Make the code shorter.
    You may feel some techniques are strange or too tricky.

    Changed:
    1) Used standard CSS by removing "Transitional" from !DOCTYPE.
    2) Used "white-space: pre;" instead of "&nbsp;" to format day numbers.
    3) Used XMLELEMENT and XMLAGG to construct tables of html.

    Code:
    ------------------------------ Commands Entered ------------------------------
    EXPORT TO Calendar_2010.html OF DEL MODIFIED BY nochardel 
    SELECT text
      FROM (VALUES 2010 ) p( target_year )
         , LATERAL
           (
            VALUES
              (     1  ,  1 , '<!DOCTYPE HTML PUBLIC "-//W3C/DTD HTML 4.01//EN">' )
            , (     1  ,  2 , '<html>' )
            , (     1  , 10 , '<head>' )
            , (     1  , 11 , '<META NAME="Description" CONTENT="Generated by DB2 SQL and exported to DEL file">' )
            , (     1  , 12 , '<META HTTP-EQUIV="Content-Type" CONTENT="text/html">' )
            , (     1  , 13 , '<title>Calendar of year ' || target_year || '</title>' )
            , (     1  , 21 , '<style type="text/css">' )
            , (     1  , 23 , 'td { text-align: center; white-space: pre; }' )
            , (     1  , 25 , 'th { font-family: monospace; }' )
            , (     1  , 27 , 'caption { font-weight: bold; font-size: x-large; }' )
            , (     1  , 29 , '</style>' )
            , (     1  , 99 , '</head>' )
            , (    10  ,  1 , '<body>' )
            , (    10  , 10 , '<H1>' || target_year || '</H1>' )
            , (   900  ,  1 , '<span style="font-size: x-small; font-style: italic;"> Generated: ' || CURRENT TIMESTAMP || '</span><br>&nbsp;' )
            , (  1000  , 10 , '</body>' )
            , (  1000  , 99 , '</html>' )
            UNION ALL
            SELECT j , k , text
              FROM
                   (VALUES 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 ) mm( mm )
                 , LATERAL
                   (VALUES DATE(target_year || '001') + mm MONTHs ) s( target_month )
                 , LATERAL
                   (VALUES
                      ( 100 + mm ,  3 , '<TABLE border="2" cellspacing="1" cellpadding="3">' )
                    , ( 100 + mm ,  4 , '<CAPTION>' || MONTHNAME( target_month ) || '</CAPTION>' )
                    , ( 100 + mm ,  5 , '<TR><TH><span style="color: red;">Sun</span></TH><TH>Mon</TH><TH>Tue</TH><TH>Wed</TH>'
                                        || '<TH>Thu</TH><TH>Fri</TH><TH><span style="color: blue;">Sat</span></TH></TR>' )
                    , ( 100 + mm , 91 , '</TABLE>' )
                    , ( 100 + mm , 97 , '<br><br>' )
                   ) s( j , k , text )
            UNION ALL
            SELECT 100 + mm
                 ,  10 + wk
                 , XMLSERIALIZE(
                      XMLELEMENT( NAME tr ,
                         XMLAGG(
                            XMLELEMENT( NAME td ,
                               REPLACE(
                                  LPAD( dd * MOD( (dd + last_day - 1) / last_day , 2 ) , 2 )
                                  , ' 0', '  ' )
                            )
                            ORDER BY dd
                         )
                      )
                      AS VARCHAR(150)
                   )
              FROM
                   (VALUES 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 ) mm( mm ) 
                 , LATERAL
                   (VALUES DATE(target_year || '001') + mm MONTHs ) s( target_month )
                 , LATERAL
                   (VALUES ( DAY( LAST_DAY(target_month) ) , DAYOFWEEK(target_month) ) ) t( last_day , day_of_week )
                 , (VALUES  2,  9, 16, 23, 30, 37 ) wk( wk )
                 , LATERAL
                   (SELECT wk + d - day_of_week AS dd
                      FROM (VALUES 0, 1, 2, 3, 4, 5, 6 ) d( d )
                     WHERE wk - day_of_week <= last_day
                   )
             GROUP BY
                   mm , wk
           ) s( j , k , text )
     ORDER BY
           j , k
    ;
    ------------------------------------------------------------------------------
    SQL3104N  The Export utility is beginning to export data to file 
    "Calendar_2010.html".
    
    SQL3105N  The Export utility has finished exporting "140" rows.
    
    
    Number of rows exported: 140
    Last edited by tonkuma; 01-03-10 at 17:19.

Posting Permissions

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