Order by several static string values, error "Conversion failed when converting the varchar value 'FX' to data type int"

jkirkerx

I thought it would be clever to sort the results by FX, OP, and finally EQ. I modeled this in SQL Manager and it runs fine, well not the version I posted. But when I run this in PHP off the DEV server, it gives me this error .... "Conversion failed when converting the varchar value 'FX' to data type int" Maybe I'm totally off on this one, and I don't understand the "THEN" in (CASE WHEN Proj_Job.job_oper = 'FX' THEN 15 END) , then what ... I used the number 15, because it's the 15th select column. I did search for quite awhile but the examples where more sophisticated than what I wanted to do.

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_job.EST_COST,
proj_job.ACT_COST,
vendor.Company_Name,
Catalog.Cat_Name,
Catalog.Default_Vendor,
Proj_Job.job_oper
FROM proj_invoice
RIGHT JOIN Proj_Job ON Proj_Job.Proj_ID = proj_invoice.Proj_ID
RIGHT JOIN vendor ON proj_job.Vendor_id = proj_invoice.Vendor_ID
RIGHT JOIN catalog ON catalog.cat_id = Proj_Job.Job_Oper
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_oper = 'FX' THEN 15 END),
(Case WHEN Proj_Job.job_oper = 'OP' THEN 15 END),
(CASE WHEN proj_job.job_oper = 'EQ' THEN 15 END)";

If it ain't broke don't fix it Discover my world at jkirkerx.com

jkirkerx

I had the wrong column name to sort on. [edit 11/21/2022] But I worked on it some more and figured out the logic behind doing things like this. And applied it to another database call where I wanted vendors that went out of business first or marked as deleted with a delete flag, and then vendors that are not marked deleted, and finally the default vendor at the end and it works pretty good. This ORDER BY CASE is pretty slick, and is coming in very handy. Maybe in the future if I think about it more, I can figure out how to handle vendors that retired, and still show them in past projects and reports, yet move on to the new vendors that replace them.

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

This one is slick on the ORDER BY CASE

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

If it ain't broke don't fix it Discover my world at

jsc42

Instead of

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

I'd rather do

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

than make the ORDER BY have to do CASE statements. This allows the optimiser to have greater control rather than second guessing how it works internally. This is just a personal preference - there may be other respondents who would be horrified by this simplistic approach. Your original

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

is a bit harder, but dropping the test for 'EQ' and just having ELSE 2 might be easier / quicker. e.g.

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

jkirkerx

Not many made a comment on this, so I wonder if my current method is sound or not. SMH :doh: Let's see if others chime in.

If it ain't broke don't fix it Discover my world at jkirkerx.com

jsc42

Another idea that came to me overnight ... Use the alternative version of CASE which always tests against the same value viz:

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

It might do the same under the hood (not tested), but it looks more elegant.

jkirkerx

I like that one! very simple.

If it ain't broke don't fix it Discover my world at jkirkerx.com