I have the following SQL which works as expected: I’m trying to recreate these SQL in Jooq, but I don’t know how to create a Date object from the milliseconds I have as creation_date in my database. Answer A common confusion when writing SQL GROUP BY is the logical order of SQL operations. While s…
Tag: sql
How to perform mysql query with goal of returning 2 rows when a single record is found in
This will return one row with a result of: How would it be possible to return multiple rows for a given column while retaining the other column field values. Essentially, adding rows on the fly when the conditions are met. My thought process attempted: Desired Result: Answer Are you looking for union all? Or …
Stored Procedure error – incorrect syntax near else
I’m working on my first stored procedure, and it’s showing an error at the else (where I have a comment at the else below). If anyone knows how I can fix the error, I’d appreciate it. I’ve tried looking online, and I think it may have to do with my begin/end and if/else positioning. I&…
sqlite select hard coded text given a matching “or” condition
I have a table like this: I am querying for column values whose length is 0 using a statement like this: SELECT key FROM table WHERE length(c1) = 0 OR length(c2) = 0 OR … OR length(cn) = 0; I’d like to have a text associated with the key indicating which condition lead to the match. so something l…
How to set unique column values using an update from select statement
I am trying to achieve a 1:1 mapping between two tables based on their content. Unfortunately, the 1:1 mapping can not be perfect, as not both tables contain the exact same set of data. Anyways, I want to have the best possible match. Let the code talk in an example: My expected output would be something like…
Update row if exists or insert
I’m working on a web based PHP form that’s currently connect to an Oracle database. I’m trying to get the functionality working so that after a user submits the form it’ll check if a row exists based on the Job Number, if it does then update, otherwise insert the values. It’s bri…
TSQLT – Error while trying to fake a table
Hi I am pretty new for TSQLT Unit tests I am getting error while trying to create a fake table, any help is appreciated. I have my dev project in one database and TSQLT in another database EXEC …
Check constraint before insert a value into the database
I’m trying to create a table to store order_details. The table has a column status that should only be allowed to contain the value true once per each order_id, but may contain the value false multiple times per order_id. I want to do this within the table structure itself. Any help would be appreciated…
Impala: Split single row into multiple rows based on Date and time
I want to split a single row into multiple rows based on time. Expected output is below: Day start from 00:00 AM to next day 00:00 AM. When EndDate time is greater than 00:00 AM (midnight) then split this date in two rows. First row end date is 30/03/2020 11:59:00 and next row start 31/03/2020 00:00:00. Pleas…
Convert this SQL into eloquent ORM
I want to convert the following sql statement into eloquent: I did this as like below: The problem is that it is converting to the same sql but showing error. The error is shown below: * order_number, order_id, sum(quantity) as qty, sum(price) as pr from orders right join order_details on orders.id = order_de…