postgresql - Oracle equivalent to Postgres' @> (contains) function -


i'm trying write conversion postgresql work in oracle. problem struggling find oracle sql statement replace postgres @> (contains) operator.

the query in postgres reads this:

case         when descriptivegroup @> '{building}' , descriptiveterm null 'building fill'     when descriptivegroup @> '{"general surface"}' , descriptiveterm = '{"multi surface"}' 'multi surface fill' 

etc., meaning 'building' or 'general surface' (in above example) should contained within array called descriptivegroup.

i've been trawling through stackoverflow , other websites , think nearest equivalent can find @> in oracle 'member of' in example:

how check if array contains particular string?

however, i'm struggling work out how work statement case > when type of statement or suggestions community gratefully received!

many thanks.

ps promised, here truncated version of postgres query. isn't far big:

create table osmm.topo_area_style select a.*, case         when descriptivegroup @> '{building}' , descriptiveterm null 'building   fill'     when descriptivegroup @> '{"general surface"}' , descriptiveterm = '{"multi   surface"}' 'multi surface fill'     when descriptivegroup @> '{"general surface"}' , descriptiveterm null , make   = 'natural' 'natural fill'     when descriptivegroup @> '{"road or track"}' , descriptiveterm null , make =   'manmade' 'road or track fill'     when descriptivegroup @> '{"general surface"}' , descriptiveterm null , make   = 'manmade' or make = 'unknown' 'manmade fill'     when descriptivegroup @> '{roadside}' , make = 'natural' 'roadside natural   fill'     else 'unclassified'      end style_description, case     when descriptivegroup @> '{building}' , descriptiveterm null 1     when descriptivegroup @> '{"general surface"}' , descriptiveterm = '{"multi   surface"}' 2     when descriptivegroup @> '{"general surface"}' , descriptiveterm null , make   = 'natural' 3     when descriptivegroup @> '{"road or track"}' , descriptiveterm null , make =   'manmade' 4     when descriptivegroup @> '{"general surface"}' , descriptiveterm null , make   = 'manmade' or make = 'unknown' 5     when descriptivegroup @> '{roadside}' , make = 'natural' 6     when descriptivegroup @> '{roadside}' , make = 'manmade' or descriptivegroup @>   '{roadside}' , make = 'unknown' 7     else 99      end style_code osmm.topographicarea a; 

i don't have access table sql written can see table being created straight copy of existing table code adds 2 columns populates descriptivegroup , descriptiveterm.


Comments

Popular posts from this blog

toolbar - How to add link to user registration inside toobar in admin joomla 3 custom component -

linux - disk space limitation when creating war file -