Home » SQL & PL/SQL » SQL & PL/SQL » HTML TABLE OUTPUT FORMAT
icon4.gif  HTML TABLE OUTPUT FORMAT [message #435724] Thu, 17 December 2009 10:42 Go to next message
roseline43
Messages: 10
Registered: October 2009
Junior Member
Hello,

I have a DB containing PROJECT NAME, DVDNAME (ex. C1345, F234, G4576) and the FILES on the DVD.
In my page result, I want the list of the DVD from a project with the following formatting ( 7 columns table), using PL/SQL.
1 DVD can contain from 5000 files

Ex:

select DVDNAME, FILENAME  fromTABLE where project = 2
 
*DVD NAME: C1345*
12.txt     13.txt     15.txt     16.txt     17.txt     18.txt     19.txt   
22.txt     24.txt     26.txt     27.txt     28.txt     29.txt     31.txt         
32.txt     33.txt     36.txt     37.txt     39.txt     42.txt     45.txt     
44.txt     45.txt    47.txt      49.txt     55.txt     57.txt     59.txt     
61.txt     63.txt     64.txt     66.txt     68.txt     69.txt     70.txt     
71.txt     73.txt     75.txt     77.txt     78.txt     79.txt     80.txt     
81.txt     82.txt     83.txt     85.txt     86.txt     88.txt     89.txt
93.txt     95.txt     97.txt     99.txt    
 
*DVD NAME: C2334*
112.txt     113.txt     115.txt     116.txt     117.txt     118.txt     119.txt   
122.txt     124.txt     126.txt     127.txt     218.txt     219.txt     311.txt         
132.txt     133.txt     136.txt     137.txt     319.txt     412.txt     415.txt     
144.txt     145.txt     147.txt     149.txt     155.txt     517.txt     519.txt     
161.txt     163.txt     164.txt     166.txt     168.txt     619.txt     710.txt     
171.txt     173.txt     175.txt     177.txt     178.txt     719.txt     810.txt     
181.txt     182.txt     183.txt     185.txt     186.txt     818.txt     819.txt
193.txt     195.txt     197.txt    1 99.txt




HOW can I modify my code to have the result needed? I have only a 1 row table with the DVDTITLE repeating...

BEGIN
htp.p('<HTML>');
htp.p('<HEAD>');
htp.p('<TITLE>DISQUES C</TITLE>');
htp.p('</HEAD>');
htp.p('<BODY>');
for idx in (select DVDNAME, FILENAME  fromTABLE where project = 2 )loop
    htp.p('<TABLE>');
    htp.p('<TR>');
   htp.p('<TD>'||idx.DVDNAME||' </TD> ');
   htp.p('</TR>');
   htp.p('<TR>');
   htp.p('<TD>'||idx.FILENAME||' </TD>  <TD>'||idx.FILENAME||' </TD>  <TD>'||idx.FILENAME||
' </TD> <TD>'||idx.FILENAME||' </TD> <TD>'||idx.FILENAME||' </TD> <TD>'||idx.FILENAME||' </TD> <TD>'||
idx.FILENAME||' </TD> <TD>'||idx.FILENAME||' </TD>');
   htp.p('</TR>');
htp.p('</TABLE>');
end loop;
htp.p('</BODY>');
htp.p('</HTML>');
end;


thanks,

Roseline

[Updated on: Thu, 17 December 2009 10:47] by Moderator

Report message to a moderator

Re: HTML TABLE OUTPUT FORMAT [message #435726 is a reply to message #435724] Thu, 17 December 2009 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Client tool?

It can help if you post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: HTML TABLE OUTPUT FORMAT [message #435777 is a reply to message #435726] Thu, 17 December 2009 20:28 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
As you have not provided the complete test case.What i can figure out from this code is that you have an issue with the loop.You need to display the DVD name on the top of the table and files in seven columns.

1) Add a condition to check when the DVD name changes.If changes you have to start a new table.
2) Check the value of <TD> if reached to 7 then start a new row.

Regards,
Rajat
Re: HTML TABLE OUTPUT FORMAT [message #439784 is a reply to message #435777] Tue, 19 January 2010 07:06 Go to previous messageGo to next message
roseline43
Messages: 10
Registered: October 2009
Junior Member
Hi,

I attached a TESTCASE

Here is the query:
SELECT Id, DVD_ID,NomFichier,PROJET_ID FROM elements


Here is the OUTPUT that I would like:

DVD 85
2635080	2636608	2637084	2637091	2637092	2637093	2637147
2637152	2637153	2637154	2637155	2637156	2637157	2637164
2637169	2637170	2637172	2637173	2637202	2637203	2637213
2637214	2637215	2637216	2637218	2637217	2637219	2637220
...	...	...	...	...	...	

DVD 86
2639497	2639498	2639502	2639504	2639505	2639506	2639507
2639508	2639509	2639511	2639512	2639519	2639521	2639522
2639523	2639525	2639527	2639530	2639542	2639543	2639552
2639553	2639554	2639555	2639559	2639560	2639561	2639563
2639564	2639565	2639567	2639574	2639575	2639578	2639579
...	...	...	...	...	...	

DVD 87
2641806	2641807	2641808	2641809	2641810	2641819	2641820
2641821	2641824	2641827	2641828	2641830	2641831	2641832
2641834	2641838	2641839	2641846	2641847	2641849	2641850
2641851	2641852	2641853	2641854	2641855	2641863	2641864
...	...	...	...	...	...	...

etc...


Code to modify

BEGIN
htp.p('<HTML>');
htp.p('<HEAD>');
htp.p('<TITLE>DISQUES C</TITLE>');
htp.p('</HEAD>');
htp.p('<BODY>');
for idx in (select DVDNAME, FILENAME  fromTABLE where project = 2 )loop
    htp.p('<TABLE>');
    htp.p('<TR>');
   htp.p('<TD>'||idx.DVDNAME||' </TD> ');
   htp.p('</TR>');
   htp.p('<TR>');
   htp.p('<TD>'||idx.FILENAME||' </TD>  <TD>'||idx.FILENAME||' </TD>  <TD>'||idx.FILENAME||
' </TD> <TD>'||idx.FILENAME||' </TD> <TD>'||idx.FILENAME||' </TD> <TD>'||idx.FILENAME||' </TD> <TD>'||
idx.FILENAME||' </TD> <TD>'||idx.FILENAME||' </TD>');
   htp.p('</TR>');
htp.p('</TABLE>');
end loop;
htp.p('</BODY>');
htp.p('</HTML>');
end;





  • Attachment: testcase.txt
    (Size: 127.58KB, Downloaded 634 times)

[Updated on: Tue, 19 January 2010 07:18]

Report message to a moderator

Re: HTML TABLE OUTPUT FORMAT [message #439787 is a reply to message #435726] Tue, 19 January 2010 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can do it in the same way in your previous topic: Return result of query into a 6 columns table.
I know it was 3 months ago but it should also work.

Regards
Michel
Re: HTML TABLE OUTPUT FORMAT [message #439790 is a reply to message #439787] Tue, 19 January 2010 07:21 Go to previous messageGo to next message
roseline43
Messages: 10
Registered: October 2009
Junior Member
I thought I would have more flexibility by using this method to apply CSS to my tables ( ex: alternate link color).

Re: HTML TABLE OUTPUT FORMAT [message #439793 is a reply to message #439784] Tue, 19 January 2010 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I attached a TESTCASE

SQL> CREATE TABLE  `elements`.`elements` (
  2    `Id` int(10) unsigned NOT NULL auto_increment,
  3    `DVD_ID` varchar(45) NOT NULL,
  4    `NomFichier` varchar(45) NOT NULL,
  5    `PROJET_ID` varchar(45) NOT NULL,
  6    PRIMARY KEY  (`Id`)
  7  ) ENGINE=InnoDB AUTO_INCREMENT=107496 DEFAULT CHARSET=latin1;
CREATE TABLE  `elements`.`elements` (
              *
ERROR at line 1:

Please post a VALID test case.

Regards
Michel
Re: HTML TABLE OUTPUT FORMAT [message #439825 is a reply to message #439793] Tue, 19 January 2010 10:02 Go to previous messageGo to next message
roseline43
Messages: 10
Registered: October 2009
Junior Member
Sorry,

I attached the right TestCase.
  • Attachment: testcase.sql
    (Size: 515.53KB, Downloaded 602 times)
Re: HTML TABLE OUTPUT FORMAT [message #439853 is a reply to message #439825] Tue, 19 January 2010 13:11 Go to previous messageGo to next message
roseline43
Messages: 10
Registered: October 2009
Junior Member
Someone suggest me that code but all the filename are on the same row.

How can I modify this code to have 7 row table?

thanks again Roseline

DECLARE
  vtemp  varchar2(4000) DEFAULT ' ';
 
BEGIN
  htp.p('<HTML>');
  htp.p('<HEAD>');
  htp.p('<TITLE>DISQUES C</TITLE>');
  htp.p('</HEAD>');
  htp.p('<BODY>');
 
FOR idx IN
    (
    SELECT DVD_ID, NomFichier,
           row_number() over(partition BY DVD_ID ORDER BY NomFichier  ASC) AS rna,
           row_number() over(partition BY DVD_ID ORDER BY NomFichier DESC) AS rnd
      FROM elements
     WHERE PROJET_ID = 1
  ORDER BY DVD_ID ASC, rna ASC
    )
      loop
      
        IF idx.rna = 1
        then
          htp.p('<TABLE>');
          htp.p('<TR>');
          htp.p('<TD>' || idx.DVD_ID ||' </TD> ');
          htp.p('</TR>');
          htp.p('<TR>');
        end IF;
 
        vtemp := vtemp || '<TD>' || idx.NomFichier || ' </TD>';
 
        IF mod(idx.rna, 7) = 0 OR idx.rnd = 1
        then
          htp.p(vtemp);
          vtemp := ' ';
        end IF;
 
        IF idx.rnd = 1
        then
          htp.p('</TR>');
          htp.p('</TABLE>');
        end IF;
      
      end loop;
 
htp.p('</BODY>');
htp.p('</HTML>');
 
end;
Re: HTML TABLE OUTPUT FORMAT [message #439917 is a reply to message #435724] Wed, 20 January 2010 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Text format:
SQL> set heading off
SQL> set feedback off
SQL> col c format a7
SQL> with 
  2    data as (
  3      select dvd_id, nomfichier,
  4              row_number() over (partition by dvd_id order by nomfichier)-1 rn
  5      from elements
  6      where projet_id = 1
  7    )
  8  select decode(grouping_id(dvd_id,trunc(rn/7), nomfichier),
  9                 3, chr(10)||'DVD '||dvd_id,
 10                 1, max(decode(mod(rn,7),0,nomfichier))) c,
 11          decode(grouping_id(dvd_id,trunc(rn/7), nomfichier),
 12                 1, max(decode(mod(rn,7),1,nomfichier))) c,
 13          decode(grouping_id(dvd_id,trunc(rn/7), nomfichier),
 14                 1, max(decode(mod(rn,7),2,nomfichier))) c,
 15          decode(grouping_id(dvd_id,trunc(rn/7), nomfichier),
 16                 1, max(decode(mod(rn,7),3,nomfichier))) c,
 17          decode(grouping_id(dvd_id,trunc(rn/7), nomfichier),
 18                 1, max(decode(mod(rn,7),4,nomfichier))) c,
 19          decode(grouping_id(dvd_id,trunc(rn/7), nomfichier),
 20                 1, max(decode(mod(rn,7),5,nomfichier))) c,
 21          decode(grouping_id(dvd_id,trunc(rn/7), nomfichier),
 22                 1, max(decode(mod(rn,7),6,nomfichier))) c
 23  from data
 24  group by rollup(dvd_id,trunc(rn/7), nomfichier)
 25  having grouping_id(dvd_id,trunc(rn/7), nomfichier) in (1,3)
 26  order by dvd_id, grouping_id(dvd_id,trunc(rn/7)) desc, trunc(rn/7)
 27  /

DVD 85
2635080 2636608 2637084 2637091 2637092 2637093 2637147
2637152 2637153 2637154 2637155 2637156 2637157 2637164
2637169 2637170 2637172 2637173 2637202 2637203 2637213
2637214 2637215 2637216 2637217 2637218 2637219 2637220
2637234 2637235 2637236 2637237 2637239 2637240 2637241
2637242 2637244 2637246 2637247 2637249 2637250 2637251
2637252 2637253 2637257 2637263 2637266 2637269 2637270
2637274 2637276 2637279 2637284 2637290 2637291 2637292
2637293 2637294 2637295 2637296 2637301 2637303 2637304
2637305 2637306 2637311 2637320 2637322 2637324 2637331
2637333 2637336 2637352 2637353 2637354 2637358 2637359
...
2639423 2639426 2639427 2639429 2639434 2639437 2639440
2639446 2639448 2639450 2639457 2639465 2639466 2639467
2639468 2639469 2639472 2639473 2639474 2639475 2639478
2639485 2639486 2639487 2639488 2639489 2639491 2639492
2639495

DVD 86
2639497 2639498 2639502 2639504 2639505 2639506 2639507
2639508 2639509 2639511 2639512 2639519 2639521 2639522
2639523 2639525 2639527 2639530 2639542 2639543 2639552
2639553 2639554 2639555 2639559 2639560 2639561 2639563
2639564 2639565 2639567 2639574 2639575 2639578 2639579
2639581 2639582 2639584 2639585 2639586 2639587 2639588
2639589 2639591 2639595 2639596 2639597 2639598 2639599
2639605 2639618 2639621 2639623 2639624 2639627 2639628
2639637 2639638 2639639 2639644 2639647 2639648 2639649
2639650 2639654 2639658 2639662 2639665 2639669 2639671
2639672 2639673 2639675 2639678 2639679 2639680 2639683
...
2641733 2641734 2641735 2641737 2641738 2641739 2641740
2641741 2641742 2641743 2641744 2641749 2641750 2641751
2641752 2641754 2641755 2641768 2641771 2641773 2641774
2641775 2641777 2641783 2641784 2641785 2641790 2641792
2641793 2641797 2641798 2641799 2641804 2641805

HTML format:
set lines 300
col c format a20
col c1 format a120
set trimspool on
with 
  data as (
    select dvd_id, nomfichier,
            row_number() over (partition by dvd_id order by nomfichier)-1 rn,
            lag(dvd_id) over (order by dvd_id, nomfichier) prev_dvd
    from elements
    where projet_id = 1
  )
select decode(grouping_id(dvd_id,trunc(rn/7), nomfichier),
               3, decode(prev_dvd,
                         null, '<HTML><HEAD><TITLE>DISQUES C</TITLE></HEAD><BODY>')
                  ||decode(prev_dvd, dvd_id, '',
                           decode(prev_dvd,null,'','</TR></TABLE>')||
                           chr(10)||
                           '<TABLE><TR><TD COLSPAN="7" ALIGN="CENTER">'||
                           dvd_id||'</TD></TR>'),
               1, max(decode(mod(rn,7),0,'<TR><TD>'||nomfichier||'</TD>')),
               7, '</TR></TABLE>'||chr(10)||'</BODY></HTML>') c1,
        decode(grouping_id(dvd_id,trunc(rn/7), nomfichier),
               1, max(decode(mod(rn,7),1,'<TD>'||nomfichier||'</TD>'))) c,
        decode(grouping_id(dvd_id,trunc(rn/7), nomfichier),
               1, max(decode(mod(rn,7),2,'<TD>'||nomfichier||'</TD>'))) c,
        decode(grouping_id(dvd_id,trunc(rn/7), nomfichier),
               1, max(decode(mod(rn,7),3,'<TD>'||nomfichier||'</TD>'))) c,
        decode(grouping_id(dvd_id,trunc(rn/7), nomfichier),
               1, max(decode(mod(rn,7),4,'<TD>'||nomfichier||'</TD>'))) c,
        decode(grouping_id(dvd_id,trunc(rn/7), nomfichier),
               1, max(decode(mod(rn,7),5,'<TD>'||nomfichier||'</TD>'))) c,
        decode(grouping_id(dvd_id,trunc(rn/7), nomfichier),
               1, max(decode(mod(rn,7),6,'<TD>'||nomfichier||'</TD>'))) c
from data
group by rollup((dvd_id,prev_dvd),trunc(rn/7), nomfichier)
having grouping_id(dvd_id,trunc(rn/7), nomfichier) != 0
order by dvd_id, 
          grouping_id(dvd_id,trunc(rn/7), nomfichier) desc, 
          trunc(rn/7)
/

Result in the attached file.

Regards
Michel
  • Attachment: t.html
    (Size: 60.77KB, Downloaded 321 times)
Re: HTML TABLE OUTPUT FORMAT [message #440014 is a reply to message #439917] Wed, 20 January 2010 10:39 Go to previous messageGo to next message
roseline43
Messages: 10
Registered: October 2009
Junior Member
Thanks Michel!!!!

A last question:

1- I tried the text format in the SQL WORKSHOP on APEX.
The result is the same as the on you showed me.
But I have to remove the following code because it dosen't work

set heading off
 set feedback off
 col c format a7


But the result is exactly the same

2- Same thing with the HTML FORMAT, it worked in SQL Workshop without the code:

set lines 300
col c format a20
col c1 format a120
set trimspool on


3- I tried to put the Text Code in my Application
1- Create New report - SQL report - and past the code

without the code
set heading off
 set feedback off
 col c format a7


and I have this result ( on attachement)

you will see that I needed to change de columnname because I hade errors for duplicated alias.

Can you help me?

thanks

Roseline
Re: HTML TABLE OUTPUT FORMAT [message #440016 is a reply to message #440014] Wed, 20 January 2010 11:09 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"set" and "col" are SQL*Plus commands to format the output.
They are in my post just to prevent from line and column splitting and to remove column heading and feedback (number of returned rows).

Quote:
you will see that I needed to change de columnname because I hade errors for duplicated alias.

I don't know this, in SQL (and SQL*Plus) columns may have the same alias, it does not matter.

I don't know "SQL WORKSHOP on APEX", may be someone else will be able to answer on the result you have but I bet he/she will need you provide the modified code.

Regards
Michel
Previous Topic: Inconsistency in Interval Date Seconds handling?
Next Topic: everytime an update is done.. how can i insert?
Goto Forum:
  


Current Time: Sat Dec 03 08:13:18 CST 2016

Total time taken to generate the page: 0.07108 seconds