oracle – AND clause with IN function to compare/validate values from subquery

Greeting,
In the hypothetical scenario, I have a procedure with with an update statement which works by updating the values of specified table with the variable inputs. However, I have five AND clauses with an IN comparison function to validate if the variable values exists with the tables specified in the subquery inside the parenthesis. The procedure executes as expected but no value are updated. The value from the variables exist in the tables specified in the subqueries. Without these five AND clauses, the procedure updates the values as expected but I need to make these validations. Please let me know what could be the issue or if these is a better way to perform this validation. Please also see the image for reference on the specific code. Thank you in advance.

DECLARE 
VAR1 DATE := TO_DATE('11/15/2019', 'MM/DD/YYYY');
VAR2 VARCHAR2(25) := '0000809349';
VAR3 NUMBER := 2017;
VAR4 NUMBER := 4;
VAR5 DATE := TO_DATE('11/18/2019', 'MM/DD/YYYY');

BEGIN

UPDATE MFT 
SET PD = VAR1,
    JI = VAR2,
    FY = VAR3,
    AP = VAR4,
    DPD = VAR5
WHERE RSN = 9097
AND VAR1 IN (SELECT PD FROM MEC)
AND VAR2 IN (SELECT JI FROM CGL)
AND VAR3 IN (SELECT FY FROM CGL)
AND VAR4 IN (SELECT AP FROM CGL)    
AND VAR5 IN (SELECT DPD FROM CGL)
AND PD = TO_DATE('06/09/2017', 'MM/DD/YYYY')
AND JI = '0000911979'
AND FY = 2017
AND AP = 12
AND DPD = TO_DATE('06/09/2017', 'MM/DD/YYYY');
COMMIT;
END;

enter image description here

group by – I want to perform max and sum aggregate on same column for different where clause in SQL server

I have a patient with 2 rows that have same admit and discharge dates and their admit flag is 1 and los is 14, 15 and other patient with 3 rows with different admit and discharge dates that have admit flag as 1 for all 3 rows and los 7, 11, 1 respectively for 3 rows. I want to aggregate this data and need one row per patient in the output but for 1st patient I want max(admit) = 1 and max(los) that is 15 and for 2nd patient I need admit as sum(admits) = 3 and sum(los) = 19. How can I achieve this?

Current code:
select a.LOB, a.year, a.key1, a.(age-band), a.prov_name, a.dx_catgy,
case when (DATEDIFF(DD,B.ADMIT_DT,A.ADMIT_DT) = 0 AND a.prov_billing_tin = b.prov_billing_tin ) THEN max(a.Admits)
ELSE sum(a.admits) end as Admits, case when (DATEDIFF(DD,B.ADMIT_DT,A.ADMIT_DT) = 0 AND a.prov_billing_tin = b.prov_billing_tin )
THEN max(a.LOS) ELSE sum(a.LOS) end as los, SUM(CAST(a.(30DayREADMIT) AS INT)) as READMIT
from #admits2 a
INNER JOIN #ADMITS2 b On
a.KEY1 = b.key1 and
— (a.rank – b.rank = 1 OR a.rank – b.rank = 0) and
a.lob = b.lob — and
group by a.LOB, a.year, a.key1, a.(age-band), a.prov_name, a.dx_catgy, a.admit_dt, b.admit_dt, a.prov_billing_tin, b.prov_billing_tin

enter image description here

How to Delete Multiple Rows With Multiple SELECT Statements In WHERE Clause in DB2

In the given inventory table below:

SKU COST DATE YYMMDD
202114000 0 210109
202114000 12 210228
202115000 50 210108
202115000 30 210109
202116000 54 210103
202116000 60 210301
202116000 100 210605

I want to retain SKU with the latest date and remove all others, and just retaining one SKU per row.

My query script goes:

SELECT *
FROM `inventory`
WHERE
    INUMBR IN (
        SELECT 
            SKU
        FROM
            `inventory`
        WHERE
            SKU IN (
                SELECT
                    SKU
                FROM
                    `inventory`
                GROUP BY
                    SKU
                HAVING
                    COUNT(*) > 1
                ORDER BY
                    SKU
            )
        GROUP BY
            SKU
        ORDER BY
            SKU
    )
    AND
    DATE IN (
        SELECT
            MIN (DATE) as DATE
        FROM
            `inventory`
        WHERE
            SKU IN (
                SELECT
                    SKU
                FROM
                    `inventory`
                GROUP BY
                    SKU
                HAVING
                    COUNT(*) > 1
                ORDER BY
                    SKU
            )
        GROUP BY
            SKU
        ORDER BY
            SKU
    )
ORDER BY
    SKU

However, this script is problematic. Why? because it will delete the two rows with the SKU 20211500 because, on the second IN criteria in the WHERE clause, two of DATEs will match: 210109 and 210108. How can I improve my query?

NOTE: The table is just an example, I will be dealing with millions of rows here.

query performance – is HAVING clause before GROUP BY clause processed differently than after?

Is there any difference how query is processed (translated to extended relational algebra), if HAVING condition is written before GROUP BY (compared to after)?
In logical query plan it should be initially upper in the tree (after GROUP BY) ? As far as I understand the topic(having selection should execute later in any case)? Is that correct?

I was translating some sql query to logical query plan (extended relational algebra) and in the query HAVING was before GROUP BY, which I thought might be incorrect, but on google I found it might be possible in some implementations.

mysql – Why does this sql join query take much longer using a reference to a row from the first table in the ‘on’ clause versus using a literal variable?

I have two tables, ‘auth’ and ‘ips’, which I’d like to join in a query.

Table ‘auth’:

column name data type Extra
id int AI
date varchar(6)
timestamp varchar(8)
result varchar(8)
ip varchar(15)
user text
service varchar(4)

No indexes.
Example row:

1 Aug 29 03:39:31 Failed 39.109.71.83 etherpad ssh2

Context: I have a program running locally which monitors the /var/log/auth.log file and inserts ssh login attempts into this table. Contains about 3,200 rows and counting.

Table ‘ips’:

column name data type
start int (unsigned)
end int (unsigned)
country text

Index for start and end (in that order), the table is also ordered by start (ascending) and rows start and end only contain unique values.
Example row:

16777216 16777471 United States of America

Context: this table stores IP ranges and the country to which it (very likely) is assigned to. IPs are stored as unsigned int as one would get from using INET_ATON. Contains 486,257 rows and is not added to anymore, only rarely updated in its entirety.

Now to the queries:
The query I’m currently using:
SELECT auth.date, auth.timestamp, auth.result, auth.ip, auth.user, ips.country FROM `auth` INNER JOIN ips ON (INET_ATON(auth.ip) >= ips.start AND INET_ATON(auth.ip) < ips.end) LIMIT 100;
This query takes approximately 9.7 seconds on my system, not even mentioning going through all 3200+ rows.

If I change the auth.ip references in the ‘ON’ clause to a static IP (let’s make one up: 200.200.200.200), we get the following query:
SELECT auth.date, auth.timestamp, auth.result, auth.ip, auth.user, ips.country FROM `auth` INNER JOIN ips ON (INET_ATON("200.200.200.200") >= ips.start AND INET_ATON("200.200.200.200") < ips.end) LIMIT 100;
This query returns in just 0.15 seconds.

  1. Why is the first query taking roughly 63 times longer than the second query?
  2. Can the first query be improved regarding performance?

If I missed something let me know, as it is my first post.

mysql – PAGER set to stdout when to add argument on select clause

Export all data in table into output file:

select * from mytest  into outfile  '/tmp/mytest.txt' ;

It works fine,i want to add some argument FIELDS TERMINATED BY ',' ESCAPED BY '' LINES TERMINATED BY 'n'.

select * from mytest into outfile  '/tmp/mytest.txt' 
FIELDS TERMINATED BY ',' ESCAPED BY ''  LINES TERMINATED BY 'n' ;

Click enter,it seems something can’t be parsed properly

PAGER set to stdout
    '> 

How can fix it?

sharepoint online – Graph SDK C# – PageIterator.IterateAsync() – Invalid filter clause

I am calling Graph with the SDK in C#, my initial request works fine and I get 200 results but the iterator returns an error with an invalid filter clause.

The initial call with the filter works perfectly am I calling the iterator correctly?

DateTime date = DateTime.Now.AddDays(-25);
Date graphDate = new Date(date.Year, date.Month, date.Day);
string weekendingFilterdate = graphDate.ToString();

string timesheetItemFilterString = $"Fields/FridayDate eq '{weekendingFilterdate}'";
var resultsList = new List<ListItem>();
        try
        {
            var results = await _graphServiceClient.Sites(siteName).Lists(listName).Items.Request().Expand("fields").Filter(filterString).GetAsync();
            var pageIterator = PageIterator<ListItem>.CreatePageIterator(_graphServiceClient, results, (i) => { resultsList.Add(i); return true; });
            await pageIterator.IterateAsync();
        }
        catch (Exception ex)
        {
            _log.LogInformation(ex.Message);
        }
        return resultsList;



Error = {Code: BadRequest
Message: Invalid filter clause
Inner error:
    AdditionalData:
    date: 2021-08-10T08:15:21
    request-id: 3fd61829-ab4e-4acc-bd37-f9064a0296f2
    client-request-id: 3fd61829-ab4e-4acc-bd37-f9064a0296f2
ClientRequestId: 3fd61829-ab4e-4acc-b...

select – SQL Distinct with Inner join where clause

Have a stored procedure that selects distinct data from two tables using an inner join with multiple where clauses and order by for sorting the records. Gives Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ‘ ‘.Could not figure out whats wrong with my query. Any help would be appreciated . Thanks

create PROCEDURE (dbo).(ProcedureName)

@Testvalue nvarchar(50)

AS
    select Distinct (LabAccepedTests.TestID),
    LabAccepedTests.Department ,LabAccepedTests.Code,
    LabAccepedTests.TestName ,LabAccepedTests.SubUnit,LabAccepedTests.Rate 
    
    from LabAccepedTests
    
    inner join LabTests 
    ON LabAccepedTests.TestID =  LabTests.ID 
    
    where LabAccepedTests.GroupType='No' 
    and LabTests.Category <>'TESTDELETED'
    and LabAccepedTests.TestName like + '%'+@Testvalue + '%'
    
    ORDER BY  TestName ASC;

RETURN

mysql – shortening two subqueries into one subquery inside FROM clause

this query is correct but if there’s someone that could shorten this query where it will only have one sub query as derived table or a subquery inside FROM clause, been stuck for 3 hours or so trying to shorten the query.

SELECT
    COUNT(DISTINCT p.patient_id) AS Patient
    
FROM 
    patient p
    INNER JOIN transactions_detail td ON p.`patient_id` = td.`patient_id`
    INNER JOIN transactions_header th ON td.`tran_num` = th.`tran_num`
WHERE
    
    (p.`patient_id` NOT IN
        (SELECT 
            t_d1.patient_id
        FROM
            
            transactions_detail t_d1,
            transactions_header t_h1
        WHERE
            t_d1.`tran_num` = t_h1.`tran_num`
            AND t_h1.`tran_date` <= "2016-12-31"
            AND t_h1.`impacts` = "P"
        )
    OR 
    p.`patient_id` IN
        (SELECT 
            t_d2.patient_id
        FROM
            transactions_detail t_d2,
            transactions_header t_h2
        WHERE
            t_d2.`tran_num` = t_h2.`tran_num`
            AND t_h2.tran_date <= DATE_SUB("2017-01-01", INTERVAL 3 YEAR)
            AND t_h2.`impacts` = "P"
        )   
    )
    AND th.`tran_date` BETWEEN "2017-01-01" AND "2017-12-31"
    AND th.`impacts` = "P"

and this is what I am trying to work on to shorten the query up above

SELECT
    COUNT(DISTINCT p.`patient_id`) AS Total
    
FROM 
    patient p
    INNER JOIN transactions_detail AS td ON p.`patient_id` = td.`patient_id`
    JOIN (SELECT 
            t_d.patient_id AS id,
            t_h.`tran_date` AS t_date,
            t_d.`tran_num` AS t_num
        FROM
            
            transactions_detail t_d,
            transactions_header t_h
        WHERE
            t_d.`tran_num` = t_h.`tran_num`
            AND t_h.`impacts` = "P"
    )AS q1 AND p.`patient_id` = q1.id
WHERE
    (q1.t_date <= "2016-12-31"
    OR
    q1.t_date <= DATE_SUB("2016-12-31", INTERVAL 3 YEAR))
    
    AND q1.t_date BETWEEN "2017-01-01" AND "2017-12-31"
    
    

mysql 5.7 – IN clause too slow compared to multi-queries

select calendar.slot_id 
from partner_availability calendar 
left outer join partner_bookings pb 
    on  pb.partner_team_id = calendar.partner_team_id
    AND (pb.slot_id = '0' OR pb.slot_id = calendar.slot_id) 
    AND pb.date >= '2021-07-25' 
    and pb.date < date_add('2021-07-25', INTERVAL 1 DAY)       
where working_days & 64 > 0 
  and calendar.partner_team_id IN ('f173', 'f224', 'f602')
  AND pb.date is null;

The above takes 3 seconds


select distinct(calendar.slot_id) 
from partner_availability calendar        
left outer join partner_bookings pb  
    on  pb.partner_team_id = calendar.partner_team_id 
    AND (pb.slot_id = '0' OR pb.slot_id = calendar.slot_id) 
    AND pb.date >= '2021-07-25' 
    and pb.date < date_add('2021-07-25', INTERVAL 1 DAY)       
where working_days & 64 > 0 
  and calendar.partner_team_id = 'f173' 
  AND pb.date is null

UNION ALL

select distinct(calendar.slot_id) 
from partner_availability calendar        
left outer join partner_bookings pb 
    on  pb.partner_team_id = calendar.partner_team_id 
    AND (pb.slot_id = '0' OR pb.slot_id = calendar.slot_id) 
    AND pb.date >= '2021-07-25' 
    and pb.date < date_add('2021-07-25', INTERVAL 1 DAY)       
where working_days & 64 > 0 
  and calendar.partner_team_id = 'f224' 
  AND pb.date is null

UNION ALL

select distinct(calendar.slot_id) 
from partner_availability calendar        
left outer join partner_bookings pb 
    on  pb.partner_team_id = calendar.partner_team_id 
    AND (pb.slot_id = '0' OR pb.slot_id = calendar.slot_id) 
    AND pb.date >= '2021-07-25' 
    and pb.date < date_add('2021-07-25', INTERVAL 1 DAY)       
where working_days & 64 > 0 
  and calendar.partner_team_id ='f602' 
  AND pb.date is null;

This takes <0.01 seconds. The difference is second one is using index on partner_team_id and first one isn’t. How can I rewrite this in a more readable form. This union all solution isn’t practically usable, I would rather use multi-queries.