case statement

is it possible to use the case statement in the reports.plz give the example
thanksIn Report Properties there's a code tab.
There you can write function like this:
Public Function DisplayLink(ByVal Country, ByVal ShipPostalCode) As String
Dim strMapLink As String
Dim _PostalCode As String = ShipPostalCode.ToString()
Dim _Country As String = Country.ToString()
Select Case _Country
Case "USA"
strMapLink="string1"
Case "France"
strMapLink="string2"
Case "Germany"
strMapLink="string3"
Case Else
strMapLink = ""
End Select
Return strMapLink
End Function
"ter" wrote:
> is it possible to use the case statement in the reports.plz give the example
> thanks|||There is also a case/switch function in RS (like iif). Don't know the exact
syntax by now, but look at the newer posts. There was a guy with the same
prob of you. he
"ש×?×?×?" wrote:
> In Report Properties there's a code tab.
> There you can write function like this:
> Public Function DisplayLink(ByVal Country, ByVal ShipPostalCode) As String
> Dim strMapLink As String
> Dim _PostalCode As String = ShipPostalCode.ToString()
> Dim _Country As String = Country.ToString()
> Select Case _Country
> Case "USA"
> strMapLink="string1"
> Case "France"
> strMapLink="string2"
> Case "Germany"
> strMapLink="string3"
> Case Else
> strMapLink = ""
> End Select
> Return strMapLink
> End Function
> "ter" wrote:
> > is it possible to use the case statement in the reports.plz give the example
> > thanks

Case sensitive problem

You simply need to change the collation in the WHERE clause so that it is
case sensitive. The following example illustrates how the COLLATE clause ca
n
be used to define the collation:
CREATE TABLE [Function]
(
[Function] VARCHAR(100)
)
INSERT [Function] SELECT 'dr'
INSERT [Function] SELECT 'DR'
INSERT [Function] SELECT 'Dr'
SELECT *
FROM [Function]
WHERE [Function] = 'Dr'
Returns:
Function
dr
DR
Dr
(3 row(s) affected)
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Function
Dr
(1 row(s) affected)
HTH
- Peter Ward
WARDY IT Solutions
"bad_boyu" wrote:

> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>To add on to Peter's response, you can also include the case-insensitive
predicate so that an index on the column can be used:
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' AND
[Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Hope this helps.
Dan Guzman
SQL Server MVP
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||Hello,
I have a very.. very.. very big database that has a table with let's
say column "Function". I want to do different selects on this column
"Function". This select must be case sensitive, so if i do a select
with like "Dr" then the results must contain the Function that have D
in uppercase and r in lowercase. When the database was created there
were no constraints concerning column "Function", concern like all
fields are in uppercase.
Is there a solution to do this selects(case sensitive) without changing
the database?
Thanks,
BB|||You simply need to change the collation in the WHERE clause so that it is
case sensitive. The following example illustrates how the COLLATE clause ca
n
be used to define the collation:
CREATE TABLE [Function]
(
[Function] VARCHAR(100)
)
INSERT [Function] SELECT 'dr'
INSERT [Function] SELECT 'DR'
INSERT [Function] SELECT 'Dr'
SELECT *
FROM [Function]
WHERE [Function] = 'Dr'
Returns:
Function
dr
DR
Dr
(3 row(s) affected)
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Function
Dr
(1 row(s) affected)
HTH
- Peter Ward
WARDY IT Solutions
"bad_boyu" wrote:

> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||To add on to Peter's response, you can also include the case-insensitive
predicate so that an index on the column can be used:
SELECT *
FROM [Function]
WHERE [Function] = 'Dr' AND
[Function] = 'Dr' COLLATE LATIN1_General_CS_AS
Hope this helps.
Dan Guzman
SQL Server MVP
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
> Hello,
> I have a very.. very.. very big database that has a table with let's
> say column "Function". I want to do different selects on this column
> "Function". This select must be case sensitive, so if i do a select
> with like "Dr" then the results must contain the Function that have D
> in uppercase and r in lowercase. When the database was created there
> were no constraints concerning column "Function", concern like all
> fields are in uppercase.
> Is there a solution to do this selects(case sensitive) without changing
> the database?
> Thanks,
> BB
>|||Thanks a lot for these replies!
But I have another problem, I need that the method LIKE or something
similar to be case-sensitive! The Function column has fields like this:
"Dr Bad Boy", "DR Angelina",
"Prof dr Italian", "Prof Dr Adrian",...
Is there any solution for this kind of problem?
Best regards,
BB
Dan Guzman wrote:
> To add on to Peter's response, you can also include the case-insensitive
> predicate so that an index on the column can be used:
> SELECT *
> FROM [Function]
> WHERE [Function] = 'Dr' AND
> [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP|||See if this helps:
http://vyaskn.tripod.com/case_sensi..._sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"bad_boyu" <silaghi.ovidiu@.gmail.com> wrote in message
news:1150245243.474122.145330@.i40g2000cwc.googlegroups.com...
Hello,
I have a very.. very.. very big database that has a table with let's
say column "Function". I want to do different selects on this column
"Function". This select must be case sensitive, so if i do a select
with like "Dr" then the results must contain the Function that have D
in uppercase and r in lowercase. When the database was created there
were no constraints concerning column "Function", concern like all
fields are in uppercase.
Is there a solution to do this selects(case sensitive) without changing
the database?
Thanks,
BB|||On 14 Jun 2006 02:08:55 -0700, bad_boyu wrote:

>Thanks a lot for these replies!
>But I have another problem, I need that the method LIKE or something
>similar to be case-sensitive! The Function column has fields like this:
>"Dr Bad Boy", "DR Angelina",
>"Prof dr Italian", "Prof Dr Adrian",...
>Is there any solution for this kind of problem?
Hi BB,
SELECT *
FROM Function
WHERE Function LIKE '%Dr%' COLLATE LATIN1_General_CS_AS
Hugo Kornelis, SQL Server MVP|||Thanks for these quick replies!!! I believe it works ;)
You are the best!|||Thanks a lot for these replies!
But I have another problem, I need that the method LIKE or something
similar to be case-sensitive! The Function column has fields like this:
"Dr Bad Boy", "DR Angelina",
"Prof dr Italian", "Prof Dr Adrian",...
Is there any solution for this kind of problem?
Best regards,
BB
Dan Guzman wrote:
> To add on to Peter's response, you can also include the case-insensitive
> predicate so that an index on the column can be used:
> SELECT *
> FROM [Function]
> WHERE [Function] = 'Dr' AND
> [Function] = 'Dr' COLLATE LATIN1_General_CS_AS
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP

case insensitive

Sqlserver is case insensitive in this way ?
for example:
select...where col1 like '%BURG%'
will return 'Burger King' ?!!!klabu wrote:
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
>
That depends on the collation being used...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||By default, yes. If you want the query to be case sensitive, you have
several options. Well, some only port easily for equality rather than
LIKE...
http://sqlserver2000.databases.aspfaq.com/how-can-i-make-my-sql-queries-case-sensitive.html
"klabu" <klabu@.klabucom> wrote in message
news:12m6h19lhljpr65@.corp.supernews.com...
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
>|||Standard SQL is a case sensitive language for strings, so you might
want to look up the details to write portable, readable code.|||I just "hopped" over from Oracle to write a UDF and I was rather shocked to
find this
(among other things..but this definite almost made me throw up) lol|||klabu wrote:
> I just "hopped" over from Oracle to write a UDF and I was rather shocked to
> find this
> (among other things..but this definite almost made me throw up) lol
Glasshouse + stone:
'' IS NULL
'Hello' <> 'Hello '
Sequences, routines and tables share the same namespace
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/Forum2006/Forum2006.html|||klabu wrote:
> I just "hopped" over from Oracle to write a UDF and I was rather shocked to
> find this
> (among other things..but this definite almost made me throw up) lol
Welcome to the insane world of Microsoft and collition and bs.|||klabu (klabu@.klabucom) writes:
> Sqlserver is case insensitive in this way ?
> for example:
> select...where col1 like '%BURG%'
> will return 'Burger King' ?!!!
Maybe. It depends on the collation of col1. In SQL Server you can specify
the collation per column, although normally it's the same for all columns in
a database.
Default when you install SQL Server is a case-insensitive collation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||lol dude you're everywhere
doesn't IBM keep you busy enough ? ;)|||klabu wrote:
> lol dude you're everywhere
> doesn't IBM keep you busy enough ? ;)
Keeping my cross vendor skills up is part of the job description.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/Forum2006/Forum2006.html

case expression in where clause and null's

Using SQL Server 2005
I have a simple table example with two columns: FirstName varchar(20) and
LastName varchar(20)
I am doing something like this in a stored procedure where @.firstname and
@.lastname are passed in and @.lastname could be null
select * from table where FirstName = @.firstname and LastName =
COALESCE(@.LastName, LastName)
I am also using "set ansi_nulls off".
The query should give names where LastName is null if @.Lastname = null but
that's not happening. Why?
John DalbergBecause COALESCE returns the first non-NULL value. If @.LastName is null,
then it won't return @.LastName, it will Return LastName (the column, not the
variable).
Essentially making your stmt:
select * from table where FirstName = @.firstname and LastName = LastName
or rather
select * from table where FirstName = @.firstname
"John Dalberg" <nospam@.nospam.sss> wrote in message
news:20060405191639.387$oV@.newsreader.com...
> Using SQL Server 2005
> I have a simple table example with two columns: FirstName varchar(20) and
> LastName varchar(20)
> I am doing something like this in a stored procedure where @.firstname and
> @.lastname are passed in and @.lastname could be null
> select * from table where FirstName = @.firstname and LastName =
> COALESCE(@.LastName, LastName)
> I am also using "set ansi_nulls off".
> The query should give names where LastName is null if @.Lastname = null but
> that's not happening. Why?
> John Dalberg|||>The query should give names where LastName is null if @.Lastname = null but
>that's not happening. Why?
It sounds like this might be confusion over how NULL works.
If table.LastName IS NULL, and @.LastName IS NULL, then
COALESCE(@.LastName, LastName) will resolve to NULL. In that situation
the test:
LastName = COALESCE(@.LastName, LastName)
resolves to:
NULL = NULL
Which comparison will never resolve to TRUE. NULL is never equal to
anything, including another NULL. Consider these comparisons:
NULL = 'banana'
NULL <> 'banana'
NULL = NULL
NULL <> NULL
None of those can ever be resolved as TRUE, because equality (or
inequality) can only result from comparing something to something.
NULL is nothing, and can not be compared at all.
Roy Harvey
Beacon Falls, CT
On 05 Apr 2006 23:06:02 GMT, nospam@.nospam.sss (John Dalberg) wrote:

>Using SQL Server 2005
>I have a simple table example with two columns: FirstName varchar(20) and
>LastName varchar(20)
>I am doing something like this in a stored procedure where @.firstname and
>@.lastname are passed in and @.lastname could be null
>select * from table where FirstName = @.firstname and LastName =
>COALESCE(@.LastName, LastName)
>I am also using "set ansi_nulls off".
>The query should give names where LastName is null if @.Lastname = null but
>that's not happening. Why?
>John Dalberg|||Roy Harvey <roy_harvey@.snet.net> wrote:
> It sounds like this might be confusion over how NULL works.
> If table.LastName IS NULL, and @.LastName IS NULL, then
> COALESCE(@.LastName, LastName) will resolve to NULL. In that situation
> the test:
> LastName = COALESCE(@.LastName, LastName)
> resolves to:
> NULL = NULL
> Which comparison will never resolve to TRUE. NULL is never equal to
> anything, including another NULL. Consider these comparisons:
> NULL = 'banana'
> NULL <> 'banana'
> NULL = NULL
> NULL <> NULL
> None of those can ever be resolved as TRUE, because equality (or
> inequality) can only result from comparing something to something.
> NULL is nothing, and can not be compared at all.
But when you have set ansi_nulls off and run:
select * from table where lastname = null, it returns rows where lastname=
null
Doesn't that statement translate to:
select * from table where null = null ?
and when you set ansi_nulls on
one needs to write it as: select * from table where lastname is null ?
John Dalberg|||take a look at this
declare @.v1 int,@.v2 int
select @.v1 = null,@.v2 = null
if @.v1 =@.v2
print 'equal'
else
print 'not equal'
go
declare @.v1 int,@.v2 int
select @.v1 = null,@.v2 = null
if @.v1 is null and @.v2 is null
print 'Both null'
else
print 'both not null'
go
set ansi_nulls off
go
declare @.v1 int,@.v2 int
select @.v1 = null,@.v2 = null
if @.v1 =@.v2
print 'equal'
else
print 'not equal'
go
set ansi_nulls on
go
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"Paul Wehland" <PaulWe@.REMOVE-ME.Avanade.com> wrote:
> Because COALESCE returns the first non-NULL value. If @.LastName is null,
> then it won't return @.LastName, it will Return LastName (the column, not
> the variable).
> Essentially making your stmt:
> select * from table where FirstName = @.firstname and LastName =
> LastName or rather
> select * from table where FirstName = @.firstname
What does COALESCE return in this case when there's no none null values?
(not that it makes sense)
select * from table where FirstName = @.firstname and LastName =
COALESCE(@.LastName, @.LastName)
Would that translate to:
select * from table where FirstName = @.firstname and LastName = null ?
Doesn't 'set ansi_null off' make 'null =null' evaluate to true?
Anyways, I need the where clause to include the lastname if @.lastname has a
value and return null lastnames rows if @.lastname is null. I couldn't find
a way to do it in a CASE expression. I can do it in a dynamic sql.
John Dalberg
> "John Dalberg" <nospam@.nospam.sss> wrote in message
> news:20060405191639.387$oV@.newsreader.com...|||>>What does COALESCE return in this case when there's no none null values?
(not that it makes sense)
COALESCE will return the first non NULL value
Here is an example
declare @.v1 int,@.v2 int,@.v3 int,@.v4 int
select @.v1 = null,@.v2 = null,@.v3 =4,@.v4 =8
select coalesce(@.v1,@.v2,@.v3,@.v4)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote:
> take a look at this
> declare @.v1 int,@.v2 int
> select @.v1 = null,@.v2 = null
> if @.v1 =@.v2
> print 'equal'
> else
> print 'not equal'
> go
> declare @.v1 int,@.v2 int
> select @.v1 = null,@.v2 = null
> if @.v1 is null and @.v2 is null
> print 'Both null'
> else
> print 'both not null'
> go
> set ansi_nulls off
> go
> declare @.v1 int,@.v2 int
> select @.v1 = null,@.v2 = null
> if @.v1 =@.v2
> print 'equal'
> else
> print 'not equal'
> go
> set ansi_nulls on
> go
I understand the principles. That's why I included set ansi_nulls off in my
clarification.|||It is easier to communicate using DDL. I've added DDL at the end of this tex
t so we can use that
from here on.
Let me see if I can re-phrase your question:
You have some rows which has NULL on the lastname column. You want to find t
hem using ANSI_NULLS OFF
and by passing in NULL in the @.lastname parameter of your stored procedure.
So, in the query, you
have the following condition:
AND LastName = COALESCE(@.LastName, LastName)
So, if if you pass NULL for the @.lastname parameter, the condition will tran
slate to:
AND LastName = LastName
And you wonder why that will not return the rows where you have NULL in the
lastname column. Is that
correct?
If so, read in Books Online about SET ANSI_NULLS OFF. It only comments about
comparsions between a
column and NULL, not between two columns where each has NULL. I guess that t
his is how Sybase
defined it some 20 years ago, and MS has kapt this behavior. You could do a
BOL feedback and ask
them to clarify this in the 2005 BOL.
DDL:
USE tempdb
CREATE TABLE t(firstname varchar(30) not null, lastname varchar(30) null)
insert into t (firstname, lastname)
VALUES('John', 'Dalberg')
insert into t (firstname, lastname)
VALUES('Franz', NULL)
SET ANSI_NULLS OFF
GO
CREATE PROC p
@.firstname varchar(30), @.lastname varchar(30)
AS
SELECT firstname, lastname
from t
where FirstName = @.firstname
AND LastName = COALESCE(@.LastName, LastName)
GO
EXEC p 'John', 'Dalberg'
EXEC p 'John', NULL
EXEC p 'Franz', NULL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Dalberg" <nospam@.nospam.sss> wrote in message news:20060406121819.765$iB@.newsreader.c
om...
> Roy Harvey <roy_harvey@.snet.net> wrote:
> But when you have set ansi_nulls off and run:
> select * from table where lastname = null, it returns rows where lastname=
> null
> Doesn't that statement translate to:
> select * from table where null = null ?
> and when you set ansi_nulls on
> one needs to write it as: select * from table where lastname is null ?
> John Dalberg|||"Paul Wehland" <PaulWe@.REMOVE-ME.Avanade.com> wrote:
> Because COALESCE returns the first non-NULL value. If @.LastName is null,
> then it won't return @.LastName, it will Return LastName (the column, not
> the variable).
> Essentially making your stmt:
> select * from table where FirstName = ffirstname and LastName =
> LastName or rather
Right and that's the way it should work so when you have ansi_nulls off,
lastname = lastname will return rows where lastname is null and it will not
return these rows if set ansi_nulls on. I am missing what was wrong in my
statement.
so:
set ansi_nulls off;select * from table where FirstName = firstname and
LastName =lastname
&
select * from table
both return the same # of rows regardless whether lastname is null or not.
John Dalbergsql

Case Conditional in SQL Statement - MS SQL 2000

Hi,

I'm trying to do calculations in a SQL statement, but depending on one
variable (a.type in example) I'll need to pull another variable from
seperate tables.

Here is my code thus far:

select a.DeptCode DeptCode,
a.Type Type,
(a.ExpenseUnit / (select volume from TargetData b where b.type =
a.type)
) Expense
Fromcalc1 a

The problem... a.Type can be FYTD, Budget, or Target... and depending
on which one it is, I need to make b either FYTDData, TargetData, or
BudgetData. I'm thinking a case statement might do the trick, but I
can't find any syntax on how to use Case in an MS SQL statement. Even
If statements will work (if that's possible), though case would be
less messy.

Any suggestions would be much appriciative. Thanks...

Alex.Hi

Is it not totally clear how you are joining these tables, but this may be a
start.

SELECT a.DeptCode DeptCode,
a.Type Type,
a.ExpenseUnit / ( CASE WHEN a.Type = 'FYTD' THEN b.volume
WHEN a.Type = 'Budget' THEN
c.volume
WHEN a.Type = 'Target' THEN
d.volume
ELSE 1 END ) AS Expense
From calc1 a
LEFT JOIN FYTDData d ON b.type = a.type
LEFT JOIN BudgetData d ON c.type = a.type
LEFT JOIN TargetData d ON d.type = a.type

John

"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310010840.5910e221@.posting.google.c om...
> Hi,
> I'm trying to do calculations in a SQL statement, but depending on one
> variable (a.type in example) I'll need to pull another variable from
> seperate tables.
> Here is my code thus far:
> select a.DeptCode DeptCode,
> a.Type Type,
> (a.ExpenseUnit / (select volume from TargetData b where b.type =
> a.type)
> ) Expense
> From calc1 a
> The problem... a.Type can be FYTD, Budget, or Target... and depending
> on which one it is, I need to make b either FYTDData, TargetData, or
> BudgetData. I'm thinking a case statement might do the trick, but I
> can't find any syntax on how to use Case in an MS SQL statement. Even
> If statements will work (if that's possible), though case would be
> less messy.
> Any suggestions would be much appriciative. Thanks...
> Alex.|||Alex (alex@.totallynerd.com) writes:
> The problem... a.Type can be FYTD, Budget, or Target... and depending
> on which one it is, I need to make b either FYTDData, TargetData, or
> BudgetData. I'm thinking a case statement might do the trick, but I
> can't find any syntax on how to use Case in an MS SQL statement.

Books Online is a very resource for this kind of information, just
look up CASE. Be careful to notice that this is not a statement, but
an expression.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<3f7b0da9$0$8765$ed9e5944@.reading.news.pipex.net>...
> Hi
> Is it not totally clear how you are joining these tables, but this may be a
> start.
> SELECT a.DeptCode DeptCode,
> a.Type Type,
> a.ExpenseUnit / ( CASE WHEN a.Type = 'FYTD' THEN b.volume
> WHEN a.Type = 'Budget' THEN
> c.volume
> WHEN a.Type = 'Target' THEN
> d.volume
> ELSE 1 END ) AS Expense
> From calc1 a
> LEFT JOIN FYTDData d ON b.type = a.type
> LEFT JOIN BudgetData d ON c.type = a.type
> LEFT JOIN TargetData d ON d.type = a.type
> John

Hi John...

I did get it going yesterday after spending about an hour testing
syntax. Below is the final SQL statement. Works great!

select a.DeptCode,
a.Type,
(((a.TotalPaidHoursUnit/(Case a.type
When 'FYTD04' Then null
When 'Budget' Then (select b.monthly from it_budvol b where
b.deptcode = a.deptcode)
When 'Prior Year' Then (select b.avemonth from it_pyvolume b
where b.deptcode = a.deptcode)
Else (select b.AveMonthVolume from solucient_dss b where
b.deptcode = a.deptcode and b.type = a.type)
end)
- a.FYTD_TotalPaidHoursUnit ) / a.Hours) * a.FYTD_Volume) LaborFTE

Fromdss_calc a

Thanks for the feedback.

Alex.|||alex@.totallynerd.com (Alex) wrote in message news:<2ba4b4eb.0310020704.4c08463e@.posting.google.com>...
> Hi John...
> I did get it going yesterday after spending about an hour testing
> syntax. Below is the final SQL statement. Works great!
>
> select a.DeptCode,
> a.Type,
> (((a.TotalPaidHoursUnit/(Case a.type
> When 'FYTD04' Then null
> When 'Budget' Then (select b.monthly from it_budvol b where
> b.deptcode = a.deptcode)
> When 'Prior Year' Then (select b.avemonth from it_pyvolume b
> where b.deptcode = a.deptcode)
> Else (select b.AveMonthVolume from solucient_dss b where
> b.deptcode = a.deptcode and b.type = a.type)
> end)
> - a.FYTD_TotalPaidHoursUnit ) / a.Hours) * a.FYTD_Volume) LaborFTE
> Fromdss_calc a
> Thanks for the feedback.
> Alex.
Hi

You should make sure that you are not dividing by zero.

John

Cascading Triggers

Hi,
I have 3 Tables with After Update Triggers.
They all cascade like City -> Province -> State (as a simplified example)
If I Update a bit (A) in City, a Trigger Sets a bit (B) in Province.
If I Update the bit B in Province, a Trigger Sets a Bit (C) in State.
This works.
However:
If I Set A, a trigger on table City sets B BUT a trigger on table Province
DOESN'T in turn Set C in table State.
So: Individually the Triggers work, but they don't Cascade their action.
Any advise?
TIA,
MichaelHi Michael,
I think I'm having the same problem. I'm hoping to take action via a
trigger on child records when the parent is deleted, but the child table's
trigger doesn't seem to fire. Hopefully someone has a suggestion.
Rgds,
Bill
"Michael Maes" <michael@.merlot.com> wrote in message
news:5BEB7F99-288B-48A8-98DE-F2FBD22AFE75@.microsoft.com...
> Hi,
> I have 3 Tables with After Update Triggers.
> They all cascade like City -> Province -> State (as a simplified example)
> If I Update a bit (A) in City, a Trigger Sets a bit (B) in Province.
> If I Update the bit B in Province, a Trigger Sets a Bit (C) in State.
> This works.
> However:
> If I Set A, a trigger on table City sets B BUT a trigger on table Province
> DOESN'T in turn Set C in table State.
> So: Individually the Triggers work, but they don't Cascade their action.
> Any advise?
> TIA,
>
> Michael
>|||There is a server option "Allow triggers to be fired which fire other
triggers (nested triggers)". Here is the description copied from BO:
1. Expand a server group.
2. Right-click a server, and then click Properties.
3. Click the Server Settings tab.
4. Under Server behavior, select or clear the Allow triggers to be
fired which fire other triggers (nested triggers) check box.
For more information, see Books Online, article "Using Nested Triggers"|||Hi Sergei,
Thanks for your advise!
I just found an article on this:
http://www.sqlservercentral.com/col.../triggers_1.asp
Nested and Recursive Triggers
Nested triggers are triggers that fire due to actions of other triggers.
For instance, I delete a row from TableA. A trigger on TableA fires to
delete rows from TableB. Because I'm deleting rows from TableB, a trigger
fires on TableB to record the deletes. This is an example of a nested
trigger. As we've talked about, SQL Server 7.0 doesn't support cascading
updates and deletes based on foreign key relationships. Therefore, if we
want to relate our data we can't use DRI and must resort to triggers or some
application oversight. Let's say we've got a cascade delete to fire down 3
or 4 tables. Nested triggers are our answer. Our delete on a particular
table fires a trigger which deletes rows for another table, which fires a
trigger, so on and so forth. SQL Server 7 and 2000 support up to 32 levels
of nested triggers.
Now the big question is, does my SQL Server allow nested triggers? That's
an easy question to answer. It's on by default, but in Query Analyzer we ca
n
issue the following command:
EXEC sp_configure 'nested triggers'
If your run_value is set to 0, your server isn't allowing nested triggers.
If it's set to 1, nested triggers may fire. This is a server wide setting.
Now, to change your setting, once again use the sp_configure command:
To turn off nested triggers:
EXEC sp_configure 'nested triggers', 0
RECONFIGURE
To turn on nested triggers:
EXEC sp_configure 'nested triggers', 1
RECONFIGURE
"Sergei Almazov" wrote:

> There is a server option "Allow triggers to be fired which fire other
> triggers (nested triggers)". Here is the description copied from BO:
> 1. Expand a server group.
> 2. Right-click a server, and then click Properties.
> 3. Click the Server Settings tab.
> 4. Under Server behavior, select or clear the Allow triggers to be
> fired which fire other triggers (nested triggers) check box.
> For more information, see Books Online, article "Using Nested Triggers"
>|||I wonder why the defualt setting is off for this. (Protecting us from
potentially faulty programming perhaps?) ;-) Also, seems like it would be
handy to be able to turn it on per table or database, rather than at the
server level.
In my case where I'm just trying to perform some actions first with the
information in detail records that will be deleted, would it be better to
turn off the cascade delete for the related table and handle deleting of the
detail records in the delete trigger of the master table, or is it better to
allow nested triggers?
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:1127473954.892200.319820@.g43g2000cwa.googlegroups.com...
> There is a server option "Allow triggers to be fired which fire other
> triggers (nested triggers)". Here is the description copied from BO:
> 1. Expand a server group.
> 2. Right-click a server, and then click Properties.
> 3. Click the Server Settings tab.
> 4. Under Server behavior, select or clear the Allow triggers to be
> fired which fire other triggers (nested triggers) check box.
> For more information, see Books Online, article "Using Nested Triggers"
>|||I wonder why the defualt setting is off for this. (Protecting us from
potentially faulty programming perhaps?) ;-) Also, seems like it would be
handy to be able to turn it on per table or database, rather than at the
server level.
In my case where I'm just trying to perform some actions first with the
information in detail records that will be deleted, would it be better to
turn off the cascade delete for the related table and handle deleting of the
detail records in the delete trigger of the master table, or is it better to
allow nested triggers?
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:1127473954.892200.319820@.g43g2000cwa.googlegroups.com...
> There is a server option "Allow triggers to be fired which fire other
> triggers (nested triggers)". Here is the description copied from BO:
> 1. Expand a server group.
> 2. Right-click a server, and then click Properties.
> 3. Click the Server Settings tab.
> 4. Under Server behavior, select or clear the Allow triggers to be
> fired which fire other triggers (nested triggers) check box.
> For more information, see Books Online, article "Using Nested Triggers"
>|||Hi Bill,
What scares me is that someone or something else can disable your nested
triggers because it's DataServer-Wide :-(((
"Bill Hicks" wrote:

> I wonder why the defualt setting is off for this. (Protecting us from
> potentially faulty programming perhaps?) ;-) Also, seems like it would be
> handy to be able to turn it on per table or database, rather than at the
> server level.
> In my case where I'm just trying to perform some actions first with the
> information in detail records that will be deleted, would it be better to
> turn off the cascade delete for the related table and handle deleting of t
he
> detail records in the delete trigger of the master table, or is it better
to
> allow nested triggers?
> "Sergei Almazov" <almazik@.ukr.net> wrote in message
> news:1127473954.892200.319820@.g43g2000cwa.googlegroups.com...
>
>

Cascading Parameters based off Analysis Services

Hello,

I was trying to do cascading parameters based off my cube and I wasn't able to do this. Is it possible?

For example, I have a dimension that has Products so I first select the parameter for product type (Dairy, Frozen, Candy) and then I have another dropdown listbox that has the name of each product (Milk, Ice Cream, Lemon Drops). The second dropdown listbox should only contain the products that match what parameter was selected in the first dropdown.

When I couldn't get that to work, I went to the source system containing the Dimension tables and just did nice and easy SQL statements from there. It worked but I, for some reason that I can't explain, think this is not the proper way to do it.

Also, is there a way to have a default on the second parameter based on the first parameter selected? I would assume that default would be [All].

Thank you.

-Gumbatman

Yes it is possible. When you use the query designer, by default each parameter depends on the one before it having a value, even if the parameters are not really related. For this, you want to maintain that link. Then, in your MDX that creates the dataset for parameter #2 you can refer to parameter #1 (it will be called something like @.MyParameterName), i.e. you can use it as part of a STRTOSET or STRTOMEMBER function.|||

Sluggy,

Thank you so much for this answer. I was banging my head against the wall trying to figure out how to do this.

-Gumbatman

|||

Could you please post an example of this? My head also aches a lot

I just need the MDX syntax of the second dataset, the one of the parameter that depends on the first one selected.

Is there any way, from within SSRS to "filter" based on the previous parameter inside the MDX sentence?

Using SQL tables datasources and cascading parameters is quite straightforward, not the same with Olap cubes as datasources.

Thank you

Mike

Cascading Filters

Hi,

is there a way to define cascading filters?

Let me provide you an example:

Let's say, we have a dimension "client", a dimension "cost unit" and a dimension "product category". Each client has it's own cost units and product categories and we want to use this 3 dimensions as filters: When a client is selected in the client filter, there should only this client's cost units and product categories be available in the other two filters.

Is there a way to archive that?

Whishes,

Manfred

Hi Manfred,

Are the "cost unit" and "product category" intrinsically associated with a "client", in which case they could be modelled as attributes of "client", or only dynamically associated via fact data? In either case, cascading filtering is typically a functionality supported in fornt-end tools - but it certainly can be used with OLAP data sources. For example, Reporting Services has such support - the exact MDX expressions will depend on how the association between the parameters is modelled in the cube:

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

>>

SQL Server 2005 Books Online

How to: Add Cascading Parameters to a Report

New: 17 July 2006

Cascading parameters provide a way of managing large amounts of report data. You can define a set of parameters where the list of values for one parameter depends on the value chosen in another parameter. For example, the first parameter could present a list of product categories. When the user selects a category, the second parameter is updated with a list of subcategories within the category. A third parameter could then display a list of products within the selected subcategory. The value for the product parameter could then be used to filter the report to a particular product. This process of filtering a list of parameter values based on a value from another parameter is known variously as cascading, dependent, or hierarchical parameters.

You create a separate dataset that supplies available values for each cascading parameter. Order is important for cascading parameters because the dataset query for a parameter later in the list includes references to parameters earlier in the list. The order of the parameters determines the order in which the parameter queries are run. When you open the Report Properties dialog box, the parameters are listed in order. You can change the order by using the up and down arrow buttons.

...

>>

|||

Hi,

thanks for your reply. I forgot to mention that I want to use OLAP-Dimensions as filters.

For Example, if I put the ProductCategory und the ProductSubCategory-Dimension on the filter section in MS Excel (after connecting to a cube) the Drowdown containing the SubCategories is not updated after selecting a ProductCategory.

Of corse, in this case I could use a hierarchy - but this is not suitable in all cases, especially when there is one parameter that should filter the possible values of several other parameters, like in the mentioned saple.

Whishes,

Manfred

|||So, to clarify - the front-end OLAP tool you're using is an Excel pivot table - which version? I'm not sure whether you can implement cascading parameters in Excel OLAP pivot tables, by adding custom event programming.|||

Hi,

we're using 2007 Excel pivot tables. Is this a client-issue or is there a way to define filtering-behavior

on the server-side? Is ProClarity capable of that?

Regards,

Manfred

|||This would be client-side functionality - don't know a way to do this on the server (other than using a dimension hierarchy). As I mentioned, Reporting Services supports this, as do some others - maybe someone more familiar with Proclarity could comment?|||Hi,

I am facing same problem with proclarity.
can anyone suggest how to use cascading filters in proclarity?

Cascade Delete Question

I am attempting to clarify how the cascade delete works. For example I have a Parent table and two child tables used for lookup that have the ID_FK = ID_PK from the Parent table.

tblUsers
-----
ID_PK
Fname
Lname
...

tblUser_Phone
------
ID_FK
Phone
...

tbUser_Email
------
ID_FK
Email
...

If I needed to delete all records from tblUser_Phone would this delete my USER entry from the parent table tblUsers if I have cascade delete enabled?

Thanks in advance.I believe cascade delete only deletes children records, so the parent table would be safe.|||I believe you are correct. Cascading only travels down the hierarchy, not up.

Carriage return in column values

Hi,
How can I find out where the carriage returns are in a column value in a
table? For example, we have an Address column in a Customer table. This
column value can have a maximum of 255 characters where carriage returns are
allowed. How can I find out where those carriage returns are?
Thank you in advance,
DeeOne way is using CHARINDEX, if you only want to first position:
USE tempdb
CREATE TABLE t(c1 int identity, c2 varchar(2000))
INSERT INTO t VALUES('Hello
there')
INSERT INTO t VALUES('Hi everybody
I''ts Doctor Nick')
SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
> Hi,
> How can I find out where the carriage returns are in a column value in a
> table? For example, we have an Address column in a Customer table. This
> column value can have a maximum of 255 characters where carriage returns are
> allowed. How can I find out where those carriage returns are?
> Thank you in advance,
> Dee|||Thank you, Tibor, for your response. Let's put a different twist to it now.
Let's say
I want to identify customer records where the address has 1 or more lines in
the Address column that are greater than 40 characters. Obviously, the text
before each carriage return is considered as one line in the Address column.
"Tibor Karaszi" wrote:
> One way is using CHARINDEX, if you only want to first position:
> USE tempdb
> CREATE TABLE t(c1 int identity, c2 varchar(2000))
> INSERT INTO t VALUES('Hello
> there')
> INSERT INTO t VALUES('Hi everybody
> I''ts Doctor Nick')
> SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
> news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
> > Hi,
> >
> > How can I find out where the carriage returns are in a column value in a
> > table? For example, we have an Address column in a Customer table. This
> > column value can have a maximum of 255 characters where carriage returns are
> > allowed. How can I find out where those carriage returns are?
> >
> > Thank you in advance,
> > Dee
>|||Dealing with unknowns (like unknown number of address lines), I'd suggest writing a scalar user
defined function for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:1F0AF68D-40DA-4F7A-9C84-C086922573FE@.microsoft.com...
> Thank you, Tibor, for your response. Let's put a different twist to it now.
> Let's say
> I want to identify customer records where the address has 1 or more lines in
> the Address column that are greater than 40 characters. Obviously, the text
> before each carriage return is considered as one line in the Address column.
> "Tibor Karaszi" wrote:
>> One way is using CHARINDEX, if you only want to first position:
>> USE tempdb
>> CREATE TABLE t(c1 int identity, c2 varchar(2000))
>> INSERT INTO t VALUES('Hello
>> there')
>> INSERT INTO t VALUES('Hi everybody
>> I''ts Doctor Nick')
>> SELECT CHARINDEX(CHAR(13) + CHAR(10), c2) FROM t
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
>> news:0EF8E157-C008-4370-A355-DB9E43A3F741@.microsoft.com...
>> > Hi,
>> >
>> > How can I find out where the carriage returns are in a column value in a
>> > table? For example, we have an Address column in a Customer table. This
>> > column value can have a maximum of 255 characters where carriage returns are
>> > allowed. How can I find out where those carriage returns are?
>> >
>> > Thank you in advance,
>> > Dee
>>

Capture time alone in SQL Server database

Is there any data type in SQL Server 2005 which captures only the TIME in default DATETIME type?

For example, if the datatime field has a value2007-12-11 12:31:00.000, i need a datatype which can capture12:31:00.000 alone. The data type should be in a fashion so that i can find differences in time also...

Any ideas??

Hi,

SQL Server does not have any type which can store only time. One way is to store it as datetime, and when fetching these times, you can convert them to only show time.

|||

venkatesh_ur:

Is there any data type in SQL Server 2005 which captures only the TIME in default DATETIME type?

I'm afraid there is no Datatype to fetch the time only. But of course there are some functions that can be used to get the time part out of any datatime value. Below query will get you the time (converted as varchar):

select getdate() , convert ( varchar , getdate() , 8 )

venkatesh_ur:

he data type should be in a fashion so that i can find differences in time also...

To compare date and time values theDatepartandDateNamefunctions can be useful. They both are quite similar to each other. Read Books Online for more help on these functions.

capture error messages from dynamic tsql

How does one go about capturing error messages for a procedure that executes
dynamic sql...
for example ten lines out of 1000 produce an error... can the error be
captured and returned to the user?
--
Regards,
JamieI'm not sure what you are asking. Exceptions *are* returned to the user by default. See below:
USE tempdb
CREATE TABLE t(c1 int CHECK (c1 < 10))
GO
CREATE PROC p AS
EXEC('INSERT INTO t (c1) VALUES (20)')
GO
--Verify error
EXEC p
GO
--Capture using TRY and CATCH
BEGIN TRY
EXEC p
END TRY
BEGIN CATCH
DECLARE @.errStr nvarchar(4000)
SET @.errStr = ERROR_MESSAGE()
PRINT @.errStr
END CATCH
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:A075E032-9E58-4F19-A8E6-D66D5ED66357@.microsoft.com...
> How does one go about capturing error messages for a procedure that executes
> dynamic sql...
> for example ten lines out of 1000 produce an error... can the error be
> captured and returned to the user?
> --
> Regards,
> Jamie|||I should have added we are still using SQL 2000. Tested this in 2005 and it
works great. Thanks.
--
Regards,
Jamie
"Tibor Karaszi" wrote:
> I'm not sure what you are asking. Exceptions *are* returned to the user by default. See below:
> USE tempdb
> CREATE TABLE t(c1 int CHECK (c1 < 10))
> GO
> CREATE PROC p AS
> EXEC('INSERT INTO t (c1) VALUES (20)')
> GO
> --Verify error
> EXEC p
> GO
> --Capture using TRY and CATCH
> BEGIN TRY
> EXEC p
> END TRY
> BEGIN CATCH
> DECLARE @.errStr nvarchar(4000)
> SET @.errStr = ERROR_MESSAGE()
> PRINT @.errStr
> END CATCH
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:A075E032-9E58-4F19-A8E6-D66D5ED66357@.microsoft.com...
> > How does one go about capturing error messages for a procedure that executes
> > dynamic sql...
> > for example ten lines out of 1000 produce an error... can the error be
> > captured and returned to the user?
> > --
> > Regards,
> > Jamie
>
>