sql

Handling nulls in Datawarehouse

Hi all, I'd like to ask your input on what the best practice is for handling null or empty data values when it pertains to data warehousing and SSIS/SSAS. I have several fact and dimension tables that contain null values in different rows. Specifics: 1) What is the best way to handle null date/times values? Should I make a 'default' ...

Getting a Chunk of Work

Recently I had to deal with a problem that I imagined would be pretty common: given a database table with a large (million+) number of rows to be processed, and various processors running in various machines / threads, how to safely allow each processor instance to get a chunk of work (say 100 items) without interfering with one another?...

SQL WHERE excluding records using AND and NOT

Here's a query I'm working on: SELECT TBL_SUB_KEY AS port , poe.[TBL_COMPANY] , poe.[TBL_DIVISION_1] FROM dbo.TMVKTAB AS poe WHERE ( TBL_NUMBER = '8A' ) AND ( TBL_SUB_KEY <> '' ) AND ( poe.[TBL_COMPANY] <> '011' AND poe.[TBL_DIVISION_1] <> '11' ) What I want returned are all ...

SQL Query to get a row, and the count of associated rows

I have two tables, like this: #Articles: ID | Title 1 "Article title" 2 "2nd article title" #Comments: ID | ParentID | Comment 1 1 "This is my comment" 2 1 "This is my other comment" I've always wanted to know, what is the most elegant way to get the following result: ID | Title | NumComments 1...

How do I make DBIx::Class join tables using other operators than `=`?

Summary I've got a table of items that go in pairs. I'd like to self-join it so I can retrieve both sides of the pair in a single query. It's valid SQL (I think), the SQLite engine actually does accept it, but I'm having trouble getting DBIx::Class to bite the bullet. Minimal example package Schema; use parent 'DBIx::Class::Schema';...

SQL simplify a characteristic function? or PIVOT tables?

I have this a Table with a bunch of dates and prices: Room Name, Price, Bookdate, etc And I can transform it like so: (which essentially flips the columns) SELECT availables.name, rooms.id, MAX(IF(to_days(availables.bookdate) - to_days('2009-06-13') = 0, availables.price, '')) AS day1, MAX(IF(to_days(availables.bookdate) - to_days...

SQL: how to find a complement to a set with a derived function/value

This one has me stumped, so I'm hoping someone who's smarter than me can help me out. I'm working on a rails project in which I've got a User model which has an association of clock_periods joined to it, having the following partial definition: User has_many :clock_periods #clock_periods has the following properties: #clock_i...

sql and xml

Hi there, I try to find out to export data from a table to xml file. Here is the situation Table1 contractID subcontractid ContractName ContractType 123 John Electrical 123 1 John Comercial 123 2 John Resi...

How to create a file system directory in a SQL Server 2005 call/query

I need to create a directory for sql server backups. I have to dynamically place the backup file in different folders on many different sql server installs. The backup fails if the directory doesn't exist. So if I can create the directory in a stored procedure or some other call before I call the backup command that would be helpful. Any...

how to union record from another table with only different id by "Union All"

I have 2 tables with duplicate record including 'Id' field, and I need union them. But I think [union all] is much more efficient than [union] for the reason that it need not sorting. Following is my sql: SELECT * FROM [dbo].[RealTime] UNION ALL SELECT * FROM [dbo].[Query] a WHERE NOT EXISTS(    SELECT TOP 1 1    FROM [dbo].[RealTime]...

General SQL model optimization question (MySql)

Hi everyone! I need to know if there's a best way to optimize this kind of DB model : Here's my tables : [category] idCategory name [postCategory] (a post can be in more than 1 category) idCategory idPost [post] idPost post [comment] idComment idPost inputDate comment I'm going to have to display all the posts, from a specific ca...

Do I want a degree in CS or MIS?

I'm a junior right now and have been thinking lately is CS really the route for me or would MIS be the better route? I've been lucky enough to been taken into a development team and it's given me some insight and quite frankly I know I don't need a CS degree for what I currently do, or maybe it's not that I don't need a CS degree, may ...

InTime and OutTime for the modified date?

Using SQL Server 2000 Table CARDNO CARDEVENTDATE CARDEVENTDATE CARDEVENTTIME ADJUSTED 0121 20090611 20090610 025050 0121 20090611 20090611 040000 0121 20090611 20090611 050000 0121 20090612 20090611...

SQL Server Integration Services Package Deployment Wizard Error

I am trying to deliver an SSIS package to SQL Server 2005 Enterprise Edition running on Windows Server 2003 R2 (64bit). The package itself is quite simple, just a basic read/write of a MySQL table (testing the integration). I am able to build and deploy the package on my development machine, and I'm able to debug & build the package o...

How can I copy data using T-SQL?

I have tables named Categories, Questions and Selections. The relationship of the tables are: there can be 1 or more Selections in a Question and there can be one or more Questions in a Category. The table columns are the following: Categories - CategoryID (pk) - Name Questions - QuestionID (pk) - Question - Cat...

SQL decode on column4 but only when column5 is distinct

Hi folks, I need to change the sum(decode()) expressions that are like SUM(Decode(vcon.WAGON_TYPE_CODE,'MS',1,0)) to something that counts rows with vcon.WAGON-TYPE-CODE = 'MS' but only when wag.ACI-TAG-NO is distinct. So if two columns look like this vcon.WAGON_TYPE_CODE wag.ACI_TAG_NO MS HI1111 SS ...

How to improve 'Incorrect syntax near the keyword 'select'.'

this query give me 'Incorrect syntax near the keyword 'select'.' look please below bold character area. declare @date1 smalldatetime, @date2 smalldatetime, @page nvarchar(100) ,@sum int select @date1='2009-06-06',@date2='2009-06-13',@page='Tüm Sayfalar' set @sum = select Sum(t.[VISITINGCOUNT]) from ( select count(page) as [VISIT...

MySQL -> RESTful API

Is there anything out there that given a MySQL database can create RESTful webservice endpoints? I'm ok with it doing stupid basic things to start with, as long as the data model is tunable after. For bonus points it could understand foreign keys to join data, and create searching endpoints for indexed columns. ...

condense SQL in mySQL

How can I simplify this code in MySQL? SELECT name, MAX(IF(to_days(thedate) - to_days('2009-06-13') = 0, price, '')) AS date1, MAX(IF(to_days(thedate) - to_days('2009-06-13') = 1, price, '')) AS date2, MAX(IF(to_days(thedate) - to_days('2009-06-13') = 2, price, '')) AS date3, MAX(IF(to_days(thedate) - to_days('2009-06-13') = 3, ...

Extension method to convert SubSonic SqlQuery object to plain SQL

I sometimes need to see what SQL statement SubSonic generates. That works great with: SqlQuery qry = DB.Select().From<Product>() .Where(Products.Columns.Name).IsEqualTo("Productname"); Console.WriteLine(qry.BuildSqlStatement()); However that returns the query with parameters (SELECT ... WHERE productname = ?productn...