I'm trying to write a conversion from PostgreSQL that will work in Oracle.
My problem is that I am struggling to find an Oracle SQL statement that will replace the Postgres @> (contains) operator.
The query in Postgres reads like this:
CASE
WHEN descriptivegroup @> '{Building}' AND descriptiveterm IS NULL THEN 'Building Fill'
WHEN descriptivegroup @> '{"General Surface"}' AND descriptiveterm = '{"Multi Surface"}' THEN 'Multi Surface Fill'
etc., meaning that 'Building' or 'General Surface' (in the above example) should be contained within an array called descriptivegroup.
I've been trawling through Stackoverflow and other websites and I think the nearest equivalent I can find for @> in Oracle is 'member of' as in this example:
How to check if an array contains a particular string?
However, I'm struggling to work out how to work a statement like this into a CASE > WHEN type of statement so any help or suggestions that I could get from this community would be gratefully received!
Many thanks.
PS As promised, here is a truncated version of the Postgres query. This isn't everything as it would be far too big:
CREATE TABLE osmm.topo_area_style AS SELECT
a.*,
CASE
WHEN descriptivegroup @> '{Building}' AND descriptiveterm IS NULL THEN 'Building
Fill'
WHEN descriptivegroup @> '{"General Surface"}' AND descriptiveterm = '{"Multi
Surface"}' THEN 'Multi Surface Fill'
WHEN descriptivegroup @> '{"General Surface"}' AND descriptiveterm IS NULL AND make
= 'Natural' THEN 'Natural Fill'
WHEN descriptivegroup @> '{"Road Or Track"}' AND descriptiveterm IS NULL AND make =
'Manmade' THEN 'Road Or Track Fill'
WHEN descriptivegroup @> '{"General Surface"}' AND descriptiveterm IS NULL AND make
= 'Manmade' OR make = 'Unknown' THEN 'Manmade Fill'
WHEN descriptivegroup @> '{Roadside}' AND make = 'Natural' THEN 'Roadside Natural
Fill'
ELSE 'Unclassified'
END AS style_description,
CASE
WHEN descriptivegroup @> '{Building}' AND descriptiveterm IS NULL THEN 1
WHEN descriptivegroup @> '{"General Surface"}' AND descriptiveterm = '{"Multi
Surface"}' THEN 2
WHEN descriptivegroup @> '{"General Surface"}' AND descriptiveterm IS NULL AND make
= 'Natural' THEN 3
WHEN descriptivegroup @> '{"Road Or Track"}' AND descriptiveterm IS NULL AND make =
'Manmade' THEN 4
WHEN descriptivegroup @> '{"General Surface"}' AND descriptiveterm IS NULL AND make
= 'Manmade' OR make = 'Unknown' THEN 5
WHEN descriptivegroup @> '{Roadside}' AND make = 'Natural' THEN 6
WHEN descriptivegroup @> '{Roadside}' AND make = 'Manmade' OR descriptivegroup @>
'{Roadside}' AND make = 'Unknown' THEN 7
ELSE 99
END AS style_code
FROM osmm.topographicarea as a;
I don't have access to the table that this SQL was written for but as you can see the table being created is a straight copy of the existing table but this code adds another 2 columns which it then populates from the descriptivegroup and descriptiveterm.
create tablestatement for the table in question. It also doesn't seem to be PostGIS related - the@>is a Postgres standard operator for e.g. arrays. I don't see any GIS specific stuff in your query.osmm.topographicarea? Especially: what is the datatype ofdescriptivegroup?@>is an array operator I'm pretty sure the column is defined as an array e.g.text[]orvarchar[]. The closest thing in Oracle to a Postgres array would be a nested table - but that is horribly inconvenient to work with (compare to a Postgres array). Your question has nothing to do with PostGIS