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.
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
This one is slick on the ORDER BY CASE
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
If it ain't broke don't fix it
Discover my world at jkirkerx.com
I am trying to manage the information related to Share and Security such as:
- The company.
- The person who is the CEO, Director of the company.
- The number of share of this company.
- The company related to this company.
So on and so many.
I am wondering if I can ask here to looking for people who have do the similar and can share the database schema in Microsoft SQL or NEO4J.
Thanks in advanace.
There was a website with database models but the business does not exist now and the website went away. We can still get to the stuff using the Wayback Machine. Have a look at Industry Data Models in case it helps.
Depending on your data, COALESCE is probably not the right approach. You unconditionally execute subquery 2, and only execute subquery 1 if #2 returns Null. That's a change in behaviour from your original query.
You should probably use a CASE statement instead:
SELECT Column1, Column2, CASEWHEN CRITERIA1 IS TRUE THEN (SUBQUERY1) ELSE (SUBQUERY2) ENDAs SomeData
WHERE CRITERIA1 IS TRUE
OR CRITERIA2 IS TRUE
If you want to prioritize criteria 2 matches, then swap the case around:
CASEWHEN CRITERIA2 IS TRUE THEN (SUBQUERY2) ELSE (SUBQUERY1) ENDAs SomeData
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
I need a way to return an identity value for a SELECT statement that is guaranteed to be unique.
So I wrote the following stored procedure:
CREATE PROCEDURE agsp_UniqueDocnum
@DOCUMENTTYPE AS NVARCHAR(15),
@ORDERID AS INT
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO ags_UniqueDocnum
The ags_UniqueDocnum table contains an Identity column that I'm using as the unique identifier.
But when I try:
SELECT agsp_UniqueDocnum('TEST', 0)
It tells me that that's not the name of a FUNCTION. So I'm stuck in a catch 22. You can't use an INSERT inside a function, and you can't SELECT a stored procedure!
How can I get a unique identifier within a SELECT statement?
It must be all digits, no alpha, and no more than 16 characters in length.
The difficult we do right away...
...the impossible takes slightly longer.
You can "redirect" the output from the stored procedure into a table variable or temporary table and then select from that (or join the results if the select is meant to be more complex) - a bit of a kludge but works e.g.
I am using a software from our vendor. The software works well in some machines, but get an error "[FireDAC][Phys][IBLite]-314. Cannot load vendor library [ibtogo64.dll]" on other machines. All of these machines have 64 bit Windows 10 Enterprise OS. And the ibtogo64.dll file is present in the same folder where the software exe file is located, in all of the machines. I would really be grateful, if someone could point me to right direction on how to resolve this issue. Thank you.
Well there is no way anyone here can guess what the error could be. We have no idea what this library is for, what application you are using to access it, or what the error message means. So the first thing to do is to find out what that error message means, and work from there.
Last Visit: 31-Dec-99 19:00 Last Update: 31-Jan-23 17:15