|
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.
|
|
|
|
|
You'll probably want to look into sequences, which is what SQL Server uses behind the scenes to implement identity columns.
Sequence Numbers - SQL Server | Microsoft Learn[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you. I'll check it out.
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.
Declare @Temp Table ([Id] [int])
Insert @Temp Exec agsp_UniqueDocnum 'TEST', 0
Select * from @Temp;
|
|
|
|
|
Thank you.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
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.
Dhyanga
|
|
|
|
|
Dhyanga wrote: right direction on how to resolve this issue.
Contact the vendor of the library.
|
|
|
|
|
I did. The vendor said it is my computer issue. I had someone from IT look into it, who said that there is no known issue.
Dhyanga
|
|
|
|
|
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.
|
|
|
|
|
Thanks for your input!
Dhyanga
|
|
|
|
|
If the vendor won't support their own product, which you have presumably paid for, then demand a refund and find a better alternative.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I struggled with this in the past, like 10 years ago, and it's been quite awhile since I've needed something like this. I'm trying to get the count, the number of records that meet this criteria. But I get an array of records because of the JOIN.I searched the internet, but didn't really see anything that came close, or the examples were very simple. I don't really need anything in SELECT, except the count. $designerId is PHP 8
SELECT
project.project_no,
COUNT(commission_summary.project_no)
FROM project
INNER JOIN commission_summary ON commission_summary.project_no = project.project_no
WHERE project.sales_no = '$designerId'
AND (project.status = 'construction' OR project.status = 'finished')
AND (commission_summary.startup_check_date is NULL OR CONVERT(char(10), commission_summary.startup_check_date, 120) = '1900-01-01' OR CONVERT(char(10), commission_summary.startup_check_date, 120) >= '2021-01-01')
AND (commission_summary.finished_check_date is NULL OR CONVERT(char(10), commission_summary.finished_check_date, 120) = '1900-01-01' OR CONVERT(char(10), commission_summary.finished_check_date, 120) >= DATEADD(month, -1, GETDATE()) )
GROUP BY project.project_no
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
You could use a CTE to identify the records that match then count. Bit hard to help without sample data though. Example:
;with cte as
(
SELECT distinct
p.project_no
FROM @project p
INNER JOIN @commission_summary cs ON cs.project_no = p.project_no
WHERE p.sales_no = @designerId
AND (p.status = 'construction' OR p.status = 'finished')
AND (cs.startup_check_date is NULL OR CONVERT(char(10), cs.startup_check_date, 120) = '1900-01-01' OR CONVERT(char(10), cs.startup_check_date, 120) >= '2021-01-01')
AND (cs.finished_check_date is NULL OR CONVERT(char(10), cs.finished_check_date, 120) = '1900-01-01' OR CONVERT(char(10), cs.finished_check_date, 120) >= DATEADD(month, -1, GETDATE()) )
)
select COUNT(*)
from cte
I would question all those CONVERTs - surely commission_summary.startup_check_date is a date not a string?
|
|
|
|
|
That's a pretty good idea, I'll give it a try.
I knew somebody would question the converts. It's a PHP program, and PHP8 doesn't support SmallDateTime very well. I'm rewriting a companies PHP4 app written from 2003 to 2012, and I didn't want to change the database because it has 26 gigs of data. I needed a consistent way of working with dates already written, so I choose the ISO120 format. ISO120 just makes it easier to fabricate new dates, and do comparisons in PHP8.
Let me try out your ideas.
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
Thanks @Chill60
Works like a champ!
I thought more about what you said about dates and strings. I've concluded that in PHP8, or really what's stored in the database (SQL Server) as a Date or DATETIME column, is just a string formatted to a particular ISO format, labeled as something special or unique. I could be wrong here, there really isn't much help or support available, and I'm on my own here with PHP8. I've seemed to have gone so far beyond the common PHP programmer, that I'm in uncharted waters.
This code gets a list of qualified swimming pool designers that have actual projects (Swimming Pools) to pay commission on, so they get their paycheck. I got tired of going through the entire pool of designers that didn't have projects to test with.
$designerId = (rtrim($row1[0]));
$query2 = "
WITH cte AS
(
SELECT distinct
project.project_no
FROM project
RIGHT JOIN commission_summary ON commission_summary.project_no = project.project_no<br />
WHERE project.sales_no = '$designerId'
AND (project.status = 'construction' OR project.status = 'finished')
AND (commission_summary.startup_check_date is NULL OR CONVERT(char(10), commission_summary.startup_check_date, 120) = '1900-01-01' OR CONVERT(char(10), commission_summary.startup_check_date, 120) >= '2021-01-01')
AND (commission_summary.finished_check_date is NULL OR CONVERT(char(10), commission_summary.finished_check_date, 120) = '1900-01-01' OR CONVERT(char(10), commission_summary.finished_check_date, 120) >= DATEADD(month, -1, GETDATE()) )
)<br />
SELECT COUNT(*) FROM cte";
$result2 = sqlsrv_query($conn, $query2) or die(" getDesignersWithProjectsKeyValuesByLastName " . LINE . " - " . $query2 . " - " . print_r(sqlsrv_errors()));
if (sqlsrv_has_rows($result2)) {
$row2 = sqlsrv_fetch_array($result2);
$projectCount = $row2[0];
if ($projectCount > 0) {
$keyValue = new KeyValue();
$keyValue->setKey(rtrim($row1[1]));
$keyValue->setValue(rtrim($row1[0]));
$keyValues->add($keyValue);
}
}
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
