Home » SQL & PL/SQL » SQL & PL/SQL » Query challenge - complex logic
Query challenge - complex logic [message #199690] Wed, 25 October 2006 20:41 Go to next message
jung1975
Messages: 5
Registered: October 2006
Location: U.S.A
Junior Member
I have a table looks like below

ID         HGB                    EPO                    Y_N 
---------- ---------------------- ---------------------- --- 
1001       14                     5000                       
1001                              4800                       
1001                              4900                       
1001       13                     4000                       
1001                              4200                       
1001       14                     4000                       
1001                              4200                       
1001                              4100                       
1002       14                     4000                       
1002                              4200                       
1002                              4100                       
1002       14                     5000                       
1002                              4800                       
1002                              4100       


I have a complex logic that I need to use to populate the Y_N value

1) I need to loop thru the records in the same ID and if HGB is greater than 13 than start the comparison using the logic. But, If the HGB is < 13 skip the lows until you see the next available and move to the next available HGB in the same PID..

2) In this example, the fist HGB value of ID1001 is 14, so it’s valid for the loop comparison. As far as the comparison, you basically take the value of EPO which has the HGB and compare that number to the other EPOs which doesn’t have the HGB value…

For the first comparison,


I have to compare 5000 to 4800 and 4900. Because HGB value of 5000 is > 14 and it’s in the same ID.
If the record is less than the number that has HGB value then put Y in Y_N column and if its greater, then Put NO

And

Move to the next until you find the next available HGB in the same ID.

In this case it’s 13, 13 is less then 14 so we just skip rows until you see the next available HGB in the same ID.. For this example, the next available HGB is 14 and it’s EPO value is 4000, so we start doing a comparison. Both 4200 and 4100 are greater than 4000, so we put N in the Y_N column.. And Go to the next... in this example go to the next ID... and apply the same logic

So the ideal result should look like:


ID         HGB                    EPO                    Y_N 
---------- ---------------------- ---------------------- --- 
1001       14                     5000                       
1001                              4800                         Y
1001                              4900                         Y
1001       13                    4000                        
1001                              4200                        
1001       14                    4000                       
1001                              4200                         N
1001                              4100                         N
1002       14                    4000                        
1002                              4200                          N
1002                              4100                          N
1002       14                     5000                        
1002                              4800                          Y
1002                              4100                          Y


How can I do this? Can you show me some code examples? Can I do this in set based query or do I have to use a cursor?


to create a test data:
Here is DDL

CREATE TABLE "RA"."TEST" ("ID" VARCHAR2(10 byte) NOT NULL, "HGB"
NUMBER(10), "EPO" NUMBER(10), "Y_N" CHAR(1 byte))
TABLESPACE "RA" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING

and
here is DML

insert into ra.test(Id,HGB, EPO) values ( 1001, 14, 5000);
insert into ra.test(Id, EPO) values ( 1001, 4800);
insert into ra.test(Id, EPO) values ( 1001, 4900);
insert into ra.test(Id,HGB, EPO) values ( 1001, 13, 4000);
insert into ra.test(Id, EPO) values ( 1001, 4200);
insert into ra.test(Id,HGB, EPO) values ( 1001, 14, 4000);
insert into ra.test(Id, EPO) values ( 1001, 4200);
insert into ra.test(Id, EPO) values ( 1001, 4100);
insert into ra.test(Id,HGB, EPO) values ( 1002, 14, 4000);
insert into ra.test(Id, EPO) values ( 1002, 4200);
insert into ra.test(Id, EPO) values ( 1002, 4100);
insert into ra.test(Id,HGB, EPO) values ( 1002, 14, 5000);
insert into ra.test(Id, EPO) values ( 1002, 4800);
insert into ra.test(Id, EPO) values ( 1002, 4100);

[Updated on: Wed, 25 October 2006 20:42]

Report message to a moderator

Re: Query challenge - complex logic [message #199691 is a reply to message #199690] Wed, 25 October 2006 20:42 Go to previous messageGo to next message
jung1975
Messages: 5
Registered: October 2006
Location: U.S.A
Junior Member
you can order it by ID and date ( I forget to add this column)
Re: Query challenge - complex logic [message #199696 is a reply to message #199691] Wed, 25 October 2006 21:21 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Look, this CAN be done using Analytic Functions, and I know exactly how I would go about it. It would also be fairly efficient. BUT there is not a chance in hell that 95% of Oracle developers would ever understand what the SQL was doing, and therefore whether it was doing it correctly.

The problem is that SQL is a SET PROCESSING language, whereas you need a PROCEDURAL LANGUAGE. When you try to write procedural logic in SQL, you end up with incompehensible rubbish.

It's a lot like teaching your dog to bark "Hello" (think Scooby-Doo - "Rrrerrroooo"). Its a clever, nay impressive, trick; but in tems of communication, it doesn't compare with a doggy-grin and wagging tail.

So (it took a while to get here, but here we are), I recommend in the stongest possible terms that you ignore the likely deluge of Analytic Function solutions that will be posted below this one, and just code it in PL/SQL with a cursor and some IT/THENs. If you need to call it from a SQL statement (instead of from a PL/SQL block) for whatever reason, put it in a Pipelined Function (search the PL/SQL manual) so that the output can be piped to a SELECT statment.

Ross Leishman
Re: Query challenge - complex logic [message #199699 is a reply to message #199696] Wed, 25 October 2006 21:29 Go to previous messageGo to next message
jung1975
Messages: 5
Registered: October 2006
Location: U.S.A
Junior Member
Russ- Can you show me how to do this in pl/sql using a cursor?
Re: Query challenge - complex logic [message #199703 is a reply to message #199699] Wed, 25 October 2006 21:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Something like this
declare
  cursor c is 
  select * from tab order by whatever;

  lasthgb c%rowtype;
begin
  for curr in c loop
    if hgb is not null then
      lasthgb := curr;
    else
      if lasthgb.hgb >= 14 
      and lasthgb.id = curr.id then
        if lasthgb.epo > curr.epo then
          update tab set y_n = 'Y' where ....;
        else
          update tab set y_n = 'N' where ....;
        end if;
      end if
    end if;
  end loop;
end;


Ross Leishman
Re: Query challenge - complex logic [message #199704 is a reply to message #199703] Wed, 25 October 2006 22:08 Go to previous messageGo to next message
jung1975
Messages: 5
Registered: October 2006
Location: U.S.A
Junior Member

Thanks,but what do I have to put in the where clause?

update tab set y_n = 'Y' where ....;
else
update tab set y_n = 'N' where ....;
Re: Query challenge - complex logic [message #199735 is a reply to message #199704] Thu, 26 October 2006 01:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Try it and see if you can work it out on your own.

Hint: It will update only 1 row. What sort of WHERE clause can be guaranteed to updated only 1 row?

Ross Leishman
Re: Query challenge - complex logic [message #199772 is a reply to message #199690] Thu, 26 October 2006 03:18 Go to previous message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
That logic will not work for his requirement.He is not checking

for the entire grouping by id.He is checking serially .
Previous Topic: how to write Sql script for culmalative balance for 2 table
Next Topic: Changing Fieldtypes
Goto Forum:
  


Current Time: Fri Dec 09 04:03:24 CST 2016

Total time taken to generate the page: 0.12927 seconds