Home » SQL & PL/SQL » SQL & PL/SQL » Comparison Operator from column (SQL Statement)
Comparison Operator from column [message #275165] Thu, 18 October 2007 11:46 Go to next message
ramyababu
Messages: 9
Registered: October 2007
Junior Member
Hi, I'm new here...

I have 3 columns A, B and C

I need to compare A and B with the comparison Operator from column C

This column C can contain any comparison operator like '<' or '<=' or '>' or '>='

Right I have this sql

SELECT A, B , C from Table 1
WHERE A C B

I need to get this on c# coding.

when my query get executed it with the column name rather than the value of that column. Is there any other way to get this done?

Thanks!
Re: Comparison Operator from column [message #275168 is a reply to message #275165] Thu, 18 October 2007 11:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is a very bad design.
If you stick to it, you will have to create a dynamic sql statement based on the contents of your C-column and execute that.
Re: Comparison Operator from column [message #275170 is a reply to message #275165] Thu, 18 October 2007 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or you can use a big case statement with all values that can contain C column.

Regards
Michel
Re: Comparison Operator from column [message #275178 is a reply to message #275170] Thu, 18 October 2007 12:36 Go to previous messageGo to next message
ramyababu
Messages: 9
Registered: October 2007
Junior Member
Thanks for the suggestion.

My syntax is below since I get only the below 4 operators.

A (CASE C WHEN '<' THEN < WHEN '<=' THEN < WHEN '>' THEN > WHEN '>=' THEN > END) B

I get "missing expression" error. Please advice
Re: Comparison Operator from column [message #275181 is a reply to message #275178] Thu, 18 October 2007 12:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is "case when c = '<' and a < b then ... when ..."

Regards
Michel
Re: Comparison Operator from column [message #275189 is a reply to message #275181] Thu, 18 October 2007 13:29 Go to previous messageGo to next message
ramyababu
Messages: 9
Registered: October 2007
Junior Member
Thanks but this doesn't work for me. Because I want to set the sql statement on the THEN. May be I should have been clear.

CASE c

WHEN "<" Then A < B
WHEN "<=" THEN A <= B

END...

For exmaple when the sql executes it should execute

SELECT A, B, C FROM table1
WHERE A (value of C) B
Re: Comparison Operator from column [message #275190 is a reply to message #275189] Thu, 18 October 2007 13:38 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
How's this for a totally hokey work around.

A large UNION statement .. something along the lines of ..

select a,b,c from table1 where a < b and c = '<' 
UNION ALL
select a,b,c from table1 where a > b and c = '>'
etc etc ..



It's a silly solution I know.
Re: Comparison Operator from column [message #275193 is a reply to message #275189] Thu, 18 October 2007 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
where 1 = case when c = '<' and a < b then 1 when ... else 0 end

Regards
Michel
Re: Comparison Operator from column [message #275198 is a reply to message #275165] Thu, 18 October 2007 14:30 Go to previous messageGo to next message
ramyababu
Messages: 9
Registered: October 2007
Junior Member
Thanks for the ideas. The Union ALL works fine on my query. I think I got the expected results. Thanks again.
Re: Comparison Operator from column [message #275200 is a reply to message #275198] Thu, 18 October 2007 14:59 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And you'll also get it with case with the benefit of scanning the table only once.

Regards
Michel
Previous Topic: reverse engineer
Next Topic: identify table associated with a blob.
Goto Forum:
  


Current Time: Fri Dec 09 09:39:08 CST 2016

Total time taken to generate the page: 0.09848 seconds