Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Problem
icon5.gif  PL/SQL Problem [message #298182] Tue, 05 February 2008 04:24 Go to next message
joselfmp
Messages: 23
Registered: August 2007
Location: Madrid
Junior Member

Hi friends,
I have to create a select , that receives a parameter (padre) which would be put into the where clause.. and that has to show all its "descendants" ...
It is for this structure... and I'm thinking about how to do it.. but I don't kow how!
Please help me with your code if you are able to do it.
It seems to be simple.. but it's not so.
------------------------------------------------------------------------
Imagine you have:
CREATE TABLE FAMILY
(PADRE VARCHAR2(10),
CHILD_LOW VARCHAR2(10),
CHILD_HIGH VARCHAR2(10)
);

INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('01','01','01');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('01','AA','AN');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('01','K0','K50');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('AA','100','199');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('K2','500','600');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('500','VV','VV');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('02','02','02');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('02','B0','B9');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('02','1000','2000');
INSERT INTO FAMILY (PADRE ,CHILD_LOW,CHILD_HIGH) VALUES ('1500','MA','MZ');

------------------------------------------------------------------------
This will show this structure, a "parent" with a range of values (low and high) for his sons and descendants

--------------------
PADRE CHILD_LOW CHILD_HIGH
01 01 01
01 AA AN
01 K0 K50
AA 100 199
K2 500 600
500 VV VV
02 02 02
02 B0 B9
02 1000 2000
1500 MA MZ
------------------------------
Te idea is give a "parent" value to the select and... the magic query will return the collection of descendants ranges.
I.e. for '01' it would return:
AA AN
K0 K50
100 199
500 600
VV VV

for 'K2' it would return:
500 600
VV VV

I have the initial query:

select child_low ||'-' ||child_high DESCENDANTS
from (select padre,child_low,child_high from family where
padre<>child_low)
start with padre = '01'
connect by prior child_low <=padre and prior child_high >= padre;

The from clause where it appears padre<>child_low is to avoid the loops (i.e. when a parent and the child would be '01' in this example)

PROBLEM:
In this query it apears as descendant an interval that it isn't that, MA-MZ. this is because ranges are varchars,and, in this case, when we compare '1500'<'199' .. the problem appears.

So.. your help would be very apreciated...
Thanks,
Jose L
Re: PL/SQL Problem [message #298186 is a reply to message #298182] Tue, 05 February 2008 04:39 Go to previous messageGo to next message
raj1222
Messages: 5
Registered: February 2008
Location: hyd
Junior Member
hai ,
i am raj
to put a parameter in the where clause u can use &.

select * from emp where &condition;
or
select * from emp where sal=&sal;

at every ponit it will ask for a condition.
icon5.gif  Re: PL/SQL Problem [message #298187 is a reply to message #298186] Tue, 05 February 2008 04:51 Go to previous messageGo to next message
joselfmp
Messages: 23
Registered: August 2007
Location: Madrid
Junior Member
Hi,
the problem is not how to put a parameter Wink... it's how to avoid the wrong result in the query....
Thanks anyway for reading the question Wink

Regards,
Jose L.
Re: PL/SQL Problem [message #298190 is a reply to message #298182] Tue, 05 February 2008 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well the problem is that you mixed upi characters and numbers.
You can't compare AT THE SAME TIME as if it is string or number.
You have to choose. This is a model problem.
Now, I recommend you change your number string ot a fixed length number string: '1500' -> '000001500', '199' -> '000000199' then you will no more have this problem.
I repeat number strings are not numbers there are strings.

Regards
Michel
Re: PL/SQL Problem [message #298196 is a reply to message #298190] Tue, 05 February 2008 05:24 Go to previous messageGo to next message
joselfmp
Messages: 23
Registered: August 2007
Location: Madrid
Junior Member
Hi,
thanks for answer.. but.. you're not exactly right.
The values are in that way because is a customer requirement.. In fact.. that are similar to values the customer has in the Database...
It's possible to compare the values if you make it with a function... like fnd_number.canonical_to_number in oracle applications... but... the problem appears when we try to compare the strings that have numbers inside when strings that have characters.. The difficulty of this problem is just that we need to compare strings... that may contain numbers or varchars.. but string-numbers have to be treated as numbers when we comprae it whith other string-numbers.. That's why I think this is not a newbie question... This is an expert question... I'm developing with pl/sql 8 years...... but this damm query.. is hard ;-P

More ideas?

Jose L.
Re: PL/SQL Problem [message #298200 is a reply to message #298196] Tue, 05 February 2008 05:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
It's possible to compare the values if you make it with a function... like fnd_number.canonical_to_number in oracle applications

I know but this is not the correc to do it.
It is ALWAYS a bad way to fix model error with technical workaround.

Quote:
The difficulty of this problem is just that we need to compare strings... that may contain numbers or varchars.. but string-numbers have to be treated as numbers when we comprae it whith other string-numbers..

This is just a problem of your "canonical" function. Instead of converting number strings to numbers, it should convert them to fixed length number strings.
This is why I think it is a newbie question.

Regards
Michel
icon5.gif  Re: PL/SQL Problem [message #298207 is a reply to message #298200] Tue, 05 February 2008 06:00 Go to previous messageGo to next message
joselfmp
Messages: 23
Registered: August 2007
Location: Madrid
Junior Member
It's not possible..
If you have a database in customer with these values.. and you can't modify them (we're always talking about values in the database of a client)and you can't redesign the data model (of course.. because we're talking about data model in oracle applications)... how would you do the query...
You told is a newbie question.. so...how coulld you do the query?
Thanks a lot,

Jose
Re: PL/SQL Problem [message #298209 is a reply to message #298207] Tue, 05 February 2008 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You talked about "canonical" function, what is it? Post code.
Where do you use it?

Before, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.


Regards
Michel

[Updated on: Tue, 05 February 2008 06:14]

Report message to a moderator

Re: PL/SQL Problem [message #298219 is a reply to message #298209] Tue, 05 February 2008 06:36 Go to previous messageGo to next message
joselfmp
Messages: 23
Registered: August 2007
Location: Madrid
Junior Member
Hi,
canonical_to_number is a function of FND_NUMBER package in Oracle Applications. There exists another function number_to_canonical.
They're used in different standard views of oracle applications

-----------------------

C_FORMAT constant varchar2(60) := 'FM999999999999999999999.99999999999999999999';
--
-- Canonical functions
--
function canonical_to_number(canonical varchar2)
return number is
decimal_char varchar2(1);
begin
if (canonical_mask <> C_FORMAT) then
-- old behavior for 3757291
return to_number(canonical, canonical_mask);
end if;
decimal_char := substr(ltrim(to_char(.3,'0D0')),2,1);
return round(to_number(translate(canonical, '.', decimal_char)), 20);
end canonical_to_number;

function number_to_canonical(numberval number)
return varchar2 is
decimal_char varchar2(1);
begin
if (canonical_mask <> C_FORMAT) then
-- old behavior for 3757291
return rtrim(to_char(numberval, canonical_mask),'.');
end if;
decimal_char := substr(ltrim(to_char(.3,'0D0')),2,1);
return translate(to_char(round(numberval, 20)), decimal_char, '.');
end number_to_canonical;
Re: PL/SQL Problem [message #298231 is a reply to message #298219] Tue, 05 February 2008 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't read what I posted.
Use "Edit" button and fix your post.

Regards
Michel
Re: PL/SQL Problem [message #298243 is a reply to message #298209] Tue, 05 February 2008 08:01 Go to previous messageGo to next message
joselfmp
Messages: 23
Registered: August 2007
Location: Madrid
Junior Member
Hi,
canonical_to_number is a function of FND_NUMBER package in Oracle Applications. There exists another function number_to_canonical.
They're used in different standard views of oracle applications

-----------------------

C_FORMAT constant varchar2(60) := 
'FM999999999999999999999.99999999999999999999';
--
--  Canonical functions
--
function canonical_to_number(canonical varchar2)
  return number is
    decimal_char varchar2(1);
  begin
    if (canonical_mask <> C_FORMAT) then 
     -- old behavior for 3757291
      return to_number(canonical, canonical_mask);
    end if;
    decimal_char := substr(ltrim(to_char(.3,'0D0')),2,1);
    return round(to_number(translate(canonical, '.', decimal_char)), 20);
  end canonical_to_number;

function number_to_canonical(numberval number)
 return varchar2 is
    decimal_char varchar2(1);
begin
    if (canonical_mask <> C_FORMAT) then 
  -- old behavior for 3757291
      return rtrim(to_char(numberval, canonical_mask),'.');
    end if;
    decimal_char := substr(ltrim(to_char(.3,'0D0')),2,1);
    return translate(to_char(round(numberval, 20)), decimal_char, '.');
end number_to_canonical;
Re: PL/SQL Problem [message #298244 is a reply to message #298231] Tue, 05 February 2008 08:02 Go to previous messageGo to next message
joselfmp
Messages: 23
Registered: August 2007
Location: Madrid
Junior Member
You're right, sorry.. here I post with format.

Thanks,
Jose L.
Re: PL/SQL Problem [message #298247 is a reply to message #298244] Tue, 05 February 2008 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So, as I said, instead of returning a number, return a VARCHAR2 with "canonical" fixed length number string if it is a number string and the string itself otherwise and all is done.

Regards
Michel
Re: PL/SQL Problem [message #298253 is a reply to message #298247] Tue, 05 February 2008 08:17 Go to previous messageGo to next message
joselfmp
Messages: 23
Registered: August 2007
Location: Madrid
Junior Member
It's not so simple because you don't know if you need to put canonical_to_number or number_to_canonical when you write the where clause... if you use 1st in example.. an error appears because you can't put as a parameter a string that have no numbers...
I imagine.. it must be used something similar.. but not the same..
but.. I can't see it.
Please.. if you know what/how.. post the query....

Thanks and Regards,
Jose L
Re: PL/SQL Problem [message #298260 is a reply to message #298253] Tue, 05 February 2008 08:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't care.
Just use 1 function that:
- take a varchar2
- check if it is a number
- if is, then return a canonical fixed length string left padding it
- if is not, then return the input string

Regards
Michel
Re: PL/SQL Problem [message #298266 is a reply to message #298260] Tue, 05 February 2008 08:52 Go to previous messageGo to next message
joselfmp
Messages: 23
Registered: August 2007
Location: Madrid
Junior Member
Hi Michel,
you can't do it:

Here is the function you say:

CREATE OR REPLACE FUNCTION fnd_strings(pi varchar2) RETURN 
VARCHAR2 IS
[B]-- take a varchar2[/B]
BEGIN
   begin
[B]-- check if it is a number[/B]
     if fnd_number.canonical_to_number(pi)<=99999999 then
[B]-- if is, then return a canonical fixed length string left padding it[/B]
 	  return to_char(fnd_number.canonical_to_number(pi));
     else
      -- anyway
   	  return to_char(fnd_number.canonical_to_number(pi));
     end if;

    exception
    when others then
     -- No numbers
[B]-- if is not, then return the input string[/B]
    	 return pi;
    end;
   END;
/


Now, running the query:
select child_low ||'-' ||child_high DESCENDANTS
from (select padre,child_low,child_high from family where
padre<>child_low)
start with padre = '01'
connect by prior fnd_strings(child_low) <=fnd_strings(padre)
 and prior fnd_strings(child_high) >= fnd_strings(padre);


The result is the same:

DESCENDANTS
------------
AA-AN
100-199
MA-MZ
K0-K50
500-600
VV-VV

More ideas?

Jose L.

[Updated on: Tue, 05 February 2008 08:54] by Moderator

Report message to a moderator

Re: PL/SQL Problem [message #298268 is a reply to message #298266] Tue, 05 February 2008 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use the previous functions, write a brand new one.

Regards
Michel
Re: PL/SQL Problem [message #298271 is a reply to message #298268] Tue, 05 February 2008 08:58 Go to previous messageGo to next message
joselfmp
Messages: 23
Registered: August 2007
Location: Madrid
Junior Member
Hi Michel,
no matter that..
it will fail because as you recommend if I return a canonical fixed length string left padding it.. for this case in example if we do with '1500' and '0199'... the result would be the comparation of '1500'<'199' and this will be true...
The idea.. would be "to guess" if the two strings to compare will be numbers inside strings... and if then.. compare then as numbers.. not build a function that return a string....

Regards,
Jose L.
Re: PL/SQL Problem [message #298274 is a reply to message #298271] Tue, 05 February 2008 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
for this case in example if we do with '1500' and '0199'... the result would be the comparation of '1500'<'199' and this will be true...

I don't understand your point.
If the function returns '0001500' and '0000199' then the second is less than the first.

Regards
Michel
Re: PL/SQL Problem [message #298275 is a reply to message #298274] Tue, 05 February 2008 09:08 Go to previous messageGo to next message
joselfmp
Messages: 23
Registered: August 2007
Location: Madrid
Junior Member
It's my fault,
I'll correct the function.. what I did was ltrim the zero's.
Thanks for the correction.
I'll tell the result.

Jose L.
icon5.gif  Re: PL/SQL Problem [message #298277 is a reply to message #298274] Tue, 05 February 2008 09:28 Go to previous messageGo to next message
joselfmp
Messages: 23
Registered: August 2007
Location: Madrid
Junior Member
Hi again
I've tested it... It will work for my example but in general it fails when we have strings to compare like:
'010A' <='010'
This original string comparation would be true...
but if we justify with left padding (i.e. with 10 positions)second value because it's number... we'll have the following
'010A' <='0000000010'
And this is not true.. so leftpadding will not work..
We could extned this to "parts" of a string which contains chars and numbers... but with these in the middle...
So.. more things to try


Regards,
Jose L
Re: PL/SQL Problem [message #298288 is a reply to message #298277] Tue, 05 February 2008 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Modify the function accordingly to your needs.
Expand each number parts or not as you want.
This does not change the principle.
You say you have 8 years in PL/SQL, this should be easy for you.
Otherwise, there are many topics here describing many ways to have an order with mixed character and number strings.

Regards
Michel
Re: PL/SQL Problem [message #298308 is a reply to message #298288] Tue, 05 February 2008 11:34 Go to previous messageGo to next message
joselfmp
Messages: 23
Registered: August 2007
Location: Madrid
Junior Member
Well Michel...
It's not SO easy as you say... I'll give it (on this forum)
If you say it's SO easy.. please post the solution... (if you consider that it's for newbiews in PL/SQL... newbiews would need help and if they don't find the answer after trying a lot.. it would be just for them to post the solution) but.. I don't consider so.. and all those many topics here describing many ways to have an order with mixed character and number strings you say... I don't think that could be apply here for the use of the prior statement...
Anyway.. if you're not post your SO easy solution... please... give it....

About "Modify the function accordingly to your needs.Expand each number parts or not as you want.": Think now about thousands of values to compare... It would be not a great solution in time response terms (anyway I'll try iy.. but i think it will have errors)
Consider this question as newbie... is a bit generous for them... don't you think so?

8 years? yes.. and OCP.. but it's not important..I Just mentioned to clarify that I know the language...

Regards,
Jose
Re: PL/SQL Problem [message #298313 is a reply to message #298308] Tue, 05 February 2008 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
(if you consider that it's for newbiews in PL/SQL... newbiews would need help and if they don't find the answer after trying a lot.. it would be just for them to post the solution)

This is not my policy.
Newbies need to learn, giving solution is not a good way to help them learn, better give them clue and then see what they do with it and go on.
What function did you write with I said?

Quote:
I don't think that could be apply here for the use of the prior statement...

Of course they are not EXACTLY what you want but may be adapt to your case. There is little difference between ORDER BY and < or >, both define an order.

You should investigate REGEXP_REPLACE and correlated functions.

By the way, OCP only measures the ability to learn by heart and the one to write, create or understand something. If I were you I will not mention this, it is depreciating.

Regards
Michel

[Updated on: Tue, 05 February 2008 11:51]

Report message to a moderator

Re: PL/SQL Problem [message #298317 is a reply to message #298313] Tue, 05 February 2008 11:59 Go to previous messageGo to next message
joselfmp
Messages: 23
Registered: August 2007
Location: Madrid
Junior Member
Oh man.. I only said it because you mentioned about the 8 yars.. just for explain don't do a personal question please.

Of course I agree with your policy about how to learn and not give an asnwer to who asks immediately.. But.. after trying what I've tried... If this is considered newbie question.. don't you think it would be suitable to give an answer instead the other..?

Well.. just are opinion.. don't centre the question in other things.

Peace Wink

Jose
Re: PL/SQL Problem [message #298319 is a reply to message #298313] Tue, 05 February 2008 12:02 Go to previous messageGo to next message
joselfmp
Messages: 23
Registered: August 2007
Location: Madrid
Junior Member
REGEXP_REPLACE is for 10g... Didn't I mentioned that I worked with 9.2.0.7? (If so.. It's my fault for not mention it)

Jose L.
Re: PL/SQL Problem [message #298320 is a reply to message #298317] Tue, 05 February 2008 12:03 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I already recentered the post to the question, didn't you see my remark on regexp_replace? This is another way to investigate.

Regards
Michel
Previous Topic: Trigger - bad bind PLS-00049
Next Topic: ORA-04068: existing state of packages has been discarded
Goto Forum:
  


Current Time: Wed Dec 07 10:21:07 CST 2016

Total time taken to generate the page: 0.08169 seconds