Help with Query

Hello all on this group, I hope you can help. I'm developing an asp page
that displays (in a table) paint colors. An older version is at:

http://tforward.aspfreeserver.com/modeling/paints/paints.asp

A popup layer menu on each color will give the option to find similar
colors from that list based on decimal RGB values in separate RGB columns.
I'm using a query that searches the RGB values in increments of +/-15 but
I've realized some colors may not be selected at all if one or two values
are a little outside the range.

Any suggestions how to do this? Thanks very much.

TForward

Re: Help with Query

TForward wrote:
> Hello all on this group, I hope you can help. I'm developing an asp
> page that displays (in a table) paint colors. An older version is at:
>
> http://tforward.aspfreeserver.com/modeling/paints/paints.asp
>
> A popup layer menu on each color will give the option to find similar
> colors from that list based on decimal RGB values in separate RGB
> columns. I'm using a query that searches the RGB values in increments
> of +/-15 but I've realized some colors may not be selected at all if
> one or two values are a little outside the range.
>
> Any suggestions how to do this? Thanks very much.
>
I'm not clear about what you want to do. If the selected values don't match
a color, isn't it correct to tell the user that his selection did not match
a color? If not, you need to be more specific about what you want to have
happen. It will also help if you show us your table structure, perhaps a few
rows of sample data, and, oh yes!!!, most importantly: what database type
and version are you using?

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Help with Query

"Bob Barrows [MVP]" wrote in
news:#vNvTJKUFHA.1148@tk2msftngp13.phx.gbl:

> TForward wrote:
>> Hello all on this group, I hope you can help. I'm developing an asp
>> page that displays (in a table) paint colors. An older version is
>> at:
>>
>> http://tforward.aspfreeserver.com/modeling/paints/paints.asp
>>
>> A popup layer menu on each color will give the option to find similar
>> colors from that list based on decimal RGB values in separate RGB
>> columns. I'm using a query that searches the RGB values in increments
>> of +/-15 but I've realized some colors may not be selected at all if
>> one or two values are a little outside the range.
>>
>> Any suggestions how to do this? Thanks very much.
>>
> I'm not clear about what you want to do. If the selected values don't
> match a color, isn't it correct to tell the user that his selection
> did not match a color? If not, you need to be more specific about what
> you want to have happen. It will also help if you show us your table
> structure, perhaps a few rows of sample data, and, oh yes!!!, most
> importantly: what database type and version are you using?
>
> Bob Barrows

Bob,

Yeah, it is kind of vague. OK. Using an Access '03 dbase with system DSN
running IIS 5 on my laptop (development system). Here's my connection
info:

'create the objects
Set objPaintsConn = Server.CreateObject("ADODB.Connection")
Set objPaintsRS = Server.CreateObject("ADODB.Recordset")
'open the DSN connection
objPaintsConn.Open "DSN=SiteDbase_DSN"

The table has paint brands/manufacturers, color names, product codes and
decimal red, green and blue values (0-255) in individual columns. When a
user clicks on a displayed color on the webpage, the database should be
queried for a range of similar colors and display them in a new table. I'm
working on a query which selects colors where all three RGB values fall
w'in a certain range of the original color, say +/-15. The page queries
the database several times, increasing the variance from the original color
a bit each time by searching for RGB values +/-15 to 30 and then +/-30 to
45. Here's simplified SQL for this (ClickedRed, ClickedGreen and
ClickedBlue represent the RGB value of the selected color):

query #1: SELECT * FROM Paints WHERE Red BETWEEN ClickedRed and
ClickedRed+15 AND (green and blue the same way)

query #2: SELECT * FROM Paints WHERE Red BETWEEN ClickedRed+15 and
ClickedRed+30 AND (green and blue the same way)

query #3: SELECT * FROM Paints WHERE Red BETWEEN ClickedRed+30 and
ClickedRed+45 AND (green and blue the same way)

Using this method gets mostly what I need but some colors may escape. For
examply, if the RGB of a color is ClickedRed+10, ClickedGreen+17,
ClickedBlue+8, my method will skip it all three RGB values didn't fall w/in
one of the ranges.

I hope I'm making sense now and sorry for the length of this. Is there a
better approach to what I'm trying to do? Thanks for the help.

TForward

Re: Help with Query

TForward wrote:
>
> Using this method gets mostly what I need but some colors may escape.
> For examply, if the RGB of a color is ClickedRed+10, ClickedGreen+17,
> ClickedBlue+8, my method will skip it all three RGB values didn't
> fall w/in one of the ranges.
>
So again: what do you want it to do in this situation?

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Help with Query

"TForward" wrote in message
news:HW7ee.15264$Au1.4088@tornado.socal.rr.com...
> "Bob Barrows [MVP]" wrote in
> news:#vNvTJKUFHA.1148@tk2msftngp13.phx.gbl:
>
>> TForward wrote:
>>> Hello all on this group, I hope you can help. I'm developing an asp
>>> page that displays (in a table) paint colors. An older version is
>>> at:
>>>
>>> http://tforward.aspfreeserver.com/modeling/paints/paints.asp
>>>
>>> A popup layer menu on each color will give the option to find similar
>>> colors from that list based on decimal RGB values in separate RGB
>>> columns. I'm using a query that searches the RGB values in increments
>>> of +/-15 but I've realized some colors may not be selected at all if
>>> one or two values are a little outside the range.
>>>
>>> Any suggestions how to do this? Thanks very much.
>>>
>> I'm not clear about what you want to do. If the selected values don't
>> match a color, isn't it correct to tell the user that his selection
>> did not match a color? If not, you need to be more specific about what
>> you want to have happen. It will also help if you show us your table
>> structure, perhaps a few rows of sample data, and, oh yes!!!, most
>> importantly: what database type and version are you using?
>>
>> Bob Barrows
>
> Bob,
>
> Yeah, it is kind of vague. OK. Using an Access '03 dbase with system DSN
> running IIS 5 on my laptop (development system). Here's my connection
> info:
>
> 'create the objects
> Set objPaintsConn = Server.CreateObject("ADODB.Connection")
> Set objPaintsRS = Server.CreateObject("ADODB.Recordset")
> 'open the DSN connection
> objPaintsConn.Open "DSN=SiteDbase_DSN"
>
> The table has paint brands/manufacturers, color names, product codes and
> decimal red, green and blue values (0-255) in individual columns. When a
> user clicks on a displayed color on the webpage, the database should be
> queried for a range of similar colors and display them in a new table.
> I'm
> working on a query which selects colors where all three RGB values fall
> w'in a certain range of the original color, say +/-15. The page queries
> the database several times, increasing the variance from the original
> color
> a bit each time by searching for RGB values +/-15 to 30 and then +/-30 to
> 45. Here's simplified SQL for this (ClickedRed, ClickedGreen and
> ClickedBlue represent the RGB value of the selected color):
>
> query #1: SELECT * FROM Paints WHERE Red BETWEEN ClickedRed and
> ClickedRed+15 AND (green and blue the same way)
>
> query #2: SELECT * FROM Paints WHERE Red BETWEEN ClickedRed+15 and
> ClickedRed+30 AND (green and blue the same way)
>
> query #3: SELECT * FROM Paints WHERE Red BETWEEN ClickedRed+30 and
> ClickedRed+45 AND (green and blue the same way)
>
> Using this method gets mostly what I need but some colors may escape. For
> examply, if the RGB of a color is ClickedRed+10, ClickedGreen+17,
> ClickedBlue+8, my method will skip it all three RGB values didn't fall
> w/in
> one of the ranges.
It sounds from your initial description of the requirements that this is
what you want.

>
> I hope I'm making sense now and sorry for the length of this. Is there a
> better approach to what I'm trying to do? Thanks for the help.
>
> TForward

Would this be an accurate statement of the requirements?

The user enters a point in color-space (think of R,G,B as x,y,z
coordinates).
The system should display all of the available colors within a specific
radius of that color.

Re: Help with Query

"Mark Schupp" wrote in message
news:OyiNjlNUFHA.2172@tk2msftngp13.phx.gbl...
> "TForward" wrote in message
> news:HW7ee.15264$Au1.4088@tornado.socal.rr.com...
>> "Bob Barrows [MVP]" wrote in
>> news:#vNvTJKUFHA.1148@tk2msftngp13.phx.gbl:
>>
>>> TForward wrote:
>>>> Hello all on this group, I hope you can help. I'm developing an asp
>>>> page that displays (in a table) paint colors. An older version is
>>>> at:
>>>>
>>>> http://tforward.aspfreeserver.com/modeling/paints/paints.asp
>>>>
>>>> A popup layer menu on each color will give the option to find similar
>>>> colors from that list based on decimal RGB values in separate RGB
>>>> columns. I'm using a query that searches the RGB values in increments
>>>> of +/-15 but I've realized some colors may not be selected at all if
>>>> one or two values are a little outside the range.
>>>>
>>>> Any suggestions how to do this? Thanks very much.
>>>>
>>> I'm not clear about what you want to do. If the selected values don't
>>> match a color, isn't it correct to tell the user that his selection
>>> did not match a color? If not, you need to be more specific about what
>>> you want to have happen. It will also help if you show us your table
>>> structure, perhaps a few rows of sample data, and, oh yes!!!, most
>>> importantly: what database type and version are you using?
>>>
>>> Bob Barrows
>>
>> Bob,
>>
>> Yeah, it is kind of vague. OK. Using an Access '03 dbase with system
>> DSN
>> running IIS 5 on my laptop (development system). Here's my connection
>> info:
>>
>> 'create the objects
>> Set objPaintsConn = Server.CreateObject("ADODB.Connection")
>> Set objPaintsRS = Server.CreateObject("ADODB.Recordset")
>> 'open the DSN connection
>> objPaintsConn.Open "DSN=SiteDbase_DSN"
>>
>> The table has paint brands/manufacturers, color names, product codes and
>> decimal red, green and blue values (0-255) in individual columns. When a
>> user clicks on a displayed color on the webpage, the database should be
>> queried for a range of similar colors and display them in a new table.
>> I'm
>> working on a query which selects colors where all three RGB values fall
>> w'in a certain range of the original color, say +/-15. The page queries
>> the database several times, increasing the variance from the original
>> color
>> a bit each time by searching for RGB values +/-15 to 30 and then +/-30 to
>> 45. Here's simplified SQL for this (ClickedRed, ClickedGreen and
>> ClickedBlue represent the RGB value of the selected color):
>>
>> query #1: SELECT * FROM Paints WHERE Red BETWEEN ClickedRed and
>> ClickedRed+15 AND (green and blue the same way)
>>
>> query #2: SELECT * FROM Paints WHERE Red BETWEEN ClickedRed+15 and
>> ClickedRed+30 AND (green and blue the same way)
>>
>> query #3: SELECT * FROM Paints WHERE Red BETWEEN ClickedRed+30 and
>> ClickedRed+45 AND (green and blue the same way)
>>
>> Using this method gets mostly what I need but some colors may escape.
>> For
>> examply, if the RGB of a color is ClickedRed+10, ClickedGreen+17,
>> ClickedBlue+8, my method will skip it all three RGB values didn't fall
>> w/in
>> one of the ranges.
> It sounds from your initial description of the requirements that this is
> what you want.
>
>>
>> I hope I'm making sense now and sorry for the length of this. Is there a
>> better approach to what I'm trying to do? Thanks for the help.
>>
>> TForward
>
> Would this be an accurate statement of the requirements?
>
> The user enters a point in color-space (think of R,G,B as x,y,z
> coordinates).
> The system should display all of the available colors within a specific
> radius of that color.


What about something like this? I think this will get you all colors for
which the sum of difference in either direction is below a specified
threshold.

An aside, this is a little study of the safety palette I did awhile back
http://www.pagespy.com/dynsafetypal.htm ... colors that vary by 0x33 in one
component are still visually quite similar... (no point there, just a
curiosity.) :-)

-Mark


CREATE PROCEDURE spFindSimilarColors (
@targetred int,
@targetgreen int,
@targetblue int,
@allowablediff int
)
AS

SELECT [id], bluediff + reddiff + greendiff As diff
FROM
(SELECT
[id],
ABS(red - @targetred) As reddiff,
ABS( green - @targetgreen) As greendiff,
ABS(blue - @targetblue) As bluediff
FROM colors) drs
WHERE bluediff + reddiff + greendiff < @allowablediff

Re: Help with Query

Mark J. McGinty wrote:
> An aside, this is a little study of the safety palette I did awhile
> back http://www.pagespy.com/dynsafetypal.htm ... colors that vary by
> 0x33 in one component are still visually quite similar... (no point
> there, just a curiosity.) :-)
>
> -Mark
>
>
> CREATE PROCEDURE spFindSimilarColors (
> @targetred int,
> @targetgreen int,
> @targetblue int,
> @allowablediff int
> )
> AS
>
> SELECT [id], bluediff + reddiff + greendiff As diff
> FROM
> (SELECT
> [id],
> ABS(red - @targetred) As reddiff,
> ABS( green - @targetgreen) As greendiff,
> ABS(blue - @targetblue) As bluediff
> FROM colors) drs
> WHERE bluediff + reddiff + greendiff < @allowablediff

And here is how to translate this into an Access saved parameter query:

SELECT [id], bluediff + reddiff + greendiff As diff
FROM
(SELECT
[id],
ABS(red - [ptargetred]) As reddiff,
ABS( green - [ptargetgreen]) As greendiff,
ABS(blue - [ptargetblue]) As bluediff
FROM colors) drs
WHERE bluediff + reddiff + greendiff < [pallowablediff]

Save it as qFindSimilarColors.

Then, in ASP, do:

set rs=createobject("adodb.recordset")
conn.qFindSimilarColors ClickedRed,ClickedGreen,ClickedBlue,rs

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Help with Query

"Mark Schupp" wrote in
news:OyiNjlNUFHA.2172@tk2msftngp13.phx.gbl:

>
> Would this be an accurate statement of the requirements?
>
> The user enters a point in color-space (think of R,G,B as x,y,z
> coordinates).
> The system should display all of the available colors within a
> specific radius of that color.
>
>
>


Mark,

Exactly what I want, thank you. The clicked color becomes the center of a
sphere cut from the color cube. Whatever other colors' RGB values that
fall within the sphere are displayed in the result table. Defining a
simple plus/minus range from each of the original RGB values would cut a
cube from the color cube, which would be much easier and get the overall
results I'm after. But to make it all presentable, I need a way to sort
the resulting colors according to thier proximity to the original clicked
color using thier RGB values which are in separate fields. Any
suggestions? Thanks again!

T Forward

Re: Help with Query

TForward wrote:
> "Mark Schupp" wrote in
> news:OyiNjlNUFHA.2172@tk2msftngp13.phx.gbl:
>
>>
>> Would this be an accurate statement of the requirements?
>>
>> The user enters a point in color-space (think of R,G,B as x,y,z
>> coordinates).
>> The system should display all of the available colors within a
>> specific radius of that color.
>>
>>
>>
>
>
> Mark,
>
> Exactly what I want, thank you. The clicked color becomes the center
> of a sphere cut from the color cube. Whatever other colors' RGB
> values that fall within the sphere are displayed in the result table.
> Defining a simple plus/minus range from each of the original RGB
> values would cut a cube from the color cube, which would be much
> easier and get the overall results I'm after. But to make it all
> presentable, I need a way to sort the resulting colors according to
> thier proximity to the original clicked color using thier RGB values
> which are in separate fields. Any suggestions? Thanks again!
>
Drawing on my less than vast memories of geometry, I seem to recall that the
distance from a source point to any 3d point can be calculated by:

(x^2 + y^2 + z^2)^.5

i.e., the square root of the sum of the squares.

So, it's "simply" a matter of incorporating this formula into your sql
statement. Before we go in this direction, what comment to you have to make
about the adaptation of Mark McGinty's query that i posted?

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Help with Query

"Bob Barrows [MVP]" wrote in
news:OwWA#QWUFHA.2444@TK2MSFTNGP10.phx.gbl:

>> presentable, I need a way to sort the resulting colors according to
>> thier proximity to the original clicked color using thier RGB values
>> which are in separate fields. Any suggestions? Thanks again!

> Drawing on my less than vast memories of geometry, I seem to recall
> that the distance from a source point to any 3d point can be
> calculated by:
>
> (x^2 + y^2 + z^2)^.5
>
> i.e., the square root of the sum of the squares.
>
> So, it's "simply" a matter of incorporating this formula into your sql
> statement. Before we go in this direction, what comment to you have to
> make about the adaptation of Mark McGinty's query that i posted?
>
> Bob Barrows

Bob,
I copied the text from the other post here for reference. I'm a little
(OK, a lot) fuzzy about what's going on in this SQL and procedure. I
understand there's a subquery happening. I think ABS is absolute values
and that [ptargetcolors] are the original clicked colors (passed in
querystring, by the way). I don't know how "as" works or the @ tag.
Please explain as my SQL knowledge is pretty limited. Thanks.


**** COPY COPY COPY *****

(Mark's text)
CREATE PROCEDURE spFindSimilarColors (
@targetred int,
@targetgreen int,
@targetblue int,
@allowablediff int
)
AS


(your text)
And here is how to translate this into an Access saved parameter query:

SELECT [id], bluediff + reddiff + greendiff As diff
FROM
(SELECT
[id],
ABS(red - [ptargetred]) As reddiff,
ABS( green - [ptargetgreen]) As greendiff,
ABS(blue - [ptargetblue]) As bluediff
FROM colors) drs
WHERE bluediff + reddiff + greendiff < [pallowablediff]

Save it as qFindSimilarColors.

Then, in ASP, do:

set rs=createobject("adodb.recordset")
conn.qFindSimilarColors ClickedRed,ClickedGreen,ClickedBlue,rs

**** COPY COPY COPY *****

Re: Help with Query

"TForward" wrote in message
news:Jzree.18135$Au1.17084@tornado.socal.rr.com...
> "Bob Barrows [MVP]" wrote in
> news:OwWA#QWUFHA.2444@TK2MSFTNGP10.phx.gbl:
>
>>> presentable, I need a way to sort the resulting colors according to
>>> thier proximity to the original clicked color using thier RGB values
>>> which are in separate fields. Any suggestions? Thanks again!
>
>> Drawing on my less than vast memories of geometry, I seem to recall
>> that the distance from a source point to any 3d point can be
>> calculated by:
>>
>> (x^2 + y^2 + z^2)^.5
>>
>> i.e., the square root of the sum of the squares.
>>
>> So, it's "simply" a matter of incorporating this formula into your sql
>> statement. Before we go in this direction, what comment to you have to
>> make about the adaptation of Mark McGinty's query that i posted?
>>
>> Bob Barrows
>
> Bob,
> I copied the text from the other post here for reference. I'm a little
> (OK, a lot) fuzzy about what's going on in this SQL and procedure. I
> understand there's a subquery happening. I think ABS is absolute values
> and that [ptargetcolors] are the original clicked colors (passed in
> querystring, by the way). I don't know how "as" works or the @ tag.
> Please explain as my SQL knowledge is pretty limited. Thanks.

"AS" defines an alias for a field, or in the case of derived fields, gives
it a name. So the query "SELECT 1 AS one, 2 AS two" would return one row
from a derived table that looks like this:

one two
---- ----
1 2

The @ sign is a SQL Server thing; in SQL Server batch statements and
procedures, all parameters and variables must be prefixed with "@". If
you're using Jet, ignore that part.

What the query does is define a derived rowset with the absolute difference
between each color component in the table, and the "clicked-on" color's
components. It answers the question, "how much different is this color's
red/greeb/blue from the color being sought?"

The outer SQL adds those three numbers, and that sum can be tested against a
threshold value. So if the threshold was 30, it would find colors that are:

red +/-10, green +/-10 and blue +/- 10
red +/-20, green +/-5 and blue +/- 5
red +/-20, green +/-10 and blue +/- 0
[etc]

It will find colors with components that differ in either direction, and the
threshold applies to the color as a whole, not an individual component of
it. It might work.

I've been dying to generate some data and toss it into a web page to see if
it works out! :-)


-Mark



> **** COPY COPY COPY *****
>
> (Mark's text)
> CREATE PROCEDURE spFindSimilarColors (
> @targetred int,
> @targetgreen int,
> @targetblue int,
> @allowablediff int
> )
> AS
>
>
> (your text)
> And here is how to translate this into an Access saved parameter query:
>
> SELECT [id], bluediff + reddiff + greendiff As diff
> FROM
> (SELECT
> [id],
> ABS(red - [ptargetred]) As reddiff,
> ABS( green - [ptargetgreen]) As greendiff,
> ABS(blue - [ptargetblue]) As bluediff
> FROM colors) drs
> WHERE bluediff + reddiff + greendiff < [pallowablediff]
>
> Save it as qFindSimilarColors.
>
> Then, in ASP, do:
>
> set rs=createobject("adodb.recordset")
> conn.qFindSimilarColors ClickedRed,ClickedGreen,ClickedBlue,rs
>
> **** COPY COPY COPY *****
>
>
>
>

Re: Help with Query

"Mark J. McGinty" wrote in
news:DTree.16131$_K.8767@fed1read03:

>
> "AS" defines an alias for a field, or in the case of derived fields,
> gives it a name. So the query "SELECT 1 AS one, 2 AS two" would
> return one row from a derived table that looks like this:
>
> one two
> ---- ----
> 1 2
>
> The @ sign is a SQL Server thing; in SQL Server batch statements and
> procedures, all parameters and variables must be prefixed with "@".
> If you're using Jet, ignore that part.
>
> What the query does is define a derived rowset with the absolute
> difference between each color component in the table, and the
> "clicked-on" color's components. It answers the question, "how much
> different is this color's red/greeb/blue from the color being sought?"
>
> The outer SQL adds those three numbers, and that sum can be tested
> against a threshold value. So if the threshold was 30, it would find
> colors that are:
>
> red +/-10, green +/-10 and blue +/- 10
> red +/-20, green +/-5 and blue +/- 5
> red +/-20, green +/-10 and blue +/- 0
> [etc]
>
> It will find colors with components that differ in either direction,
> and the threshold applies to the color as a whole, not an individual
> component of it. It might work.
>
> I've been dying to generate some data and toss it into a web page to
> see if it works out! :-)
>
>
> -Mark
>

Mark,
In 3d color cube space, your method will slice out a piece of the color
cube w/volume (NOT dimensions) regulated by the "max value" but flexible
and stretchable. My method just takes rigid cube and some colors may be
missed. I like yours much better. I got it working on my development
laptop using your SQL, very slightly modified for field names, query
strings, etc. Here's my SQL:

SELECT *, bluediff + reddiff + greendiff As diff FROM (SELECT *, ABS
(redValue - 184) As reddiff, ABS(greenValue - 114) As greendiff, ABS
(blueValue - 34) As bluediff FROM AllPaints ) drs WHERE bluediff + reddiff
+ greendiff < 80 AND brand='delta ceramcoat'

RedValue, GreenValue and BlueValue are the RGB field values from the table.
184, 114 and 34 are the original clicked color's RGB passed in querystring
(in this example). AllPaints is the table and delta ceramcoat's the brand.
80, of course, is the threshold. I'd put a simple text field (not in a
form) to allow users to set the threshold but I'm not sure how to add it to
the querystring. I tried to sort the results by "diff" but got errors.
Suggestions how?

I posted the working page on my site. Try it out, tell me what you think.

http://tforward.aspfreeserver.com/modeling/paints/xpaints.as p


Thanks.

T Forward

Re: Help with Query

"TForward" wrote in message
news:U0Fee.23637$R46.16508@tornado.socal.rr.com...
> "Mark J. McGinty" wrote in
> news:DTree.16131$_K.8767@fed1read03:
[snip]

> Mark,
> In 3d color cube space, your method will slice out a piece of the color
> cube w/volume (NOT dimensions) regulated by the "max value" but flexible
> and stretchable. My method just takes rigid cube and some colors may be
> missed. I like yours much better. I got it working on my development
> laptop using your SQL, very slightly modified for field names, query
> strings, etc. Here's my SQL:
>
> SELECT *, bluediff + reddiff + greendiff As diff FROM (SELECT *, ABS
> (redValue - 184) As reddiff, ABS(greenValue - 114) As greendiff, ABS
> (blueValue - 34) As bluediff FROM AllPaints ) drs WHERE bluediff + reddiff
> + greendiff < 80 AND brand='delta ceramcoat'
>
> RedValue, GreenValue and BlueValue are the RGB field values from the
> table.
> 184, 114 and 34 are the original clicked color's RGB passed in querystring
> (in this example). AllPaints is the table and delta ceramcoat's the
> brand.
> 80, of course, is the threshold. I'd put a simple text field (not in a
> form) to allow users to set the threshold but I'm not sure how to add it
> to
> the querystring. I tried to sort the results by "diff" but got errors.
> Suggestions how?

That's pretty cool!

To sort by the value of diff, you'll have to derive it a third time (order
by bluediff + reddiff + greendiff ), or else nest the derived rowset one
more level, i.e.,

SELECT * FROM ( [your current query] ) drs2 ORDER BY diff

That may lead you to wonder, "why not move the WHERE condition to the outer
derivision too?" By absolutely all means any time you wonder something like
that, you should try it out. But my reasoning would be that, it's been my
experience (based largely on SQL Server) that it's more efficient to filter
from the inner-most derivision possible without affecting results, because
it reduces the number of rows upon which processing was wasted before they
were filtered.

In this case, if you placed the WHERE clause at the outer-most level, the db
engine would have to calculate diff for every row in the table -- of course
logically it'll have to do that either way, but the key factor is the number
of rows rendered at each level: drs will return all rows in the table, drs2
will return the filtered subset... by that logic you should probably move
the brand condition to the inner-most query.

In any case, the reason you got errors is that you can't use derived fields
in WHERE or ORDER BY clauses, at the same level at which they are derived.

Now the question is, will that provide a reasonably logical order for the
colors? It looks like it'll be at least half-way decent, and more logical
than without any sort. :-)


> I posted the working page on my site. Try it out, tell me what you think.
>
> http://tforward.aspfreeserver.com/modeling/paints/xpaints.as p

Thanks for showing that to me! :-)


-Mark




> Thanks.
>
> T Forward
>
>
>