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 1t0pQF-00DOiz-BA for pgsql-sql@arkaria.postgresql.org; Tue, 15 Oct 2024 21:49:44 +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 1t0pQD-00Bvwo-F7 for pgsql-sql@arkaria.postgresql.org; Tue, 15 Oct 2024 21:49:41 +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 1t0pQB-00Bvwf-Ko for pgsql-sql@lists.postgresql.org; Tue, 15 Oct 2024 21:49:41 +0000 Received: from mail-pf1-x42b.google.com ([2607:f8b0:4864:20::42b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t0pQ8-001FTE-DN for pgsql-sql@lists.postgresql.org; Tue, 15 Oct 2024 21:49:39 +0000 Received: by mail-pf1-x42b.google.com with SMTP id d2e1a72fcca58-71e74f35acaso132779b3a.2 for ; Tue, 15 Oct 2024 14:49:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=unochapeco.edu.br; s=google; t=1729028973; x=1729633773; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=b25vkKOwrkutbP6ZSZTWn6GphknmDMaoHjqSy/RmzDM=; b=o5I2X8rBXLfPsAgfe1P7xAWoSOErDwQSu004ly8F2Vd0JyBCFCX0dBJpHPpFd6+bzn +qDkZY3i6mi3aMbAd+S/dqEO/2JpyzTMVjySABnpDJAa8LoxCEd5fJCk2vQx+ACN8NAV GOqjBuHxMOmVF5FnN/q2vjypeIiulElVdPlubRiHu0CuIPIgtfFRo+EN3On2XIYEAjL+ T54J5n3jTBIqtwXjyddWLr1PPHfvudRlQqZdZuQPXg2S2ZJoS6KrPAqBEzZeuPJJGo4a 5maM2yNL75VsaDi3QK2/cTzWaW3/evg50Q8FVbfsVeBRu7EUQm1sI3uQFphzJidYnyS+ X8cg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729028973; x=1729633773; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=b25vkKOwrkutbP6ZSZTWn6GphknmDMaoHjqSy/RmzDM=; b=DsgwkAKTCURYMBC30Pl11J/XKiTLAjINt8lP0ZzH/xPxz6pEBnf66O3Xxeu5tHGgNe ERguVmjAFFM0H8XWQ6GLKAfcJmlPXpLZ5pB2jWRoN4WZldY/qB5hEozHu5CCzjXvoMiZ eYnATqEmVspSpRssF+5xoR0FrjsQ/CflhMpYN6gUYQ2NVndYNJ052nFDlypTNRbxC1yL TsPSOtodhapQKmF7HPm8t+Mo3OG8sLcqVM6c4FoVINtKjHoecczKDyRQ8Zz04Cd8a8mc N8Wkjc/R3xmHBTsdGUPTTPIkuIRLD77kzl3CRyz5BEow0GBmRFi4+H8FgjIvxesZ4ttH 6hdw== X-Gm-Message-State: AOJu0YxQTTsqNGwAerMp3jONHREU1Ac0LoLK+0XfhWL7yBT8IIpig6Q0 ppSleVitY7XIwYc5x0hlUMh2hz3SXMrc3k+ER2tN9w6PmUrPXHaDSbbnrJv5gAo= X-Google-Smtp-Source: AGHT+IGPr2lt2w/APmQBgDL22xqON7rsJul/Vcn4oNhDDPRdshei1IGTdw5hNYMkz+gG7untiCFgiw== X-Received: by 2002:a05:6a00:949a:b0:71e:5033:c6 with SMTP id d2e1a72fcca58-71e50330253mr7754357b3a.5.1729028972711; Tue, 15 Oct 2024 14:49:32 -0700 (PDT) Received: from smtpclient.apple ([2804:108c:c89b:2900:fc36:d66e:c1ff:a108]) by smtp.gmail.com with ESMTPSA id d2e1a72fcca58-71e77518af7sm1786884b3a.213.2024.10.15.14.49.31 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 15 Oct 2024 14:49:32 -0700 (PDT) From: William Alves Da Silva Message-Id: <38FB1E49-C63E-4AD5-97FD-81F417268AE7@unochapeco.edu.br> Content-Type: multipart/alternative; boundary="Apple-Mail=_5BCA0D5E-0541-4CE0-AA4A-D942EC6E368E" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.300.61.1.2\)) Subject: Re: Oracle ==> Postgres View Date: Tue, 15 Oct 2024 18:49:19 -0300 In-Reply-To: Cc: pgsql-sql To: Sam Stearns References: X-Mailer: Apple Mail (2.3774.300.61.1.2) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_5BCA0D5E-0541-4CE0-AA4A-D942EC6E368E Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Hello. I think you want this. coalesce(CASE impact_category=20 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: >=20 > Howdy, >=20 > 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: >=20 > ERROR: syntax error at or near "," > LINE 12: ...esce(CASE WHEN impact_category=3D'BULK_RATE_REQUEST', 1, = -- To... >=20 > The problem block of code: >=20 > 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), >=20 > Would anyone be able to advise how to correct this for Postgres, = please? >=20 > Thanks, >=20 > Sam > = ^ >=20 > -- > Samuel Stearns > Lead Database Administrator > c: 971 762 6879 | o: 503 672 5115 | DAT.com >=20 > = --Apple-Mail=_5BCA0D5E-0541-4CE0-AA4A-D942EC6E368E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii 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,
<= div>William Alves

On 15 Oct = 2024, at 18:30, Sam Stearns <sam.stearns@dat.com> 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, -- To...

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
&= nbsp;                   =                     =                     =                     =                     =                   =  ^

-- =

Samuel = Stearns
Lead Database = Administrator
c: 971 762 6879 | o: 50= 3 672 5115 | DAT.com

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

= --Apple-Mail=_5BCA0D5E-0541-4CE0-AA4A-D942EC6E368E--