I wrote following sql query in where condition doesn’t affect the following condition What I did mistake here? Answer You can use the IN operator in the WHERE clause like below;
Tag: sql
ORA-01756: quoted string not properly terminated, a part of my query it’s failing
Is there something bad in this Query? It’s not the full query it’s just a part of it. I’ts throwing this error: Answer Use the q-quoting mechanism. By the way, your code can be simplified to No need to to_char and then to_date current_date; it already is DATE datatype, just remove (truncate …
Two rows in two columns
I have table: Owner Book Date John Vile Bodies by Evelyn Waugh 2009-05-20 Tom Vile Bodies by Evelyn Waugh 2009-05-22 Jim Moab is my Washpot by Stephen Fry 2009-05-26 Kate Moab is my Washpot by Stephen Fry 2009-06-02 How can I get table: Owner Book Pick_Date Gave_Back_Book_Date John Vile Bodies by Evelyn Waugh…
Oracle sql, filling the missing values in the join between the table and the dictionary
A request for help in solving problem, example below on image: Example: Answer The data densification technique using “partition by” claude after an “outer join” clause comes very handy for that purpose. demo on db<>fiddle
SQL group by return null if one is null
I have orders table that have delete marker column date_deleted which value is null or date order_number item_id date_deleted 111 aaa null 111 bbb null 111 ccc 2021-x-x I have query to select the fully deleted orders with group by order_number It give me this result order_number date_deleted 111 2021-x-x Whic…
Limit number of occurrences of value in a table oracle sql
I have oracle sql table of students, students are assigned to groups. I have to set constraint so there will be no more than 10 students in a group (ideally no more than N students in a group). Basically restrict table from having more than N rows with the same GROUP_ID value. Here is table declaration. IR…
Using the ‘astar’ function of OrientDB: an SQL call in Java
I’m using OrientDB to represent large city maps and calculate the shortest traversal times between a pair of nodes when the need arises. I have the following method: The getNode(nodeID) method returns the OVertex object present in the database, hence we can identify the @rid. Clearly, this method serves…
Getting a Monthly Date Range
How can you make a date range in a big query? A date range starts from 29th of the month and ends with 28th of the next month. It should be like this Date | Starting Date | Ending Date 03-13-2020 | 02-29-2020 | 03-28-2021 06-30-2020 | 06-29-2020 | 07-28-2021 01-01-2021 | 12-29-2020 | 01-28-2021 11-11-2021 | 1…
BigQuery – Scalar subquery produced more than one element –
I have this query that helps me to find separate key words within strings (very useful with utm_campaign and utm_content): For example: if I have a umt_campaign like this: us_latam_mkt_google_black-friday_audiencie-custom_NNN-NNN_nnn_trafic_responsiv The query from above will help me to separate each word wit…
Xquery Get Consecutive Distinct Values
Trying to get consecutive distinct from the following XML. Expected Result : Current Result : Code : The distinct-values get distinct from the whole set, but I would like to get distinct values only if there are consecutive. Answer We have a pure XQuery solution. SQL Output