Dev Stack Source

Answer : DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%e %b %Y') AS 'date_formatted' Convert timestamp to date in MYSQL Make the table with an integer timestamp: mysql> create table foo(id INT, mytimestamp INT(11)); Query OK, 0 rows affected (0.02 sec) Insert some values mysql> insert into foo values(1, 1381262848); Query OK, 1 row affected (0.01 sec) Take a look mysql> select * from foo; +------+-------------+ | id | mytimestamp | +------+-------------+ | 1 | 1381262848 | +------+-------------+ 1 row in set (0.00 sec) Convert the number to a timestamp: mysql> select id, from_unixtime(mytimestamp) from foo; +------+----------------------------+ | id | from_unixtime(mytimestamp) | +------+----------------------------+ | 1 | 2013-10-08 16:07:28 | +------+----------------------------+ 1 row in set (0.00 sec) Convert it into a readable format: mysql> select id, from_unixtime(mytimestamp, '%Y %D %M %H:%i:%s') from foo; +------+...

Answer : Use the convert function. SELECT CONVERT(varchar(10), field_name) FROM table_name Use the STR function: SELECT STR(field_name) FROM table_name Arguments float_expression Is an expression of approximate numeric (float) data type with a decimal point. length Is the total length. This includes decimal point, sign, digits, and spaces. The default is 10. decimal Is the number of places to the right of the decimal point. decimal must be less than or equal to 16. If decimal is more than 16 then the result is truncated to sixteen places to the right of the decimal point. source: https://msdn.microsoft.com/en-us/library/ms189527.aspx You can use CAST function: SELECT CAST(your_column_name AS varchar(10)) FROM your_table_name

Answer : Use the aggregate FILTER option in Postgres 9.4 or later: SELECT category , count(*) FILTER (WHERE question1 = 0) AS zero , count(*) FILTER (WHERE question1 = 1) AS one , count(*) FILTER (WHERE question1 = 2) AS two FROM reviews GROUP BY 1; Details for the FILTER clause: Aggregate columns with additional (distinct) filters If you want it short : SELECT category , count(question1 = 0 OR NULL) AS zero , count(question1 = 1 OR NULL) AS one , count(question1 = 2 OR NULL) AS two FROM reviews GROUP BY 1; Overview over possible variants: For absolute performance, is SUM faster or COUNT? Proper crosstab query crosstab() yields the best performance and is shorter for longer lists of options: SELECT * FROM crosstab( 'SELECT category, question1, count(*) AS ct FROM reviews GROUP BY 1, 2 ORDER BY 1, 2' , 'VALUES (0), (1), (2)' ) AS ct (category text, zero int, one int, two int); Detailed explanation...

Answer : Update: Since Django 2.1, everything works out of the box. No workarounds needed and the produced query is correct. This is maybe a bit too late, but I have found the solution (tested with Django 1.11.1). The problem is, call to .values('publisher') , which is required to provide grouping, removes all annotations, that are not included in .values() fields param. And we can't include dbl_price to fields param, because it will add another GROUP BY statement. The solution in to make all aggregation, which requires annotated fields firstly, then call .values() and include that aggregations to fields param(this won't add GROUP BY , because they are aggregations). Then we should call .annotate() with ANY expression - this will make django add GROUP BY statement to SQL query using the only non-aggregation field in query - publisher . Title.objects .annotate(dbl_price=2*F('price')) .annotate(sum_of_prices=Sum('dbl_price')) ....

Answer : Add an identity to your table variable, and do an easy loop from 1 to the @@ROWCOUNT of the INSERT-SELECT. Try this: DECLARE @RowsToProcess int DECLARE @CurrentRow int DECLARE @SelectCol1 int DECLARE @table1 TABLE (RowID int not null primary key identity(1,1), col1 int ) INSERT into @table1 (col1) SELECT col1 FROM table2 SET @RowsToProcess=@@ROWCOUNT SET @CurrentRow=0 WHILE @CurrentRow<@RowsToProcess BEGIN SET @CurrentRow=@CurrentRow+1 SELECT @SelectCol1=col1 FROM @table1 WHERE RowID=@CurrentRow --do your thing here-- END DECLARE @table1 TABLE ( idx int identity(1,1), col1 int ) DECLARE @counter int SET @counter = 1 WHILE(@counter < SELECT MAX(idx) FROM @table1) BEGIN DECLARE @colVar INT SELECT @colVar = col1 FROM @table1 WHERE idx = @counter -- Do your work here SET @counter = @counter + 1 END Believe it or not, this is actually more efficient and performant than using a cursor. ...

Answer : I think you need to add the endDelimiter clause in the Liquibase tag, based on the documentation found here Below is an example <changeSet author="newbie" id="function_rad2deg" dbms="mysql,h2"> <sqlFile encoding="utf8" path="sql/function_rad2deg.sql" relativeToChangelogFile="true" splitStatements="false" stripComments="false" endDelimiter="\nGO" /> </changeSet> Your SQL file with the above delimiter would then look like DROP FUNCTION IF EXISTS rad2deg; GO CREATE FUNCTION rad2deg(rad DOUBLE) RETURNS DOUBLE BEGIN RETURN (rad * 180 / PI()); END GO Hope this helps With above example there are two problems one is it will not work on h2 and 2 is splitStatements should be true: <changeSet author="me" id="01_functions_mysql" dbms="mysql"> <sqlFile encoding...

Answer : Try this: SELECT availablePlaces, capacity, ROUND(availablePlaces * 100.0 / capacity, 1) AS Percent FROM mytable You have to multiply by 100.0 instead of 100, so as to avoid integer division. Also, you have to use ROUND to round to the first decimal digit. Demo here

Answer : You can chain REPLACE functions: select replace(replace('hello world','world','earth'),'hello','hi') This will print hi earth . You can even use subqueries to replace multiple strings! select replace(london_english,'hello','hi') as warwickshire_english from ( select replace('hello world','world','earth') as london_english ) sub Or use a JOIN to replace them: select group_concat(newword separator ' ') from ( select 'hello' as oldword union all select 'world' ) orig inner join ( select 'hello' as oldword, 'hi' as newword union all select 'world', 'earth' ) trans on orig.oldword = trans.oldword I'll leave translation using common table expressions as an exercise for the reader ;) Cascading is the only simple and straight-forward solution to mysql for multiple character replacement. UPDATE table1...

Answer : CAST(timestamp_expression AS DATE) For example, The query is : SELECT CAST(SYSTIMESTAMP AS DATE) FROM dual; Try using TRUNC and TO_DATE instead WHERE TRUNC(start_ts) = TO_DATE('2016-05-13', 'YYYY-MM-DD') Alternatively, you can use >= and < instead to avoid use of function in the start_ts column: WHERE start_ts >= TO_DATE('2016-05-13', 'YYYY-MM-DD') AND start_ts < TO_DATE('2016-05-14', 'YYYY-MM-DD') Format like this while selecting: to_char(systimestamp, 'DD-MON-YYYY') Eg: select to_char(systimestamp, 'DD-MON-YYYY') from dual;

Answer : Just as the error says, the CREATE VIEW statement needs to be the only statement in the query batch. You have two option in this scenario, depending on the functionality you want to achieve: Place the CREATE VIEW query at the beginning CREATE VIEW showing as select tradename, unitprice, GenericFlag from Medicine; with ExpAndCheapMedicine(MostMoney, MinMoney) as ( select max(unitprice), min(unitprice) from Medicine ) , findmostexpensive(nameOfExpensive) as ( select tradename from Medicine, ExpAndCheapMedicine where UnitPrice = MostMoney ) , findCheapest(nameOfCheapest) as ( select tradename from Medicine, ExpAndCheapMedicine where UnitPrice = MinMoney ) Use GO after the CTE and before the CREATE VIEW query -- Option #2 with ExpAndCheapMedicine(MostMoney, MinMoney) as ( select max(unitprice), min(unitprice) from Medicine ) , findmostexpensive(nameOfExpensive) as ( select tradename from Medicine, ExpAndCheapMedicine ...

Answer : Those who are still looking for an alternate answer, a Clob object could be created without the need for a connection object as shown below. Clob myClob = new javax.sql.rowset.serial.SerialClob(stringData.toCharArray()); Throws warning: Clob not initialized. You need an OracleConnection to create a Clob, using the Oracle Database. OracleConnection conn; // initialize this first Clob myClob = conn.createClob(); private OracleConnection conn = null; public void setConnection( OracleConnection conn ) { this.conn = conn; } void setClob( String cookie ) throws SQLException { Clob myClob = conn.createClob(); myClob.setString( 1, cookie); } Try this : OracleConnection conn; // initialize this first CLOB clob = conn.createClob(); public Clob getClob(String data){ return clob.setString(position, data); }

Answer : The error Error on rename of ... errno: 150 - Foreign key constraint is incorrectly formed) happens because you are trying to drop a referenced primary key, even though you are disabling foreign key constraint checking with SET FOREIGN_KEY_CHECKS=0; Disabling foreign key checks would allow you to temporarily delete a row in the currency table or add an invalid currencyId in the foreign key tables, but not to drop the primary key. Changing a PRIMARY KEY which is already referenced by other tables isn't going to be simple, since you risk losing referential integrity between the tables and losing the relationship between data. In order to preserve the data, you'll need a process such as: Add a new Foreign key column ( code ) to each FK table Map the code foreign key from the previous currencyId via an update Drop the existing foreign key Drop the old currencyId foreign key column Once all FK's have been dropped, change the primary key on the...

Answer : Just add an app.config and not web.config because it is not a web application. And after that it's too simple, just add a reference to to System.Configuration and then use this. var ConnStr = ConfigurationManager.AppSettings["Trackboard"]; I've figured it out! I shouldn't have created a new config file. There is a default app.config file in the project. Now everything is fine. <?xml version="1.0" encoding="utf-8" ?> <configuration> <configSections> </configSections> <connectionStrings> <add name="Trackboard.Properties.Settings.TrackboardConnectionString" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\DATABASE\Trackboard.mdf;Integrated Security=True" providerName="System.Data.SqlClient" /> </connectionStrings> </configuration> private static string ConnStr = ConfigurationMana...

Answer : Solution 1: First of all, don't just take my word for it! Test my suggestion out with this: select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema' limit 10; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema' limit 10; If you feel good with the outcome of that, remove the limit clauses and save the output to an SQL script or, get fancy and pipe the output directly to mysql similar to what I demonstrate here. That would look like this: mysql -B -N --host=prod-db1 --user=admin --password=secret -e "select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema...

Answer : Use: SELECT SUM(CASE WHEN t.your_column IS NULL THEN 1 ELSE 0 END) AS numNull, SUM(CASE WHEN t.your_column IS NOT NULL THEN 1 ELSE 0 END) AS numNotNull FROM YOUR_TABLE t That will sum up the column NULL & not NULL for the entire table. It's likely you need a GROUP BY clause, depending on needs.

Answer : I think you may be after select jobID, JobName, sum(case when Priority = 1 then 1 else 0 end) as priority1, sum(case when Priority = 2 then 1 else 0 end) as priority2, sum(case when Priority = 3 then 1 else 0 end) as priority3, sum(case when Priority = 4 then 1 else 0 end) as priority4, sum(case when Priority = 5 then 1 else 0 end) as priority5 from Jobs group by jobID, JobName However I am uncertain if you need to the jobID and JobName in your results if so remove them and remove the group by, Using COUNT instead of SUM removes the requirement for an ELSE statement: SELECT jobId, jobName, COUNT(CASE WHEN Priority=1 THEN 1 END) AS Priority1, COUNT(CASE WHEN Priority=2 THEN 1 END) AS Priority2, COUNT(CASE WHEN Priority=3 THEN 1 END) AS Priority3, COUNT(CASE WHEN Priority=4 THEN 1 END) AS Priority4, COUNT(CASE WHEN Priority=5 THEN 1 END) AS Priority5 FROM TableName GROUP BY jobId, jobName IIF is not a standard SQL construct...

Answer : Without Converting you can find Maximum using this query select max(cast(stuff as decimal(5,2))) as mySum from test; check this SQLfiddle your demo table: create table test ( name varchar(15), stuff varchar(10) ); insert into test (name, stuff) values ('one','32.43'); insert into test (name, stuff) values ('two','43.33'); insert into test (name, stuff) values ('three','23.22'); Your Query: For SQL Server, you can use: select max(cast(stuff as decimal(5,2))) as mySum from test; I think you need to try doing something like this on your MySQL if you have admin privilege on your MySQL. ALTER TABLE tablename MODIFY columnname DECIMAL(M,D) for the M,D variables, read this - http://dev.mysql.com/doc/refman/5.0/en/fixed-point-types.html And MySQL should be able to automatically converting a text to a numeric. Just that the data type in MySQL might not be a decimal yet that's why you can't store any decimal. Be aware that...

Answer : Here is the best example of Cross Join and Inner Join. Consider the following tables TABLE : Teacher x------------------------x | TchrId | TeacherName | x----------|-------------x | T1 | Mary | | T2 | Jim | x------------------------x TABLE : Student x--------------------------------------x | StudId | TchrId | StudentName | x----------|-------------|-------------x | S1 | T1 | Vineeth | | S2 | T1 | Unni | x--------------------------------------x 1. INNER JOIN Inner join selects the rows that satisfies both the table . Consider we need to find the teachers who are class teachers and their corresponding students. In that condition, we need to apply JOIN or INNER JOIN and will Query SELECT T.TchrId,T.TeacherName,S.StudentName FROM #Teacher T INNER JOIN #Student S ON T.TchrId = S.TchrId SQL FIDDLE Result x--------------------------------------x | TchrId | TeacherName | StudentName | x----...

Answer : If you have an iterable in Python, to make a list, one can simply call the list() built-in: list(cursor.fetchall()) Note that an iterable is often just as useful as a list, and potentially more efficient as it can be lazy. Your original code fails as it doesn't make too much sense. You loop over the rows and enumerate them, so you get (0, first_row), (1, second_row) , etc... - this means you are building up a list of the nth item of each nth row, which isn't what you wanted at all. This code shows some problems - firstly, list() without any arguments is generally better replaced with an empty list literal ( [] ), as it's easier to read. Next, you are trying to loop by index, this is a bad idea in Python. Loop over values, themselves, not indices you then use to get values. Also note that when you do need to build a list of values like this, a list comprehension is the best way to do it, rather than creating a list, then appending to it. cursor = connnect_db()...

Answer : Simplify in a similar fashion to what you had in mind: DO $do$ BEGIN IF NOT EXISTS ( SELECT FROM pg_catalog.pg_roles -- SELECT list can be empty for this WHERE rolname = 'my_user') THEN CREATE ROLE my_user LOGIN PASSWORD 'my_password'; END IF; END $do$; (Building on @a_horse_with_no_name's answer and improved with @Gregory's comment.) Unlike, for instance, with CREATE TABLE there is no IF NOT EXISTS clause for CREATE ROLE (up to at least pg 12). And you cannot execute dynamic DDL statements in plain SQL. Your request to "avoid PL/pgSQL" is impossible except by using another PL. The DO statement uses plpgsql as default procedural language. The syntax allows to omit the explicit declaration: DO [ LANGUAGE lang_name ] code ... lang_name The name of the procedural language the code is written in. If omitted, the default is plpgsql . The accepted answer suffers from a race condition if two such scripts...