Hi,
Ihave to compare 2 columns in a given table Table_Test in oracle database . if the column1 > = column2 then "True" is displayed and if the column1< column2 then "False " is displayed. if i use the Decode() funtion then it just checks the Equality but not "<" or ">" . if you have any clue on this please give the solution.
Thanks
Create resultset as
select col1,col2 from table
then use resultset.getXXXX()
methods,then proceed with your comparison operations
Thanks for the detailed solution.
But i need to do that Comparison at the Database itself. So, is there any function or query to do that?
I think it would help if you gave more complete requirements.
The order of a row in a table is unknown, so comparing one column between multiple rows in two tables isn't meaningful. You will never know which rows you are comparing because there is no implicit order.
Can you expound on what you are trying to do or what problem you are attempting to solve? I'm guessing this is a fairly simple problem once the needs are understood.
I agree with WorkForFood that this problem is ill-specified. Nonetheless it sounds like the OP may just want:
select (col1 >= col2) as bar from foo;
(edit)
But as WorkForFood points out this is meaningless - you don't know what the results mean unless you add an order by statement, or alternatively add a primary key column to the output. i.e.
select (col1 >= col2) as bar from foo order by spong;
Or
select id, (col1 >= col2) as bar from foo;
If the OP thinks he can avoid doing either of these things then he's under the misapprehension that database rows have innate ordering.
> select (col1 >= col2) as bar from foo;
> But as WorkForFood points out this is meaningless -
> you don't know what the results mean unless you add
> an order by statement, or alternatively add a primary
> key column to the output. i.e.
> select (col1 >= col2) as bar from foo order by
> spong;
> Or
> select id, (col1 >= col2) as bar from foo;
> If the OP thinks he can avoid doing either of these
> things then he's under the misapprehension that
> database rows have innate ordering.
It's hard to tell if just the "order by" clause regardless of variant is an adequate solution for the OP or not based on the question.
Because the "order by" clause is applied after the select comparison completes there would still be an issue with no implicit order of the rows being compared and the results while pleasingly ordered would still be inconsistent or meaningless.
If I was to guess I would say that a join will be required in order to match column values regardless of row position (but again, real hard to say based on the requirements).
> Because the "order by" clause is applied after the
> select comparison completes there would still be an
> issue with no implicit order of the rows being
> compared and the results while pleasingly ordered
> would still be inconsistent or meaningless.
I think you've misunderstood something. This won't compare the column values from unrelated rows - it will always compare the column values from the same row - in exactly the same way that concatenation operations work on columns from the same row. There's no ambiguity here. The unit of work is the row, not the column.
(edit: fixed my terminology)
D.
