I’m trying to optimise my query for when an internal customer only want to return one result *(and it’s associated nested dataset). My aim is to reduce the query process size. However, it appears to be the exact same value regardless of whether I’m querying for 1 record (with unnested 48,000…
Tag: sql
Liquibase coming with Spring Boot handles the database well with changelog.xml. How can it generate its sql at the same time?
I’m using the default Liquibase configuration coming with Spring Boot. I have: a changelog.xml file in src/main/resources/db/changelog that works fine, along with url, changeLogFile, driver, username, password properties in application.properties, and a Maven pom.xml having this for <dependency> a…
How to delete defined value from the begining of character in Oracle SQL?
I have table in Oracle SQL like below: So I have in col1: always “ABC|” at the begining of each value different length of values after “ABC|” I need result like below, so I need to delete “ABC|” from begining of each value How can I do that in Oracle SQL ? Answer A simple s…
Accept parameters in the combination of columns for retrieving many records
Table1 has 4 columns = Col1 , Col2 , Col3 , Col4 Col1 is id(integer) column = 156 , 159 , 175 Col2 is varchar column = CAA , DFE , EME Col3 is varchar column = 12345 , 23465 , 43122 Col4 is varchar column = 2 , 3 , 6 I am creating a stored procedure which accepts
Calculate conversion rate with the specified conditions
Here is my sample data table: ID Status 1 New 1 Processed 2 New 2 Processed 3 New 3 Processed 4 Processed 5 New What I am trying to solve here is calculate the conversion rate from Status ‘New’ to Status ‘Processed’. From the dataset, only ID no.1,2 and 3 fulfilled the requirements of …
condition where a ‘ ‘ and a ” not working
I wrote a SQL to query table mat from an oracle db where column A is not null. Column A is varchar and its default value is ‘ ‘. I wrote the sql below: But it return an empty data set. Then I ran: This query worked. So what is the reason? Thx. Answer In Oracle, ” means NULL. Any
Create hierarchical summary of XML nodes
Is there a way of using SQL or Python (or another language) to return the node-tree for an XML document? In the XML document example below, the employee node appears twice but the second time it has more nodes within it… same with the address node. Is there a way to return the structure (without values …
SAME SQL regexp_extract, different impala and hive output. Why?
The same SQL command has two different output on Hive and Impala: Hive output: ff Impala output: ffff Why such difference? Please explain difference in terms of each engine’s method of processing and outputting characters space-by-space, from left to right or right to left, step by step, and the reasoni…
How to return the count of a table created and dropped within a SQL stored procedure in Snowflake?
I am trying to use a variable to store the count in a temporary table created within a stored procedure in Snowflake so that I can include the value in the return statement. When I try to do a select count(*) from the table I get SQL compilation error: Object ‘CDP_SMS.DOMAIN_CANONICAL.TEMP_DELTA_MANUFAC…
Extracting timestamp from timestamp with time zone Presto
Is there a native Presto function that provides support to extract the timestamp from a timestamp with time zone? Taking something like this Which returns a value of: 2022-03-13+02:00:99 UTC To: 2022-03-13+02:00:99 I couldn’t find information in the docs for this kind of support. It seems as though my o…