How do I avoid repeating the query?-CodePudding

I have a query in below. I select column with some conditions. In the case when statement, if the when clause is true then I select column which defines in when clause so I repeat the same query.

How can I prevent this in below and also Do you have an idea about prevent repeat the same query in sql.

CASE WHEN EXISTS(
    SELECT
        TOP 1
        wo.licence_plate
    FROM
        work wo
        JOIN work_order ds ON wo.pk_id = ds.fk_work_order_id
        JOIN parameter par  ON ds.status = par.code
    WHERE
        par.id = 1246
        AND
        par.code = 5
        AND
        wo.customer_id = cus.pk_id
        AND
        wo.delivery_date > DATEADD( day, -7, GETDATE() )
)
THEN 
(
    SELECT
        TOP 1 wo.licence_plate
    FROM
        work wo (NOLOCK)
        JOIN work_order ds ON wo.pk_id = ds.fk_work_order_id
        JOIN parameter par  ON ds.status = par.code
    WHERE
        par.parameter_group_id = 1246
        and
        par.code = 5
        and
        wo.customer_id = cus.pk_id
        and
        wo.delivery_date > DATEADD(DAY, -7, GETDATE())
) ELSE ' ' END
    as result
FROM
    customer cus

CodePudding user response：

This will return ' ', if the record does not exist.

SELECT ISNULL(SELECT
        1 wo.licence_plate
    FROM
        dbo.tbl_asx_work_order wo
        JOIN tbl_asx_work_order_delivery_status ds ON wo.pk_id = ds.fk_work_order_id
        JOIN tbl_par_parameter par ON ds.status = par.code
    WHERE
        par.parameter_group_id = 1246
        and
        par.code = 5
        and
        wo.customer_id = cus.pk_id
        and
        wo.delivery_date > DATEADD(DAY, -7, GETDATE()), ' ')

CodePudding user response：

You could use an OUTER APPLY for this.
Then use COALESCE or ISNULL to default into an empty string if nothing matched.

SELECT cus.*
, COALESCE(oa.licence_plate, '') AS licence_plate
FROM customer cus
OUTER APPLY (
 SELECT TOP 1 wo.licence_plate
 FROM work wo
 JOIN work_order ds ON wo.pk_id = ds.fk_work_order_id
 JOIN [parameter] par ON ds.status = par.code
 WHERE wo.customer_id = cus.pk_id
  AND par.id = 1246 AND par.code = 5 
  AND wo.delivery_date > DATEADD(day, -7, GETDATE())
) oa;