OraFAQ Forum: SQL & PL/SQL » sql replacement price

Home » SQL & PL/SQL » SQL & PL/SQL » sql replacement price (11gr2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sql replacement price [message #640784]

Wed, 05 August 2015 02:52

hudo
Messages: 165
Registered: May 2004

Senior Member

Hello,

the table UNITS contains articles. Each article belongs to a group and can be replaced by another article from this group. The group is indicated by position 7 of the UNITS_NAME.
The table REPTYPE is used to describe the mapping of the replacement.

If an article TEXT1-3AAA is selected and this article has e.g. no price UNITS_P0, the highest/lowest price UNITS_P0 from articles of this group should be selected, highest UNITS_P0 is here 1040 from TEXT1-4AAA.

CREATE TABLE units
  (
     units_id   NUMBER,
     units_name VARCHAR2(20),
     units_p0   NUMBER,
     units_p1   NUMBER,
     units_p2   NUMBER
  );

ALTER TABLE units
  ADD ( CONSTRAINT units_pk PRIMARY KEY (units_id), CONSTRAINT units_name_uk
  UNIQUE (units_name) );

--DROP TABLE UNITS;
INSERT INTO units
            (units_id,
             units_name,
             units_p0,
             units_p1,
             units_p2)
VALUES      (1,
             'TEXT1-1AAA',
             1010,
             810,
             610 );

INSERT INTO units
            (units_id,
             units_name,
             units_p0,
             units_p1,
             units_p2)
VALUES      (2,
             'TEXT1-2AAA',
             NULL,
             820,
             620 );

INSERT INTO units
            (units_id,
             units_name,
             units_p0,
             units_p1,
             units_p2)
VALUES      (3,
             'TEXT1-3AAA',
             NULL,
             NULL,
             NULL );

INSERT INTO units
            (units_id,
             units_name,
             units_p0,
             units_p1,
             units_p2)
VALUES      (4,
             'TEXT1-4AAA',
             1040,
             840,
             640 );

INSERT INTO units
            (units_id,
             units_name,
             units_p0,
             units_p1,
             units_p2)
VALUES      (5,
             'TEXT2-1BBB',
             2010,
             910,
             710 );

INSERT INTO units
            (units_id,
             units_name,
             units_p0,
             units_p1,
             units_p2)
VALUES      (6,
             'TEXT2-2BBB',
             2020,
             920,
             720 );

INSERT INTO units
            (units_id,
             units_name,
             units_p0,
             units_p1,
             units_p2)
VALUES      (7,
             'TEXT2-3BBB',
             2030,
             930,
             730 );

INSERT INTO units
            (units_id,
             units_name,
             units_p0,
             units_p1,
             units_p2)
VALUES      (8,
             'TEXT2-4BBB',
             2040,
             940,
             740 );

INSERT INTO units
            (units_id,
             units_name,
             units_p0,
             units_p1,
             units_p2)
VALUES      (9,
             'TEXT3-3CCC',
             3030,
             1030,
             830 );

INSERT INTO units
            (units_id,
             units_name,
             units_p0,
             units_p1,
             units_p2)
VALUES      (10,
             'TEXT3-4CCC',
             NULL,
             1040,
             NULL );

/

CREATE TABLE reptype
  (
     reptype_id     NUMBER,
     reptpype_val   NUMBER,
     reptype_detail VARCHAR2(30)
  );

ALTER TABLE reptype
  ADD ( CONSTRAINT reptype_pk PRIMARY KEY (reptype_id), CONSTRAINT
  reptpype_val_uk UNIQUE (reptpype_val) );

--DROP TABLE REPTYPE;
INSERT INTO reptype
            (reptype_id,
             reptpype_val,
             reptype_detail)
VALUES      ( 1,
             1,
             'light');

INSERT INTO reptype
            (reptype_id,
             reptpype_val,
             reptype_detail)
VALUES      ( 2,
             2,
             'medium');

INSERT INTO reptype
            (reptype_id,
             reptpype_val,
             reptype_detail)
VALUES      ( 3,
             3,
             'hard');

INSERT INTO reptype
            (reptype_id,
             reptpype_val,
             reptype_detail)
VALUES      ( 4,
             4,
             'under water');

/

Thx for any hints

Report message to a moderator

Re: sql replacement price [message #640788 is a reply to message #640784]

Wed, 05 August 2015 03:06

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Explain:
"Each article belongs to a group and can be replaced by another article from this group. The group is indicated by position 7 of the UNITS_NAME. "
"If an article TEXT1-3AAA is selected ... highest UNITS_P0 is here 1040 from TEXT1-4AAA."

For me, position 7 is 3 for TEXT1-3AAA and 4 for TEXT1-4AAA, so they do not belong to the same group, so how the second sentence?

Be the way, a column should not contain several information, you should have a GROUP_ID column in your table.

Report message to a moderator

Re: sql replacement price [message #640792 is a reply to message #640788]

Wed, 05 August 2015 03:29

hudo
Messages: 165
Registered: May 2004

Senior Member

Hello Michel,

if e.g. TEXT3-2AAA is selected I use the following query to get the other members of this group:


SELECT Substr('TEXT3-2AAA', 1, 6)
       ||reptpype_val
       || Substr('TEXT3-2AAA', 8, 3) AS "REPL",
       reptype_detail
FROM   reptype
WHERE  reptpype_val != ( Substr('TEXT3-2AAA', 7, 1) )

If UNITS_P0 from TEXT3-2AAA is NULL, then take the highest UNITS_P0 from the othter members of the group.

Report message to a moderator

Re: sql replacement price [message #640797 is a reply to message #640792]

Wed, 05 August 2015 04:23

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I use the following query to get the other members of this group:

So the other members of the group are all those that differ from only the 7th character.
Correct?
This is the opposite to what you said "...another article from this group. The group is indicated by position 7 of the UNITS_NAME. " which means the other members of the group have the same 7th character (whatever are the other characters).

Report message to a moderator

Re: sql replacement price [message #640798 is a reply to message #640797]

Wed, 05 August 2015 04:39

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Is this what you expect?

SQL> select units_id, units_name, units_p0 old_units_p0,
  2         nvl(units_p0,
  3             (select max(units_p0) from units b
  4              where units_name in
  5                      (select substr(a.units_name,1,6)||reptpype_val||Substr(a.units_name,8,3)
  6                       from reptype
  7                       where reptpype_val != substr(a.units_name,7,1))))
  8           new_units_p0
  9  from units a
 10  order by 1
 11  /
  UNITS_ID UNITS_NAME           OLD_UNITS_P0 NEW_UNITS_P0
---------- -------------------- ------------ ------------
         1 TEXT1-1AAA                   1010         1010
         2 TEXT1-2AAA                                1040
         3 TEXT1-3AAA                                1040
         4 TEXT1-4AAA                   1040         1040
         5 TEXT2-1BBB                   2010         2010
         6 TEXT2-2BBB                   2020         2020
         7 TEXT2-3BBB                   2030         2030
         8 TEXT2-4BBB                   2040         2040
         9 TEXT3-3CCC                   3030         3030
        10 TEXT3-4CCC                                3030

Report message to a moderator

Re: sql replacement price [message #640803 is a reply to message #640798]

Wed, 05 August 2015 06:24

hudo
Messages: 165
Registered: May 2004

Senior Member

Yes, this is what I expected. I struggled with the syntax of NVL in combination with the subselect.

Thank you for your effort and sorry for the misunderstanding regarding the groups.

Report message to a moderator

Previous Topic:	only show the duplicated records
Next Topic:	update century in timestamp column in table (topics merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 14:48:08 CDT 2024