Debug Dataset error

I got this error:

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

With the debug tricks from PaulStock, that helps!

References: http://stackoverflow.com/questions/7026566/failed-to-enable-constraints-one-or-more-rows-contain-values-violating-non-null

 

Advertisements

Filter by Comma Separated Values in IN Clause ?

The Customer table has ‘PrimaryCode’ and ‘OtherCode’ columns store Code from Codes table:Image

However some of the codes store in Customer table do not exist in Codes table.

I need to let Codes table left join Customer table, this is the first version SQL:

select * from Codes a
left outer join Customer b
on a.CodeID in (b.PrimaryCode)

That did not give me the correct outcome as query above miss out the values in OtherCodes column, so I try this:

select * from Codes a
 left outer join Customer b
 on a.CodeID in (CAST(b.PrimaryCode as varchar) + ',' + b.OtherCodes)

I try to concat the PrimaryCode + OtherCodes to have a clause like this: IN(‘1,2,3,4,5’), but that does not seems right, and I get the error message:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘23,24,26,27’ to data type int.

Ok, now I know that is not right doing that, there are few solutions provided in internet (search from Google of course!). The final solution would be using LIKE keyword, then I have this which work perfect:

select * from Codes a
left outer join Customer b
on ',' + Cast(b.PrimaryCode as varchar) + ',' + b.OtherCodes + ',' LIKE '%,' + Cast(a.CodeID as varchar) + ',%'

The result I want:

Image

 

Hints to search in Google: Comma separated values in IN Clause

Keywords: LIKE

Reference: http://stackoverflow.com/questions/4613273/sql-use-comma-separated-values-with-in-clause

Create your own system stored procedure

Try to add our own global s.proc/system s.proc to keyboard shortcut, and need it available to all instances under one server.

Firstly we need to have a s.proc, says it select * from certain table, therefore we have below:

USE [master]
GO

Create Procedure sp_SelectAll
(
     @TableName varchar(50)    -- Receive highlighted parameter
)
As
Declare @sql varchar(500)
Select @sql = 'select * from ' + DB_NAME() + '.' + SCHEMA_NAME() + '.' + @TableName
execute (@sql)

Go

Line 1,2: sp_SelectAll need to be created under [master] since we want it available to all instances.

Line 6: An input parameter is required as later it will receive the table highlighted in query windows.

Line 10: Same table name may appear in different databases, therefore above s.proc need to recognize where the table come from. Use DB_Name() and SCHEMA_NAME() to make a distinct table call, so we will get something like

select * from Northwind.dbo.Customers

that clear enough to differentiate with

select * from pubs.dbo.Customers

 

*Note 1: In sql server 2008, or even sql server 2005, you may not able to run the sp_SelectAll in all databases, you need to run undocumented query below to fix this:


Use [master]
Go
sp_MS_marksystemobject sp_SelectAll

*Note 2: sp_SelectAll start with “sp_” prefix because we need to “cheat” sql server to treat it as system s.proc, since only system s.proc can be call from all instances, also undocumented s.proc sp_MS_marksystemobject only work for s.proc start with prefix “sp_“.

After s.proc created, allocate it with a keyboard shortcut, I’ve  allocated CTRL + 3 shortcut to this new s.proc.

sp_SelectAll with keyboard shortcut

— [ End break ] —

Reference:

  1. sp_helpindex2 to show included columns (2005+) and filtered indexes (2008) which are not shown by sp_helpindex
  2. Add keyboard query shortcuts to your SQL Query Windows

Keywords: Add customize system stored procedure to keyboard shortcut.

Add keyboard query shortcuts to your SQL Query Windows

You can add the frequently use s.procedure as your keyboard shortcut in your query analyzer(sql 2000) or sql server management studio.

For example, you always need sp_helptext to check existing s.proc in your database, you may type “sp_helptext myStoreProcedure” in your query screen, and hit “F5” to see the outcome.

Now you have faster way to do that, by attach sp_helptext to a keyboard shortcut,

  1. Go to “Tools -> Options”
  2. When Options screen pop up, go to “Environment -> Keyboard”.
  3. Key in “sp_helptext” in the keyboard shortcut you want, refer to image below, in this case is taking “CTRL + F1” shortcut.
  4. Click OK.
  5. Restart Query Analyzer/ SQL Server Management Studio to let the changes take effect.

How to use?

Highlighted the s.procedure you want to check, press “CTRL + F1”, this will call up sp_helptext shortcut that you set up in earlier steps, the content of the stored procedure will be shown immediately in Results pane.

Picture reference:

You can also add your own s.proc to keyboard shortcut, it never restrict to system s.procedure.

Keywords: Query shortcuts, keyboard shortcuts, Keyboard options, Environment settings, keyboard settings, Customize keyboard shortcuts.

Determine if a time is within Time Range

We know 1pm is within time range 11am-2pm, also 11pm is within 10pm-2am.

And we need to know how to do that in code, sql, etc…

===== [ Preparation ] =====

Assume the followings are true for later comparison:

  1. No date involved in time comparison.
  2. 24-hours time format is in use.

Basically there are three kinds of time range we need to cater when compare with time:

  1. end time > start time. Eg 0000 – 2359, 0800 – 1800 (working hrs…)
  2. end time = start time. Eg 0100-0100, 1530-1530. Means only need that moment.
  3. end time < start time, cross midnight time range. Eg 1200 – 0300, 2000 – 0600 (sleeping hrs…)

===== [ How it works ? ] =====

Scenario 1 and 2 quite straight forward. To compare it, see example:

Declare @TIME_FOR_CHECK as varchar(6)
Set @TIME_FOR_CHECK = '104500' -- 10:45:00am

SELECT * FROM ValidTimeRangeTable WHERE

@TIME_FOR_CHECK BETWEEN StartTime AND EndTime

-- Result return if StartTime = 080000 and EndTime = 180000

However above example will give wrong outcome for scenario 3 because EndTime always less than StartTime, therefore no result will return.

To compare time in scenario 3, lets take time range 20:00 – 06:00 as example.

Firstly, we need to divide the time range at time 00:00, so will get two time range which is before 00:00 (2000 – 0000) and after 00:00 (0000 – 0600).

Secondly, we have a time T1 to be compare, and we know the time T1 will fall in three ranges, they are 2000-0000 (just name it TR1), 0000 – 0600 (TR2) and 0600 – 2000 (TR3).

So we have:

TR1 = 2000 – 0000

TR2 = 0000 – 0600

TR3 = 0600 – 2000

* Notice that original time range is 2000 – 0600, but we split it to 2000 – 0000 and 0000 – 0600. 0600 – 2000 is the time range that never mention and not cover by original time range 2000 – 0600.

* TR = Time Range

For TR1, we can replace the ‘0000’ with ‘2400’ since we know TR1 is taking time range before midnight. End up we have

TR1 = 2000 – 2400

TR2 = 0000 – 0600

TR3 = 0600 – 2000

Things become straight forward now. Just need to check whether time T1 fall within TR1, TR2 or TR3. Notice that we can make a simple time comparison for TR1, TR2 and TR3, like what we did in scenario 1 and 2. Refer to sample code below:


Declare @TIME_FOR_CHECK as varchar(6)
Set @TIME_FOR_CHECK = '104500' -- 10:45:00am

SELECT * FROM ValidTimeRangeTable WHERE

@TIME_FOR_CHECK BETWEEN

 -- Start Time
 (CASE WHEN EndTime < StartTime THEN (Case When @TIME_FOR_CHECK <= EndTime Then '000000' Else StartTime End) ELSE StartTime END)
 AND
 -- End Time
(CASE WHEN EndTime < StartTime THEN (Case When @TIME_FOR_CHECK <= EndTime Then EndTime Else '240000' End) ELSE EndTime END)

As we can see in last sample code, it only take TR1 or TR2 for comparison. A simple checking still needed to determine which time range is use for comparison.

We can summarize the last sample code into following:


if (endTime < startTime)  // This is cross midnight time range
   if (compareTime <= endTime)
      // compareTime is between '000000' and endTime?
   else
      // compareTime is between startTime and '240000'?
else
   // compareTime is between startTime and endTime?

~~~ * ~~~ * ~~~

Keyword: Check time range.  How to determine time, time period.

SQL Select random row from table

Reference: SQL to Select a random row from a database table

Quick reference to article above:


-- Select a random row with MySQL:
SELECT column FROM table
ORDER BY RAND()
LIMIT 1

-- Select a random row with PostgreSQL:
SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

-- Select a random row with Microsoft SQL Server:
SELECT TOP 1 column FROM table
ORDER BY NEWID()

-- Select a random row with IBM DB2
SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

-- Thanks Tim

-- Select a random record with Oracle:
SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

Keyword:  random record