0

I created a query that doubles the logic - I want to return same data I am validating in condition logic.

What I have now:


select uid, created,
((xpath('/*["a"]//*[name()="b"]/text()', XML_RAW::XML))[1]::text like '%1%'
    and 
    ((xpath('/*["a"]//*[name()="c"]/text()', XML_RAW::XML))[1]::text like '%2%'
        or
     (xpath('/*["a"]//*[name()="d"]/text()', XML_RAW::XML))[1]::text like '%2%'
)) as "AAA",
((xpath('/*["a"]//*[name()="b"]/text()', XML_RAW::XML))[1]::text like '%2%'
    and 
    ((xpath('/*["a"]//*[name()="c"]/text()', XML_RAW::XML))[1]::text like '%1%'
        or
     (xpath('/*["a"]//*[name()="d"]/text()', XML_RAW::XML))[1]::text like '%1%'
)) as "BBB",
((xpath('/*["a"]//*[name()="b"]/text()', XML_RAW::XML))[1]::text like '%1%'
    and 
    ((xpath('/*["a"]//*[name()="c"]/text()', XML_RAW::XML))[1]::text like '%1%'
        or
     (xpath('/*["a"]//*[name()="d"]/text()', XML_RAW::XML))[1]::text like '%1%'
)) as "AAA+BBB"

from db.table
where 

    (
((xpath('/*["a"]//*[name()="b"]/text()', XML_RAW::XML))[1]::text like '%1%'
    and 
    ((xpath('/*["a"]//*[name()="c"]/text()', XML_RAW::XML))[1]::text like '%2%'
        or
     (xpath('/*["a"]//*[name()="d"]/text()', XML_RAW::XML))[1]::text like '%2%'
))
    or 
    ((xpath('/*["a"]//*[name()="b"]/text()', XML_RAW::XML))[1]::text like '%2%'
    and 
    ((xpath('/*["a"]//*[name()="c"]/text()', XML_RAW::XML))[1]::text like '%1%'
        or
     (xpath('/*["a"]//*[name()="d"]/text()', XML_RAW::XML))[1]::text like '%1%'
))
    or 
    ((xpath('/*["a"]//*[name()="b"]/text()', XML_RAW::XML))[1]::text like '%1%'
    and 
    ((xpath('/*["a"]//*[name()="c"]/text()', XML_RAW::XML))[1]::text like '%1%'
        or
     (xpath('/*["a"]//*[name()="d"]/text()', XML_RAW::XML))[1]::text like '%1%'
))
)
    

As you can see, each possibility has multiple conditions and the information which possibility is fullfilled is returned. I was thinking of maybe having in the where clause something like and ("AAA" or "BBB" or "AAA+BBB") but I've seen it is impossible here. Could I maybe save as some kind of variable the xpath evalutaions? Having (xpath('/*["a"]//*[name()="b"]/text()', XML_RAW::XML))[1]::text stored so it is not repeated and the script doesnt have to find it twice would be nice.

I've seen that the subqueries could be used, but that would be even worse performance-wise than the repeated Xpath as I can imagine.

EDIT:

expected result

The Xpath checks whether in each XML in specific place there is an element that matches either "1" or "2" (true/false basically). There are 3 data sources, for simplity name()="b","c", and "d" - each can be true or false. So to simplify, the logic is like that:

Scenario 1: a: true AND (b: false OR c: false)

Scenario 2: a: false AND (b: true OR c: true)

Scenario 3: a: true AND (b: true OR c: true)

(scenarios are not up to me).

In the where clause I want to check every case, but return which case has been used - true in the end result column "A","C" or "C".

mintas123
  • 131
  • 14
  • 1
    Cześć. Could you perhaps provide a small data sample and the exact expected result? It will significantly improve your chances to get a decent answer ;-) cheers! – Jim Jones Mar 01 '21 at 14:35
  • @JimJones Thanks for the feedback! Can't show much of the data sample, but i've tried to tell more info about expected result :) – mintas123 Mar 01 '21 at 15:08

0 Answers0