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
Post a Comment