Link to home
Start Free TrialLog in
Avatar of Jblue R
Jblue R

asked on

Concatenations with placeholders

I need to develop a series of concatenations based on values.. some are numbers some are varchar2.

The placeholders are required:
CREATE TABLE tb1
(
  ID              NUMBER(19)                    NOT NULL,
  tank            NUMBER(10),
  roll         NUMBER(10),
  weld_1    VARCHAR2(2 BYTE),
  weld_2    NUMBER(10),
 weld_3    VARCHAR2(5 BYTE),
  weld_4    VARCHAR2(5 BYTE)
);

Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values (1, NULL, NULL, NULL, NULL, NULL, NULL);
 
Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values(2, 1037, NULL, NULL, NULL, NULL, NULL);

Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values(3, 1038, 6, NULL, NULL, NULL, NULL);

Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values(4, 1039, 7, 'Y', NULL, NULL, NULL);

Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values(5, 1040, 8, 'Y', 96, NULL, NULL);

Insert into tb1(ID, tank, roll, weld_1, weld_2, weld_3,weld_4)
 Values(6, 1041, 0, 'Y', 55, '102', NULL);

Open in new window


desired results

 id      concat_field
  1
  2      1037.0()()()()
  3      1038.6()()()()
  4      1039.7(Y)()()()
  5      1040.8(Y)(96)()()
  6      1041.0(Y)(55)(102)()


logic:
When tank is null, the record shows ID and nothing else
 when tank is not null and roll is null record shows tank.0 followed by placeholders ()()()()
 when tank is not null and roll is not null and all others are null  record shows tank.roll followed by placeholders ()()()()
 when tank is not null and roll is not null and weld_1 is not null and all others are null record shows tank.roll(weld_1)()()() the placeholders are required.
 when tank is not null and roll is not null and weld_1 is not null and weld_2 is not null  and all others are null record shows tank.roll(weld_1)(weld_2) followed by placeholders ()()
Avatar of Jblue R
Jblue R

ASKER

I have it halfway accomplished with a stack of case statements, but I feel there is a far more elegant way to get the results I need.
 
 Any and all pointers & suggestions are appreciated.
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jblue R

ASKER

Perfect  !! Thank you once more!!