Manda QoP

DB2 is currently driving me insane. All the normal things you can do in SQLServer seem to be missing, PIVOT for example, which in SQLServer allows you to.... wait for it.... cross tab your queries, weird that eh?  Though if they'd been really creative about it then it could have caused your data to do a rendition of swan lake before settling down into a cross-tab; that I would pay to see.   Anyhoo, back to DB2 and the lack of PIVOT.  In order to achieve the same cross-tab result I have to jump into Excel, link my worksheet to a DB2 query, refresh the data in Excel, and then use Excel to perform the PIVOT.  How drawn out and painful is that?  Okay, whining over, back to the DB2 adventures.

Weird things with dates I have a simple sql script that extracts raw data from a quality centre database and extracts it to an excel spreadsheet, part of this is shown below: SELECT bg_bug_id as DefectID, bg_detection_date as RawDetectionDate, convert(varchar(10), bg_detection_date, 103) as FormatDetDate, bg_closing_date as RawClosingDate, convert(varchar(10), bg_closing_date, 103) as FormatCloseDate FROM BUG The issue I'm having is with the formatted dates. Some come through correctly and are left justified with the correct date, the others come through in US format and are right justified. When looking at the excel cell format, the left justified correct dates are showing as General format, and the incorrect right justified dates are Date format. Yes, I could just do the formatting on the RawDate in Excel which guarantees the correct date, and yes I could do some fancy malarky casting to a string and then back to a date but that...

As it's getting kinda large now, version 1.1. of the script is attached as a link on the right. Constructive comments/suggestions welcomed. Also, let me know if it's useful for you. :)

/*Version 1 of my script to fix all fragged indexes on a database */ /*Must be logged in as [sa] for this to work, if not, you will get permissions errors*/ /*You should be able to copy and paste this script in its entirity to your SQL 2005 or higher query window and run it without altering anything other than the USE .*/ /*Perform a 'USE ' to select the database in which to run the script.*/ USE AdventureWorks GO -- Make sure temp table doesn't exist IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmp_fraglist]') AND type in (N'U')) DROP TABLE [dbo].[tmp_fraglist] GO -- Declare variables SET NOCOUNT ON DECLARE @tablename NVARCHAR (255) DECLARE @indexname NVARCHAR (255) DECLARE @indextype NVARCHAR (255) DECLARE @execstr NVARCHAR (255) DECLARE @pagecounts INT DECLARE @frag DECIMAL DECLARE @reorgFragSize DECIMAL DECLARE @rebldFragSize DECIMAL DECLARE @numrecords INT DECLARE @rowcount INT DECLARE @errorMessage ...

We have Pat Martin from Microsoft presenting at the April meeting. The meeting will be at Level 7, Fronde House, 131 Queen Street starting at 6pm. Go to www.aucklandsql.com for more information and to register.