'Default' NON_EMPTY_BEHAVIOR behavior...

Hi,

The below query returns 20 rows when run against our production DW. When the NON_EMPTY_BEHAVIOR is removed, it returns 10 rows. Note the lack of a member list on the N_E_B. Anyone else seen this, or have any ideas as to why it's causing me a 'problem'?

WITH MEMBER

[Container].[Container Type].[AllCalc] AS [Container].[Container Type].[All],

NON_EMPTY_BEHAVIOR={}

SELECT

([Measures].[Container Load Count]) ON COLUMNS,

NON EMPTY

[Trade].[Trade].&[EAST_AFRICA] * [Container].[Container Number].[All].Children ON ROWS

FROM [DW_SM]

WHERE (

<various slicers>,

[Container].[Container Type].[AllCalc]

)

Thanks,

Will.

You should remove NON_EMPTY_BEHAVIOR from your calculated member, because it is set incorrectly. Specifying an empty set for NEB is FAAP always wrong. And really to get any benefit out of it in the form you are trying to use it, it should've only been used on calculated measure, not on calculated member.

Default member behaviour

I was under the impression that the behaviour of an MDX query would be the same if I didn't specify anything in the where clause or if I specified the hierarchies default members explicitly. But I've found an example on the Adventure Works cube where this is not the case. Is this expected?

For example:

If I execute the following:

SELECT { [Account].[Account].MEMBERS } ON COLUMNS FROM [Adventure Works]

The first few members I get back are

All Accounts, Balance Sheet, Net Income, Assets, etc. etc.

The [Account].[Accounts] hierarchy has the default member [Account].[Accounts].&[47] so I would expect the following MDX to return me exactly the same members, but it doesn't.

SELECT { [Account].[Account].MEMBERS } ON COLUMNS FROM [Adventure Works] WHERE ( [Account].[Accounts].&[47] )

It misses out Balance Sheet, Assets and other members.

Your assertion is right about omitting default members as long as we are talking about hierarchies belonging to *other dimensions*. Meaning dimensions you do not have on columns or rows.

For hierarchies belonging to the same dimension your assertion does not apply. Other forum participants may be able to describe this in more detail.

DEFAULT keyword performance

I have a function which performs a query and returns a table. The one
parameter that can get passed in is a date which defaults to NULL.
There is an IF statement in the function that will set the paramter to
an actual date if null. If I call the function while passing in a date
the function comes back a second or 2 later. But if I pass in DEFAULT
to the function, the same query takes 8 minutes. See code below and
sample call below.

CREATE FUNCTION fCalculateProfitLossFromClearing (
@.TradeDate DATETIME = NULL
)
RETURNS @.t TABLE (
[TradeDate] DATETIME,
[Symbol] VARCHAR(15),
[Identity] VARCHAR(15),
[Exchange] VARCHAR(5),
[Account] VARCHAR(10),
[Value] DECIMAL(18, 6)
)
AS
BEGIN
-- Use previous trading date if none specified
IF @.TradeDate IS NULL
SET @.TradeDate = Supporting.dbo.GetPreviousTradeDate()

-- Make the query
INSERT @.t
SELECT
@.TradeDate,
tblTrade.[Symbol],
tblTrade.[Identity],
tblTrade.[Exchange],
tblTrade.[Account],
SUM((CASE tblTrade.[Side] WHEN 'B' THEN -ABS(tblTrade.[Quantity])
ELSE ABS(tblTrade.[Quantity]) END) * (tblPos.[ClosingPrice] -
tblTrade.[Price])) AS [Value]
FROM
Historical.dbo.ClearingTrade tblTrade
LEFT JOIN Historical.dbo.ClearingPosition tblPos ON (@.TradeDate =
tblPos.[TradeDate] AND tblTrade.[Symbol] = tblPos.[Symbol] AND
tblTrade.[Identity] = tblPos.[Identity])
WHERE
([TradeTimestamp] >= @.TradeDate AND [TradeTimestamp] < DATEADD(DAY,
1, @.TradeDate))
GROUP BY tblTrade.[Symbol],tblTrade.[Identity],tblTrade.[Exchange],tblTrade.[Account]

RETURN
END

If I call the function as

SELECT * FROM fCalculateProfitLossFromClearing('09/25/2003')

it returns in 2 seconds.

If I call the function as

SELECT * FROM fCalculateProfitLossFromClearing(DEFAULT)

in which GetPreviousTradeDate() will set @.TradeDate to 09/25/2003 it
returns in 8 minutes.[posted and mailed, please reply in news]

Jason (JayCallas@.hotmail.com) writes:
> I have a function which performs a query and returns a table. The one
> parameter that can get passed in is a date which defaults to NULL.
> There is an IF statement in the function that will set the paramter to
> an actual date if null. If I call the function while passing in a date
> the function comes back a second or 2 later. But if I pass in DEFAULT
> to the function, the same query takes 8 minutes. See code below and
> sample call below.

The query seems familiar. :-)

The reason for this is that when SQL Server builds the query plan,
it considers the value of the input parameter. When you provide an
explicit date, SQL Server can consult the statistics for the table
and see that the value you provided is very selective, and use the
index.

But if you provide NULL, SQL Server will build the query plan on that
assumption. Obviously a NULL value would return no rows, but SQL Server
never makes any assumptions that could yield incorrect results. Since
you WHERE condition is for a range, SQL Server appears to prefer to
scan the table, than using a non-clustered index.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>
> The query seems familiar. :-)
> The reason for this is that when SQL Server builds the query plan,
> it considers the value of the input parameter. When you provide an
> explicit date, SQL Server can consult the statistics for the table
> and see that the value you provided is very selective, and use the
> index.
> But if you provide NULL, SQL Server will build the query plan on that
> assumption. Obviously a NULL value would return no rows, but SQL Server
> never makes any assumptions that could yield incorrect results. Since
> you WHERE condition is for a range, SQL Server appears to prefer to
> scan the table, than using a non-clustered index.

I hate the restart this thread but I have hit a brick wall...

I am at a lose of whether to creat functions or stored procedures (or
even views).

The below questions/issues are based on a need to return information
on a particular date for one to many symbols.

For my example lets say 15 symbols. You could do a query like Symbol =
'a' OR Symbol = 'b' OR Symbol... but it would make more sense to do
Symbol IN ('a','b',...))

I would also like to give my functions and stored procedures to use a
default date if none is specified. I created a function named
GetPreviousTradeDate() which does this based on a calendar.

SO here is how I see it.

Stored procedures seem to be the fastest in terms of returning data
back. But they seem to be limited in the sense that they can return
ONE row or ALL the rows since you cannot pass in a variable list of
symbols. You also cannot use the SP as part of a query. You could just
return all the rows back to the client and do filter or searching on
that end but that does not seem efficient or professional.

A function also does not let you pass in a variable list of symbols
but at least you can use it in a query. You could do something like
SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
happens at the server side and only the needed rows gets sent back.

But functions seem to have MAJOR performance problems when default
values are passed in. When I pass in a specific date the query takes a
few seconds but when I pass in DEFAULT and set the date to the results
of the GetPreviousTradeDate() function the query takes anywhere from 8
minutes to 15 minutes. (This even happens if I do not use the
GetPreviousTradeDate() function and set my parameter to a hard-coded
value)

Any thoughts or comments would be appreciated.|||Jason (JayCallas@.hotmail.com) writes:
> A function also does not let you pass in a variable list of symbols
> but at least you can use it in a query. You could do something like
> SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
> happens at the server side and only the needed rows gets sent back.

I happen to have an article on my web site that discusses this case
in detail. You don't have to read all of it, but you can just get
the function you need at
http://www.algonet.se/~sommar/array...html#iterative.

> But functions seem to have MAJOR performance problems when default
> values are passed in. When I pass in a specific date the query takes a
> few seconds but when I pass in DEFAULT and set the date to the results
> of the GetPreviousTradeDate() function the query takes anywhere from 8
> minutes to 15 minutes. (This even happens if I do not use the
> GetPreviousTradeDate() function and set my parameter to a hard-coded
> value)

The difference is not always that big, but apparently your query is
sensitive for this. I suggest that you split up the procedure in two:

EXEC outer_sp @.date = NULL datetime
IF @.date IS NULL
SELECT @.date = dbo.yourfunctionfordefault()
EXEC inner_sp @.date

And then inner_sp includes the actual query.

For a long treatise on this subject, search on Google news for articles
by Bart Duncan (a escalation engineer at Microsoft) and the phrase
"parameter sniffing".

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||JayCallas@.hotmail.com (Jason) wrote in message news:<f01a7c89.0310141510.28e9c846@.posting.google.com>...
> I hate the restart this thread but I have hit a brick wall...
> I am at a lose of whether to creat functions or stored procedures (or
> even views).
> The below questions/issues are based on a need to return information
> on a particular date for one to many symbols.
> For my example lets say 15 symbols. You could do a query like Symbol =
> 'a' OR Symbol = 'b' OR Symbol... but it would make more sense to do
> Symbol IN ('a','b',...))
> I would also like to give my functions and stored procedures to use a
> default date if none is specified. I created a function named
> GetPreviousTradeDate() which does this based on a calendar.
> SO here is how I see it.
> Stored procedures seem to be the fastest in terms of returning data
> back. But they seem to be limited in the sense that they can return
> ONE row or ALL the rows since you cannot pass in a variable list of
> symbols. You also cannot use the SP as part of a query. You could just
> return all the rows back to the client and do filter or searching on
> that end but that does not seem efficient or professional.
> A function also does not let you pass in a variable list of symbols
> but at least you can use it in a query. You could do something like
> SELECT * FROM TheFunction() WHERE Symbol IN ('a','b',...). All this
> happens at the server side and only the needed rows gets sent back.
> But functions seem to have MAJOR performance problems when default
> values are passed in. When I pass in a specific date the query takes a
> few seconds but when I pass in DEFAULT and set the date to the results
> of the GetPreviousTradeDate() function the query takes anywhere from 8
> minutes to 15 minutes. (This even happens if I do not use the
> GetPreviousTradeDate() function and set my parameter to a hard-coded
> value)
> Any thoughts or comments would be appreciated.

Since the stored procedure has both the speed and the ability to use
default values without performance hit, would it be normal practice or
efficient to send separate queries for each symbol to the stored
procedure? This could result in anywhere from a few to several hundred
calls at a time.|||Jason (JayCallas@.hotmail.com) writes:
> Since the stored procedure has both the speed and the ability to use
> default values without performance hit, would it be normal practice or
> efficient to send separate queries for each symbol to the stored
> procedure? This could result in anywhere from a few to several hundred
> calls at a time.

That does not seem like a good idea. Certainly more efficient to get
data for all symbols at once. See my previous post for suggestions.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> A function also does not let you pass in a variable list of symbols
but at least you can use it in a query ... Any thoughts or comments
would be appreciated. <<

Ever try putting the list of symbols into a one column table and using
an "IN (SELECT parm FROM Parmlist)" instead?

Default Field Value?

Is it possible to change the default value of a field using a stored proc or query? Any hints on how to do this if it is possible would be appreciated!
Mike BJust supply the new value on the INSERT?

Or to permanently change it you need to use ALTER TABLE...I think...gotta check...go look up ALTER in Books Online (BOL)

OK?

Default constraints

Does anyone know a query that will return the value defined on a default constraint for a database table.column ?

So, if I have table :

create table #bill (
column1 int not null,
column2 char(4) default 'AAAA'
)

Something that would give me the 'AAAA' back ?

Thanks,

BillSelect column_default
from Information_Schema.Columns
where table_name = 'table_name' AND
column_name = 'column'|||Thank you very much,

Billsql

Dedupe Query

Below, I have some T-SQL to create a table, add some sample records and do a dedupe query. This all works but performance is poor on large data sets and I was wondering if someone had any optimization tips.

Thanks to blindman for helping me develop this version.

Some background: the staging table has undeduped records about users. I want to dedupe and get the record with the most fields. I don't want to mix fields from different records. The staging table is currently unindexed but that can be changed.

CREATE TABLE StagingRecords
(
EmailAddress VARCHAR(75) NULL,
FirstName VARCHAR(255) NULL,
LastName VARCHAR(255) NULL,
StreetAddress VARCHAR(255) NULL,
City VARCHAR(255) NULL,
State VARCHAR(255) NULL,
ZipCode VARCHAR(255) NULL,
RecordID INT IDENTITY NOT NULL
)

INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('usera@.hotmail.com', 'John', 'Doe', NULL, NULL, NULL, NULL)
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('usera@.hotmail.com', 'Abe', 'Abelman', NULL, NULL, 'MI', NULL)
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('usera@.hotmail.com', 'Zach', 'Zedcynsky', NULL, NULL, 'TX', NULL)
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('usera@.hotmail.com', 'Mary', 'Jane', NULL, NULL, NULL, NULL)

INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('zzz@.yahoo.com', 'Cletus', 'Van Damme', NULL, NULL, NULL, NULL)
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('zzz@.yahoo.com', 'Alfonse', 'Ackbar', NULL, NULL, 'AL', '12345')
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('zzz@.yahoo.com', 'Zoom', 'Zuckerman', NULL, NULL, 'NJ', '54321')
INSERT INTO StagingRecords (EmailAddress, FirstName, LastName, StreetAddress, City, State, ZipCode)
VALUES ('zzz@.yahoo.com', 'Mary', 'Jane', NULL, 'Springfield', NULL, NULL)

SELECT
NULLCountTable.MinNULLCount, IDTable.TargetRecordID,
StagingRecords.EmailAddress, StagingRecords.FirstName, StagingRecords.LastName, StagingRecords.StreetAddress, StagingRecords.City, StagingRecords.State, StagingRecords.ZipCode
FROM
(SELECT EmailAddress
, MIN(CASE WHEN StagingRecords.FirstName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.LastName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.StreetAddress IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.City IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.State IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.ZipCode IS NULL THEN 1 ELSE 0 END) AS MinNULLCount
FROM StagingRecords
GROUP BY EmailAddress) AS NULLCountTable
INNER JOIN
(SELECT Min(RecordID) AS TargetRecordID, EmailAddress
, (CASE WHEN StagingRecords.FirstName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.LastName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.StreetAddress IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.City IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.State IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.ZipCode IS NULL THEN 1 ELSE 0 END) AS NULLCount
FROM StagingRecords
GROUP BY EmailAddress, (CASE WHEN StagingRecords.FirstName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.LastName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.StreetAddress IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.City IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.State IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StagingRecords.ZipCode IS NULL THEN 1 ELSE 0 END)) AS IDTable
ON (NULLCountTable.EmailAddress = IDTable.EmailAddress AND NULLCountTable.MinNULLCount = IDTable.NULLCount)
INNER JOIN StagingRecords ON (StagingRecords.EmailAddress = IDTable.EmailAddress AND StagingRecords.RecordID = IDTable.TargetRecordID)This has been running on an undeduped table of 170 million records for over 2.5 hours and has yet to output a single row.

Recently, people in this forum said I should use set based solutions such as this over cursors. I should get dramatic/exponential performance gains. And if I wasn't seeing that, then I wasn't doing it right. Well, I must not be doing this right so I'm asking for help.

This seems like it must do extra logic and sorting that the cursor based code doesn't have to do. For example, the cursor code doesn't need unique staging record IDs and never has to join on them or perform a fraction of the joining of this query.

The provided SQL should be say to run and experiment with on a tempdb.

Any help is much appreciated.|||There are probably more efficient solutions, but this approach is about 4 times faster than blindmans more complex query
if you build the view and indexes (which may make the whole thing a wash in the end)

Also, you suggested that your cursor solution appeared faster. It may return some rows faster than blindmans query, but
his suggestion is still fairly efficient. It will read the table 2-3 times but iterating through each row with a cursor,
even if you read each row only once, will still be MUCH SLOWER to complete the entire process.

SET CONCAT_NULL_YIELDS_NULL ON
SET ARITHABORT ON

--Create a view with a calculated colum for count of null records
CREATE VIEW v_StagingRecords WITH SCHEMABINDING AS
SELECT EmailAddress,
CASE WHEN FirstName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN LastName IS NULL THEN 1 ELSE 0 END
+ CASE WHEN StreetAddress IS NULL THEN 1 ELSE 0 END
+ CASE WHEN City IS NULL THEN 1 ELSE 0 END
+ CASE WHEN State IS NULL THEN 1 ELSE 0 END
+ CASE WHEN ZipCode IS NULL THEN 1 ELSE 0 END NullCount, RecordID
FROM dbo.StagingRecords

--3 seconds. 1700 reads
CREATE UNIQUE CLUSTERED INDEX vSR_IDX on v_StagingRecords (RecordID)
CREATE INDEX v_EN_IDX on v_StagingRecords (EmailAddress, NullCount)

--100,000 row table. 0.6 seconds, 800 reads vs 2.8 seconds, 2200 reads for original query.
SELECT * FROM v_StagingRecords WHERE RecordID IN
(
SELECT (SELECT TOP 1 RecordID FROM v_StagingRecords WITH (NOEXPAND)
WHERE EmailAddress = o.EmailAddress Order BY NullCount ASC) RecordID
FROM v_StagingRecords o WITH (NOEXPAND)
GROUP BY EmailAddress
)
ORDER BY EmailAddress|||I loaded 17 million rows into a test table and built the view, indexes, and ran the query. It took 16 minutes in total, 101 seconds for the query itself. I tried blindmans query on the same data it it took 113, seconds so scrap my suggestion. I might play with it a little more and see if I can come up with anything better.

Dedub query

I am trying to join two table using a primary key, my problem is that one table has multiple listing of that primary key, I only want to join to the primary key once. Can anyone show me how this can be done?

Table1

acct_no sale_am tran_cd

123 50 2

123 54 1

113 20 9

124 30 7

Table2

acct_no exp_am res_am

123 50 20

113 24 30

124 60 10

What I need:

acct_no sum(sale_am) sum(exp_am) sum(res_am)

123 104 50 20

113 20 24 30

124 30 60 10

Thanks

There are several possibilities. And each one could provide different resultsets.

Please let us know what is your expected output and we can better assist you.

|||

Assuming you want the latest tran_cd value, you can just use a derived table and the ROW_NUMBER() windowed function:

select *

from (select acct_no, sale_am, row_number() over (partition by acct_no order by tran_cd desc) as rowNbr

from table1) as table1
join table2
on table1.acct_no = table2.acct_no

and table1.rowNbr = 1

If this is something that you do often, especially something that needs a lot of performance, I might consider implementing a current_row_flag in your table to denote the row you want to usually use (especially if those rows don't change much)

|||

Code Snippet

createtable #Table1( acct_no int, sale_am money, tran_cd int)

insertinto #Table1

select 123, 50, 2

union allselect 123, 54, 1

union allselect 113, 20, 9

union allselect 124, 30, 7

createtable #Table2( acct_no int, exp_am money, res_am money)

insertinto #Table2

select 123, 50, 20

union allselect 113, 24, 30

union allselect 124, 60, 10

select t1.acct_no,sum(t1.sale_am)as sale_am,

sum(t2.exp_am)as exp_am,sum(t2.res_am)as res_am

from #Table1 t1

innerjoin #Table2 t2

on t1.acct_no = t2.acct_no

groupby t1.acct_no

sql

DECODE?

I have to run a query to give a column a value based on a time range. Can I
use DECODE?

select decode(trans_date, trans_date>='01-Jul-2002' and
trans_date<='30-Jun-2003','Fiscal2002', ....) as fiscal,
from. . .
where. . .Sherman H. (shung@.earthlink.net) writes:
> I have to run a query to give a column a value based on a time range.
> Can I use DECODE?
> select decode(trans_date, trans_date>='01-Jul-2002' and
> trans_date<='30-Jun-2003','Fiscal2002', ....) as fiscal,
> from. . .
> where. . .

Maybe in some other DBMS, but there is no such function in SQL Server.

I don't know what decode is supposed to achieve, but it seems that
the CASE expression might to the task:

SELECT CASE WHEN transdate BETWEEN '20020701' AND '20030630'
THEN 'Fiscal2002'
...
END

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Declaring explicit transaction for a select stmt.

Does it make sense to declare a transaction for a query that is only
performing a data read.
for example:
BEGIN tran
select * from pubs..authors
if @.@.error <> 0
Begin
ROLLBACK tran
raiserror('blah', 16,1)
RETURN
END
COMMIT
My thinking is that if there is an error on reading, than the client will
anyway get the error message, so adding an explicit transaction may be
addding overhead.Why would you need a transaction? You have nothing to rollback?
-- Jesse
"MG" <y4forums.t.mdgoyal@.xoxy.net> wrote in message
news:%235nPdDsDFHA.148@.TK2MSFTNGP14.phx.gbl...
> Does it make sense to declare a transaction for a query that is only
> performing a data read.
> for example:
> BEGIN tran
> select * from pubs..authors
> if @.@.error <> 0
> Begin
> ROLLBACK tran
> raiserror('blah', 16,1)
> RETURN
> END
> COMMIT
> My thinking is that if there is an error on reading, than the client will
> anyway get the error message, so adding an explicit transaction may be
> addding overhead.
>|||> Does it make sense to declare a transaction for a query that is only
> performing a data read.
No, It does not make sense.
AMB
"MG" wrote:

> Does it make sense to declare a transaction for a query that is only
> performing a data read.
> for example:
> BEGIN tran
> select * from pubs..authors
> if @.@.error <> 0
> Begin
> ROLLBACK tran
> raiserror('blah', 16,1)
> RETURN
> END
> COMMIT
> My thinking is that if there is an error on reading, than the client will
> anyway get the error message, so adding an explicit transaction may be
> addding overhead.
>
>|||On Wed, 9 Feb 2005 11:03:19 -0500, MG wrote:

>Does it make sense to declare a transaction for a query that is only
>performing a data read.
Hi MG,
Not in the default isolation mode (read committed). In repeatable read or
serializable mode, it does make sense.
You don't need the rollback, though.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Declaring and using an UPDATE CURSOR with SQL SERVER

In databases like Oracle or Sybase, if you have multiple records returned
from a query and want to update them, you declare an update cursor.
While I have read the "Help" files available in Enterprise, I have not
figured out the syntax for declaring and using an UPDATE CURSOR.
The query I am running is like this:
UPDATE A
SET A.Field1 = (SELECT B.Field1
FROM B INNER JOIN A ON A.id = B.id)
The tables have a many to one relationship on id.
I do not know if when I declare the cursor, if I put the whole update
statement in it... I do not know if when I use an update cursor, I have to
fetch next... And beyond that, if I do get the syntax and put it into a
stored procedure, how do I execute the query from within Enterprise Manager
(I am not writing code to call the procedure, I just want to execute it
against the table. I would execute it by just creating a query, but the
cursor format is not recognized in the query pane.)
I am completely new to SQL Server but not to databases.
What I am looking for is just a simple example of declaring and using an
UPDATE CURSOR.
Any help you can provide would be appreciated. Thanks!Hi
you can use somewhat this
Update
Set A.Field1 = B.Field
From
Where A.id = B.i
This statement this convert to UPDATE CURSOR in Oracle
Hermilson Tinoco.|||The simple syntax for declaring a cursor which allows updates is doc'd in
SQL books online , search for "Declare cursor", but it looks like
declare mycur Cursor for <Select statement> for update
you can then open it, fetch rows, and update table set col = value where
current of mycur
Generally in SQL, we try to avoid cursors and use relational update
statements whenever possible, because cursors (generally) do not perform as
well.
hope this helps.
"Carol Berry" <carol@.123marbella.com> wrote in message
news:OAGSMlx6DHA.2404@.TK2MSFTNGP11.phx.gbl...
> In databases like Oracle or Sybase, if you have multiple records returned
> from a query and want to update them, you declare an update cursor.
> While I have read the "Help" files available in Enterprise, I have not
> figured out the syntax for declaring and using an UPDATE CURSOR.
> The query I am running is like this:
> UPDATE A
> SET A.Field1 => (SELECT B.Field1
> FROM B INNER JOIN A ON A.id = B.id)
> The tables have a many to one relationship on id.
> I do not know if when I declare the cursor, if I put the whole update
> statement in it... I do not know if when I use an update cursor, I have
to
> fetch next... And beyond that, if I do get the syntax and put it into a
> stored procedure, how do I execute the query from within Enterprise
Manager
> (I am not writing code to call the procedure, I just want to execute it
> against the table. I would execute it by just creating a query, but the
> cursor format is not recognized in the query pane.)
> I am completely new to SQL Server but not to databases.
> What I am looking for is just a simple example of declaring and using an
> UPDATE CURSOR.
> Any help you can provide would be appreciated. Thanks!
>

Declaring and using an UPDATE CURSOR with SQL SERVER

In databases like Oracle or Sybase, if you have multiple records returned
from a query and want to update them, you declare an update cursor.
While I have read the "Help" files available in Enterprise, I have not
figured out the syntax for declaring and using an UPDATE CURSOR.
The query I am running is like this:
UPDATE A
SET A.Field1 =
(SELECT B.Field1
FROM B INNER JOIN A ON A.id = B.id)
The tables have a many to one relationship on id.
I do not know if when I declare the cursor, if I put the whole update
statement in it... I do not know if when I use an update cursor, I have to
fetch next... And beyond that, if I do get the syntax and put it into a
stored procedure, how do I execute the query from within Enterprise Manager
(I am not writing code to call the procedure, I just want to execute it
against the table. I would execute it by just creating a query, but the
cursor format is not recognized in the query pane.)
I am completely new to SQL Server but not to databases.
What I am looking for is just a simple example of declaring and using an
UPDATE CURSOR.
Any help you can provide would be appreciated. Thanks!Hi.
you can use somewhat this:
Update A
Set A.Field1 = B.Field1
From B
Where A.id = B.id
This statement this convert to UPDATE CURSOR in Oracle.
Hermilson Tinoco.|||The simple syntax for declaring a cursor which allows updates is doc'd in
SQL books online , search for "Declare cursor", but it looks like
declare mycur Cursor for <Select statement> for update
you can then open it, fetch rows, and update table set col = value where
current of mycur
Generally in SQL, we try to avoid cursors and use relational update
statements whenever possible, because cursors (generally) do not perform as
well.
hope this helps.
"Carol Berry" <carol@.123marbella.com> wrote in message
news:OAGSMlx6DHA.2404@.TK2MSFTNGP11.phx.gbl...

quote:

---

> In databases like Oracle or Sybase, if you have multiple records returned
> from a query and want to update them, you declare an update cursor.
> While I have read the "Help" files available in Enterprise, I have not
> figured out the syntax for declaring and using an UPDATE CURSOR.
> The query I am running is like this:
> UPDATE A
> SET A.Field1 =
> (SELECT B.Field1
> FROM B INNER JOIN A ON A.id = B.id)
> The tables have a many to one relationship on id.
> I do not know if when I declare the cursor, if I put the whole update
> statement in it... I do not know if when I use an update cursor, I have

---

to

quote:

---

> fetch next... And beyond that, if I do get the syntax and put it into a
> stored procedure, how do I execute the query from within Enterprise

---

Manager

quote:

---

> (I am not writing code to call the procedure, I just want to execute it
> against the table. I would execute it by just creating a query, but the
> cursor format is not recognized in the query pane.)
> I am completely new to SQL Server but not to databases.
> What I am looking for is just a simple example of declaring and using an
> UPDATE CURSOR.
> Any help you can provide would be appreciated. Thanks!
>

---

|||Thank you all for your help. If I need more help, I will post a more
complete problem. I am new to "posting" issues, too.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

declaring a sql query to a variable...

Hello all! After I declar a variable how would I set the result of a sql query to the variable so i can utilize it further in my stored procedure?

-Thanks,
Rich

declare @.what varchar(2)

Code Snippet

select @.what = targetColumn

from targetTable

Where testKey = 'Whatever'

-- or the SET alternative:

Code Snippet

set @.what

= ( select taretColumn

from targetTable

where testKey = 'Whatever'

)

Give a look at SET and SELECT in books online.

|||

Kent Waldrop Se07 wrote:

declare @.what varchar(2)

Code Snippet

select @.what = targetColumn

from targetTable

Where testKey = 'Whatever'

-- or the SET alternative:

Code Snippet

set @.what

= ( select taretColumn

from targetTable

where testKey = 'Whatever'

)

Give a look at SET and SELECT in books online.

Thanks! When you mentioned to have a look at SET and SELECT in books online... are the books free or do i need to purchase them?

-Thanks,
Rich
|||

Free. As a download:

http://www.microsoft.com/downloads/results.aspx?pocId=&freetext=sql%20server%20books%20online&DisplayLang=en

As a webpage:

http://msdn2.microsoft.com/en-us/library/bb545450.aspx

declare variable slower then direct variable

Hi expert,
i have one doubt when i try 2 query give me big different
return time:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
example 1:
this cost like 1 minutes
declare @.starttime datetime
declare @.endtime datetime
set @.starttime = '2007/06/14'
set @.endtime = '2007/06/15'
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= @.startdate
and startdatetime <= @.enddate
example 2:
this cost like 1 sec.
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= '2007/06/14'
and startdatetime <= '2007/06/15'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
can someone tell me whats going on?XJ
Yes it is expected behaviour especially you change the values of
variables.
1) Don't use TOP clause without ORDER BY clause (you may get wrong result)
2) Search on internet for 'parameter sniffing'
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegroups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> can someone tell me whats going on?
>|||Compare the execution plans. You will most probably find that they aren't the same. For 1, the
optimizer doesn't know the values of the variables,m so it has to guess on selectivity. For 2, the
values are hard-coded in the query, so thay are known to the optimizer.
You were suggested in another post to read up on "parameter sniffing", which is a good idea. I just
want t point out that none of your examples will actually expose parameter sniffing behaviour.
Here's some good reading: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegroups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> can someone tell me whats going on?
>

declare variable slower then direct variable

Hi expert,
i have one doubt when i try 2 query give me big different
return time:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
example 1:
this cost like 1 minutes
declare @.starttime datetime
declare @.endtime datetime
set @.starttime = '2007/06/14'
set @.endtime = '2007/06/15'
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= @.startdate
and startdatetime <= @.enddate
example 2:
this cost like 1 sec.
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= '2007/06/14'
and startdatetime <= '2007/06/15'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
can someone tell me whats going on?
XJ
Yes it is expected behaviour especially you change the values of
variables.
1) Don't use TOP clause without ORDER BY clause (you may get wrong result)
2) Search on internet for 'parameter sniffing'
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegr oups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
> can someone tell me whats going on?
>
|||Compare the execution plans. You will most probably find that they aren't the same. For 1, the
optimizer doesn't know the values of the variables,m so it has to guess on selectivity. For 2, the
values are hard-coded in the query, so thay are known to the optimizer.
You were suggested in another post to read up on "parameter sniffing", which is a good idea. I just
want t point out that none of your examples will actually expose parameter sniffing behaviour.
Here's some good reading: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegr oups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
> can someone tell me whats going on?
>

declare variable slower then direct variable

Hi expert,
i have one doubt when i try 2 query give me big different
return time:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~
example 1:
this cost like 1 minutes
declare @.starttime datetime
declare @.endtime datetime
set @.starttime = '2007/06/14'
set @.endtime = '2007/06/15'
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= @.startdate
and startdatetime <= @.enddate
example 2:
this cost like 1 sec.
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= '2007/06/14'
and startdatetime <= '2007/06/15'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~
can someone tell me whats going on?XJ
Yes it is expected behaviour especially you change the values of
variables.
1) Don't use TOP clause without ORDER BY clause (you may get wrong result)
2) Search on internet for 'parameter sniffing'
http://blogs.msdn.com/khen1234/arch.../02/424228.aspx
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegroups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~
> can someone tell me whats going on?
>|||Compare the execution plans. You will most probably find that they aren't th
e same. For 1, the
optimizer doesn't know the values of the variables,m so it has to guess on s
electivity. For 2, the
values are hard-coded in the query, so thay are known to the optimizer.
You were suggested in another post to read up on "parameter sniffing", which
is a good idea. I just
want t point out that none of your examples will actually expose parameter s
niffing behaviour.
Here's some good reading: http://www.microsoft.com/technet/pr...r />
comp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegroups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~
> can someone tell me whats going on?
>

Declare Variable For All In SP

In a previous life, for each variable that we passed into a query, we would set -1 to the default for all so that when we converted it to an SP, we could query a specific dataset or or all. The following is a sample bit of code, I can not for the life of me remember how to pull back all using -1.

The following is the code that I currently have, it's a simplified version of the total SP that I am trying to use, but enough to give you the idea of what I am trying to do.

The MemberId field is a varchar(20) in the table.

Create procedure sp_GetClaims_BY_MemberID
@.Memberid varchar (50)
as
Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible
where Membid = @.memberid

EXEC sp_GetClaims_BY_MemberID '99999999999'

The above SP works fine, I just need to be able to modify it so that I can pull back all records for all member id's, any suggestions?

I am currently working in SQL 2000.

Here's one way I think should work.

Assume we have decided that '*' (star) is to mean 'all' (as in the T-SQL wildcard)
What below does, is just to turn '*' into null, and in the WHERE, if the var is null, use the column instead.
End result for parameter '*', is then WHERE Membid = Membid, which is what you want - all rows.

Create procedure sp_GetClaims_BY_MemberID
@.Memberid varchar (50)
as
Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible
where Membid = COALESCE(NULLIF( @.memberid, '*'), Membid)

=;o)
/Kenneth

|||PERFECT!

Declare dynamicly variable

I'd like to know, if there is a possibility reference declare SQLServer dynamically?

For example, I've tried execute this query(under), but I received this message (Server: Msg 137, Level 15, State 1, Line 6
Must declare the variable '@.t1'.):

declare @.Frase varchar(50)
set @.Frase = 'declare @.t1 varchar(10), @.t2 int'
exec (@.Frase)
select @.t1 = 'AAAAA'
select @.t2 = 1000
Print @.t1
Print @.t2

ThanksMay be because Dynamic SQL is executed/Parsed last by the query Parser and the variable declared are within the statement which is limited to the "exec" and is considered a seperate stored procedure outside of main query|||The EXEC statement executes within its own scope, outside of the procedure that calls it. Therefore, EXEC cannot share variables with its calling procedure. As soon as EXEC completes, the variables go out of scope and "poof", they disappear. Temporary tables, however, are connection specific and can be referenced within EXEC statements.

blindman

Declare cursor based on dynamic query

Hi,

I am declaring the cursor based on a query which is generated dynamically. but it is not working

Declare @.tempSQL varchar(1000)

This query will be generated based on my other conditon and will be stored in a variable

set @.tempsql = 'select * from orders'

declare cursor test for @.tempsql

open test

This code is not working.

please suggest

Nitin

Hi

I am writing the code as below

Declare @.testSQl varchar(1000)

set @.testsql = 'select * from orders'

declare test1 cursor for @.testSQl

The declare statement is not working . My @.testsql will be generated at run time.

Help

Nitin

|||You can not use dynamic sql while opening the cursors..

it should be like this

Declare Test1 cursor for
Select * From Orders|||

You could add the cursor creation to your dynamic sql and then just call sp_executesql for the built up string. Something like...

DECLARE @.sql nvarchar(4000)

--Get beginning of cursor

SELECT @.sql = 'DECLARE c CURSOR FOR'

--Decision code for what query is built

SELECT @.sql = @.sql + 'SELECT * FROM orders'

--Remainder of cursor with specific columns from above query

SELECT @.sql = @.sql + 'OPEN c FETCH NEXT FROM c INTO ....'

--Execute the string we just built

EXEC sp_executesql @.sql

|||

I don't like to ever advocate the use of cursors, but you can do this using a global cursor, if you really must:

create procedure test
as
declare @.name nvarchar(128)
exec ('declare bob cursor global for select name from sys.objects')
open bob

fetch next from bob into @.name
select @.name as works
close bob
deallocate bob
go

test

|||

Hi,

I dont know for the moment how to declare a cursor on a query from a string.. I dont think its possible this way. An alternative is to find a solution other than using the cursor, else you'd lose development time in trying to find a solution.

If you cannot find a solution, try to explain the problem, someone will try help out, and also cursors generally tend to be less performant.

|||this is not possible. i agree with waaz|||

Instead of local cursor, you can create a Global cursor with dynamic sql, which is available beyond the scope the dynamic sql

like this

set @.sql='declare test cursor global for '+ @.tempsql

exec sp_executesql @.sql

open test

close test

|||You can use dynamic SQL to create a global cursor as shown in another reply in this thread. But what are you trying to do? Why do you need to use a cursor? And why do you need to use dynamic SQL? Both have performance implications. And dynamic SQL has serious security implications that can compromise your database system and/or network. You will have to use techniques (both in the database and client-side depending on how you call your SP) that avoid SQL injection to protect your database and network from malicious users. Apart from these problems, dynamic SQL requires more maintainence because you have to grant more permissions to end users since checks are deferred to run-time unlike SPs with static SQL statements. So it is easy to create a cursor dynamically but that is not the right thing to do in majority of the cases.|||

Also, try not to ask the same question twice. This question was also answered in another thread. I have merged the threads into one.

|||

hey whitney,

i got the same problem of dynamic query with cursors..

You gave the alternative but i got the big cursor and its difficult for me to put the entire stuff in string.

Because it gets difficult to maintain for me.

Any help or comment regarding this will be appreciated.

Thanks a ton!!

dromyl@.hotmail.com

Declare a variable in an SP

Hi.
I have this sql query, which works:
INSERT INTO tblac
(type, t_id, startdate)
SELECT
tblac.type, ***182*** AS Expr1, tblac.startdate
FROM
tblac
WHERE
tblac.t_id = @.t_id
This works, but I want to put it into an SP. I can't figure out from
books online, how to replace the integer, 182, with a variable passed to
the SP.
I've tried:
CREATE PROCEDURE copyDates AS
(
DECLARE @.new_t_id int,
@.t_id int
)
INSERT INTO tblac
(type, t_id, startdate)
SELECT
tblac.type, @.new_t_id AS Expr1, tblac.startdate
FROM
tblac
WHERE
tblac.t_id = @.t_id
GO
..
but that doesn't work.
thanks for any help,
Mark
*** Sent via Developersdex http://www.examnotes.net ***Mark wrote:
You need to declare the variables you want to pass (the parameters)
BEFORE the AS.
CREATE PROCEDURE copyDates (@.new_t_id int, @.t_id int) AS
INSERT INTO tblac
(type, t_id, startdate)
SELECT
tblac.type, @.new_t_id AS Expr1, tblac.startdate
FROM
tblac
WHERE
tblac.t_id = @.t_id
GO
HTH,
Stijn Verrept.

Decision trees, DMX and CONTAINS (T-SQL)

I would appreciate answers to the following doubts I have regarding Decision trees, CONTAINS and using CONTAINS in a DMX query:

1. Does MS decision tree work only off equality/inequality conditions for the nodes? Is it possible to use a predicate as the branch criteria for a node?

2. Can the T-SQL predicate CONTAINS(...) be used in a DMX query? I need to check if a column-value is a substring of another column and create an intermediate column that will enable me to construct a decision tree with the phrase-present/absent branch.

3. Can CONTAINS(...) be used in a select clause? Like -

SELECT CONTAINS(JAT.column1, '"Good day"')

FROM JustAnotherTable;

4. Does CONTAINS(...) support both arguments to be column references? Or, is it mandatory that the pattern (argument #2) has to be a literal string or a variable? E.g.: I need to know the validity of the following expression -

SELECT * FROM JustAnotherTable JAT

WHERE CONTAINS(JAT.column1, JAT.column3);

The decision tree split conditions are based on equality/inequality conditions for categorical attributes and numeric/range comparisons for continuous values - we don't do arbitrary predicates.

CONTAINS is not supported in DMX.