Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t16gO-00ElJy-Cn for pgsql-sql@arkaria.postgresql.org; Wed, 16 Oct 2024 16:15:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t16gM-005TDI-FS for pgsql-sql@arkaria.postgresql.org; Wed, 16 Oct 2024 16:15:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t16gM-005TD9-5I for pgsql-sql@lists.postgresql.org; Wed, 16 Oct 2024 16:15:30 +0000 Received: from mx0b-0039f802.pphosted.com ([205.220.176.45]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t16gI-001Nln-I3 for pgsql-sql@lists.postgresql.org; Wed, 16 Oct 2024 16:15:29 +0000 Received: from pps.filterd (m0209982.ppops.net [127.0.0.1]) by mx0b-0039f802.pphosted.com (8.18.1.2/8.18.1.2) with ESMTP id 49G7E8Pw030128 for ; Wed, 16 Oct 2024 09:15:24 -0700 Received: from mail-yw1-f198.google.com (mail-yw1-f198.google.com [209.85.128.198]) by mx0b-0039f802.pphosted.com (PPS) with ESMTPS id 42a3bngq9m-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128 verify=NOT) for ; Wed, 16 Oct 2024 09:15:24 -0700 (PDT) Received: by mail-yw1-f198.google.com with SMTP id 00721157ae682-6e31d9c8efcso1158357b3.0 for ; Wed, 16 Oct 2024 09:15:24 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729095323; x=1729700123; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=NqgCFqDLKrhf3Q5RbzkuAqrgMBTQd/wzUE3tUl16RFk=; b=mwqOSN93n2E/5v9e0qLOw1Y/aTexLvo0MUgu04wEt3Ik5droIG1zmyZY3Xbpoc15kq +yfxfURRXTiiBGKkDB0WqX8fVyStuuzwLdFyos0zsSS3CzKfm+8pTXKY9Bug3VJZt4US dJL8A971xAUf3QUoaEvrZALvxJaH2VLgIc1nwI+CSrHb4xm1NUwzQxLg/8waRvfXtWao 7noR/f4JgWKiQ2CJEIPbCNCbu5+Ji8hOrd+ptsoOEA4WkRU9dpayMKbZBTeiE9Tc9Shn h7ljk54a2ccq2I3Fp5nXhKS/dz5jEj+e+eW4vo12aASHJADxarl4e4R4pSSyEY+d1JIJ 8+mg== X-Gm-Message-State: AOJu0YzID1aiv2LtL2si/yLP/kOtjjmL705pczgjV4MwkEGLjE3fIxBJ 6i9Ie1GRbqR478L2sefRzeiwScf1aM1+jMeeoPMJxFONn5AbsXr4iNV+A6tgA+w+1ECB1U1GTdW iL8r15JILq214cIHNsJUDzB18jZa2UXgyzBX8wUY6JwbwqTjSfpd7UePUpMnm8t6VyL5NUCqx6+ jC4jTQnqFHU9/r+kZHowR1nSa+ugLXTxoyuxHNZhnDeHUCs0G8CXCgx0n+6CtYpvEZ X-Received: by 2002:a05:690c:6704:b0:6e2:1527:4447 with SMTP id 00721157ae682-6e3477bac1cmr180338777b3.1.1729095323191; Wed, 16 Oct 2024 09:15:23 -0700 (PDT) X-Google-Smtp-Source: AGHT+IEdHzL+fm0gCmyDUN8GCoREgOyy34o10bAXhPACpQP9l9yVnZLogKawLzgaLEWHMTKLqeOoRmXd+xzr33tSN/0= X-Received: by 2002:a05:690c:6704:b0:6e2:1527:4447 with SMTP id 00721157ae682-6e3477bac1cmr180338417b3.1.1729095322742; Wed, 16 Oct 2024 09:15:22 -0700 (PDT) MIME-Version: 1.0 References: <38FB1E49-C63E-4AD5-97FD-81F417268AE7@unochapeco.edu.br> In-Reply-To: <38FB1E49-C63E-4AD5-97FD-81F417268AE7@unochapeco.edu.br> From: Sam Stearns Date: Wed, 16 Oct 2024 09:15:11 -0700 Message-ID: Subject: Re: Oracle ==> Postgres View To: William Alves Da Silva Cc: pgsql-sql Content-Type: multipart/alternative; boundary="0000000000007edc1306249a60c5" X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1051,Hydra:6.0.680,FMLib:17.12.62.30 definitions=2024-10-16_13,2024-10-16_01,2024-09-30_01 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007edc1306249a60c5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you all for the help! William's advice did the trick. Sam On Tue, Oct 15, 2024 at 2:49=E2=80=AFPM William Alves Da Silva < william_silva@unochapeco.edu.br> wrote: > Hello. > > I think you want this. > > coalesce(CASE impact_category > WHEN 'BULK_RATE_REQUEST' THEN 1 -- To handle Portal's category for the > old RIP > WHEN 'CONTRACT_BULK_RATE' THEN 1 > WHEN 'SPOT_BULK_RATE' THEN 2 > WHEN 'CONTRACT_HISTORY' THEN 3 > WHEN 'SPOT_HISTORY' THEN 4 > WHEN 'RATE_SUBMISSION' THEN 5 > WHEN 'SPOT_BACKHAUL' THEN 6 END , 0) > > Regards, > William Alves > > On 15 Oct 2024, at 18:30, Sam Stearns wrote: > > 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=3D'BULK_RATE_REQUEST', 1, -- T= o... > > The problem block of code: > > coalesce(CASE WHEN impact_category=3D'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] > > > > > --=20 *Samuel Stearns* Lead Database Administrator *c:* 971 762 6879 | *o:* 503 672 5115 | DAT.com [image: DAT] --0000000000007edc1306249a60c5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thank you all for the help!=C2=A0 William's advice did= the trick.

Sam


On Tue, Oct 15, 2024= at 2:49=E2=80=AFPM William Alves Da Silva <william_silva@unochapeco.edu.br> wrote:
Hello.

<= /div>
I think you want this.

<= div>coalesce(CASE impact_category
WHEN 'BULK_RATE_REQUEST' THEN 1 -- To handle Portal's category for the= old RIP
WHEN 'CONTRACT_BULK_RATE= ' THEN 1
WHEN &#= 39;SPOT_BULK_RATE' THEN 2
= WHEN 'CONTRACT_HISTORY' THEN 3
WHEN 'SPOT_HISTORY' THEN 4
WH= EN 'RATE_SUBMISSION'<= /span> THEN 5
WHEN 'SPOT= _BACKHAUL' THEN 6 END , 0)
=

Regards,
William Alves
=

On 15 Oct 2024, at 18:30, Sam Stear= ns <sam.stearns= @dat.com> wrote:

Howdy,

I have 2 views attached.=C2=A0 An Oracle view written with NVL.=C2=A0= The same view was converted to Postgres=C2=A0using COALESCE.=C2=A0 Postgre= s is throwing an error:

ERROR: =C2=A0syntax error = at or near ","
LINE 12: ...esce(CASE WHEN impact_category=3D&#= 39;BULK_RATE_REQUEST', 1, -- To...

The problem= block of code:

=C2=A0 =C2=A0 coalesce(CASE WHEN i= mpact_category=3D'BULK_RATE_REQUEST', 1, -- To handle Portal's = category for the old RIP
=C2=A0 =C2=A0 =C2=A0 =C2=A0'CONTRACT_BULK_R= ATE', 1,
=C2=A0 =C2=A0 =C2=A0 =C2=A0'SPOT_BULK_RATE', 2,
= =C2=A0 =C2=A0 =C2=A0 =C2=A0'CONTRACT_HISTORY', 3,
=C2=A0 =C2=A0 = =C2=A0 =C2=A0'SPOT_HISTORY', 4,
=C2=A0 =C2=A0 =C2=A0 =C2=A0'= RATE_SUBMISSION', 5,
=C2=A0 =C2=A0 =C2=A0 =C2=A0'SPOT_BACKHAUL&#= 39; THEN =C2=A06 END , 0),

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

= Thanks,

Sam
=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^

--
=

Samuel Stearns
Lead Database Administrator
c:=C2=A09= 71 762 6879=C2=A0|=C2=A0o:=C2=A0503 672 5115=C2=A0|=C2=A0DAT.com

3D"DAT"
<oracle_view.txt><= /span><postgres_view.= txt>

<= br clear=3D"all">

-- <= /span>

Samuel Stearns
Lead Database Administrator
c:=C2=A0971 762 6879=C2=A0|=C2=A0o:=C2=A0503 672 5115=C2=A0|=C2=A0DAT.com

3D"DAT"
--0000000000007edc1306249a60c5--