此报表是利用 4GL+HTML 打印出来的。效果如下:
用过TIPTOP的人都知道,特别的TIPTOP的低版本,报表很挫,
对于有格子的报表很难弄。高版本用的CR就不说了。
废话不多说,直接看下图:
(问题:小于零的数值型数据转换成字符型的时候前面的零自动没有了)
SQL没改之前的预览效果(预览效果一):
LET l_sql = " SELECT qcgg02, tc_qcj031||tc_qcj03,tc_qcj04,tc_qcj032,TRIM(tc_qcj05) ,tc_qcj051,tc_qcj052,
WM_CONCAT((DECODE(QCGG04,'1', TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))),
WM_CONCAT((DECODE(QCGG04,'2', TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'3', TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'4', TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'5', TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'6', TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'7', TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'8', TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'9', TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'10',TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'11',TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'12',TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'13',TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'14',TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'15',TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'16',TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'17',TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'18',TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'19',TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'20',TRIM(qcgg06||'/'||QCGG061||qcgg07),NULL))) ",
" FROM qci_file,qcgg_file,tc_qcj_file ",
" WHERE qcgg01 = '", g_argv3,"'",
" AND qci01 = qcgg01 AND qci00='1' ",
" AND qci02 = tc_qcj02 AND qci02=qcgg02 ",
" AND qci03 = qcgg03 AND qci03 ='",g_argv6,"'",
" AND (qcgg06 IS NOT NULL OR qcgg061 IS NOT NULL OR qcgg07 IS NOT NULL) ",
" AND qci041 = tc_qcj01 "
CASE
WHEN g_argv5 ='1' LET l_sql = l_sql CLIPPED," AND qcgg02 LIKE 'B%' "
WHEN g_argv5 ='2' LET l_sql = l_sql CLIPPED," AND qcgg02 LIKE 'C%' "
OTHERWISE
LET l_sql = l_sql
END CASE
LET l_sql = l_sql CLIPPED," GROUP BY qcgg02,tc_qcj031,tc_qcj03,tc_qcj032,tc_qcj04,tc_qcj05,tc_qcj051,tc_qcj052 ORDER BY qcgg02 "
SQL改后的预览效果(预览效果二):
#20121025 BY FMX 数字转换为字符前面加一个0
LET l_sql = " SELECT qcgg02, tc_qcj031||tc_qcj03,tc_qcj04,tc_qcj032,TRIM(tc_qcj05) ,tc_qcj051,tc_qcj052,
WM_CONCAT((DECODE(QCGG04,'1', TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'2', TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'3', TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'4', TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'5', TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'6', TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'7', TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'8', TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'9', TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'10',TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'11',TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'12',TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'13',TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'14',TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'15',TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'16',TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'17',TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'18',TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'19',TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ,
WM_CONCAT((DECODE(QCGG04,'20',TRIM((CASE INSTR(qcgg06,'.',1,1) WHEN 1 THEN '0'||qcgg06 ELSE to_char(qcgg06) END)||'/'||(CASE INSTR(qcgg061,'.',1,1) WHEN 1 THEN '0'||qcgg061 ELSE to_char(qcgg061) END)||qcgg07),NULL))) ",
" FROM qci_file,qcgg_file,tc_qcj_file ",
" WHERE qcgg01 = '", g_argv3,"'",
" AND qci01 = qcgg01 AND qci00='1' ",
" AND qci02 = tc_qcj02 AND qci02=qcgg02 ",
" AND qci03 = qcgg03 AND qci03 ='",g_argv6,"'",
" AND (qcgg06 IS NOT NULL OR qcgg061 IS NOT NULL OR qcgg07 IS NOT NULL) ",
" AND qci041 = tc_qcj01 "
CASE
WHEN g_argv5 ='1' LET l_sql = l_sql CLIPPED," AND qcgg02 LIKE 'B%' "
WHEN g_argv5 ='2' LET l_sql = l_sql CLIPPED," AND qcgg02 LIKE 'C%' "
OTHERWISE
LET l_sql = l_sql
END CASE
LET l_sql = l_sql CLIPPED," GROUP BY qcgg02,tc_qcj031,tc_qcj03,tc_qcj032,tc_qcj04,tc_qcj05,tc_qcj051,tc_qcj052 ORDER BY qcgg02 "