1

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.

8
  • How is descriptivegroup defined and populated? Is it a nested table, or is this in a PL/SQL block? Might be helpful to show more of the statement, sample data and expected result. Commented Jul 8, 2015 at 11:11
  • 1
    Please show us the create table statement 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. Commented Jul 8, 2015 at 11:18
  • Hi chaps, thanks for replying so quickly. I will edit my original post if I can with some more details. Commented Jul 8, 2015 at 14:56
  • And what is the definition of osmm.topographicarea? Especially: what is the datatype of descriptivegroup? Commented Jul 8, 2015 at 16:06
  • 2
    That is not the definition of that table in Postgres. As the @> is an array operator I'm pretty sure the column is defined as an array e.g. text[] or varchar[]. 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 Commented Jul 9, 2015 at 8:04

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.