Settiing_TableRow_Colors.

sam15
sam15 used Ask the Experts™
on
What is the best way to set different colors for every row generated by a CURSOR in PL/SQL.

The colors are for HTML table rows. I basically do
<tr bgcolor="blue">-->Row 1
<tr bgcolor="red">---->Row 2

My 1st reqt is to do different color every row on one webpage.

My 2nd reqt is to do different color for every group of rows whenever a book number changes on anotehr webpage.
For example, if book No. 1000 has 3 rows it will have one color and the next set of rows for following book No. 1001 will have a different color.


I was thinking of adding a counter in the CURSOR and using MOD() function to decide on color for the first case. For 2nd case it seems the code needs to check current book value with previous book value to set the color.

Any ideas or best practice for this.







Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Where is yourlookup for what row number/group gets what color?

What web dev product?  I assume pl/sql server pages with the pl/sql tag but I have to ask.

Author

Commented:
just regular pl/sql. I use HTP function to print the HTML tags but you do not need to worry about that.

I just need to setup a variable and then i can paster that into the HTML.

you can just demo regular SQL or PL/SQL on how to set the variable.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
You didn't mention where the cross reference values are coming from.

Where do you determine blue is row 1, red is 2, etc...

Once you figure that out it should be a simple matter to use rownum, row_number (window function for groups) or a loop counter to look up the correct color.

C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Sr. System Analyst
Commented:
just use

dense_rank() over(order by bookID) rn

you will get a number for each group

if you want n color, then mod it

mod(dense_rank() over(order by bookID), n)+1 as ColorCode

it will give you 1,2,3,...,n,1,2,3,...,n,1,2,3,...

also you can wrap it with decode to get colors

case
mod(dense_rank() over(order by bookID), n)+1
when 1 then 'red'
when 2 thehn 'blue'
...
else 'white'
end
as ColorCode

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
HK,

That is the easy part but we have yet to figure out where the cross reference from 1=blue comes from.

This can change the code/design.
HainKurtSr. System Analyst

Commented:
probably hard coded :) what I understand is he wants just different colors for different groups, and wnat to use query foir this purpose, smart idea :) if he uses this as a part of the query he will get

case
mod(dense_rank() over(order by bookID), n)+1
when 1 then 'red'
when 2 then 'blue'
...
else 'white'
end
as ColorCode

... BookNo RowNo ColorCode
    1001  1 red
    1001  2 red
    1001  3 red
    1002  1 blue
    1002  2 blue
    1003  1 cyan
    1003  2 cyan
    1004  3 cyan
...

   
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>what I understand is he wants just different colors for different groups

Two different requirements:  My 1st reqt is to do different color every row on one webpage.

The second is the groups.
HainKurtSr. System Analyst

Commented:
for first one just use

row_number() over (order by BookNo, RowNo) as ColorCode

and again use mod (to limit the colors to n) & case (to convert numbers to color codes or names) if you wish...
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Mod does not necessarily give a 'different' color for each since it assumes you know the maximum number of rows/groups.

Until we know where this informations comes from we really cannot provide any solution.

This is why I asked first before I posted any code.

I suppose you could assume 65 Billion colors...

Author

Commented:
the two color codes will be stored in lookup table but you can also assume they are hardcoded in local variables.

l_first_color := 'Blue';
l_second_color := 'Red';

Based on yur solution i can use ROWNUM in the SQL query and then just do this check in pl/sql or shall i add MOD within a CASE statement in the SQL

If MOD(rownum,2) = 0 then --even numbers get red
 htp.p('<TR bgcolor='||l_second_color||'>');
else
 htp.p('<TR bgcolor='||l_first_color||'>');
end;

for 2nd case, I can use DENSE_RANK() and do the same MOD logic since the number assigned will be per group of ROWS and not for each row.

correct?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>--even numbers get red

This is different than what was asked.  you asked for a different color for each row/group.

Mod will allow you to alternate through a list of predefined colors.  For example if you declare three colors, every third row/group will repeat.

If this is actually what you want then HainKurts suggestions of MOD and dense_rank will work as you described.

Note that rownum is a virtual column and is assigned before any sorting.  If this is a problem you might want to switch to ROW_NUMBER().

Author

Commented:
I meant two colors only. one for each row or one for each group of rows.But using
ROWNUMBER and DENSE_RANK and MOD worked nicely if I hardcode the color codes int the SQL.

http://www.oramoss.com/blog/2010/03/05/rank-v-dense_rank-v-row_number/

If i store the two color codes in a table, can i use SELECT in the case statement or do i need to make that select a subquery and then the DENSE_RANK() and CASE  would get the code from subquery.
HainKurtSr. System Analyst

Commented:
join the result with ColorCodes (ColorIndex, ColorCode) table and use ColorCode in result:

select t.*, cc.ColorCode
from (
  select ..., mod(dense_rank() over(order by bookID), n)+1 ColorIndex
  from yourTable
) t left join ColorCodes.cc on cc.ColorIndex=x.ColorIndex

where n is the max of color you want, it should be <= num of records in ColorCodes table
you may have 24 colors, but you may want to use just first 7 (one color for each day for example, or 12 for each month)

Author

Commented:
my lookup table has only one row like:

REF_SYS_PARAM
-----------------------
SEQ
COLORCODE1
COLORCODE2

I cant join both table since there is no common field. Would it be bad if i did not do a join since there is only one row.

select a.*,b.colorcode1,b.colorcode2 from table t, ref_sys_param

or woult it be better to do a subquery like

select a.*,(select colorcode1 from ref_sys_param),select colorcode 2 from ref_sys_params) from table t;
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I would suggest a color table design change.  Two columns, multiple rows:

group_number, color
1,red
2,blue

If you cannot, the first query, the cartesian join, should work but both will break if there is ever another row added to the params table.  Not a very flexible design.

Author

Commented:
well the table has a bunch of other parameters. I like this design better which oracle uses sometimes too. One column for each value instead of inserting a parameter name and searching for the value of that parameter.
HainKurtSr. System Analyst

Commented:
I suggested this table before

ColorCodes (ColorIndex, ColorCode) and extend it a bit to

ColorCodes (ColorIndex, Colorname, ColorCode)

1 AliceBlue  #F0F8FF
2 AntiqueWhite  #FAEBD7
3 Aqua  #00FFFF
...

and put all 256 fixed colors here :)

http://www.w3schools.com/html/html_colornames.asp

after this, you may create another table to use which colors to use in which result

MyColorList(MyData, rowIndex, ColorIndex) and put

MyGrid 1 24
MyGrid 2 45
MyGrid 1 12
MyGridDaily 1 10
MyGridDaily 2 67
MyGridDaily 3 234
MyGridDaily 4 126
MyGridDaily 5 1
MyGridDaily 6 78
MyGridDaily 7 34

:) so in your queries, use both tables to return the colorname or colorcode to html page and you directly use in page...

you can create a view to join these two tables as

create view MyHTMLColors as
select cl.MyData, cl.rowIndex, cl.ColorIndex, cc.Colorname, cc.ColorCode
from MyColorList cl, ColorCodes cc
where cl.ColorIndex=cc.ColorIndex

and use MyHTMLColors in your queries to get Colorname, ColorCode

and I used mod (..., n) in my previous posts
here n will be

(select count(1) from MyHTMLColors where MyData='MyGridDaily')

which returns 7

after all everything will be dynamic, flexible, nice, easy peacy :)

HainKurtSr. System Analyst

Commented:
after all final solution would be (all script & objects)
ColorCodes (ColorIndex, Colorname, ColorCode)

1 AliceBlue  #F0F8FF
2 AntiqueWhite  #FAEBD7
3 Aqua  #00FFFF 
...

MyColorList(MyData, rowIndex, ColorIndex) and put

MyGrid 1 24
MyGrid 2 45
MyGrid 1 12
MyGridDaily 1 10
MyGridDaily 2 67
MyGridDaily 3 234
MyGridDaily 4 126
MyGridDaily 5 1
MyGridDaily 6 78
MyGridDaily 7 34
...

create view MyHTMLColors as 
select cl.MyData, cl.rowIndex, cl.ColorIndex, cc.Colorname, cc.ColorCode
from MyColorList cl, ColorCodes cc
where cl.ColorIndex=cc.ColorIndex

select t.*, c.ColorCode, c.ColorIndex, c.ColorName
from (
  select ..., mod(dense_rank() over(order by bookID), ((select count(1) from MyHTMLColors where MyData='MyGridDaily')))+1 ColorIndex
  from yourTable 
) t left join MyHTMLColors c on c.ColorIndex=x.ColorIndex and MyData='MyGridDaily'

Open in new window

Author

Commented:
Excellent.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial