UDF in MS SQL Server returning a query starting with “with” cause

This is a perfectly valid SQL Query and works nicely (for e.g.) in Microsoft SQL Server 2014:

with t as (select 'a' as attr) select * from t 

it returns a, surely one can simplify it and drop the with part, but that’s not the point, the point is to find out a smallest example that illustrates the need/problem. I can pack part of it as a UDF:

CREATE PROCEDURE x(@__employee_id uniqueidentifier) AS RETURN (select 'a' as attr); 

How can I pack a query that has with block? My aim is to achieve something like:

CREATE PROCEDURE x (@__employee_id uniqueidentifier) AS RETURN (     with t as (select 'a' as attr) select * from t ); 

however sql server does not like it, firing an error:

Msg 156, Level 15, State 1, Procedure x, Line 4 [Batch Start Line 0] Incorrect syntax near the keyword 'with'. 

NOT EXISTS with two subquery fields that match 2 fields in main query

Background: Two different document types in a document management system. Both Doc Type A and Doc Type B have a Ticket #, and a Ticket Date. What we’re looking for: Doc Type A docs that don’t have a matching Doc Type B doc (NOT EXISTS) with the same Ticket # and Ticket Date. There like are Doc Type B docs that have the same Ticket # but NOT the same Ticket Date. We want to ignore those. Seems simple…. but I am stuck. So far what I have is something like this:

SELECT distinct ki110.keyvaluechar AS "Ticket #", ki101.keyvaluedate AS "Ticket Date"  FROM itemdata  left outer join hsi.keyitem110 ki110 on ( itemdata.itemnum = ki110.itemnum ) left outer join hsi.keyitem101 ki101 on ( itemdata.itemnum = ki101.itemnum ) WHERE   ki101.keyvaluedate BETWEEN '01-01-2021' AND '01-31-2021' AND ( itemdata.itemtypenum  = 178  ) -- this is Doc Type A  AND NOT EXISTS (select ki110.keyvaluechar, ki101.keyvaluedate from itemdata, keyitem110 ki110 , keyitem101 ki101 where --(itemdata.itemnum = ki110.itemnum) --Ticket # 

— ** the problem is here for Date: I need to say Date in Doc Type B doc is not the same as Date in Doc Type A doc using ki101.keyvaluedate)

AND itemdata.itemtypenum = 183) -- this  is DOC Type B 

Does mysql “order by” guarantees to give the same order for the same query every time if the sort key is equal?

I’m planning to write a query that sorts the result by a value s and then paginate the result. Let’s say I have ten items that match the query and all the items have the same s. In the first query, I sort the value by s and get the first five items. Then in the next query, I sort the value by s and get the sixth to tenth items. Is it possible that the items that appear on my first query will appear again in my second query, and that some items will not show up in either query?

Query to find the second highest row in a subquery

The goal is to send notifications about the customer updates but only for the first one if there are consecutive updates from the customer in a ticketing system.

This is the simplified query that I’m using to get the data that I need. There are a few more columns in the original query and this subquery for threads is kind of required so I can also identify if this is a new ticket or if existing one was updated (in case of update, the role for the latest threads will be a customer):

SELECT t.ref, m.role    FROM tickets t    LEFT JOIN threads th ON (t.id = th.ticket_id)    LEFT JOIN members m ON (th.member_id = m.id)   WHERE th.id IN ( SELECT MAX(id)                      FROM threads                     WHERE ticket_id = t.id                 ) 

It will return a list of tickets so the app can send notifications based on that:

+------------+----------+ | ref        | role     | +------------+----------+ | 210117-001 | customer | | 210117-002 | staff    | +------------+----------+ 

Now, I want to send only a single notification if there a multiply consecutive updates from the customer.

Question:

How I can pull last and also one before last row to identify if this is consecutive reply from the customer?

I was thinking about GROUP_CONCAT and then parse the output in the app but tickets can have many threads so that’s not optimal and there are also a few more fields in the query so it will violate the ONLY_FULL_GROUP_BY SQL mode.

db<>fiddle here

Recovery with Postgresql logical replication

I’m using Postgresql 13 on Debian 10.6 and learning about logical replication.

I’ve set up logical replication with one publisher and one subscriber of one table. I’m wondering what my options are for recovering data (or rolling back) when, for example, someone accidentally does something on the publisher side like updating all the data in the table with the wrong value, or even deleting everything from a table. With logical replication these unintentional changes will of course be applied to the subscriber.

I’ve relentlessly searched online but have had no luck finding out what my options are. I read about PITR but I’m thinking that’s suited more for physical replication, whereas I want to test rolling back changes on a specific database on a server.

PostgreSQL extended statistics

I have a 1 TB read-only database where performance is critical. It’s difficult to predict queries since they are dynamically generated by the users (the whole thing is basically a visualization platform atop a large collection of medical studies, and users select what they want to visualize). Queries can often be complex and involve 10+ joins. I recently learned about the extended statistics feature, but I find little information online about when best to use it (other than what’s in the documentation).

The DB is pretty well normalized, but makes extensive use of materialized views which are de-normalized. Is there any performance penalty or other issue with creating extended statistics (dependency and top n) for all pairwise columns? It would result in, say, 500 statistics on some 70 tables. Time for analyze or inserts is not of relevance, only read performance. Also, is there a tool or code snippet to help me do this?

I’m using Postgresql 12 and it’s optimized as far as possible w.r.t. indexing.

Pause and resume execution of stored procedure

There are two processes(p1, p2) that may run simultaneously. p2 is a scheduled execution of SPROC while p1 consists of a group of stored procedures that is triggered on the request. Execution of p1 while p2 is in progress can create issues. Only one should run at a time. There are three ways to solve this problem

  1. When p1 starts check whether p2 is in progress and wait until it completes. p2 can run for more than a day and it might not be a preferable solution
  2. Kill p2, complete p1 and then restart p2. Killing and restarting p2 isn’t safe due to nature of the sproc
  3. pause p2 and resume p2 when p1 completes

Questions

1. How can I search and stop store procedure being executed?

2. Is there any way I can pause and resume a stored procedure that is being executed?

conversion failed when converting datetime from character string while inserting and updating to database

I’m trying to insert time and date separately and calculate if it is late to the set time and then insert it to database…

here’s my try code for update

string Date = DateTime.Now.ToString("dd-MM-yyyy"); string Time = DateTime.Now.ToString("h:mm:ss tt"); SqlCommand comm2 = conn.CreateCommand(); comm2.CommandText = "Update Time_Logs SET Time_Out = ‘" + Time + "’ where Emp_Id = ‘" + EmpId.Text + "’ and Date = ‘" + Date + "’"; try { conn.Open(); comm2.ExecuteNonQuery(); MessageBox.Show("Time_Out…"); conn.Close(); TimeCompute(); } catch (Exception x) { MessageBox.Show(x.Message); conn.Close(); }

and here is the code for inserting

string Date = DateTime.Now.ToString("dd-MM-yyyy"); string Time = DateTime.Now.ToString("h:mm:ss tt");

        SqlCommand comm = conn.CreateCommand();         comm.CommandText = "INSERT INTO Time_Logs (Emp_Id, Date, Time_In) VALUES('" + EmpId.Text + "','" + Date + "','" + Time + "')";         try         {             conn.Open();             comm.ExecuteNonQuery();             MessageBox.Show("Time_In...");             conn.Close();             DateTime time = DateTime.Parse(Time);             DateTime inDate = DateTime.Parse("8:00:00 AM");             TimeSpan ts1 = inDate.TimeOfDay;             TimeSpan ts = time - inDate;             if (ts < ts1)             {                 SqlCommand comm2 = conn.CreateCommand();                 comm2.CommandText = "Update Time_Logs SET Late = '" + ts + "' where Emp_Id = '" + EmpId.Text + "' and Date = '" + Date + "'";                 conn.Open();                 comm2.ExecuteNonQuery();                 conn.Close();             }             else             {                 SqlCommand comm2 = conn.CreateCommand();                 comm2.CommandText = "Update Time_Logs SET Late = '" + ts + "' where Emp_Id = '" + EmpId.Text + "' and Date = '00:00:00'";                 conn.Open();                 comm2.ExecuteNonQuery();                 conn.Close();             }         }         catch (Exception x)         {             MessageBox.Show(x.Message);             conn.Close();         } 

Executing a query on the production to fix erroring data

During the creation of some data on the production server by our backend, an unexpected error occurred and some data became inaccessible. Currently, a fix is being found for the error, but the flawed data is still on the server, causing some trouble. This can easily be fixed by setting some data to NULL. The question is, how should we handle this? Our backend uses the Spring framework and Liquibase to update the schema when necessary. Our production server is managed by a different company and is unaccessible for us (the developers). The other company wants us to write an entry for the Liquibase XML file to fix this. We are questioning this decision, but running a query on the production database seems tricky too.

Note: we are running more than just the production environment, so by adding the query to the Liquibase XML file, it will run on all environments.