On Jul 24, 5:02 pm, jacquesh <jhu..._at_gmail.com> wrote:
> hello,
>
> I need to join 2 tables ; 1 VARCHAR2 column and a node IN XMLTYPE
> COLUMN.
>
> Here my query :
>
> select
> XMLELEMENT(FIELD,
> XMLATTRIBUTES(
> f.LABEL AS "LABEL",
> f.NOM AS "NOM",
> u.code,
> extractValue(VALUE(aclmode1),'XMLNAME/profile_in') AS
> ACLMODE
> )
> ) AS FINAL
> FROM
> utilisateurs u,
> tfields f,
> table(XMLSequence(extract(u.CUSTOM_ACL,'/FIELDS/XMLNAME')))
> aclmode1,
> table(XMLSequence(extract(f.ACL_DEF,'/FIELDS/XMLNAME'))) aclmode2
> WHERE
> extractValue(VALUE(aclmode1),'XMLNAME/@ID') =
> extractValue(VALUE(aclmode2),'XMLNAME/@ID') and
> u.code = '2134357689'
> ;
>
> have you got a new way to rewrite this query ?
>
> thanks !!
How about changing the tables. Extract the value you want ( that you
will be joining the tables on ) and put ONLY those values ( not stored
as XML ) as a column so you can join without the function extractValue
being invoked in the where clause.
Received on Tue Jul 24 2007 - 18:37:03 CDT
