Multiset problem
Hi,
Please check my query in your environment with IDS 11.70FC4 on Sun
Solaris 10 (on Linux and AIX - no problem)
create temp table tmp_vendor (
vendor_num char(10),
vend_name char(40) ) with no log;
create temp table tmp_global( id integer) with no log;
insert into tmp_global values (1) ;
insert into tmp_vendor values("1","Vendor 1") ;
insert into tmp_vendor values("2","Vendor 2") ;
insert into tmp_vendor values("3","Vendor 3") ;
CREATE TEMP TABLE v_list_t
(
vendor_num CHAR(10),
whse_code CHAR(12),
prod_manager CHAR(8)
) WITH NO LOG;
insert into v_list_t(vendor_num, whse_code) values("1", "0RO");
insert into v_list_t(vendor_num, whse_code) values("2", "060");
insert into v_list_t(vendor_num, whse_code) values("3", "060");
insert into v_list_t(vendor_num, whse_code) values("3", "060");
-- FIRST QUERY
SELECT REPLACE(REPLACE(REPLACE(REPLACE(
multiset(
SELECT TRIM(vendor_num)
||'('
||(SELECT TRIM(vend_name) FROM tmp_vendor WHERE vendor_num =
v_list_t.vendor_num)
||')'
|| NVL(' for ' ||TRIM(whse_code),'')
||' '
FROM v_list_t )::lvarchar
,"MULTISET{",""),"ROW('",""),"')",""),"}","")
FROM tmp_global ;
-- SECOND QUERY
SELECT REPLACE(REPLACE(REPLACE(REPLACE(
multiset(
SELECT UNIQUE TRIM(vendor_num)
||'('
||(SELECT TRIM(vend_name) FROM tmp_vendor WHERE vendor_num =
v_list_t.vendor_num)
||')'
|| NVL(' for ' ||TRIM(whse_code),'')
||' '
FROM v_list_t )::lvarchar
,"MULTISET{",""),"ROW('",""),"')",""),"}","")
FROM tmp_global
First query return not correct result:
(expression) ,,,
correct result:
(expression) 1(Vendor 1) for 0RO ,2(Vendor 2) for 060 ,3(Vendor 3)
for 060 ,3(
Vendor 3) for 060
Second query return error:
9791: User Defined Routine (collectionoutput) execution failed.
Error in line 49Near character position 15
On AIX and Linux - no error and result query is correct.