how to get the full resultset from SSMS
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 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
The “Replace” function should do the trick. Based on your code above: DLookup(“island”, “villages”, “village=”” & Replace(txtVillage, “””, “””) & “‘”)
select sum(row_size) from ( select char_length(column1)+ char_length(column2)+ char_length(column3)+ char_length(column4) … <– repeat for all columns as row_size from your_table ) as tbl1; char_length for enum, set might not accurate, please take note
Seems like you expected the query to return running totals, but it must have given you the same values for both partitions of AccountID. To obtain running totals with SUM() OVER (), you need to add an ORDER BY sub-clause after PARTITION BY …, like this: SUM(Quantity) OVER (PARTITION BY AccountID ORDER BY ID) But … Read more
For the field to be able to store unicode characters, you have to use the type nvarchar (or other similar like ntext, nchar). To insert the unicode characters in the database you have to send the text as unicode by using a parameter type like nvarchar / SqlDbType.NVarChar. (For completeness: if you are creating SQL … Read more
I suspect you want to check that it’s after 11pm or before 7am: select * from MyTable where CAST(Created as time) >= ’23:00:00′ or CAST(Created as time) < ’07:00:00′
Use: SELECT DATE_SUB( LAST_DAY( DATE_ADD(NOW(), INTERVAL 1 MONTH) ), INTERVAL DAY( LAST_DAY( DATE_ADD(NOW(), INTERVAL 1 MONTH) ) )-1 DAY ) AS firstOfNextMonth, LAST_DAY( DATE_ADD(NOW(), INTERVAL 1 MONTH) )AS lastOfNextMonth
This is the syntax to insert into a table from a CTE: — CREATE TABLE tmp ( tmp_id NUMBER(10) ); INSERT INTO tmp( tmp_id ) WITH cte AS ( SELECT 1 AS tmp_id FROM dual ) SELECT tmp_id FROM cte;