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 …
Tag: sql
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 use DATEDIFF to return year, month and day?
How can I use DATEDIFF to return the difference between two dates in years, months and days in SQL Server 2005 How to result that: 2 year 3 month 10 day Can anyone complete this t-sql? Answer Here’s my solution to Eric’s function: Good call on the use of ABS to handle if the start date is after th…
SELECT SUM as field
Suppose i have this table table (a,b,c,d). Datatypes are not important. I want to do this select a as a1,b as b1,c as c1, (select sum(d) from table where a=a1 and b=b1) as total from table …
Determine a table’s primary key using TSQL
I’d like to determine the primary key of a table using TSQL (stored procedure or system table is fine). Is there such a mechanism in SQL Server (2005 or 2008)?
Selecting COUNT(*) with DISTINCT
In SQL Server 2005 I have a table cm_production that lists all the code that’s been put into production. The table has a ticket_number, program_type, program_name and push_number along with some other columns. GOAL: Count all the DISTINCT program names by program type and push number. What I have so far…