Hint: There is a column named “pma_timestamp” in table “table_a”, but it cannot be referenced from this part of the query

hi i want to use a user defined function so that while inserting values in column i want to insert last_day of the month in one column by referencing other column in same table.

my code is-

insert into table_A (id,pma_timestamp,monthend) values (103,’2017-08-15′,last_day(pma_timestamp));

but getting error. how to resolve this

Given a row sum vector and a column sum vector, determine if they can form a boolean matrix

For example, for a boolean matrix of size $ 3×4$ , the row sum vector $ R = (3, 3, 0, 0)$ and the column sum vector $ C = (2, 2, 2)$ form a match because I can construct the boolean matrix:

$ $ \begin{matrix} & \begin{bmatrix} 1 & 1 & 0 & 0\ 1 & 1 & 0 & 0\ 1 & 1 & 0 & 0 \end{bmatrix} & \begin{pmatrix} 2\2\2 \end{pmatrix} = C \ R = &\begin{pmatrix} 2 & 2 & 0 & 0 \end{pmatrix} \end{matrix} $ $

However, the column vector $ C’ = (4, 1, 1)$ doesn’t form a match with $ R$ .

So given two vectors whose values are sorted in descending order $ R_{1, w}$ and $ C_{h, 1}$ , and whose accumulated sum is the same, $ T = \sum_jR[1, j] = \sum_iC[i, 1]$ , how can I polynomically check if $ R$ and $ C$ form a matching because I can form a matrix $ M_{h,w}$ having $ R$ and $ C$ as row and column sum vectors?

More specifically, in case it can help to make the check algorithm faster, in my specific case, R and C has the following properties:

  • $ h \leq w$
  • The number of positive values of $ R$ and $ C$ is $ > w$ . For example, $ R$ , in the example, has two positive values and $ C$ has three positive values, and it happens that $ 2 + 3 > w = 4$ .

Match two distinct values from a single column in a joined table

I have three tables:

class: class_id class_name  student: student_id student_name  class_schedule: class_id student_id 

I want to select all the classes where studentA and studentB are in the same class using the student names. I can use a subquery to pull all the classes studentA is in, and then from that subset the classes that studentB is in and that works. That being said, it is terribly inefficient. I have tried a number of solutions including joining the same table twice, once for each value I want to find, but always get an empty result set.

For testing and prototyping purposes I am using sqlite, but will reside on DB2 long term.

Problem using ‘Regular expression’ in order to split characters of a column when there is no delimiter between them

I have a table with below structure:

create table TBL_TEST (   col_id   NUMBER,   col_name VARCHAR2(500) ) 

Some example data :

col_id | col_name    -----------------   1    | aetnap           2    | elppa          3    | ananab        

What I need to do is to split characters of column col_name for each col_id for example for col_id=1 we must have :

col_id | col_name    -----------------   1    | a   1    | e   1    | t   1    | n   1    | a   1    | p 

this query is fine when there is only one record in the table :

SELECT col_id, REGEXP_SUBSTR(col_name, '[a-z]{1}', 1, LEVEL) AS VAL   FROM tbl_test t CONNECT BY REGEXP_SUBSTR(col_name, '[a-z]{1}', 1, LEVEL) is not null 

but as soon as I insert another record in the table (say col_id=2 and col_id=3) I can not have the desired result. I want to know two things:

  1. Why is this query works fine for one record and it does not for more ?
  2. what is the best way to split the characters when there is no delimiter between them?

Thanks in advance

i want to count the number of occurrences of certain values of my TagName column

i want to count the number of occurrences of different sensor rows in SQL, but i seem to be doing it wrong and seemingly i am not visualizing it correctly.

if i was doing this in pseudocode in a c style language i would do it like this:

FOR i in range(taglist[i]):          print(taglist[i], count(taglist[i]) ) 

i have been trying this:

Select Count(a) FROM (     SELECT Distinct [TagName]          FROM [A2ALMDB].[dbo].[AlarmMaster]         where (TagName like '%Sensor%' or GroupName like'%Sensors%')     ) a 

it returns ’66’, but i want it to return the count of each of the distinct tagnames that are returned in Select A.

Can anyone help me with how i should be trying to get all the counts of my different sensor occurrences to total instead of a count of all the distinct tagnames?

thanks for the help!

MariaDB subqueries to same table and column resulting in several columns

I have a table and want to pick monthly minute data to compare column wize in 10.3.13-MariaDB

Tested and tested for hours and hours different approaches without success, one example is below. Some tests does not complain syntactically but takes forever, and some tests complains about column names not recognized. All subqueries if tested separately return the same number of records, each in one column.

`SELECT RD, OT1, OT2, OT3 FROM

(SELECT rdate from OO where month(rdate) = 7 and year(rdate) = 2006) AS RD,

(SELECT ot from OO where month(rdate)=7 and year(rdate) =2006) AS OT1,

(SELECT ot from OO where month(rdate)=7 and year(rdate) =2007) AS OT2,

(SELECT ot from OO where month(rdate)=7 and year(rdate) =2008) AS OT3;`

The result should be something like:

`RD OT1 OT2 OT3

2006-07-01 00:00:00 1.2345 2.1234 1.543

… … … …

2006-07-31 23:59:00 3.456 3.234 2.234`

And, no I dont want to use UNION because then they will still follow one after the other…

Any thoughts?!

UPDATE with variables to renumber column gives syntax error

Searching for a way to renumber a column within mysql, I’ve found multiple articles showing the same approach:

  • Renumbering an Ordering Field in MySQL
  • How to update a MySql column with ascending numbers

among others.

But trying it on my table I get a syntax error.

mysql> SET @rankStart = 10; mysql> SET @rankInc = 10; mysql> UPDATE fileFileTbl SET rank = (@rankStart := @rankStart + @rankInc) ORDER BY `rank` ASC; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank = (@rankStart := @rankStart + @rankInc) ORDER BY `rank` ASC' at line 1 

The command looks identical to the articles posted. What am I missing?

Here’s the table (with some columns omitted):

CREATE TABLE `fileFileTbl` (   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,   `fileEngineId` int(11) DEFAULT NULL,   `rank` int(11) DEFAULT '0',   `fileName` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT 'newfile',   PRIMARY KEY (`id`),   UNIQUE KEY `fileEngineId` (`fileEngineId`,`rank`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8; 

System is Centos 8, with mysql as:

# mysql --version mysql  Ver 8.0.17 for Linux on x86_64 (Source distribution) 

How to convert a bytea column to text?

how to convert a bytea column to text in PostgreSQL so that I can read the column properly in PGADMIN?

I have the following SQL query in the PGADMIN’s query editor:

SELECT event_type, created_at, encode(metadata::bytea, 'escape')  FROM public.events  ORDER BY created_at DESC LIMIT 100 

However, it produces an encoded column with each records more or less ressembling the following output:

3t000some_textd0some_other_textd00 

How can I get rid of this encoded, so that I only see the original value of the column, in the text format:

some_text some_data 

What I have also tried:

SELECT event_id, event_type, created_at, decode((encode(metadata, 'escape')::text), 'escape') FROM public.events ORDER BY created_at DESC LIMIT 100 

But in the above case, the query returns a decode column of type bytea and I only see the field [binary data] for each record of the column.

I have also tried the first two ansers mentioned here without success and can’t properly translate the last answer to my query.

Compute duration of an event based on consecutive values in another column

I need to compute the sum of durations of multiple events based on consecutive values in another column.

             ts             | w ----------------------------+---  2020-07-27 15:40:04.045+00 | t  2020-07-27 15:41:04.045+00 | t  2020-07-27 15:41:14.045+00 | f  2020-07-27 15:42:14.045+00 | t  2020-07-27 15:43:14.045+00 | t 

The event is considered being active as long as the column w has a consecutive value of true.

The duration of the first event would be 60 seconds. '2020-07-27 15:41:04.045+00' - 2020-07-27 15:40:04.045+00. The second event has the same duration. The sum of both would be 120 seconds.

What would be the best/most performant approach to computing these values? The longest time range we’ll be looking at will probably be half a year involving about 30 million rows.

I wrote a custom aggregate function that computes the duration but it takes about 16 seconds for only 1.5 million rows.

 Aggregate  (cost=444090.03..444090.04 rows=1 width=4) (actual time=16290.826..16290.828 rows=1 loops=1)    ->  Seq Scan on discriminator0  (cost=0.00..57289.03 rows=1547203 width=9) (actual time=0.016..1723.178 rows=1547229 loops=1)  Planning Time: 0.196 ms  JIT:    Functions: 3    Options: Inlining false, Optimization false, Expressions true, Deforming true    Timing: Generation 0.889 ms, Inlining 0.000 ms, Optimization 0.508 ms, Emission 6.472 ms, Total 7.870 ms  Execution Time: 16291.836 ms 

I’m new to SQL and basically just got this working through trial and error, so I’m sure there is room for improvement. Maybe even a whole different approach. Here’s a fiddle with the aggregate function.

I’m not sure if i should include the code because it’s quite long