Here is the code: and the following function doesn’t compile: It says: PL/SQL: ORA-00947: not enough values Why is that? Answer Why is that? You are trying to put 3 values (and multiple rows) into a single variable. Use BULK COLLECT INTO and wrap the values in the object type: Given the sample data: The…
Tag: sql
simplifying postgres queries
Note: Asking to improve the working code. My data column is as follows In the above data, I want to set available to false and price to zero. To do this I Am using the below code. My question is it possible to update both values in a single query? Thanks. Answer Sure:
How to get the average of a column in MySQL
I’m trying to find a way to calculate the average of a column (as a double/float or decimal). I’ve found the AVG function in MySQL documentation but I can’t get it to work. Currently I’ve got the following code: Now for some reason this does not work. It returns “Unable to cast o…
Find value that is not a number or a predefined string
I have to test a column of a sql table for invalid values and for NULL. Valid values are: Any number and the string ‘n.v.’ (with and without the dots and in every possible combination as listed in my sql command) So far, I’ve tried this: The regular expression also matches the single charact…
Select different min dates BigQuery
I have a table with different values in BigQuery and I want to select the min or the max of these values. Answer You can use min/max with group by to get min or max date per ID group
How to format SQL Queries inside PySpark codefile
I would like to format my existing SQL queries inside the PySpark file. This is how my existing source file looks like: And this is how I wanted it to look like: I have already tried using black and other vscode extensions for formatting my code base but no luck since the SQL code is being treated as a python
i am trying to combine the PL/SQL and the SQL commands in manipulating the database. am i doing it correctly? any help and corrections?
enter image description hereuse PL/SQL with SQL commands in manipulating the database (PL/SQL structure, Data types, Variable, DBMS Output and Conditions) and this is my codes and what i have understandenter image description here Answer The sample code for me does not run. It gives The reason is that you hav…
Calculating totals and percentages for each row, in a time boxed window, for a relation
Ok, so I’ve got two tables: jobs, and job runs. I’m using Postgres. I want to look at 2 periods. 7 days ago until now, and 14 days ago to 7 days ago. For each job, I want a total of the number of runs, and a percentage of successful and unsuccessful runs for each period. I’ve cooked up this
SQL error 42P01 when using jsonb_to_recordset function
I’m trying to display the contents of the jsonb column using the jsonb_to_recordset function: But get the following error The function itself works when I substitute the values manually. Сan’t figure out what the problem is. Answer Write it as a FROM…JOIN:
Get data from database and write it back in again
I have written a function by reading an excel file and writing it to the database. This works! Getting data from the database also works. Where I get stuck is at the point that the function should read each row, calculate it and write the result back to the database. I also want to find out how many rows were