ASSERTION , SUM(POSITION()) in SQL2005

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  May 13, 2010 at 3:54 am

  #221476

  All,

  Does SQL2005 support CREATE ASSERTION statement?

  Does Sql2005 support SUM(POSITION()) ?

  I have seen a video on SQLUNIVERSITY.NET. In that video i saw the below code

  SELECT race,

  SUM(POSITION('male' IN sex) AS male_tally,

  SUM(SIGN(ABS(POSITION('female' in sex) AS female_tally

  FROM Personnel

  GROUP BY race;

  karthik

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  May 13, 2010 at 4:15 am

  #1166749

  Does SQL2005 support CREATE ASSERTION statement?

  >NO, you will need to use CHECK constraint (unfort. a bit different to SQL-92 CREATE ASSERTION idea)

  Does Sql2005 support SUM(POSITION()) ?

  >NO, use CHARINDEX/PATINDEX instead

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 13, 2010 at 5:23 am

  #1166771

  karthikeyan-444867 (5/13/2010)

  ---

  SELECT race,

  SUM(POSITION('male' IN sex) AS male_tally,

  SUM(SIGN(ABS(POSITION('female' in sex) AS female_tally

  FROM Personnel

  GROUP BY race;

  That's not SQL Server code. A very quick google search reveals that PostgreSQL supports that function.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass