Home » SQL & PL/SQL » SQL & PL/SQL » Comparing a field with a Set How to ?
Comparing a field with a Set How to ? [message #433021] Sun, 29 November 2009 01:48 Go to next message
depakjan
Messages: 12
Registered: November 2009
Junior Member
Hi Guys.

Please help write a query using cursor or a normal query for the following scenario

imagine there is a table with id,product and price and another table with price,product with price ranges

if the price of the a product from the first table is greater than every price for that product in some time period then i need that prodcut's id
(i.e)
    Product    Price   	Time				Product   Price  Timestamp
 	A	6	1:05			            A	    3       1:01
	B       5  	1:05			            A       4	    1:02		
        c       7       1:09                                A       4       1:03
                                                            A       3       1:04
                                                            A       2       1:05
                                                            B       4       1:01
                                                            B       4       1:02
                                                            B       6       1:03
                                                            B       3       1:04
                                                            B       2       1:05
                                                            c       8       1:06
                                                            c       9       1:07
                                                            c       10      1:08


From the second table i am selecting selecting price range from 1:01 to 1:05 so for product A the list will be (3,4,4,3,2), for product B it is (4,4,6,3,2) and for c it is null

Now i will compare th price of product A from first tabl which is 6 with all of the price in the second table (i.e) 6 > (3,4,4,3,2)(it shold be greater than all of them) , for b it will be 5 > (4,4,6,3,2)

Here the expected output is A since 5 is greater than all 3,4,4,3,2 and not b since 5 < 6

and for C since the price list in that timerange(1:5 - 1:05) is empty i need to find the max time from this list [1:06,1:07,1:08] which is got from time ranges less than 1:09(first table) i.e MAX(time list from second table where first table's time < second table's time) and then compare the value of the max time which is 10 in this example compare it with first table's price so 7 > 10 no then i will ignore C

i am in amatuer in oracle.. i have just started coding so please help me in this.. hope i clearly explained the situation

Expected output A

[Updated on: Sun, 29 November 2009 01:53]

Report message to a moderator

Re: Comparing a field with a Set How to ? [message #433023 is a reply to message #433021] Sun, 29 November 2009 02:13 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Hi depakjan,

Could you please provide the test case (The create table (for those two tables) and the insert statements)?

It would help you to get the desired result
Re: Comparing a field with a Set How to ? [message #433024 is a reply to message #433021] Sun, 29 November 2009 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
imagine there is a table with id,product and price

And what is time?

Quote:
From the second table i am selecting selecting price range from 1:01 to 1:05

Where does come "1:01" and "1:05"? Input? Other table?

Quote:
Here the expected output is A since 5 is greater than all 3,4,4,3,2

You mean 6?

Quote:
and for C since the price list in that timerange(1:5 - 1:05)

What is 1:5?
Please read your post BEFORE posting!

Quote:
i need to find the max time from this list [1:06,1:07,1:08] which is got from time ranges less than 1:09(first table

Ah ah, so this is the purpose of time in first table.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

Re: Comparing a field with a Set How to ? [message #433030 is a reply to message #433024] Sun, 29 November 2009 04:03 Go to previous messageGo to next message
depakjan
Messages: 12
Registered: November 2009
Junior Member
[Edit MC: remove my whole quoted post, add code tags...]
CREATE TABLE Table1
(
    Product_id      VARCHAR2(3) NOT NULL,
    Price	    integer,
    Time       	    Timestamp
)
/

CREATE TABLE Table2
(
    Product_id      VARCHAR2(3) NOT NULL,
    Price	    integer,
    Time       	    Timestamp
)
/

insert into table1 values('A',6,'8/6/2009 01:00:05.000000 AM');
insert into table1 values('B',5,'8/6/2009 01:00:05.000000 AM');
insert into table1 values('C',7,'8/6/2009 01:00:09.000000 AM');

insert into table1 values('A',3,'8/6/2009 01:00:01.000000 AM');
insert into table1 values('A',4,'8/6/2009 01:00:02.000000 AM');
insert into table1 values('A',4,'8/6/2009 01:00:03.000000 AM');
insert into table1 values('A',3,'8/6/2009 01:00:04.000000 AM');
insert into table1 values('A',2,'8/6/2009 01:00:05.000000 AM');
insert into table1 values('B',4,'8/6/2009 01:00:01.000000 AM');
insert into table1 values('B',4,'8/6/2009 01:00:02.000000 AM');
insert into table1 values('B',6,'8/6/2009 01:00:03.000000 AM');
insert into table1 values('B',3,'8/6/2009 01:00:04.000000 AM');
insert into table1 values('B',2,'8/6/2009 01:00:05.000000 AM');
insert into table1 values('C',8,'8/6/2009 01:00:06.000000 AM');
insert into table1 values('C',9,'8/6/2009 01:00:07.000000 AM');
insert into table1 values('C',10,'8/6/2009 01:00:08.000000 AM');

Now Output will be A alone .

[Updated on: Sun, 29 November 2009 04:34] by Moderator

Report message to a moderator

Re: Comparing a field with a Set How to ? [message #433032 is a reply to message #433030] Sun, 29 November 2009 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is nothing in table2?

Also:
SQL> insert into table1 values('A',6,'8/6/2009 01:00:05.000000 AM');
insert into table1 values('A',6,'8/6/2009 01:00:05.000000 AM')
                                *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

Use TO_DATE with an appropriate format. Is this 8 June or 6 August?

Regards
Michel

[Updated on: Sun, 29 November 2009 04:37]

Report message to a moderator

Re: Comparing a field with a Set How to ? [message #433041 is a reply to message #433032] Sun, 29 November 2009 06:52 Go to previous messageGo to next message
depakjan
Messages: 12
Registered: November 2009
Junior Member
Sorry for the wrong queries

insert into table1 values('A',6,to_timestamp('8/6/2009 01:00:05.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));
insert into table1 values('B',5,to_timestamp('8/6/2009 01:00:05.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));
insert into table1 values('C',7,to_timestamp('8/6/2009 01:00:05.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));


insert into table2 values('A',3,to_timestamp('8/6/2009 01:00:01.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));
insert into table2 values('A',4,to_timestamp('8/6/2009 01:00:02.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));
insert into table2 values('A',4,to_timestamp('8/6/2009 01:00:03.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));
insert into table2 values('A',3,to_timestamp('8/6/2009 01:00:04.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));
insert into table2 values('A',2,to_timestamp('8/6/2009 01:00:05.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));
insert into table2 values('B',4,to_timestamp('8/6/2009 01:00:01.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));
insert into table2 values('B',4,to_timestamp('8/6/2009 01:00:02.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));
insert into table2 values('B',6,to_timestamp('8/6/2009 01:00:03.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));
insert into table2 values('B',3,to_timestamp('8/6/2009 01:00:04.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));
insert into table2 values('B',2,to_timestamp('8/6/2009 01:00:05.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));
insert into table2 values('C',8,to_timestamp('8/6/2009 01:00:06.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));
insert into table2 values('C',9,to_timestamp('8/6/2009 01:00:07.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));
insert into table2 values('C',10,to_timestamp('8/6/2009 01:00:08.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));

[Updated on: Sun, 29 November 2009 07:34]

Report message to a moderator

Re: Comparing a field with a Set How to ? [message #433043 is a reply to message #433041] Sun, 29 November 2009 07:54 Go to previous messageGo to next message
depakjan
Messages: 12
Registered: November 2009
Junior Member
i am trying something like this


select ab.product_id from table1 ab inner join table2 bb on ab.product_id = bb.product_id where
ab.price > ( select max(bb.price) from table2 bc
where bb.product_id = ab.product_id and
bc.time between (ab.time - to_dsinterval( '00 00:00:05')) and ab.time
group by bc.product_id )


but not working
Re: Comparing a field with a Set How to ? [message #433050 is a reply to message #433043] Sun, 29 November 2009 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 29 November 2009 09:17
Quote:
From the second table i am selecting selecting price range from 1:01 to 1:05

Where does come "1:01" and "1:05"? Input? Other table?


Quote:
ab.time - to_dsinterval( '00 00:00:05'))

What is the reason of this?

Something is missing in the specifications you gave us.

Regards
Michel

[Updated on: Sun, 29 November 2009 09:39]

Report message to a moderator

Re: Comparing a field with a Set How to ? [message #433053 is a reply to message #433021] Sun, 29 November 2009 10:07 Go to previous messageGo to next message
depakjan
Messages: 12
Registered: November 2009
Junior Member
Michel thank you very much for your patience Smile


Thats the threshold i told earlier... for example product A's time in table 1 is 1:00:05 and the threshold is 5 secs so the range to look upon is 1:00:00 - 1:00:05... this should explain dsinterval too...
Re: Comparing a field with a Set How to ? [message #433056 is a reply to message #433053] Sun, 29 November 2009 10:16 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
> for example product A's time in table 1 is 1:00:05 and the threshold is 5 secs so the range to look upon is 1:00:00 - 1:00:05
A totally confusing, baffling & otherwise befuddling response.
From where do/did 5 digit values originate?
Re: Comparing a field with a Set How to ? [message #433057 is a reply to message #433053] Sun, 29 November 2009 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 29 November 2009 16:14
Michel Cadot wrote on Sun, 29 November 2009 09:17
Quote:
From the second table i am selecting selecting price range from 1:01 to 1:05

Where does come "1:01" and "1:05"? Input? Other table?


You still didn't answer to this.

Regards
Michel

Re: Comparing a field with a Set How to ? [message #433067 is a reply to message #433057] Sun, 29 November 2009 12:49 Go to previous messageGo to next message
depakjan
Messages: 12
Registered: November 2009
Junior Member
Ah... the range is 1:00:01 to 1:00:05, sorry at the top it was an example while inserting i used seconds as threshold so the confusion forget 1:00 to 1:05 .. it is 1:00:00 to 1:00:05

since product A's time is 1:00:05.00000 and the threshold for me is 5 sec (1:00:05 - 5 secs to 1:00:05) which is 1:00:00 to 1:00:05

SORRY!!!! Embarassed

[Updated on: Sun, 29 November 2009 12:54]

Report message to a moderator

Re: Comparing a field with a Set How to ? [message #433068 is a reply to message #433067] Sun, 29 November 2009 12:58 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK now in your last example C has time 01:00:05, is it really 01:00:05 or 01:00:09 as in your first example it was 1:09?

Regards
Michel

[Updated on: Sun, 29 November 2009 13:21]

Report message to a moderator

Re: Comparing a field with a Set How to ? [message #433069 is a reply to message #433067] Sun, 29 November 2009 13:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If I understand you want all rows from table1 with a price greater than the maximum price in table2 for the same product and a time bewteen the time in table1 minus 5 seconds and this time.
If this is the case You can write it:
SQL> select * from table1;
PRO      PRICE TIME
--- ---------- -------------------------
A            6 06/08/2009 01:00:05.000
B            5 06/08/2009 01:00:05.000
C            7 06/08/2009 01:00:09.000

3 rows selected.

SQL> select * from table2;
PRO      PRICE TIME
--- ---------- -------------------------
A            3 06/08/2009 01:00:01.000
A            4 06/08/2009 01:00:02.000
A            4 06/08/2009 01:00:03.000
A            3 06/08/2009 01:00:04.000
A            2 06/08/2009 01:00:05.000
B            4 06/08/2009 01:00:01.000
B            4 06/08/2009 01:00:02.000
B            6 06/08/2009 01:00:03.000
B            3 06/08/2009 01:00:04.000
B            2 06/08/2009 01:00:05.000
C            8 06/08/2009 01:00:06.000
C            9 06/08/2009 01:00:07.000
C           10 06/08/2009 01:00:08.000

13 rows selected.

SQL> select product_id from table1 a
  2  where price > (select max(price) from table2 b
  3                 where b.product_id = a.product_id
  4                   and b.time between a.time - numtodsinterval(5,'SECOND')
  5                                  and a.time
  6                )
  7  /
PRO
---
A

1 row selected.

Regards
Michel
Re: Comparing a field with a Set How to ? [message #433092 is a reply to message #433069] Mon, 30 November 2009 00:19 Go to previous messageGo to next message
depakjan
Messages: 12
Registered: November 2009
Junior Member
Hi Michel,

Your understanding is correct and thanks for the query, i was able to write the first query , thing is when i combine the second part of the logic i am not able to..

do this

delete from table1 where product_id = 'C' and price = 7;

insert into table1 values('C',11,to_timestamp('8/6/2009 01:00:15.000000 AM','mm/dd/yyyy hh:mi:ss.ff am'));

Now in that time range 1:00:10 to 1:00:15 for product c there are no prices in table2 , so what should happen is it has to consider times < time from table1 for C ... from this it has to compare the price of the maximum time with the price in table1

E.G

for time range 1:00:10 to 1:00:15 price for product C in table 2 is null
so it has to take all time less than 1:00:15 in table2 which will give times 1:00:06,1:00:07,1:00:08 .. take the price of the maximum time which is 10 and compare with the price in table1 which is 11 .... 11 > 10 product is selected

Now the final output should be A and C
Re: Comparing a field with a Set How to ? [message #433099 is a reply to message #433092] Mon, 30 November 2009 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe something like:
SQL> select * from table1;
PRO      PRICE TIME
--- ---------- ---------------------------------------------------------
A            6 06/08/2009 01:00:05.000
B            5 06/08/2009 01:00:05.000
C           11 06/08/2009 01:00:15.000

3 rows selected.

SQL> select * from table2;
PRO      PRICE TIME
--- ---------- ---------------------------------------------------------
A            3 06/08/2009 01:00:01.000
A            4 06/08/2009 01:00:02.000
A            4 06/08/2009 01:00:03.000
A            3 06/08/2009 01:00:04.000
A            2 06/08/2009 01:00:05.000
B            4 06/08/2009 01:00:01.000
B            4 06/08/2009 01:00:02.000
B            6 06/08/2009 01:00:03.000
B            3 06/08/2009 01:00:04.000
B            2 06/08/2009 01:00:05.000
C            8 06/08/2009 01:00:06.000
C            9 06/08/2009 01:00:07.000
C           10 06/08/2009 01:00:08.000

13 rows selected.

SQL> select a.product_id, a.price, nvl(max(b.price),max(c.price)) price2
  2  from table1 a, table2 b, table2 c
  3  where b.product_id (+) = a.product_id
  4    and b.time (+) between a.time - numtodsinterval(5,'SECOND')
  5                       and a.time
  6    and c.product_id (+) = a.product_id
  7    and c.time (+) < a.time
  8  group by a.product_id, a.price
  9  having a.price > nvl(max(b.price),max(c.price)) 
 10  order by 1
 11  /
PRO      PRICE     PRICE2
--- ---------- ----------
A            6          4
C           11         10

2 rows selected.

Regards
Michel
Re: Comparing a field with a Set How to ? [message #433204 is a reply to message #433099] Mon, 30 November 2009 11:42 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you don't need the prices, a better solution should be:
SQL> select product_id from table1 a
  2  where price > (select nvl(max(b.price),max(c.price))
  3                 from table2 b, table2 c
  4                 where b.product_id (+) = c.product_id
  5                   and b.time (+) between a.time - numtodsinterval(5,'SECOND')
  6                                      and a.time
  7                   and c.product_id = a.product_id
  8                   and c.time <= a.time
  9                )
 10  /
PRO
---
A
C

2 rows selected.

Regards
Michel
Re: Comparing a field with a Set How to ? [message #433238 is a reply to message #433204] Mon, 30 November 2009 23:16 Go to previous messageGo to next message
depakjan
Messages: 12
Registered: November 2009
Junior Member
Hi Michel,

Thanks you very much for the query..

Does (+)indicate join??...

b.product_id (+) = c.product_id is equivalent to
table2 b left outer join table2 c on b.product_id = c.product_id ??


Re: Comparing a field with a Set How to ? [message #433251 is a reply to message #433238] Tue, 01 December 2009 00:52 Go to previous message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
(+) is the Oracle operator for outer join.

b.product_id (+) = c.product_id is equivalent to
table2 c left outer join table2 b on b.product_id = c.product_id
or
table2 b right outer join table2 c on b.product_id = c.product_id

Regards
Michel

[Updated on: Tue, 01 December 2009 00:52]

Report message to a moderator

Previous Topic: List of tables
Next Topic: Can you have two stored functions with the same name ?
Goto Forum:
  


Current Time: Thu Sep 29 05:36:33 CDT 2016

Total time taken to generate the page: 0.16207 seconds