Re: Postgres View DDL
От | Sam Stearns |
---|---|
Тема | Re: Postgres View DDL |
Дата | |
Msg-id | CAN6TVjkTRYMBEq97tbpLsho1dGxtbJX4T_Gvy_dsZx3U82MEmA@mail.gmail.com обсуждение исходный текст |
Ответ на | Postgres View DDL (Sam Stearns <sam.stearns@dat.com>) |
Ответы |
Re: Postgres View DDL
Re: Postgres View DDL |
Список | pgsql-sql |
Tried changing to this:
(coalesce(REGEXP_REPLACE(broker_mc::numeric, '[^0-9]+', '', 'g'), 0)),
(coalesce(REGEXP_REPLACE(carrier_mc::numeric, '[^0-9]+', '', 'g'), 0)),
(coalesce(REGEXP_REPLACE(freight_forwarder_mc::numeric, '[^0-9]+', '', 'g'), 0)),
(coalesce(REGEXP_REPLACE(carrier_mc::numeric, '[^0-9]+', '', 'g'), 0)),
(coalesce(REGEXP_REPLACE(freight_forwarder_mc::numeric, '[^0-9]+', '', 'g'), 0)),
but that throws this error:
ERROR: function regexp_replace(numeric, unknown, unknown, unknown) does not exist
LINE 46: (coalesce(REGEXP_REPLACE(broker_mc::numeric, '[^0-9]+', ...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
LINE 46: (coalesce(REGEXP_REPLACE(broker_mc::numeric, '[^0-9]+', ...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Sam
On Wed, Oct 16, 2024 at 2:33 PM Sam Stearns <sam.stearns@dat.com> wrote:
Howdy,I have an Oracle view that's been converted for Postgres. This block of code in the Oracle view DDL:TO_NUMBER (NVL (REGEXP_REPLACE (broker_mc, '[^0-9]+', ''), 0)),
TO_NUMBER (NVL (REGEXP_REPLACE (carrier_mc, '[^0-9]+', ''), 0)),
TO_NUMBER (NVL (REGEXP_REPLACE (freight_forwarder_mc, '[^0-9]+', ''), 0)),has been converted for Postgres as:(coalesce(REGEXP_REPLACE(broker_mc, '[^0-9]+', '', 'g'), 0))::numeric ,
(coalesce(REGEXP_REPLACE(carrier_mc, '[^0-9]+', '', 'g'), 0))::numeric ,
(coalesce(REGEXP_REPLACE(freight_forwarder_mc, '[^0-9]+', '', 'g'), 0))::numeric ,which is throwing the following error:ERROR: COALESCE types text and integer cannot be matched
LINE 43: ...ce(REGEXP_REPLACE(broker_mc, '[^0-9]+', '', 'g'), 0))::numer...
^I have been looking through:but I'm not seeing a way to resolve it. Would anyone be able to advise how to correct this for Postgres, please?Thanks,Sam--
В списке pgsql-sql по дате отправления: