Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » APEX 5 tabular Form, UNION AND ORA-01446
APEX 5 tabular Form, UNION AND ORA-01446 [message #656803] Wed, 19 October 2016 10:17 Go to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
Hi,

Currently i migrate from APEX4 to APEX5.
In my tabular form i have query with UNION like
SELECT "UNION_PRODUCT"."PRODUCT_UID"
FROM (select
"LO_TARIFF"."PRODUCT_UID"
from "LO_TARIFF"
where "LO_TARIFF"."TARIFF_TYPE" = 'PDT'
UNION
select
"LO_TARIFF"."PRODUCT_UID"
from "LO_TARIFF"
where "LO_TARIFF"."TARIFF_TYPE" = 'PKG'
 )
UNION_PRODUCT
That give me ORA error ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.

When i remove union error disappear ??
My Query is running in SQLDevelopper and in APEX 4.0.2 why not in APEX 5.0.4 ?
Why ? Where is my mistake

Regards
Phil

[Updated on: Wed, 19 October 2016 10:27]

Report message to a moderator

Re: APEX 5 tabular Form, UNION AND ORA-01446 [message #656813 is a reply to message #656803] Wed, 19 October 2016 14:54 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This looks like a badly written query. Convert it to
select product_uid
from lo_tariff
where tariff_type in ('PDT', 'PKG')

Report message to a moderator

Re: APEX 5 tabular Form, UNION AND ORA-01446 [message #656817 is a reply to message #656803] Wed, 19 October 2016 23:32 Go to previous messageGo to next message
pcouas
Messages: 112
Registered: February 2016
Senior Member
Hi,

Reel full Query is

SELECT "UNION_PRODUCT"."TARIFF_UID",
"UNION_PRODUCT"."TARIFF_UID" TARIFF_UID_DISPLAY,
"UNION_PRODUCT"."PRODUCT_UID",
"UNION_PRODUCT"."LISTE_PRODUCT_UID",
"UNION_PRODUCT"."PACKAGE_UID",
"UNION_PRODUCT"."SUPPLIER_UID",
"UNION_PRODUCT"."PORT_UID",
"UNION_PRODUCT"."CREA_USER",
"UNION_PRODUCT"."CREA_DATE",
"UNION_PRODUCT"."MODIF_USER",
"UNION_PRODUCT"."MODIF_DATE",
"UNION_PRODUCT"."VALID_DATE_FROM",
"UNION_PRODUCT"."VALID_DATE_TO",
"UNION_PRODUCT"."UNIT_PRICE",
"UNION_PRODUCT"."TARIFF_TYPE"
FROM (select
"LO_TARIFF"."TARIFF_UID",
"LO_TARIFF"."TARIFF_UID" TARIFF_UID_DISPLAY,
"LO_TARIFF"."PRODUCT_UID",
DP_LO_APEX_QUERY.F_GEN_HTML_PRODUCT_LIST(NULL, :P23_SUPPLIER_UID , "LO_TARIFF"."PRODUCT_UID", "LO_PRODUCT".PRODUCT_DESC, '#ROWNUM#', '', '','f03_', 'PRODUCT_UID', 'N','orange','Y','2', :P23_OIL_GROUP) LISTE_PRODUCT_UID,
"LO_TARIFF"."PACKAGE_UID",
"LO_TARIFF"."SUPPLIER_UID",
"LO_TARIFF"."PORT_UID",
"LO_TARIFF"."CREA_USER",
"LO_TARIFF"."CREA_DATE",
"LO_TARIFF"."MODIF_USER",
"LO_TARIFF"."MODIF_DATE",
"LO_TARIFF"."VALID_DATE_FROM",
"LO_TARIFF"."VALID_DATE_TO",
"LO_TARIFF"."UNIT_PRICE",
"LO_TARIFF"."TARIFF_TYPE"
from "LO_TARIFF",
"LO_PRODUCT"
where "LO_TARIFF"."SUPPLIER_UID" = :P23_SUPPLIER_UID
AND ((:P23_DATE_FROM IS NULL)
     OR ("LO_TARIFF"."VALID_DATE_FROM" >= :P23_DATE_FROM))
AND ((:P23_DATE_TO IS NULL)
     OR ("LO_TARIFF"."VALID_DATE_TO" <= :P23_DATE_TO))
and ((:P23_PORT_UID is null)
     or ("LO_TARIFF"."PORT_UID" = :P23_PORT_UID))
and "LO_TARIFF"."TARIFF_TYPE" = 'PDT'
and "LO_TARIFF"."PRODUCT_UID" = "LO_PRODUCT"."PRODUCT_UID"
and DP_LO_APEX_QUERY.ischecked(:P23_OIL_GROUP, "LO_PRODUCT"."OIL_GROUP_UID") = 'Y'
and (:P23_PRODUCT_UID is null or "LO_TARIFF"."PRODUCT_UID" = :P23_PRODUCT_UID)
UNION ALL
select
"LO_TARIFF"."TARIFF_UID",
"LO_TARIFF"."TARIFF_UID" TARIFF_UID_DISPLAY,
"LO_TARIFF"."PRODUCT_UID",
null LISTE_PRODUCT_UID,
"LO_TARIFF"."PACKAGE_UID",
"LO_TARIFF"."SUPPLIER_UID",
"LO_TARIFF"."PORT_UID",
"LO_TARIFF"."CREA_USER",
"LO_TARIFF"."CREA_DATE",
"LO_TARIFF"."MODIF_USER",
"LO_TARIFF"."MODIF_DATE",
"LO_TARIFF"."VALID_DATE_FROM",
"LO_TARIFF"."VALID_DATE_TO",
"LO_TARIFF"."UNIT_PRICE",
"LO_TARIFF"."TARIFF_TYPE"
from "LO_TARIFF"
where "LO_TARIFF"."SUPPLIER_UID" = :P23_SUPPLIER_UID
AND ((:P23_DATE_FROM IS NULL)
     OR ("LO_TARIFF"."VALID_DATE_FROM" >= :P23_DATE_FROM))
AND ((:P23_DATE_TO IS NULL)
     OR ("LO_TARIFF"."VALID_DATE_TO" <= :P23_DATE_TO))
and "LO_TARIFF"."TARIFF_TYPE" = 'PKG'
and (:P23_PACKAGE_UID is null or "LO_TARIFF"."PACKAGE_UID" = :P23_PACKAGE_UID) )
UNION_PRODUCT
WHERE UNION_PRODUCT.TARIFF_TYPE = :P23_TARIFF_TYPE
One UNION as LO_PRODUCT table and the other part not !

regards

Report message to a moderator

Re: APEX 5 tabular Form, UNION AND ORA-01446 [message #656823 is a reply to message #656817] Thu, 20 October 2016 01:35 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Open the same page in Apex 4 and Apex 5. Compare validations; do they exist in both versions? From my own experience, validations might cause ORA-01446 in a tabular form. So, if you can afford it, try to remove them and run the page (on Apex 5) again. Any improvement?

Report message to a moderator

Re: APEX 5 tabular Form, UNION AND ORA-01446 [message #656835 is a reply to message #656803] Thu, 20 October 2016 04:52 Go to previous message
pcouas
Messages: 112
Registered: February 2016
Senior Member
Yes there is no problem when i remove ALL validations
But when i add from APEX5 an validation Problem Come Back !!

My validation type is Column is not null on submit button and in this case there is no result in tabular Form

[Updated on: Thu, 20 October 2016 07:54]

Report message to a moderator

Previous Topic: Apex 5 Master Detail Form
Next Topic: APEX5 avoiding HTML special caracter replacement
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Dec 04 13:12:23 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.