Convert PostgreSQL to MS SQL

Question

I am needing help converting a PostgreSQL query to MSSQ.

Below is what i have done so far but i am issuing with the function and array areas which i do not think are allowed in MS SQL.

Is there something that that i need to do change the function and looks the WHERE statement has an array in it too.

I have added the select statement for the #temp table but when i create the #temp table i am getting errors saying incorrect syntax

   CREATE FUNCTION pm_aggregate_report 
(
    _facility_ids uuid[]
    , _risk_ids uuid[] DEFAULT NULL::uuid[]
    , _assignee_ids uuid[] DEFAULT NULL::uuid[]
    , _start_date date DEFAULT NULL::date
    , _end_date date DEFAULT NULL::date
) 
    RETURNS TABLE
(
    facility character varying
    , pm_id uuid, grouped_pm boolean
    , risk_id uuid
    , risk character varying
    , pm_status_id uuid
    , user_id uuid
    , assignee text
    , completed_by uuid
    , total_labor bigint
)
 
   CREATE TABLE #tmp_pm_aggregate
   (
    facility_id VARCHAR(126),
    pm_id VARCHAR(126),
    grouped_pm VARCHAR(126),
    risk_id VARCHAR(126),
    pm_status_id VARCHAR(126),
    user_id VARCHAR(126),
    completed_by VARCHAR(126)
   )
   SELECT DISTINCT 
       COALESCE(gp.facility_id, a.facility_id) as facility_id,
       COALESCE(p.grouped_pm_id, p.id) as pm_id,
       CASE WHEN p.grouped_pm_id IS NULL THEN false ELSE true END as grouped_pm,
       COALESCE(gp.risk_id, a.risk_id) as risk_id,
       COALESCE(gp.pm_status_id, p.pm_status_id) as pm_status_id,
       COALESCE(gass.user_id, sass.user_id) as user_id,
       COALESCE(gp.completed_by, p.completed_by) as completed_by
   FROM pms p
   JOIN assets a
       ON p.asset_id = a.id
   LEFT JOIN grouped_pms gp
       ON p.grouped_pm_id = gp.id
   LEFT JOIN assignees sass
     ON p.id = sass.record_id
     AND sass.type = 'single_pm'
   LEFT JOIN assignees gass
     ON p.grouped_pm_id = gass.record_id
     AND gass.type = 'grouped_pm'
   LEFT JOIN users u
     ON (sass.user_id = u.id OR gass.user_id = u.id)
   WHERE a.facility_id = ANY(_facility_ids)
     AND NOT a.is_component
     AND COALESCE(gp.pm_status_id, p.pm_status_id) in ('f9bdfc17-3bb5-4ec0-8477-24ef05ea3b9b', '06fc910c-3d07-4284-8f6e-8fb3873f5333')
     AND COALESCE(gp.completion_date, p.completion_date) BETWEEN COALESCE(_start_date, '1/1/2000') AND COALESCE(_end_date, '1/1/3000')
     AND COALESCE(gp.show_date, p.show_date) <= CURRENT_TIMESTAMP
     AND COALESCE(gass.user_id, sass.user_id) IS NOT NULL
     AND u.user_type_id != 'ec823d98-7023-4908-8006-2e33ddf2c11b' 
     AND (_risk_ids IS NULL OR COALESCE(gp.risk_id, a.risk_id) = ANY(_risk_ids)   
     AND (_assignee_ids IS NULL OR COALESCE(gass.user_id, sass.user_id) = ANY(_assignee_ids);


     SELECT
      f.name as facility,
      t.pm_id,
      t.grouped_pm,  
      t.risk_id,
      r.name as risk, 
      t.pm_status_id,
      t.user_id,
      u.name_last + ', ' + u.name_first as assignee, 
      t.completed_by,
      ISNULL(gwl.total_labor, swl.total_labor) as total_labor
   FROM #tmp_pm_aggregate t
   JOIN facilities f
       ON t.facility_id = f.id
   JOIN risks r
       ON t.risk_id = r.id
   JOIN users u
       ON t.user_id = u.id
   LEFT JOIN (SELECT wl.record_id, wl.user_id, SUM(wl.labor_time) as total_labor
           FROM work_logs wl
           WHERE wl.type = 'single_pm'
           GROUP BY wl.record_id, wl.user_id) as swl
      ON t.pm_id = swl.record_id
      AND t.user_id = swl.user_id
      AND t.grouped_pm = false
   LEFT JOIN (SELECT wl.record_id, wl.user_id, SUM(wl.labor_time) as total_labor
           FROM work_logs wl
           WHERE wl.type = 'grouped_pm'
           GROUP BY wl.record_id, wl.user_id) as gwl
      ON t.pm_id = gwl.record_id
      AND t.user_id = gwl.user_id
      AND t.grouped_pm = true
    ORDER BY facility,
        assignee,
        risk;
       
   DROP TABLE #tmp_pm_aggregate;

In SQL server, you can use table variables and types to pass a set to a procedure or function. Like that you can simulate arrays. — sticky bit
– sticky bit, Commented Jan 4, 2022 at 20:21
SQL Server supports XML data type as well as JSON (NVARCHAR(....)) for your scenario. — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Jan 4, 2022 at 20:33
Side note: If you explain what the above does, and/or provide sample data and expected results/behaviour then you'll also be able to get help from those fluent in T-SQL but not is PostgreSQL; the 2 dialects are very different. — Thom A
– Thom A ♦, Commented Jan 4, 2022 at 21:07
@Larnu it will give me the number of total # PMs, total # completed and total labor hours per division, facilities and user — Seth Johnson
– Seth Johnson, Commented Jan 6, 2022 at 17:08

Charlieface · Accepted Answer · 2022-01-04 21:08:29Z

You can create an inline Table Valued Function, and simply return a resultset from it. You do not need (and cannot use) a temp table, you do not declare the returned "rowset" shape.

For the array parameters, you can use a Table Type:

CREATE TYPE dbo.GuidList (value uniqueidentifier NOT NULL PRIMARY KEY);

Because the table parameters are actual tables, you must query them like this (NOT EXISTS (SELECT 1 FROM @risk_ids) OR ISNULL(gp.risk_id, a.risk_id) IN (SELECT r.value FROM @risk_ids))
The parameters must start with @
There is no boolean type, you must use bit
Always use deterministic date formats for literals. yyyymmdd works for dates. Do you need to take into account hours and minutes, because you haven't?
ISNULL generally performs better than COALESCE in SQL Server, as the compiler understands it better
You may want to pass a separate parameter showing whether you passed in anything for the optional table parameters
I suggest you look carefully at the actual query: why does it need DISTINCT? It performs poorly, and is usually a code-smell indicating poorly thought-out joins. Perhaps you need to combine the two joins on assignees, or perhaps you should use a row-numbering strategy somewhere.

CREATE FUNCTION dbo.pm_aggregate_report 
(
    @facility_ids dbo.GuidList
    , @risk_ids dbo.GuidList
    , @assignee_ids dbo.GuidList
    , @start_date date
    , @end_date date
) 
RETURNS TABLE AS RETURN
   SELECT DISTINCT   -- why DISTINCT, perhaps rethink your joins
       ISNULL(gp.facility_id, a.facility_id) as facility_id,
       ISNULL(p.grouped_pm_id, p.id) as pm_id,
       CASE WHEN p.grouped_pm_id IS NULL THEN CAST(0 AS bit) ELSE CAST(1 AS bit) END as grouped_pm,
       ISNULL(gp.risk_id, a.risk_id) as risk_id,
       ISNULL(gp.pm_status_id, p.pm_status_id) as pm_status_id,
       ISNULL(gass.user_id, sass.user_id) as user_id,
       ISNULL(gp.completed_by, p.completed_by) as completed_by
   FROM pms p
   JOIN assets a
       ON p.asset_id = a.id
   LEFT JOIN grouped_pms gp
       ON p.grouped_pm_id = gp.id
   LEFT JOIN assignees sass
     ON p.id = sass.record_id
     AND sass.type = 'single_pm'
   LEFT JOIN assignees gass
     ON p.grouped_pm_id = gass.record_id
     AND gass.type = 'grouped_pm'
   LEFT JOIN users u
     ON (sass.user_id = u.id OR gass.user_id = u.id)  -- is this doubling up your rows?
   WHERE a.facility_id IN (SELECT f.value FROM @facility_ids f)
     AND a.is_component = 0
     AND ISNULL(gp.pm_status_id, p.pm_status_id) in ('f9bdfc17-3bb5-4ec0-8477-24ef05ea3b9b', '06fc910c-3d07-4284-8f6e-8fb3873f5333')
     AND ISNULL(gp.completion_date, p.completion_date) BETWEEN ISNULL(@start_date, '20000101') AND ISNULL(@end_date, '30000101')  -- perhaps use >= AND <
     AND ISNULL(gp.show_date, p.show_date) <= CURRENT_TIMESTAMP
     AND ISNULL(gass.user_id, sass.user_id) IS NOT NULL
     AND u.user_type_id != 'ec823d98-7023-4908-8006-2e33ddf2c11b' 
     AND (NOT EXISTS (SELECT 1 FROM @risk_ids) OR ISNULL(gp.risk_id, a.risk_id) IN (SELECT r.value FROM @risk_ids))
     AND (NOT EXISTS (SELECT 1 FROM @assignee_ids) OR ISNULL(gass.user_id, sass.user_id) IN (SELECT aid.value FROM @assignee_ids aid));

i have edited my code to include the SELECT statement and a question regarding creating the #temp table
Please don't "move the goalposts" after the question has been answered, it's heavily frowned upon in Stack Overflow. You can create a new question for this. To answer in short, you cannot create temp tables in functions, only in procedures. Instead, just add the other joins to the above query, and select what you need. There honestly is no reason to use a temp table anyway, it serves no purpose without proper indexing anyway

Collectives™ on Stack Overflow

Convert PostgreSQL to MS SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related