MIME-Version: 1.0
From: Sam Stearns <sam.stearns@dat.com>
Date: Tue, 15 Oct 2024 14:30:55 -0700
Message-ID: 
 <CAN6TVjmdUGuFrCn4HcUd4Q2uvNXZSEe_4TXG7neQip_m6dGiBQ@mail.gmail.com>
Subject: Oracle ==> Postgres View
To: pgsql-sql@lists.postgresql.org
Content-Type: multipart/mixed; boundary="000000000000d022f706248aaba4"
Archived-At: 
 <https://www.postgresql.org/message-id/CAN6TVjmdUGuFrCn4HcUd4Q2uvNXZSEe_4TXG7neQip_m6dGiBQ%40mail.gmail.com>
Precedence: bulk

--000000000000d022f706248aaba4
Content-Type: multipart/alternative; boundary="000000000000d022f606248aaba2"

--000000000000d022f606248aaba2
Content-Type: text/plain; charset="UTF-8"

Howdy,

I have 2 views attached.  An Oracle view written with NVL.  The same view
was converted to Postgres using COALESCE.  Postgres is throwing an error:

ERROR:  syntax error at or near ","
LINE 12: ...esce(CASE WHEN impact_category='BULK_RATE_REQUEST', 1, -- To...

The problem block of code:

    coalesce(CASE WHEN impact_category='BULK_RATE_REQUEST', 1, -- To handle
Portal's category for the old RIP
       'CONTRACT_BULK_RATE', 1,
       'SPOT_BULK_RATE', 2,
       'CONTRACT_HISTORY', 3,
       'SPOT_HISTORY', 4,
       'RATE_SUBMISSION', 5,
       'SPOT_BACKHAUL' THEN  6 END , 0),

Would anyone be able to advise how to correct this for Postgres, please?

Thanks,

Sam

                                           ^

-- 

*Samuel Stearns*
Lead Database Administrator
*c:* 971 762 6879 | *o:* 503 672 5115 | DAT.com
[image: DAT]
<https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link>

--000000000000d022f606248aaba2
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Howdy,<div><br></div><div>I have 2 views attached.=C2=A0 A=
n Oracle view written with NVL.=C2=A0 The same view was converted to Postgr=
es=C2=A0using COALESCE.=C2=A0 Postgres is throwing an error:</div><div><br>=
</div><div>ERROR: =C2=A0syntax error at or near &quot;,&quot;<br>LINE 12: .=
..esce(CASE WHEN impact_category=3D&#39;BULK_RATE_REQUEST&#39;, 1, -- To...=
</div><div><br></div><div>The problem block of code:</div><div><br></div><d=
iv>=C2=A0 =C2=A0 coalesce(CASE WHEN impact_category=3D&#39;BULK_RATE_REQUES=
T&#39;, 1, -- To handle Portal&#39;s category for the old RIP<br>=C2=A0 =C2=
=A0 =C2=A0 =C2=A0&#39;CONTRACT_BULK_RATE&#39;, 1,<br>=C2=A0 =C2=A0 =C2=A0 =
=C2=A0&#39;SPOT_BULK_RATE&#39;, 2,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0&#39;CONTR=
ACT_HISTORY&#39;, 3,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0&#39;SPOT_HISTORY&#39;, =
4,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0&#39;RATE_SUBMISSION&#39;, 5,<br>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0&#39;SPOT_BACKHAUL&#39; THEN =C2=A06 END , 0),</div><di=
v><br></div><div>Would anyone be able to advise how to correct this for Pos=
tgres, please?</div><div><br></div><div>Thanks,</div><div><br></div><div>Sa=
m<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^<br><div><br></div>=
<span class=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=
=3D"gmail_signature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr"><p=
 style=3D"color:rgb(0,0,0);font-family:Gotham,&quot;Helvetica Neue&quot;,He=
lvetica,Arial,sans-serif;font-size:0.8rem;line-height:1.2rem"><strong style=
=3D"font-size:0.9rem;color:rgb(51,51,51)">Samuel Stearns</strong><br><span =
style=3D"color:rgb(102,102,102)">Lead Database Administrator<br><strong>c:<=
/strong>=C2=A0971 762 6879=C2=A0<span style=3D"color:rgb(147,149,152)">|</s=
pan>=C2=A0<strong>o:</strong>=C2=A0503 672 5115=C2=A0<span style=3D"color:r=
gb(147,149,152)">|</span>=C2=A0DAT.com</span></p><div style=3D"color:rgb(0,=
0,0);font-family:&quot;Times New Roman&quot;;font-size:medium"><a href=3D"h=
ttps://www.dat.com/?utm_medium=3Demail&amp;utm_source=3DDAT_email_signature=
_link" target=3D"_blank"><img src=3D"https://www.dat.com/wp-content/uploads=
/media/images/global/emails/signature2/dat_126.png" alt=3D"DAT" width=3D"12=
6" height=3D"54"></a></div></div></div></div></div>

--000000000000d022f606248aaba2--

--000000000000d022f706248aaba4
Content-Type: text/plain; charset="US-ASCII"; name="oracle_view.txt"
Content-Disposition: attachment; filename="oracle_view.txt"
Content-Transfer-Encoding: base64
Content-ID: <f_m2ay9l0w0>
X-Attachment-Id: f_m2ay9l0w0

Q1JFQVRFIE9SIFJFUExBQ0UgRk9SQ0UgVklFVyBDU0JURlNQUkQuQklMTEVEX1JBVEVfVFJBTlNB
Q1RJT05TDQooDQogICAgQUNDT1VOVF9JRCwNCiAgICBDT01QQU5ZX0lELA0KICAgIEFVVEhFTlRJ
Q0FUSU5HX09SREVSX0lELA0KICAgIEFVVEhFTlRJQ0FUSU5HX1BST0RVQ1RfSUQsDQogICAgQVVU
SEVOVElDQVRJTkdfUFJJQ0VfQ0xBU1MsDQogICAgQVVUSE9SSVpJTkdfT1JERVJfSUQsDQogICAg
QVVUSE9SSVpJTkdfUFJPRFVDVF9JRCwNCiAgICBBVVRIT1JJWklOR19QUklDRV9DTEFTUywNCiAg
ICBTVEFSVF9EQVRFLA0KICAgIEVORF9EQVRFLA0KICAgIFRZUEVfQ09ERSwNCiAgICBQUklDRSwN
CiAgICBFUVVJUE1FTlQsDQogICAgTEFORV9DT1VOVCwNCiAgICBMQU5FX0VTVElNQVRFLA0KICAg
IFRSQU5TQUNUSU9OX0lELA0KICAgIFNVQk1JU1NJT05fSUQNCikNCkJFUVVFQVRIIERFRklORVIN
CkFTDQogICAgU0VMRUNUIGFjY291bnRfaWQsDQogICAgICAgICAgIGNvbXBhbnlfaWQsDQogICAg
ICAgICAgIG9yZGVyX2lkLA0KICAgICAgICAgICBwcm9kdWN0X2lkLA0KICAgICAgICAgICBwcmlj
ZV9jbGFzcywNCiAgICAgICAgICAgYXV0aG9yaXppbmdfb3JkZXJfaWQsDQogICAgICAgICAgIGF1
dGhvcml6aW5nX3N1YnNjcmlwdGlvbl90eXBlLA0KICAgICAgICAgICBhdXRob3JpemluZ19wcmlj
ZV9jbGFzcywNCiAgICAgICAgICAgc3RhcnRfZGF0ZSwNCiAgICAgICAgICAgZW5kX2RhdGUsDQog
ICAgICAgICAgIE5WTCAoDQogICAgICAgICAgICAgICBERUNPREUgKGltcGFjdF9jYXRlZ29yeSwN
CiAgICAgICAgICAgICAgICAgICAgICAgJ0JVTEtfUkFURV9SRVFVRVNUJywgMSwgLS0gVG8gaGFu
ZGxlIFBvcnRhbCdzIGNhdGVnb3J5IGZvciB0aGUgb2xkIFJJUA0KICAgICAgICAgICAgICAgICAg
ICAgICAnQ09OVFJBQ1RfQlVMS19SQVRFJywgMSwNCiAgICAgICAgICAgICAgICAgICAgICAgJ1NQ
T1RfQlVMS19SQVRFJywgMiwNCiAgICAgICAgICAgICAgICAgICAgICAgJ0NPTlRSQUNUX0hJU1RP
UlknLCAzLA0KICAgICAgICAgICAgICAgICAgICAgICAnU1BPVF9ISVNUT1JZJywgNCwNCiAgICAg
ICAgICAgICAgICAgICAgICAgJ1JBVEVfU1VCTUlTU0lPTicsIDUsDQogICAgICAgICAgICAgICAg
ICAgICAgICdTUE9UX0JBQ0tIQVVMJywgNiksDQogICAgICAgICAgICAgICAwKSwNCiAgICAgICAg
ICAgcHJpY2UsDQogICAgICAgICAgIGVxdWlwbWVudCwNCiAgICAgICAgICAgbGFuZV9jb3VudCwN
CiAgICAgICAgICAgbGFuZV9lc3RpbWF0ZSwNCiAgICAgICAgICAgVFJBTlNBQ1RJT05fSUQsDQog
ICAgICAgICAgIC0tIGNvbnZlcnQgMCB0byBOVUxMIFtUUkktNTQ5MV0NCiAgICAgICAgICAgREVD
T0RFIChTVUJNSVNTSU9OX0lELCAwLCBOVUxMLCBTVUJNSVNTSU9OX0lEKQ0KICAgICAgRlJPTSBy
YXRlaW5kZXgudHJhbnNjeWNsZTs=
--000000000000d022f706248aaba4
Content-Type: text/plain; charset="US-ASCII"; name="postgres_view.txt"
Content-Disposition: attachment; filename="postgres_view.txt"
Content-Transfer-Encoding: base64
Content-ID: <f_m2ay9l151>
X-Attachment-Id: f_m2ay9l151

Q1JFQVRFIE9SIFJFUExBQ0UgVklFVyBiaWxsZWRfcmF0ZV90cmFuc2FjdGlvbnMgKGFjY291bnRf
aWQsIGNvbXBhbnlfaWQsIGF1dGhlbnRpY2F0aW5nX29yZGVyX2lkLCBhdXRoZW50aWNhdGluZ19w
cm9kdWN0X2lkLCBhdXRoZW50aWNhdGluZ19wcmljZV9jbGFzcywgYXV0aG9yaXppbmdfb3JkZXJf
aWQsIGF1dGhvcml6aW5nX3Byb2R1Y3RfaWQsIGF1dGhvcml6aW5nX3ByaWNlX2NsYXNzLCBzdGFy
dF9kYXRlLCBlbmRfZGF0ZSwgdHlwZV9jb2RlLCBwcmljZSwgZXF1aXBtZW50LCBsYW5lX2NvdW50
LCBsYW5lX2VzdGltYXRlLCB0cmFuc2FjdGlvbl9pZCwgc3VibWlzc2lvbl9pZCkgQVMgU0VMRUNU
DQogICAgYWNjb3VudF9pZCwNCiAgICBjb21wYW55X2lkLA0KICAgIG9yZGVyX2lkLA0KICAgIHBy
b2R1Y3RfaWQsDQogICAgcHJpY2VfY2xhc3MsDQogICAgYXV0aG9yaXppbmdfb3JkZXJfaWQsDQog
ICAgYXV0aG9yaXppbmdfc3Vic2NyaXB0aW9uX3R5cGUsDQogICAgYXV0aG9yaXppbmdfcHJpY2Vf
Y2xhc3MsDQogICAgc3RhcnRfZGF0ZSwNCiAgICBlbmRfZGF0ZSwNCiAgICBjb2FsZXNjZShDQVNF
IFdIRU4gaW1wYWN0X2NhdGVnb3J5PSdCVUxLX1JBVEVfUkVRVUVTVCcsIDEsIC0tIFRvIGhhbmRs
ZSBQb3J0YWwncyBjYXRlZ29yeSBmb3IgdGhlIG9sZCBSSVANCiAgICAgICAnQ09OVFJBQ1RfQlVM
S19SQVRFJywgMSwNCiAgICAgICAnU1BPVF9CVUxLX1JBVEUnLCAyLA0KICAgICAgICdDT05UUkFD
VF9ISVNUT1JZJywgMywNCiAgICAgICAnU1BPVF9ISVNUT1JZJywgNCwNCiAgICAgICAnUkFURV9T
VUJNSVNTSU9OJywgNSwNCiAgICAgICAnU1BPVF9CQUNLSEFVTCcgVEhFTiAgNiBFTkQgLCAwKSwN
CiAgICBwcmljZSwNCiAgICBlcXVpcG1lbnQsDQogICAgbGFuZV9jb3VudCwNCiAgICBsYW5lX2Vz
dGltYXRlLA0KICAgIFRSQU5TQUNUSU9OX0lELA0KICAgIC0tIGNvbnZlcnQgMCB0byBOVUxMIFtU
UkktNTQ5MV0NCiAgICBDQVNFIFdIRU4gU1VCTUlTU0lPTl9JRD0wIFRIRU4gIE5VTEwgIEVMU0Ug
U1VCTUlTU0lPTl9JRCBFTkQNCkZST00NCiAgICByYXRlaW5kZXgudHJhbnNjeWNsZTs=
--000000000000d022f706248aaba4--