Avatar of sam15
sam15
 asked on

Settiing_TableRow_Colors.

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.







Oracle Database

Avatar of undefined
Last Comment
sam15

8/22/2022 - Mon
slightwv (䄆 Netminder)

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.
sam15

ASKER
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.
slightwv (䄆 Netminder)

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.

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
HainKurt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
slightwv (䄆 Netminder)

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.
HainKurt

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
...

   
slightwv (䄆 Netminder)

>>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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
HainKurt

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...
slightwv (䄆 Netminder)

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...
sam15

ASKER
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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
slightwv (䄆 Netminder)

>>--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().
sam15

ASKER
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.
HainKurt

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)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sam15

ASKER
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;
slightwv (䄆 Netminder)

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.
sam15

ASKER
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
HainKurt

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 :)

HainKurt

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

sam15

ASKER
Excellent.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.