If you get error:error no 1242 Subquery returns more than one row, try to put ANY before your subquery. Eg: This query return error: SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2); This is good query: SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);
Maybe I don’t understand the question, but wouldn’t this do it? (you must know what you want back) INSERT INTO some_table (…) VALUES (…) RETURNING some_column_a, some_column_b, some_column_c, … INTO :out_a, :out_b, :out_c, … @Vincent returning bulk collect into for multi-row insert works only in conjunction with forall (in another words if you insert from … Read more
The window functions in SQL 2008 are rather limited compared to later versions and if I remember correct you can only partition and you can’t use any rows/range frame limit but I think this might be what you want: ;WITH cte (rn, transactionid, value) AS ( SELECT rn = ROW_NUMBER() OVER (ORDER BY transactionid), transactionid, … Read more
Converting a subquery into a JOIN can be pretty straightforward: IN clause FROM TABLE_X x WHERE x.col IN (SELECT y.col FROM TABLE_Y y) …can be converted to: FROM TABLE_X x JOIN TABLE_Y y ON y.col = x.col Your JOIN criteria is where you have direct comparison. EXISTS clause But there are complications when you look … Read more
You might want to try this: Count the number of working days (took it from here) SELECT 5 * (DATEDIFF(‘2012-12-31’, ‘2012-01-01’) DIV 7) + MID(‘0123444401233334012222340111123400012345001234550’, 7 * WEEKDAY(‘2012-01-01’) + WEEKDAY(‘2012-12-31’) + 1, 1) This gives you 261 working days for 2012. Now you need to know your holidays that are not on a weekend SELECT … Read more
First and foremost don’t use null in your Scala code unless you really have to for compatibility reasons. Regarding your question it is plain SQL. col(“c1”) === null is interpreted as c1 = NULL and, because NULL marks undefined values, result is undefined for any value including NULL itself. spark.sql(“SELECT NULL = NULL”).show +————-+ |(NULL … Read more
I cast it to XML select @variable_with_long_text as [processing-instruction(x)] FOR XML PATH The processing-instruction bit is there to stop it entitising characters such as < to <
I may be a little off on the syntax, but this parameterized query (all the ? take the ‘1’ of the original question) should run fast, basically 2 B-Tree lookups [assuming number is indexed]. SELECT * FROM ( (SELECT id, number FROM t WHERE number >= ? ORDER BY number LIMIT 1) AS above UNION … Read more
This should do the trick: WITH CTE AS ( SELECT random() * (SELECT SUM(percent) FROM YOUR_TABLE) R ) SELECT * FROM ( SELECT id, SUM(percent) OVER (ORDER BY id) S, R FROM YOUR_TABLE CROSS JOIN CTE ) Q WHERE S >= R ORDER BY id LIMIT 1; The sub-query Q gives the following result: 1 … Read more
SQLAlchemy’s filter_by takes keyword arguments: filter_by(**kwargs) In other words, the function will allow you to give it any keyword parameter. This is why you can use any keyword that you want in your code: SQLAlchemy basically sees the arguments a dictionary of values. See the Python tutorial for more information on keyword arguments. So that … Read more