In R given a string like “fld1,fld2” how can I get something like “b.fld1=v.fld1 and b.fld2=v.fld2” for a SQL query. The string may have up to 10 distinct elements. This is for trying to build a join on the fly. b and v are the aliases for two tables. There willo only be two tables. An…
Problem with Concat results searching in MYSQL
Hi this is the query I’m trying to achieve results with but how can I get the search for concat results in that column? Answer You can’t refer to an alias defined in the select clause in the same scope (left apart the order by clause). In very recent versions of MySQL (8.0.14 or higher), you can u…
specific day of next month on a sql query
I have a complex (complex because it gave me lots of work to complete!) and it returns results that fulfill the query till the day 30 of the current month. Here is the code: I have a job that runs this query on the third monday of every month. What i need is that the query returns values not only
MYSQL – Fetching entities whose many-to-many relationship contains all terms collectively
I am using the following DB Schema and using something like this to add data/query it which returns How can I return recipes who’s ingredients are a subset of a list of ingredients provided? e.g I should not return Chocolate cake if list (egg, salt, sugar) is provided. However I should return Chocolate …
How to check json format in SQL?
I have a table with a column of type Nvarchar where json is stored. But the format of some of its columns is wrong. So I get an error when I parse them all. I want my query to ignore lines that have errors. It gives this error when I run on all lines: JSON text is not properly formatted.
Apply multiple validation to SQL records and identify all failing validations
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…
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…