Database

SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
proj_cost.cost,
proj_cost.pref_vendor
FROM vendor
LEFT OUTER JOIN proj_cost ON proj_cost.Vend_ID = vendor.Vendor_ID
WHERE (proj_cost.proj_id = '4077'
AND proj_cost.proj_stage = 'construction'
AND proj_cost.vers_id = '8'
AND proj_cost.task_Oper = '6'
AND proj_cost.vend_id = '54'
AND proj_cost.task_id = 'TK_EX_044')
-- vendor without matching proj_cost
OR proj_cost.Vend_ID IS NULL

SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
proj_cost.cost,
proj_cost.pref_vendor
FROM vendor
LEFT OUTER JOIN proj_cost 
ON proj_cost.Vend_ID = vendor.Vendor_ID
AND proj_cost.proj_id = '4077'
AND proj_cost.proj_stage = 'construction'
AND proj_cost.vers_id = '8'
AND proj_cost.task_Oper = '6'
AND proj_cost.vend_id = '54'
AND proj_cost.task_id = 'TK_EX_044'

WITH cte As
(
    SELECT 
        Vend_ID,
        cost,
        pref_vendor
    FROM
        proj_cost 
    WHERE proj_id = '4077'
    AND proj_stage = 'construction'
    AND vers_id = '8'
    AND task_Oper = '6'
    AND vend_id = '54'
    AND task_id = 'TK_EX_044'
)
SELECT
vendor.Vendor_ID,
vendor.Company_Name,
vendor.DELETE_FLAG,
cte.cost,
cte.pref_vendor
FROM vendor
LEFT OUTER JOIN cte ON cte.Vend_ID = vendor.Vendor_ID

SELECT
    proj_invoice.Recnum,
    proj_job.Recnum,    
    proj_invoice.Proj_ID,
    proj_invoice.Invoice_Class,
    proj_invoice.Invoice_Oper,
    proj_invoice.Invoice_Number, 
    proj_invoice.Invoice_Date, 
    proj_invoice.Invoice_Desc, 
    proj_invoice.Invoice_Amt, 
    proj_invoice.Due_Date,
    proj_invoice.Vendor_Id,
    proj_job.EST_COST, 
    proj_job.ACT_COST,            
    Catalog.Cat_ID,
    Catalog.Cat_Name,
    Catalog.Default_Vendor            
FROM proj_invoice
RIGHT JOIN Proj_Job ON Proj_Job.Proj_ID = proj_invoice.Proj_ID AND Proj_Job.Job_Oper = Proj_Invoice.Invoice_Oper
RIGHT JOIN catalog ON catalog.cat_id = Proj_Job.Job_Oper            
RIGHT JOIN vendor ON proj_job.Vendor_id = proj_invoice.Vendor_ID        
WHERE proj_invoice.Proj_ID = '$projectNumber'
AND CONVERT(VARCHAR, proj_invoice.Delete_Flag) <> '1' 
AND Proj_Job.EST_COST <> 0.00 
AND Proj_Job.ACT_COST = proj_invoice.Invoice_Amt 
AND vendor.Vendor_ID = proj_invoice.Vendor_ID
ORDER BY CASE 
    WHEN Proj_Job.job_type = 'FX' THEN 0
    WHEN Proj_Job.job_type = 'OP' THEN 1
    WHEN Proj_Job.job_type = 'EQ' THEN 2
END

SELECT 
    proj_cost.Vend_ID, 
    proj_cost.pref_vendor,
    vendor.Delete_Flag
FROM proj_cost 
INNER JOIN vendor ON proj_cost.Vend_ID = vendor.Vendor_ID
WHERE proj_cost.Proj_ID = '$projectNumber' 
AND proj_cost.Proj_Stage = '$projectStage' 
AND proj_cost.Vers_ID = '$versionNumber' 
AND proj_cost.Task_Oper = '$taskOperator'
GROUP BY proj_cost.Vend_Id, proj_cost.pref_vendor, vendor.Delete_Flag 
ORDER BY CASE 
    WHEN vendor.Delete_Flag = '1' THEN 0
    WHEN proj_cost.pref_vendor <> 'Y' THEN 1
    WHEN proj_cost.pref_vendor = 'Y' THEN 2

END

jkirkerx wrote:

ORDER BY CASE
WHEN vendor.Delete_Flag = '1' THEN 0
WHEN proj_cost.pref_vendor <> 'Y' THEN 1
WHEN proj_cost.pref_vendor = 'Y' THEN 2

ORDER BY
   vendor.Delete_Flag desc,   -- '1' then '0'
   proj_cost.pref_vendor asc  -- 'N' then 'Y'

ORDER BY CASE 
    WHEN Proj_Job.job_type = 'FX' THEN 0
    WHEN Proj_Job.job_type = 'OP' THEN 1
    WHEN Proj_Job.job_type = 'EQ' THEN 2
END

ORDER BY CASE 
    WHEN Proj_Job.job_type = 'FX' THEN 0
    WHEN Proj_Job.job_type = 'OP' THEN 1
    ELSE 2
END

ORDER BY 
   CASE Proj_Job.job_type
      WHEN 'FX' THEN 0
      WHEN 'OP' THEN 1
      ELSE 2
   END

SELECT Column1, Column2, CASE WHEN CRITERIA1 IS TRUE THEN (SUBQUERY1) ELSE (SUBQUERY2) END As SomeData
FROM TABLE1
WHERE CRITERIA1 IS TRUE 
OR CRITERIA2 IS TRUE

CASE WHEN CRITERIA2 IS TRUE THEN (SUBQUERY2) ELSE (SUBQUERY1) END As SomeData

where
myString has_all (Pattern1, Pattern2)