I am using AWS Athena, so functions are a bit limiting. But essentially I want to extract the first 5 consecutive and sequential numbers from a alphanumeric field. From the first example, you can see it ignores the first 1 because there aren’t 4 trailing numbers. I want to find and extract the first 5 n…
Tag: sql
How to concatenate two different values from two different columns with comma ” , ” using TSQL?
I would like to know how to concatenate two different values from two different columns from a SQL table using TSQL ? As you can see, I would like to concatenate those two different columns X e Y, resulting in the follow column table: Which query should be used here ? Answer You can use concat as
List which has no reports submitted
I have two tables in MySQL database. tbl_sites (similarly around 3500 sites) tbl_reports (Similarly multiple reports from each sites) I want to get the list of sites by site name, where the report count is still zero. A report is considered as successfully submitted when the report_status = 2. Answer Using ex…
How find last number of sequence numbers in sql?
I want find last number of sequence in sql query for fill automatically suggestion field value. forexample my code field(column) is :1,2,3,4,10,20 so i want found 4 in my query Answer If your table is called table_name and looks like this: id 1 2 3 4 10 20 Then this should work: Fiddle
Insert Procedure showing compilation error (Oracle)
I am using Oracle 12c and a certain requirement needs me to prepare an insert procedure. I am not very familiar with procedures so would you please tell me what’s wrong? Table statement: Procedure: I am getting ora-00900 invalid sql statement and ora-24344 success with compilation error Answer I’d…
How to pass a list of strings as a parameter in a stored procedure in SQL?
How to pass a list of strings as a parameter in a stored procedure in SQL? Like for example if I have a stored procedure proc_aggregation that takes two parameters @Prod_Desc of varchar data type and another parameter @Prod_Code as a list of strings (Eg : @Prod_Code = (‘12012’, ‘12011’…
how to find the maximum occurence of a string in Oracle SQL developer
i have 2 columns in a table. Data looks like this The output should give the folio_no along with the flag which occured maximum number of times for that particular folio number. i tried doing the below but it throws an error select folio_no, max(count(flag)) from table group by folio_no; Answer We can use an …
Idempotent record creation: is it better to use a unique constraint or check for existence before inserting a record?
I’ve recently been wondering what’s generally the best approach for ensuring that the creation of database records is idempotent. The two approaches I can think of are: Checking whether a record already exists before executing an INSERT Using a unique constraint on the relevant columns to insure t…
How to SUM all subchildren tree in SQL recursively?
Good day, I’ve been pulling my hair on this problem for a while ><” I have 4 categories in a tree structure. tenant_category_transaction_view: I would like to have the sum of all children “sumSubtotal” on every category Something like that: I’ve managed to come very close&#…
SQL – combined SELECT queries and getting a % output
I am using SQLiteStudio and I am trying to run the following query. However, it isn’t returning a value. Can anyone help, please? I have been asked to provide some of the data. Below I have included how I created my 3 tables and then some sample data for each section. Hopefully, this is enough but feel …