Settiing_TableRow_Colors.

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.

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.

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.

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.

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

   

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

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

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

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?

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

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.

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)

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;

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.

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.

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

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'

Select allOpen in new window

Excellent.