Home » SQL & PL/SQL » SQL & PL/SQL » Assigning Values within a Function
Assigning Values within a Function [message #187803] Tue, 15 August 2006 13:25 Go to next message
dvamsikrishna
Messages: 4
Registered: August 2006
Junior Member
Hi All:

Please help me in resolving this function:

Sample Data

Store ID, Store_Latest_ID
121, 1101
1101, 1200
1200, Null
141, 1462
1462,Null
1346,0
1455,0

Expected Output returned from the fn

Store ID
1200
1462
1346
1455

function latest_ID (A.Store_ID In Number, B.Store_Latest_ID IN Number)
Return Number IS
Begin
If B.Store_Latest_ID = 0 then
Return A.Store_ID;
ElseIf B.Store_Latest_ID > 0 then
Return B.Store_Latest_ID;
Else Return A.Store_ID;
End If;
End latest_ID;

Ended up Getting
Store ID
1101
1200
1462
1346
1455
Re: Assigning Values within a Function [message #187869 is a reply to message #187803] Wed, 16 August 2006 02:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you've got a bit of a problem with your question.
You list 7 sets of input data, say that you are expecting 4 results from these 7 tests, and then post 5 actual results.


Here's what I get running your tests.
Tell us what the actual problem you're getting is, and we may be able to fix it.

create or replace function latest_ID (Store_ID In Number, Store_Latest_ID IN Number)
Return Number IS
Begin
If Store_Latest_ID = 0 then
  Return Store_ID;
ElsIf Store_Latest_ID > 0 then
  Return Store_Latest_ID;
Else 
  Return Store_ID;
End If;
End latest_ID;
/

drop table store_ids;

create table store_ids (store_id  number, store_latest_id  number);

insert into store_ids values (121, 1101);
insert into store_ids values (1101, 1200);
insert into store_ids values (1200, Null);
insert into store_ids values (141, 1462);
insert into store_ids values (1462,Null);
insert into store_ids values (1346,0);
insert into store_ids values (1455,0);

SQL> select store_id, store_latest_id, latest_id(store_id,store_latest_id) fn_result
  2  from   store_ids;

  STORE_ID|STORE_LATEST_ID| FN_RESULT
----------|---------------|----------
       121|           1101|      1101
      1101|           1200|      1200
      1200|null           |      1200
       141|           1462|      1462
      1462|null           |      1462
      1346|              0|      1346
      1455|              0|      1455

Re: Assigning Values within a Function [message #187871 is a reply to message #187803] Wed, 16 August 2006 02:24 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Here's a quick and dirty solution.

The script:
CREATE TABLE mhe_foo(store_id number, last_id number)
/

INSERT INTO mhe_foo VALUES(121, 1101 );
INSERT INTO mhe_foo VALUES(1101, 1200);
INSERT INTO mhe_foo VALUES(1200, Null);
INSERT INTO mhe_foo VALUES(141, 1462 );
INSERT INTO mhe_foo VALUES(1462,Null );
INSERT INTO mhe_foo VALUES(1346,0    );
INSERT INTO mhe_foo VALUES(1455,0    );

PROMPT The data
SELECT * FROM mhe_foo
/

PROMPT Desired output:
SELECT store_id
FROM   mhe_foo
WHERE  nvl(last_id, 0) = 0
/
PROMPT A function accepting a store id:
CREATE OR REPLACE FUNCTION get_latest(p_store_id IN NUMBER)
RETURN NUMBER
IS
  v_return NUMBER;
BEGIN
  SELECT store_id
  INTO   v_return
  FROM   ( SELECT store_id
           FROM   mhe_foo
           CONNECT BY prior last_id = store_id
           START WITH  store_id = p_store_id
           ORDER BY LEVEL DESC
         )
  WHERE rownum = 1;
  
  RETURN v_return;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END get_latest;
/

SELECT store_id
     , get_latest(store_id) last_id
FROM   mhe_foo
/

DROP FUNCTION get_latest
/

DROP TABLE mhe_foo
/


The test run:
SQL> @orafaq

Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.

The data

  STORE_ID    LAST_ID
---------- ----------
       121       1101
      1101       1200
      1200
       141       1462
      1462
      1346          0
      1455          0

7 rows selected.

Desired output:

  STORE_ID
----------
      1200
      1462
      1346
      1455

A function accepting a store id:

Function created.


  STORE_ID    LAST_ID
---------- ----------
       121       1200
      1101       1200
      1200       1200
       141       1462
      1462       1462
      1346       1346
      1455       1455

7 rows selected.


Function dropped.


Table dropped.


MHE
Re: Assigning Values within a Function [message #188015 is a reply to message #187803] Wed, 16 August 2006 14:09 Go to previous messageGo to next message
dvamsikrishna
Messages: 4
Registered: August 2006
Junior Member
Hi Maaher:

The function worked Great!!! Thanks a Lot!!! Sorry for not explaining the problem clearly.

Can you please help me out If I have an additional field within the table called store name char(50) and it should also rollup the same way.

Ex:
Actual Table
STORE_ID Store Name LAST_ID
---------- ---- ------
121 110 1101
1101 230 1200
1200 260 Null
141 115 1462
1462 200 Null
1346 120 0
1455 130 0

Output Table
STORE_ID Store Name LAST_ID
---------- ---- ------
121 260 1101
1101 260 1200
1200 260 Null
141 200 1462
1462 200 Null
1346 NJersey 0
1455 New York 0

Thanks
Vamsi
Re: Assigning Values within a Function [message #215216 is a reply to message #187871] Fri, 19 January 2007 16:58 Go to previous message
dvamsikrishna
Messages: 4
Registered: August 2006
Junior Member
Hi Maaher:

The function worked Great!!! Thanks a Lot!!! Sorry for not explaining the problem clearly.

Can you please help me out If I have an additional field within the table called store name char(50) and it should also rollup the same way.

Ex:
Actual Table
STORE_ID Store Name LAST_ID
---------- ---- ------
121 110 1101
1101 230 1200
1200 260 Null
141 115 1462
1462 200 Null
1346 120 0
1455 130 0

Output Table
STORE_ID Store Name LAST_ID
---------- ---- ------
121 260 1101
1101 260 1200
1200 260 1200
141 200 1462
1462 200 1462
1346 NJersey 1346
1455 New York 1455

Thanks
Previous Topic: How to Insert Data into a CLOB Column of 86000 characters long
Next Topic: cursor
Goto Forum:
  


Current Time: Mon Dec 05 07:06:21 CST 2016

Total time taken to generate the page: 0.14182 seconds