I develop against Oracle databases. When I need to manually write (not use an ORM like hibernate), I use a WHERE condition instead of a JOIN. for example (this is simplistic just to illustrate the style): I learned this style from an OLD oracle DBA. I have since learned that this is not standard SQL syntax. O…
Move SQL data from one table to another
I was wondering if it is possible to move all rows of data from one table to another, that match a certain query? For example, I need to move all table rows from Table1 to Table2 where their username …
How to order by maximum of two column which can be null in MySQL?
I want to do something like : maxof(Null,1000) should be 1000, How to implement the maxof? Answer If you know that salaryplus will always be greater than salaryminus, then you can do coalesce will return the first value which is not null, or (in this example) 0, if both values are null. Otherwise, do somethin…
How do I return the SQL data types from my query?
I’ve a SQL query that queries an enormous (as in, hundreds of views/tables with hard-to-read names like CMM-CPP-FAP-ADD) database that I don’t need nor want to understand. The result of this query needs to be stored in a staging table to feed a report. I need to create the staging table, but with …
Add a column to a DB2/400 table with a specific ordinal position
Is there an SQL command on the AS400/iSeries/System-i/whatever to add a column to a table in a specific ordinal position, or moving an existing column to a different position? Answer IBM i 7.1 now allows you to add a column in front of another. ALTER TABLE table ADD COLUMN colname … BEFORE othercolumn
GROUP BY behavior when no aggregate functions are present in the SELECT clause
I have a table emp with following structure and data: When I execute the following SQL: I get the following result: On what basis did the server decide return Jill and Fred and exclude Jack and Tom? I am running this query in MySQL. Note 1: I know the query doesn’t make sense on its own. I am trying to
Naming Database Tables and Views
I recently asked a colleague why they had included _TABLE at the end of all their database table names. They said it had been a standard at another orgainisation they had worked for. Other colleagues …
How can I select records ONLY from yesterday?
I’ve spent hours searching the web for an answer to this question… Here’s what I currently have: select * from order_header oh where tran_date = sysdate-1
How to calculate age (in years) based on Date of Birth and getDate()
I have a table listing people along with their date of birth (currently a nvarchar(25)) How can I convert that to a date, and then calculate their age in years? My data looks as follows I would like to see: Answer There are issues with leap year/days and the following method, see the update below: try this: O…
How to add ‘ON DELETE CASCADE’ in ALTER TABLE statement
I have a foreign key constraint in my table, I want to add ON DELETE CASCADE to it. I have tried this: alter table child_table_name modify constraint fk_name foreign key (child_column_name) …