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:
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 ()()
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);
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 ()()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect !! Thank you once more!!
ASKER
Any and all pointers & suggestions are appreciated.