IF inside of CASE

Can anyone help me with condition inside CASE. Below is my code and I get
syntax error at IF line. Thanks.
SELECT TOP 100 PERCENT
dbo.WorkerDeductions.EmployeeNumber,
dbo.WorkerDeductions.DedCode,
dbo.WorkerDeductions.DedAmt,
dbo.WorkerDeductions.DeductionBalance,
dbo.WorkerDeductions.DedPercent,
dbo.PayInfoNHS.EarnGross,
dbo.PayInfoNHS.CheckID,
DedCalc = CASE
WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
BEGIN
dbo.WorkerDeductions.DedAmt
END
ELSE dbo.WorkerDeductions.DeductionBalance
WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
ELSE dbo.WorkerDeductions.DedAmt
END
FROM dbo.WorkerDeductions INNER JOIN
dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
dbo.PayInfoNHS.EmployeeNumber INNER JOIN
dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
dbo.DeductionCodeLookup.DedCode
WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
102))
ORDER BY dbo.WorkerDeductions.EmployeeNumberYou can't use IF in a query; IF is for flow, not statement-level control.
Try a nested CASE:
CASE
WHEN ... THEN
CASE WHEN ... THEN
ELSE ...
END
WHEN ... THEN
CASE WHEN ... THEN
ELSE ...
END
ELSE
..
END
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:ODfTYAyLFHA.2384@.tk2msftngp13.phx.gbl...
> Can anyone help me with condition inside CASE. Below is my code and I get
> syntax error at IF line. Thanks.
> SELECT TOP 100 PERCENT
> dbo.WorkerDeductions.EmployeeNumber,
> dbo.WorkerDeductions.DedCode,
> dbo.WorkerDeductions.DedAmt,
> dbo.WorkerDeductions.DeductionBalance,
> dbo.WorkerDeductions.DedPercent,
> dbo.PayInfoNHS.EarnGross,
> dbo.PayInfoNHS.CheckID,
> DedCalc = CASE
> WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
> IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
> BEGIN
> dbo.WorkerDeductions.DedAmt
> END
> ELSE dbo.WorkerDeductions.DeductionBalance
> WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
> ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
> ELSE dbo.WorkerDeductions.DedAmt
> END
> FROM dbo.WorkerDeductions INNER JOIN
> dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
> dbo.PayInfoNHS.EmployeeNumber INNER JOIN
> dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
> dbo.DeductionCodeLookup.DedCode
> WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
> 102))
> ORDER BY dbo.WorkerDeductions.EmployeeNumber
>|||David C,
You can nest a CASE expression inside another, but you can not use IF inside
a CASE.
AMB
"David C" wrote:

> Can anyone help me with condition inside CASE. Below is my code and I get
> syntax error at IF line. Thanks.
> SELECT TOP 100 PERCENT
> dbo.WorkerDeductions.EmployeeNumber,
> dbo.WorkerDeductions.DedCode,
> dbo.WorkerDeductions.DedAmt,
> dbo.WorkerDeductions.DeductionBalance,
> dbo.WorkerDeductions.DedPercent,
> dbo.PayInfoNHS.EarnGross,
> dbo.PayInfoNHS.CheckID,
> DedCalc = CASE
> WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
> IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
> BEGIN
> dbo.WorkerDeductions.DedAmt
> END
> ELSE dbo.WorkerDeductions.DeductionBalance
> WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
> ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
> ELSE dbo.WorkerDeductions.DedAmt
> END
> FROM dbo.WorkerDeductions INNER JOIN
> dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
> dbo.PayInfoNHS.EmployeeNumber INNER JOIN
> dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
> dbo.DeductionCodeLookup.DedCode
> WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
> 102))
> ORDER BY dbo.WorkerDeductions.EmployeeNumber
>
>|||That worked. Thank you.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

IDTSComponentEvents' events? When are they fired?

Hi everyone,

I was wondering when events inside IDTSComponentEvents interface are called when you throw a SSIS package? I've got a private class which implements IDTSEvents and along with that got another one that implements the IDTSComponentEvents. I see how neither of them are fired when I debug the code by using F11, and I don't know what is it for.

Let me know your comments or come back to me if you need further details on that.

Thanks in advance,

Any ideas?|||

From my usage, tasks use IDTSComponentEvents. You do not generally implement this yourself, but implementations of it are passed to you in suitable methods such as the TaskHost.Execute method which you override when building a task. It allows you to fire events within the tasks's execute method.

IDTSEvents on the other hand I use when executing a package. I implement that in a class, and then handle the events through the IDTSEvents.OnEvent methods. Never had a problem setting breakpoints in my class to track progress during execution.

You may also want to implement IDTSLogging and use that alongside IDTSEvents when executing a package.

|||Thanks Darren for your comments.

Identity constraint - Drop Create dynamically?

Greetings all,

I have an identity field that I need to drop the constraint and recreate it inside a stored procedure. Actually if there is a better way to do what I'm trying to do, I'm all ears.

My concern is that the 2^31 will eventually be exceeded on the int declaration for this field and I'm not using the field for anything other than to identify uniqueness for a specific task.

What I would like to do is re-number this field each time the table is populated. (not remember the last index)Once I'm finished with the table, I'd like the constraint to be removed.

Any thoughts?

Adamus

The only 'real' option is to drop the existing IDENTITY field, and recreate a new one.|||

I was leaning that way.

Thank you,

Adamus

Identity columns

I have been using the following query to identify the IDENTITY columns
in a given table. (The query is inside an application.)

select column_name
from information_schema.columns
where table_schema = 'user_a' and
table_name = 'tab_a' and
columnproperty(object_id(table_name), column_name, 'IsIdentity') = 1

This works. When "user_a" performs the query, everything is OK.

Now, another user wanted to use the same application. So, "user_b"
clicks on a button, and the exact same query as above is run. (No
substitutions are made; user_b is trying to see the identity column in
[user_a].[tab_a]). However, the query returns null, instead of the
identity column name. User_b can read the table and select from it
just fine.

Why am I getting two different results against the same query? Do I
need to rewrite the query to go against different information schema
views?You might try specifying the table schema in the OBJECT_ID function to avoid
ambiguity. Also, consider quoting the identifiers:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'user_a' AND
TABLE_NAME = 'tab_a' AND
COLUMNPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
COLUMN_NAME, 'IsIdentity') = 1

--
Hope this helps.

Dan Guzman
SQL Server MVP

<newtophp2000@.yahoo.com> wrote in message
news:1103334367.788877.229370@.f14g2000cwb.googlegr oups.com...
>I have been using the following query to identify the IDENTITY columns
> in a given table. (The query is inside an application.)
> select column_name
> from information_schema.columns
> where table_schema = 'user_a' and
> table_name = 'tab_a' and
> columnproperty(object_id(table_name), column_name, 'IsIdentity') = 1
> This works. When "user_a" performs the query, everything is OK.
> Now, another user wanted to use the same application. So, "user_b"
> clicks on a button, and the exact same query as above is run. (No
> substitutions are made; user_b is trying to see the identity column in
> [user_a].[tab_a]). However, the query returns null, instead of the
> identity column name. User_b can read the table and select from it
> just fine.
> Why am I getting two different results against the same query? Do I
> need to rewrite the query to go against different information schema
> views?|||Thanks, Dan! This works great.

Dan Guzman wrote:
> You might try specifying the table schema in the OBJECT_ID function
to avoid
> ambiguity. Also, consider quoting the identifiers:
> SELECT COLUMN_NAME
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_SCHEMA = 'user_a' AND
> TABLE_NAME = 'tab_a' AND
> COLUMNPROPERTY(
> OBJECT_ID(
> QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
> COLUMN_NAME, 'IsIdentity') = 1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||Dan Guzman wrote:
> You might try specifying the table schema in the OBJECT_ID function
to avoid
> ambiguity. Also, consider quoting the identifiers:
> SELECT COLUMN_NAME
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_SCHEMA = 'user_a' AND
> TABLE_NAME = 'tab_a' AND
> COLUMNPROPERTY(
> OBJECT_ID(
> QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
> COLUMN_NAME, 'IsIdentity') = 1

Hi Dan,

As I noted before, this works; however, it seems that it doesn't do the
right thing if the databases are different.

So, my question is, given a database, a table, and a column (along with
dbo/table owner), is there a way to check whether or not that column is
the identity for that table? Is it possible to generalize the above
query to work across databases/users/etc.?

Thanks!

> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||(newtophp2000@.yahoo.com) writes:
> As I noted before, this works; however, it seems that it doesn't do the
> right thing if the databases are different.
> So, my question is, given a database, a table, and a column (along with
> dbo/table owner), is there a way to check whether or not that column is
> the identity for that table? Is it possible to generalize the above
> query to work across databases/users/etc.?

SELECT *
FROM db..sysobjects o
JOIN db..syscolumns c ON o.id = c.id
JOIN db..sysusers u ON o.uid = u.uid
WHERE o.name = @.tbl
AND c.name = @.col
AND u.name = @.user
AND c.status & 0x80 <> 0

will return a row if the column is an identity column.

When I wrote this query, I assumed that I was on undocumented ground,
but this value is actually documented for syscolumns.status, and thus
permissible to use. The code should work in SQL 2005 as well. (Although
SQL 2005 also offer new catalog views which are better for the task.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> So, my question is, given a database, a table, and a column (along with
> dbo/table owner), is there a way to check whether or not that column is
> the identity for that table? Is it possible to generalize the above
> query to work across databases/users/etc.?

You can specify the desired database context with a USE statement
immediately before the SELECT to set the database context.

To return data from different databases in the same query, you'll need to
use the technique Erland suggested and use a UNION ALL to concatenate
results from different databases.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<newtophp2000@.yahoo.com> wrote in message
news:1108137110.964647.268740@.o13g2000cwo.googlegr oups.com...
> Dan Guzman wrote:
>> You might try specifying the table schema in the OBJECT_ID function
> to avoid
>> ambiguity. Also, consider quoting the identifiers:
>>
>> SELECT COLUMN_NAME
>> FROM INFORMATION_SCHEMA.COLUMNS
>> WHERE TABLE_SCHEMA = 'user_a' AND
>> TABLE_NAME = 'tab_a' AND
>> COLUMNPROPERTY(
>> OBJECT_ID(
>> QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
>> COLUMN_NAME, 'IsIdentity') = 1
> Hi Dan,
> As I noted before, this works; however, it seems that it doesn't do the
> right thing if the databases are different.
> So, my question is, given a database, a table, and a column (along with
> dbo/table owner), is there a way to check whether or not that column is
> the identity for that table? Is it possible to generalize the above
> query to work across databases/users/etc.?
> Thanks!
>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>