Error when rewriting SQL Server query to postgresql and replacing stuff() with string_agg()

Question

I'm migrating an app from SQL Server to Postgresql, and have to rewrite a few queries that are run periodically. I'm having trouble with one in particular, because I have to replace stuff() and XML Path with string_agg(). Normally I can do this, but in this case stuff() is a sub-query, so I'm receiving a "more than one row returned by a sub-query used as an expression" error when I run this. I'm not really experienced enough with SQL and Postgresqlto fix this.

SQL Server portion where I'm getting stuck:

SELECT P.pkey + '-' + CAST(JI.issuenum AS VARCHAR(18)) [CASE NUMBER]
, IT.pname [ISSUE TYPE (PREVIOUSLY CASE REASON)]
, JI.CREATED [OPENED DATE]
, CRE.display_name [CREATOR]
, DEP.BIG_DATA [DEPARTMENT]
, REG.BIG_DATA [OFFICE]
, JI.RESOLUTIONDATE [CLOSED DATE]
, STUFF((     SELECT CAST(', ' + COM.cname AS VARCHAR(MAX))
                     FROM nodeassociation NAC
                     INNER JOIN component COM ON NAC.SINK_NODE_ID = COM.ID
                     WHERE NAC.SOURCE_NODE_ID = JI.ID
                     ORDER BY COM.cname
                     FOR XML PATH('')), 1, 2, '') [COMPONENT(S)]
, RCS.customvalue [RESOLUTION CODE (SERVICE)]
, ISNULL(RCI1.customvalue + ' - ', '') + RCI2.customvalue [RESOLTUION CODE (INCIDENT)]
, ISNULL(RCMT1.customvalue + ' - ', '') + ISNULL(RCMT2.customvalue + ' - ','') + ISNULL(RCMT3.customvalue,'') [RESOLTUION CODE (3-tier)]

My failing postgres rewrite that returns error:

more than one row returned by a subquery used as an expression

SELECT P.pkey || '-' || CAST(JI.issuenum AS VARCHAR(18)) AS "CASE NUMBER"
, IT.pname "ISSUE TYPE (PREVIOUSLY CASE REASON)"
, JI.CREATED "OPENED DATE"
, CRE.display_name "CREATOR"
, JI.RESOLUTIONDATE "CLOSED DATE"
, string_agg((SELECT COM.cname 
                     FROM nodeassociation NAC
                     INNER JOIN component COM ON NAC.SINK_NODE_ID = COM.ID
                     WHERE NAC.SOURCE_NODE_ID = JI.ID
                     ORDER BY COM.cname, p.pkey, JI.issuenum, IT.pname, JI.CREATED, CRE.display_name, JI.RESOLUTIONDATE, RCS.customvalue, RCI1.customvalue, RCI2.customvalue, RCMT1.customvalue, RCMT2.customvalue, RCMT3.customvalue, DEP."BIG_DATA", REG."BIG_DATA" 
                     ), '') AS COMPONENTS
, RCS.customvalue RESOLUTION_CODE_SERVICE
, coalesce(RCI1.customvalue || ' - ', '') || RCI2.customvalue "RESOLTUION-CODE-INCIDENT"
, coalesce(RCMT1.customvalue || ' - ', '') || coalesce(RCMT2.customvalue || ' - ','') || coalesce(RCMT3.customvalue,'') "RESOLTUION-CODE 3-tier"
, DEP."BIG_DATA" "DEPARTMENT"
, REG."BIG_DATA" "OFFICE"

I've used this old post about replacing stuff() with string_agg(), but it doesn't cover a sub-query.

score 0 · Accepted Answer · answered Jan 21 '20 at 19:45

0

Try to replace this:

string_agg((SELECT COM.cname 
                     FROM nodeassociation NAC
                     INNER JOIN component COM ON NAC.SINK_NODE_ID = COM.ID
                     WHERE NAC.SOURCE_NODE_ID = JI.ID
                     ORDER BY COM.cname, p.pkey, JI.issuenum, IT.pname, JI.CREATED, CRE.display_name, JI.RESOLUTIONDATE, RCS.customvalue, RCI1.customvalue, RCI2.customvalue, RCMT1.customvalue, RCMT2.customvalue, RCMT3.customvalue, DEP."BIG_DATA", REG."BIG_DATA" 
                     ), '') AS COMPONENTS

on this:

(SELECT string_agg(COM.cname,'')
                     FROM nodeassociation NAC
                     INNER JOIN component COM ON NAC.SINK_NODE_ID = COM.ID
                     WHERE NAC.SOURCE_NODE_ID = JI.ID
                     ORDER BY COM.cname, p.pkey, JI.issuenum, IT.pname, JI.CREATED, CRE.display_name, JI.RESOLUTIONDATE, RCS.customvalue, RCI1.customvalue, RCI2.customvalue, RCMT1.customvalue, RCMT2.customvalue, RCMT3.customvalue, DEP."BIG_DATA", REG."BIG_DATA" 
                     ) AS COMPONENTS

answered Jan 21 '20 at 19:45

Max Zolotenko

1,082
7
13

Thanks @Максим Золотенко I no longer receive an error, but the columns for the select query all show null. I think the problem may be on my end with how I'm doing the joins – cpa521 Jan 21 '20 at 20:04
You can check whether your subquery returns any value by replacing `string_agg(COM.cname, '')` on `count(*)`. If all values equal to 0 then the issue in the subquery. – Max Zolotenko Jan 21 '20 at 20:25
HI @@Максим Золотенко my problem seems to be how I changed to query to get around "ERROR: relation tablename does not exist errors. If I run a query in SQL select * from dbo.AO_2C3287_USER_DATA I get a few hundred records. If I run this in postgres, I receive the relation doesn't exist error. I can get around the error by using double quotes, but no data is returned. So I believe that's why I'm getting null in my full query. I'm now looking for a way around this, but it shouldn't be too hard. – cpa521 Jan 21 '20 at 21:34
I'm dumb. The application didn't populate some tables, when I performed an in app export and import. So some of the tables that I'm querying are empty, when I expected them to have data. Adding test data confirms this. The issue is now resolved. – cpa521 Jan 21 '20 at 22:10

Error when rewriting SQL Server query to postgresql and replacing stuff() with string_agg()

1 Answers1