|
I always use parens. Removes the question for me an future maintenance programmers as to what I thought the precedence was.
|
|
|
|
|
That was hard to describe.
Because of some bad business logic, or dealing with say vendors that retire or go out of business after 20 years, the question of what to do with them remains.
I'm thinking it's best to ask for the vendor anyways, and join any task if it exists. If it doesn't exist, then at least give me the vendor info, and the rest can be blank. So if I can get the VendorID, CompanyName and Vendor DeleteFlag even if the JOIN can't JOIN or the JOIN request doesn't exists, that would solve some issues for me.
I don't know what this is called, and the keywords I'm using don't bring up much except for NOT EXIST. I'm just looking for a point in the right direction or no it can't be done.
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'
If it ain't broke don't fix it
Discover my world at jkirkerx.com
modified 21-Nov-22 15:36pm.
|
|
|
|
|
But it would interesting to be able to do it.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Not sure if this is quite what you are looking for, but here goes:
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')
OR proj_cost.Vend_ID IS NULL
|
|
|
|
|
That works perfect actually!
I did not think of the OR clause to get a null join.
This opens up some new possibilities for me to solve some business logic in this program I'm writing, and you made me a better coder today as well.
I looked at the logic closer, and I already had the vendors in an object that I passed to this DB function, so I just looped the object and filled in the blanks if it existed and that solved my problem on the cost analysis report. But I have a huge business logic factory or class that can use this type of help when building a construction project.
Thanks for the lesson!
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
The problem is that the WHERE clause is filtering out records where the proj_cost columns are Null.
The cleanest solution is to move those filters to the join itself:
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'
Alternatively, use a sub-query or common table expression[^]:
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
Both approaches work by applying the filters to the proj_cost table before the join adds back any records from the vendor table which don't have a matching record.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
That's pretty clever. my mind was no where near thinking of it like that. And it runs pretty quick as well, and gives me exactly what I want with null records on a failed join. This opens up new possibilities for me with some of the business logic.
On a side note, I'm surprised that I didn't get laughed at for wanting to craft something like this. I couldn't think of a better way to handle this without writing a bunch of extra functions that might not be fast and reliable.
Thanks Richard!
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
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,<br />
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,<br />
proj_job.EST_COST,
proj_job.ACT_COST,<br />
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
|
|
|
|
|
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 jkirkerx.com
modified 21-Nov-22 17:22pm.
|
|
|
|
|
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,
proj_cost.pref_vendor asc 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
ENDis 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
|
|
|
|
|
Not many made a comment on this, so I wonder if my current method is sound or not. SMH 
Let's see if others chime in.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
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
ENDIt might do the same under the hood (not tested), but it looks more elegant.
|
|
|
|
|
I like that one! very simple.
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.
|
|
|
|
|
I think you mean 'Securities' (financial) rather than 'Security' (keeping everything safe)
|
|
|
|
|
I have two SELECT statements joined with a UNION keyword.
There's a problem if both SELECT statements return the same record thus causing it to appear twice in the result set.
Is there any easy way to make the two SELECT statements mutually exclusive, so that if a record appears in the first one, it should not appear in the second one?
SELECT Column1, Column2, (SUBQUERY1) [SOMEDATA]
FROM TABLE1
WHERE CRITERIA1 IS TRUE
UNION
SELECT Column1, Column2, (SUBQUERY2) [SOMEDIFFERENTDATA]
FROM TABLE1
WHERE CRITERIA2 IS TRUE
SOLUTION: (This solution brought to you by rubber duck debugging.)
SELECT Column1, Column2, COALESCE((SUBQUERY2), (SUBQUERY1)) [SOMEDATA]
FROM TABLE1
WHERE CRITERIA1 IS TRUE
OR CRITERIA2 IS TRUE
The difficult we do right away...
...the impossible takes slightly longer.
modified 29-Oct-22 11:31am.
|
|
|
|
|
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, CASE WHEN CRITERIA1 IS TRUE THEN (SUBQUERY1) ELSE (SUBQUERY2) END As SomeData
FROM TABLE1
WHERE CRITERIA1 IS TRUE
OR CRITERIA2 IS TRUE If you want to prioritize criteria 2 matches, then swap the case around:
CASE WHEN CRITERIA2 IS TRUE THEN (SUBQUERY2) ELSE (SUBQUERY1) END As SomeData
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi Richard, Thanks for your response. I should have specified that if NULL is returned from either of the subqueries, then the whole record should be ignored. I appreciate your analysis. Thanks!
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
Hi. You must explicitly use UNION ALL when you need duplicate.
If you using UNION already remove duplicates.
|
|
|
|
|
Howsabout...
SELECT Column1, Column2, (SUBQUERY1) [SOMEDATA]
FROM TABLE1
WHERE CRITERIA1 IS TRUE
UNION
SELECT Column1, Column2, (SUBQUERY2) [SOMEDIFFERENTDATA]
FROM TABLE1
WHERE CRITERIA2 IS TRUE
AND CRITERIA1 IS FALSE
|
|
|
|
|
Brilliant! I can't believe I didn't think of that. 
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
How would you implement the above? I found 'has' operator but it doesn't match against compounded patterns as above. Thank you
|
|
|
|
|
I thought you could use TSQL in Azure Synapse - in which case I would implement it exactly as you have shown.
Failing that, I believe there is a has_all operator
where
myString has_all (Pattern1, Pattern2)
|
|
|
|
|
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
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO ags_UniqueDocnum
( CreationDate,
DocumentType,
OrderId )
VALUES
( GETDATE(),
@DOCUMENTTYPE,
@ORDERID );
RETURN SCOPE_IDENTITY();
END 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.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
