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