Convert postgresql to mssql query
WITH EntityData AS (
SELECT
S.DTE_ENTITY_ITEM_ID,
S.DTE_ENTITY_FIELD_ID,
CASE
WHEN S.DTE_ENTITY_FIELD_ID = 10403 THEN
(SELECT D.STRING_VALUE FROM DTE_WINDOW_VIEW D WHERE D.DTE_ENTITY_ITEM_ID = CAST(S.NUMERIC_VALUE AS bigint))
WHEN S.DTE_ENTITY_FIELD_ID = 10412 THEN
(SELECT D.VALUE FROM DTE_ENTITY_DATA_EXT D WHERE D.ID = CAST(S.ORIGINAL_VALUE AS bigint))
WHEN S.DTE_ENTITY_FIELD_ID = 10402 THEN
TO_CHAR(S.DATETIME_VALUE, ‘MM/DD/YYYY’)
WHEN S.DTE_ENTITY_FIELD_ID = 10408 THEN
REPLACE(REPLACE(REPLACE(REPLACE(S.ORIGINAL_VALUE, ‘0’, ‘No’), ‘1’, ‘Yes’), ‘True’, ‘Yes’), ‘False’, ‘No’)
ELSE
S.ORIGINAL_VALUE
END AS ORIGINAL_VALUE
FROM
DTE_ENTITY_DATA S
WHERE
S.STATUS_ID = 1
AND S.DTE_ENTITY_ITEM_ID IN (
SELECT DTE_ENTITY_ITEM_ID
FROM DTE_ENTITY_DATA
WHERE STATUS_ID = 1
AND DTE_ENTITY_FIELD_ID = 10402
AND DATETIME_VALUE BETWEEN ‘2024-01-01’ AND ‘2025-01-01’
AND DTE_ENTITY_ITEM_ID NOT IN (
SELECT ID
FROM DTE_ENTITY_ITEM
WHERE DTE_ENTITY_ID = 104
AND STATUS_ID = 7
)
AND DTE_ENTITY_ITEM_ID IN (
SELECT DTE_ENTITY_ITEM_ID
FROM DTE_ENTITY_DATA
WHERE STATUS_ID = 1
AND DTE_ENTITY_FIELD_ID = 10403
AND (
(NUMERIC_VALUE > 1 AND 1 = 1)
OR (NUMERIC_VALUE = 1 AND 1 > 1)
)
)
)
),
StaticData AS (
SELECT
I.ID AS DTE_ENTITY_ITEM_ID,
unnest(array[101, 103, 105, 106, 120]) AS DTE_ENTITY_FIELD_ID,
CASE
WHEN unnest(array[101, 103, 105, 106, 120]) = 101 THEN TO_CHAR(CREATED_DATETIME, ‘MM/DD/YYYY HH24:MI:SS’)
WHEN unnest(array[101, 103, 105, 106, 120]) = 103 THEN CAST(CREATED_BY AS VARCHAR(256))
WHEN unnest(array[101, 103, 105, 106, 120]) = 105 THEN TO_CHAR(UPDATED_DATETIME, ‘MM/DD/YYYY HH24:MI:SS’)
WHEN unnest(array[101, 103, 105, 106, 120]) = 106 THEN CAST(UPDATED_BY AS VARCHAR(256))
WHEN unnest(array[101, 103, 105, 106, 120]) = 120 THEN CAST(I.ID AS VARCHAR(256))
END AS ORIGINAL_VALUE
FROM
DTE_ENTITY_ITEM I
WHERE
I.ID IN (
SELECT DTE_ENTITY_ITEM_ID
FROM DTE_ENTITY_DATA
WHERE STATUS_ID = 1
AND DTE_ENTITY_FIELD_ID = 10402
AND DATETIME_VALUE BETWEEN ‘2024-01-01’ AND ‘2025-01-01’
AND DTE_ENTITY_ITEM_ID NOT IN (
SELECT ID
FROM DTE_ENTITY_ITEM
WHERE DTE_ENTITY_ID = 104
AND STATUS_ID = 7
)
AND DTE_ENTITY_ITEM_ID IN (
SELECT DTE_ENTITY_ITEM_ID
FROM DTE_ENTITY_DATA
WHERE STATUS_ID = 1
AND DTE_ENTITY_FIELD_ID = 10403
AND (
(NUMERIC_VALUE > 1 AND 1 = 1)
OR (NUMERIC_VALUE = 1 AND 1 > 1)
)
)
)
)
SELECT
DTE_ENTITY_ITEM_ID,
DTE_ENTITY_FIELD_ID,
ORIGINAL_VALUE
FROM
EntityData
Import csv with datetime column in postgresql
I am trying to import a CSV in postgres which has one column with datetime2(7) type.
Getting error as below