Postgresql query, replace value of one column to another-CodePudding

I have the following sql query

SELECT "SortingAttempts"."StartedAt", "ExternalId" as "ARTID", "Barcode", "LoadStationId", "CheckInLocationId", "Weight", "Length", "Height", "Width", "CheckInStatus"
FROM "SortingAttempts"
FULL OUTER JOIN "SortingAttemptBarcodes" 
ON "SortingAttempts"."Id" = "SortingAttemptBarcodes"."SortingAttemptId"
FULL OUTER JOIN "Packages"
ON "SortingAttempts"."PackageId" = "Packages"."Id"
WHERE "StartedAt" >= '${yesterday} 8:00'
AND "EndedAt" <= '${today} 8:00'
ORDER BY 1

And the following result result

I wand to get rid of the "CheckInStatus" column, but i need it's values. So i need to check, if ARTID's value is null - then i put CheckInStatus's value in ARTIS's cell. Important - i don't want to rewrite anything in DB, i just need a specific query result.

I tried to use SQL CASE WHEN, THEN and DO IF ELSE END, but, seems like i do smth wrong, or it's just impossible to do with sql query.

CodePudding user response：

I tried something for you I hope you get an idea ;

select StartedAt, 
case when ARTID is null then CheckInStatus else 
ARTID end as ARTID
from SortingAttempts;

http://sqlfiddle.com/#!9/306388/3

so you can try like this ;

SELECT "SortingAttempts"."StartedAt", case when "ExternalId" is null then "CheckInStatus" else "ExternalId" end as "ARTID", "Barcode", "LoadStationId", "CheckInLocationId", "Weight", "Length", "Height", "Width"
FROM "SortingAttempts"
FULL OUTER JOIN "SortingAttemptBarcodes" 
ON "SortingAttempts"."Id" = "SortingAttemptBarcodes"."SortingAttemptId"
FULL OUTER JOIN "Packages"
ON "SortingAttempts"."PackageId" = "Packages"."Id"
WHERE "StartedAt" >= '${yesterday} 8:00'
AND "EndedAt" <= '${today} 8:00'
ORDER BY 1