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".

2
  • 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! Commented Mar 1, 2021 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 :) Commented Mar 1, 2021 at 15:08

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.