Can I merge two column value from one table into one inside a for loop and inside the pl/sql funcition

[Mohammedtodd]

Hi Greetings to All,

I’m new to Pl/SQL.

It’s a description about a part so it really need to be fetched such a long part description. . Actually how we store this description into the above table is we split this into two separate row and store in two different rows. My task is this need to fetched into a single column while processing in the above function.

For example if the part has a description of length more 70 characters then we will store the first 65 characters into the first row with the sequence as “1” and the rest of characters will be stored into the second with the sequence of 2.What I try to accomplish is to combine both description row into one and send to the row variable.

The way I tried to get this done as below using a inline view facility.

Try 1:

(select ext1.description_text from extended_part_descriptions ext1

where ext1.part_number = ‘1’) long_desc

then I tried to fetch the description once the lopping is performed as below.

LOOP

IF part_history.long_part_number IS NULL THEN

RAISE partmaster_block;

END IF;

pmrow.part_number := part_history.long_part_number;

–pmrow.part_description := get_long_part_desc(part_history.long_part_number);

pmrow.part_description := part_history.long_desc;

–‘PARTEST1’;

–part_history.long_desc;

pmrow.part_type := part_history.part_type;

But got the error

• Error(71,9): PL/SQL: Statement ignored
• Error(71,48): PLS-00302: component ‘LONG_DESC’ must be declared .

To cut the long store into short. Two rows of a table of similar type to be joined to gather into one where I’m struck into.

To be specific If I have the table data

Table _ Extended_Descriptions.

PART DESCRIPTION_SEQUENCE DESCRIPTIONS

K200 1 CHECKOUTTHEFLAGBEFOREYOUPROCEED

K200 2 WELLTESGINGALWAYSMAKESPERFECTION

We need to join as

K200 CHECKOUTTHEFLAGBEFOREYOUPROCEEDWELLTESGINGALWAYSMAKESPERFECTION****

MY FUNCTION IS BELOW. sorry for the commented line and they are the tries I gave.

FUNCTION get_part_amendment_history(
in_start_date DATE,
in_end_date DATE
) RETURN ie92_tab pipelined
AS
pmrow ie92_row := ie92_row();
start_date DATE;
end_date date;
desc1 varchar2(65);
desc2 varchar2(65);
long_desc varchar2(160);

partmaster_block exception;
BEGIN

start_date := nvl (in_start_date, trunc (CURRENT_DATE) - 1);
end_date := nvl (in_end_date, trunc (CURRENT_DATE));

for part_history in (

select distinct pm.long_part_number,
–ext1.DESCRIPTION_TEXT as long_desc,
–ex.description_text long_desc,
– ( SELECT description_text FROM
– extended_part_descriptions epd WHERE
– epd.description_sequence =1
– AND epd.description_sequence =2) d,

CASE pm.part_type
WHEN ‘0’ THEN ‘C’
ELSE ‘R’
END part_type,
pm.stock_group,
pm.unit_of_issue,
CASE pm.withdrawn_part
WHEN ‘Y’ THEN ‘N’
ELSE ‘Y’
end withdrawn_part
from part_master pm

left join part_number_amendment_history pnah
on pm.part_number = pnah.part_number

• join extended_part_descriptions ext1
  – on pm.part_number = ext1.part_number and
  – ext1.description_sequence = 1 and
  – ext1.description_sequence = 2
  – (select ext1.description_text from extended_part_descriptions ext1
  – where ext1.part_number = ‘1’) long_desc
  – left join extended_part_descriptions ex
  – on pm.part_number = ex.part_number
  –
  – where ex.description_sequence =1 and
  – --=1 and ex.description_sequence =2
  – LEFT JOIN extended_part_descriptions ex
  – on pm.part_number = ex.part_number
  –where
  – ( ext1.description_sequence =1 and
  –
  – ext1.description_sequence =2)
  where
  pnah.history_date >= trunc (start_date)
  AND pnah.history_date < trunc (end_date)
  AND pnah.vduwka_field_name IN
  (‘STMI02’,
  ‘STMI04’,
  ‘STMI08’,
  ‘STMI17’,
  ‘STMI05’))
  LOOP
  IF part_history.long_part_number IS NULL THEN
  RAISE partmaster_block;
  end if;
  – select description_text into desc1 from extended_part_descriptions ext where ext.part_number = part_history.long_part_number and
  – ext.description_sequence =1 ;
  – select description_text into desc2 from extended_part_descriptions ext1 where ext1.part_number = part_history.long_part_number and
  – ext1.description_sequence =2 ;
  –
  pmrow.part_number := part_history.long_part_number;
  –pmrow.part_description := get_long_part_desc(part_history.long_part_number);
  –pmrow.part_description := desc1 || desc2;
  –‘PARTEST1’;
  –part_history.long_desc;
  pmrow.part_type := part_history.part_type;
  pmrow.stock_group := part_history.stock_group;
  pmrow.uom := part_history.unit_of_issue;
  pmrow.block_status := part_history.withdrawn_part;
  – select description_text into desc1 from extended_part_descriptions ext where ext.part_number = pmrow.part_number and
  – ext.description_sequence =1 ;
  – select description_text into desc2 from extended_part_descriptions ext1 where ext1.part_number = pmrow.part_number and
  – ext1.description_sequence =2 ;

PIPE ROW(pmrow);
END loop;
RETURN;
EXCEPTION
WHEN partmaster_block THEN
pmrow.part_number := NULL;
pmrow.part_description := NULL;
pmrow.part_type := NULL;
pmrow.stock_group := NULL;
pmrow.uom := NULL;
pmrow.block_status := NULL;

END;

Thanks

[NormTeamT]

Evening Mohammedtodd,
hopefully, the following will point you in the right direction.
Assuming I understand your description correctly, you have data in two tables that make up the description of a “part”.
Question: Is there exactly two rows for every description of a part? Or can there be more than two rows, for an exceedingly long description?
If there are two rows, one in each table, I would probably:
SELECT desc_1, desc_2 – Plus whatever else I wanted from each table
INTO l_desc_1, l_desc_2
FROM table_1, table_2
WHERE table_1.part = ‘K200’
and table_1.description_sequence = 1
and table_2.part = table_1.part
and table_2.description_sequence = 2;
Then join the two together:
l_full_desc := l_desc_1 || l_desc_2;
Then, after all that, the variable l_full_desc is the whole description made up of the two parts.
l_desc_1 is defined as table_1.desc_1%type, l_desc_2 is defined as table_2.desc_2%type. This anchors the two local variables to be the same as the columns which they are “fed” from, but leaves us wondering what to define l_full_desc as?
It should be long enough to hold both parts of the description, and hopefully, long enough for any future changes to the two tables. So, it can be defined as a VARCHAR2(1000) - it will only ever take up the space it needs to hold the two “halves” of the full description. (130 characters in your example).
HTH
And by the way, the best source of information on PL/SQL has to be Steven Feuerstein & Bill Pribyl’s “Oracle PL/SQL Programming” book. It’s in the 6th edition now, and is well worth investing in if you are intending to do any PL/SQL work.
The Oracle docs too are quite good, but Steven is the best!
Cheers,
Norm. [TeamT]

[Mohammedtodd]

Hi TeamT,

Many thanks to you for coming forward to help me. Your suggestion was helpful to me. But after some practice I found one style of answer for this on the day the thread was raised. Right now I’m reading and learning through the book PL/SQL For Dummies by Michael Roseenblum & Dr. Paul Dorsey. As most of the forum member suggest the author “Steve” I will try to purchase this book and read through for learning completely. Many thanks to you

CASE nvl((SELECT ext.description_text

FROM extended_part_descriptions ext

WHERE pm.part_number = ext.part_number

AND ext.description_sequence = 1),‘chil’)

WHEN ‘chil’ THEN pm.description

ELSE CASE nvl((SELECT ext2.description_text

FROM extended_part_descriptions ext2

WHERE pm.part_number = ext2.part_number

AND ext2.description_sequence = 2),‘anna’)

WHEN ‘chil’ THEN (SELECT ext.description_text

FROM extended_part_descriptions ext

WHERE pm.part_number = ext.part_number

AND ext.description_sequence = 1)

ELSE (SELECT ext.description_text

FROM extended_part_descriptions ext

WHERE pm.part_number = ext.part_number

AND ext.description_sequence = 1) || chr(10) || (SELECT ext2.description_text

FROM extended_part_descriptions ext2

WHERE pm.part_number = ext2.part_number

AND ext2.description_sequence = 2)