I have a requirement where I need to capture all failing validations in SQL database. I have below table: My validation rules are as follows: Department should be IT Age should be in 20s Age should not be 25 These are sample validation and my requirement is to capture all failed records along with failing con…
Tag: sql
Aggregate rows according to JSON array content
I have a PSQL table with json tags, that are always strings stored in a json array : I would like to query, for instance, the count of entries in the table containing each tag. For instance, I’d like to get : I tried but if does not work, since it gives I guess I need to get the list
Find entity with the biggest difference in a given column
I hava table profiles and I want to get the account with the most gained followers. I am using Postgresql. Note current record and previous record are all on the same table but with different timestamps. I have the logic in my mind but honestly I don’t know how to express/think of it in terms of a sql q…
passing sys_refcursor from function to sys_refcursor out parameter in procedure
I have problem with outputting results from the sys_refcursor returned from the function stored in the variable crs_scenarios. I then want to pass this collection of data to the output parameter pout_result. I get the error PLS-00487: Invalid reference to variable ‘POUT_RESULT’. Can you advise me …
Query not returning expected data
I’m looking for an explanation for why 1 of the following 3 queries aren’t returning what I am expecting. Assume the following: Anmodning with ANNo=1, ANCpr=1111112222 And the Person table doesn’t have a row with PSCpr=111111-2222 Queries are executed in Management Studio against a SQL Serve…
How do I specify a default value when the value is “null” in a spark dataframe?
I have a data frame like the picture below. In the case of “null” among the values of the “item_param” column, I want to replace the string’test’. How can I do it? df = sv_df….
Why SUM values are wrong in postgresql query?
I use postgresql and I have trouble in forming the correct query to get the desired result. I have a query And it gives me result as below. I wish to get the result like So I tried to SUM the third column and so changed the query a bit like this, But the result is like below, Fist this
How to add a ranking to a pyspark dataframe
I have a pyspark dataframe with 2 columns – id and count. I want to add a ranking to this by reverse count. So the highest count has rank 1, second highest rank 2, etc. testDF = spark.createDataFrame([(DJS232,437232)], [“id”, “count”]) I first tried using and this worked, ish. It…
Sum up all values from a list PLSQL
I want to sum up all values from a INDEX BY TABLE list. In the list there are values like this: 24000, 4500, 7890 and so on. This is the code: Answer Lets assume that you have an associated array and you want to sum up the values within it. And, lets also assume that the associative array’s index is
SQL: Select Rows which does not have a corresponding userID in second table
I have 2 tables below : Messages On this table, for a particular Message, if it has a type ‘Q’ it’s a question. Else it’s a conversation from 2 users. Conversation Table This keeps a track of conversations and has questionuserid and answer userid Now from the table above, I would like …