I’m having trouble translating these Oracle date functions to BigQuery because BigQuery doesn’t allow such user-friendly additions to dates/timestamps. Any help is appreciated. Below are the two date functions in Oracle and my attempted (failed) versions in BigQuery: Oracle: BigQuery attempt: Answ…
Tag: oracle
SQL Query to Update the Start and End Date in a table from a different column where the dates do not match
I need a query which will Update the Start_Date and End_Date in this table. By Checking the Mapping column.When the value of date starts to change that should become the new start and end date. For example : The above two are same till the part 25-NOV-15-15-AUG-22. So our new Start_date for 1st record should …
Ignore Null Bind Variables in SQL
I am trying to create a dynamic SQL Statement which can be used to filter based on several parameters. But one of the caveats is that not all parameters will be available, so could 4 variables or could be 0. This is my attempt at creating this query (small example with only one variable): This however throws …
DELETE By Procedure With String Input – Oracle
I am writing a procedure to delete some records of z_names table by IDs from the input as string (and I need to seprate them by special character (comma ,)). Sample Input: ‘1,4’ z_names table: ID NAME 1 jim 2 john 3 jack 4 alex After running this procedure, the record with ID 1 and 4 (jim and alex…
Sql Join selecting records from A table and selecting matching records with a condition from B table
We have Table A and Table B, Data we need is in table A and we use table B to validate case where we have matching Ids. If ids do not match we can select it without validation, but if they match we need to check if date is in between date1 and date2. We only check records in B
Unsure of what to put in the GROUP BY statement
I have the following code However I am unsure what to put in the group by clause as I am receiving this error. ORA-00979: not a GROUP BY expression 00979. 00000 – “not a GROUP BY expression” *Cause: *Action: In the group by , do you have to put the calculation and casts etc. Any help appreci…
Compound trigger to insert into another table after insert or update into the main table
I’m working on a compound trigger and I’m having difficulties figuring out how to make it work. The trigger is on the person table. There is a column woman_act. If a new row is inserted and that column is not null than we need to insert that row into the v_changes table or if that column is update…
Oracle SQL – Splitting string every 70 characters without splitting a word and deriving sequence number
I currently working on a table on Oracle and I need to split one its field (RH02X) into multiple rows when it the field exceeds 70 characters but I also need to make sure that no word would be cut. I am only allowed to use SQL without PL/SQL. For example, I have the following data from the table test_remarks
Cycle detected while executing recursive query
I am using CTE recursive query to get below output but don’t know why it is throwing “cycle detected while executing recursive WITH query”. Can anyone please tell me where is wrong with my query? my query: SELECT order_id, product_id, quantity FROM cte; table/data script: Answer In the recur…
Data inserting in between the rows in table
I have a table which has around 1,000,000 records. I noticed that when I insert a record it does not get inserted at last instead it gets inserted in between Example My table (testing) Si. Name. Address. 1. Test. 1/2 test 2. Test. 3/4 test Si has a unique index created but no primary keys defined So when I ma…