| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-31-09, 10:29
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
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' , ' ' ) )
, (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' , ' ' ) )
ELSE ' '
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 08:03.
|
12-31-09, 10:45
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
New Year
|
01-01-10, 08:01
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
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' , ' ' ) )
ELSE ' '
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' , ' ' ) )
ELSE ' '
END
)
ORDER BY dd )
AS VARCHAR(150) )
, '&' , '&' )
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 08:42.
|
01-01-10, 10:03
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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.
|
|
01-02-10, 08:29
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
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 " " 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> ' )
, ( 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 16:19.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Calendar of html format generated by DB2 SQL.
