Check mail and password with sp

How can I write a stored procedure with SQL 2000 Server to check an e-mail and password entered by the user, and if both exist select the Id field ?

Table Users:
UserId
First_name
Surname
Age
E-mail
Password

If it is possible I would like to know how can I write the error messages in three cases: If E-mail doesn't exist, if password doesnt exist and if both doesnt exist. And I would like that those messages would be able to appear on the website application.

ThanksMay check Planet SC (http://planet-source-code.com) for any CE.|||Ok thank you

Check if temporary table exists

Hello.

How can I check if a temporary table exists in the current context?

With normal tables I'd do a

EXISTS ( SELECT name FROM sysobjects

WHERE name='myTableName' AND type='U')

However, I can't do that with a temporary table. I'd have to go look at the sysobjects table in the tempdb database.

The problem is that for temporary tables, a suffix is added to the name to make it unique for each scope. I can't change the WHERE clause to name LIKE 'myTableName%' because this would return true if a temporary table with the same name exists in a different scope.

Any ideas?

Carlos

You can try the following

IF object_id('tempdb..#MyTempTable') IS NOT NULL
BEGIN
DROP TABLE #MyTempTable
END

CREATE TABLE #MyTempTable
(
ID int IDENTITY(1,1),
SomeValue varchar(100)
)
GO

|||IF OBJECT_ID('tempdb..#MyTempTable', 'U') IS NOT NULL
Print 'Yes'
Else
Print 'No'sql

Check if record has dependencies

I have a database where one table 'Project' has a one to many relationship
with several other tables. Other than doing multiple Select queries, is
there a simple quick way of testing to see if there are any dependencies in
the tables connect with the constraints
Cheerstry this:
sp_help <table>
--
current location: alicante (es)
"Newbie" wrote:

> I have a database where one table 'Project' has a one to many relationship
> with several other tables. Other than doing multiple Select queries, is
> there a simple quick way of testing to see if there are any dependencies i
n
> the tables connect with the constraints
> Cheers
>
>|||What ?,
were you replying to someone else by mistake, this makes absolutely no sense
to me whatsoever !
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:2BF857E7-DDE1-4AFF-AA13-965C3573162B@.microsoft.com...
> try this:
> sp_help <table>
> --
> current location: alicante (es)
>
> "Newbie" wrote:
>|||Basically you want to do a outer left join with any related tables. Let's
assume that the Project table has related tables Tasks and Notes. The
following will count the number of projects that have at least one task or
note:
select
count(distinct Project.ProjectID) as CountAssignedProjects
from Project
left join Tasks on Tasks.ProjectID = Project.ProjectID
left join Notes on Notes.ProjectID = Project.ProjectID
where
Tasks.ProjectID is not null or
Notes.ProjectID is not null
"Newbie" <me@.me.com> wrote in message
news:%23yfFEoBSGHA.4792@.TK2MSFTNGP14.phx.gbl...
>I have a database where one table 'Project' has a one to many relationship
>with several other tables. Other than doing multiple Select queries, is
>there a simple quick way of testing to see if there are any dependencies in
>the tables connect with the constraints
> Cheers
>|||I'm sorry I was wrong. I though that you are looking for the current
references for a table and using sp_help such request is returned...
--
current location: alicante (es)
"Newbie" wrote:

> What ?,
> were you replying to someone else by mistake, this makes absolutely no sen
se
> to me whatsoever !
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:2BF857E7-DDE1-4AFF-AA13-965C3573162B@.microsoft.com...
>
>

Check if record exists

Hello,

I created the following SQL script to check if a record exists:

IF (EXISTS (SELECT LevelName FROM dbo.by27_Levels WHERE LOWER(@.LevelName) = LOWER(LevelName)))
Return (1)
ELSE
Return (0)

And I also found in a web page another solution:
IF EXISTS(SELECT 1 FROM TABLENAME WHERE LevelName=@.LevelName)
SELECT 1
ELSE
SELECT 0

- Which approach should I use?
- Why "SELECT 1 FROM"?
- And when should I use SELECT or RETURN?

All I need is to know if the record exists ... nothing else.

I will use this procedure on an ASP.NET 2.0 / C# web site.
I am not sure if this important but anyway ...

Thank You,
Miguel

select 1 from table returns a value which is basically the same as selecting a column name when evaluating from the exists function. The difference is that 1 is a constant so the column name does not need to be looked up, and since you do not need the value of the column then select 1 can be used.

using return or select depends on what you are using to call the sql statement. If you use return then you need to look into the calls returns parameters. Using a select , you need to use a scalar or dataset return call. Most people use the select call because those calls are easier to handle but not necessarily more efficient

|||

In EXISTS you can use any of them but the result will be the same (it always look for first occurrence of value selected) I do not know about time of execution but I would prefer something like this

RETURN (CASE
when EXISTS (SELECT LevelName
FROM dbo.by27_Levels
WHERE LOWER(@.LevelName) = LOWER(LevelName))) then 1
else
0
end)

If it will be executed on SQL server and you server is Case insensitive you do not have to use LOWER and it will speed up a little.

Thanks

|||

ozkary:

If you use return then you need to look into the calls returns parameters.

What do you mean to look the calls returns parameters?

Can you point to some info about it?

Thanks,.

Miguel

|||

By default SQL Server is not case sensitive so the LOWER() is not needed.

If the LevelName is a unique key for the table I would avaoid using T-SQL and use a single generic SQL query:

SELECT COUNT(*) FROM dbo.by27_Levels WHERE @.LevelName = LevelName

RETURN ends the execution of the batch T-SQL and SELECT does not. Note any select results not stored in local variables will be output.

|||

Some stored procedures could have return parameters they are defined with OUTPUT

create procedure TEST

@.tcParam1 as varchar(100) = NULL,

@.tcOutputParam as varchar(100) = NULL OUTPUT

AS

BEGIN

...

SET @.tcOutputParam= 'result'

END

and if you call it

declare @.oparam as varchar(100)

exec test 'Test valuee', @.oparam OUTPUT

you can get output value from procedure

RETURN always is returned by stored procedure and you can get it like:but it only integer, output parameter can be almost any type

EXEC @.result = test 'Test valuee', @.oparam OUTPUT

Thanks

|||

yes, one usually uses parameters to call a stored procedure. Those parameters can have the following directions: INPUT, OUTPUT, RETURN.

To hadle a return value, one needs to add a return parameter to the call:

SqlCommand cmd = new SqlCommand("myProc", myConnection)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters..Add("ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue

add block to make the call

if using a reader make sure to close it before trying to read the return parameter

read the return parameter value

string value = cmd.Parameters.item["ReturnValue"].Value.Tostring();

for more info search on ParameterDirection.ReturnValue

I hope this helps.

Check if image column is empty

Currently I use the follow Select statement to test if a non-nullable image
column is empty and not null:
Select Case When Substring(ImageColumn,1,1)='' Then 1 Else 0 End As IsEmpty
Can any body show me a better way to check if a column (non-nullable column)
of image data type is empty.DataLength(ColName) will be zero (0) if it's empty...
"krygim" wrote:

> Currently I use the follow Select statement to test if a non-nullable imag
e
> column is empty and not null:
> Select Case When Substring(ImageColumn,1,1)='' Then 1 Else 0 End As IsEmpt
y
> Can any body show me a better way to check if a column (non-nullable colum
n)
> of image data type is empty.
>
>|||Or, actually, just check if the column's value itself = '' (empty string)...
That should work. you don't need to attempt to extract the first character
and test that...
"krygim" wrote:

> Currently I use the follow Select statement to test if a non-nullable imag
e
> column is empty and not null:
> Select Case When Substring(ImageColumn,1,1)='' Then 1 Else 0 End As IsEmpt
y
> Can any body show me a better way to check if a column (non-nullable colum
n)
> of image data type is empty.
>
>|||Thanks
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:380D392D-B82A-493E-AA22-C137F4599577@.microsoft.com...
> DataLength(ColName) will be zero (0) if it's empty...
> "krygim" wrote:
>
image
IsEmpty
column)|||I got the error message: "The text, ntext, and image data types cannot be
compared or sorted."
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:3ADDA38D-0AC0-4377-9933-182222F35875@.microsoft.com...
> Or, actually, just check if the column's value itself = '' (empty
string)...
> That should work. you don't need to attempt to extract the first
character
> and test that...
>
> "krygim" wrote:
>
image
IsEmpty
column)|||Yes, you're right, DataLength() is the only other way...
"krygim" wrote:

> I got the error message: "The text, ntext, and image data types cannot be
> compared or sorted."
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:3ADDA38D-0AC0-4377-9933-182222F35875@.microsoft.com...
> string)...
> character
> image
> IsEmpty
> column)
>
>

check if field contains numeric character

How can I select all of the rows of my table for which a certain
field's value contains other characters than a-z and A-Z (non alpha
string) ?
Thank youWhere Column like '%[0-9]%'
Look up "Pattern Matching in Search Conditions" in BOL for more information.
<samuelberthelot@.googlemail.com> wrote in message
news:1148478170.488497.325220@.j33g2000cwa.googlegroups.com...
> How can I select all of the rows of my table for which a certain
> field's value contains other characters than a-z and A-Z (non alpha
> string) ?
> Thank you
>|||If it does not help ,please post your actual data + expected result
create table #t (c1 varchar(20))
insert into #t values ('cdjdfj')
insert into #t values ('cd4jdfj')
insert into #t values ('fh')
insert into #t values ('1525')
insert into #t values ('1jj')
insert into #t values ('jkk')
select * from #t where c1 like '%[0-9]%'
<samuelberthelot@.googlemail.com> wrote in message
news:1148478170.488497.325220@.j33g2000cwa.googlegroups.com...
> How can I select all of the rows of my table for which a certain
> field's value contains other characters than a-z and A-Z (non alpha
> string) ?
> Thank you
>|||found out, had to use the PATINDEX function|||Uri Dimant wrote:
> If it does not help ,please post your actual data + expected result
> create table #t (c1 varchar(20))
> insert into #t values ('cdjdfj')
> insert into #t values ('cd4jdfj')
> insert into #t values ('fh')
> insert into #t values ('1525')
> insert into #t values ('1jj')
> insert into #t values ('jkk')
>
> select * from #t where c1 like '%[0-9]%'
>
What about :
insert into test values ('!"=A3$%^&*()_')
Try this instead:
select * from #t where c1 like '%[^A-Z]%'=20
Jamie.

Check for Temp Table

How do you check if a temp table exists?
I usually do the following for tables and Views:
if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME =
'EarningsDeductions')
DROP Table EarningsDeductions
if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'V' and NAME =
'EarningsWithRank')
DROP VIEW EarningsWithRank
But I can't seem to find out how to check for a temp Table.
I tried "select * from sysobjects where NAME = '#TestTable'" to see if it
was there and whether there was a type code there, but there wasn't.
Thanks,
Tomtry this:
http://www.devx.com/tips/Tip/13938
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ORgLLoxvFHA.3756@.tk2msftngp13.phx.gbl...
> How do you check if a temp table exists?
> I usually do the following for tables and Views:
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME
> = 'EarningsDeductions')
> DROP Table EarningsDeductions
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'V' and NAME
> = 'EarningsWithRank')
> DROP VIEW EarningsWithRank
> But I can't seem to find out how to check for a temp Table.
> I tried "select * from sysobjects where NAME = '#TestTable'" to see if it
> was there and whether there was a type code there, but there wasn't.
> Thanks,
> Tom
>
>|||or this :
if object_id('tempdb..#temp') is not null
print 'exists'
else
print 'not exists'
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ORgLLoxvFHA.3756@.tk2msftngp13.phx.gbl...
> How do you check if a temp table exists?
> I usually do the following for tables and Views:
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'U' and NAME
> = 'EarningsDeductions')
> DROP Table EarningsDeductions
> if Exists (SELECT 'x',type,Name FROM sysobjects WHERE type = 'V' and NAME
> = 'EarningsWithRank')
> DROP VIEW EarningsWithRank
> But I can't seem to find out how to check for a temp Table.
> I tried "select * from sysobjects where NAME = '#TestTable'" to see if it
> was there and whether there was a type code there, but there wasn't.
> Thanks,
> Tom
>
>|||"Yosh" <yoshi@.nospam.com> wrote in message
news:%23%23mEoyxvFHA.708@.TK2MSFTNGP10.phx.gbl...
> or this :
> if object_id('tempdb..#temp') is not null
> print 'exists'
> else
> print 'not exists'
That would do what I wanted.
Thanks,
Tom
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ORgLLoxvFHA.3756@.tk2msftngp13.phx.gbl...
NAME
NAME
it
>

Check for numeric value

Is it possible to check whether a column (define as char
type) value is numeric using something like
Select * From table_name Where Field1 is numeric ?
Thanks,
BenYou can use the ISNUMERIC function for this:
WHERE ISNUMERIC(colname) = 1
Note, however that this returns 1 if the data can be converted to int,
float, money etc. So things like "E" and "," in the string will pass the
test. If you post what you mean precisely by "numeric" we can possibly give
a better suggestion.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ben" <bluebells88@.yahoo.com> wrote in message
news:2eb201c3a9c4$176cb430$a601280a@.phx.gbl...
> Is it possible to check whether a column (define as char
> type) value is numeric using something like
> Select * From table_name Where Field1 is numeric ?
> Thanks,
> Ben|||Thank your very much for your answer. This one works fine
for my case :)
I've another question: Is it possible to determine if the
column value is NOT alphabet. (using ASCII ? It seems
impossible to me.)
Thanks,
Ben
>--Original Message--
>You can use the ISNUMERIC function for this:
>WHERE ISNUMERIC(colname) = 1
>Note, however that this returns 1 if the data can be
converted to int,
>float, money etc. So things like "E" and "," in the
string will pass the
>test. If you post what you mean precisely by "numeric"
we can possibly give
>a better suggestion.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Ben" <bluebells88@.yahoo.com> wrote in message
>news:2eb201c3a9c4$176cb430$a601280a@.phx.gbl...
>> Is it possible to check whether a column (define as
char
>> type) value is numeric using something like
>> Select * From table_name Where Field1 is numeric ?
>> Thanks,
>> Ben
>
>.
>

Check for NULL in CASE

SELECT whatever_field,
CASE LEN(DrAccount)
WHEN 12 THEN DrAccount
ELSE CASE (Note1)
WHEN NULL THEN Location + DrAccount
ELSE Note1 + DrAccount
END
END AS Account
FROM Table1

The purpose of the CASE(Note1) is when Note1 column is null, return Location+DrAccount.

The actual result is when Note1 column is null, it always returns null, Location+DrAccount is not executed. When Note1 column is not null, it returns correctly Note1+DrAccount.

The problem seems to reside in validating the null value in
WHEN NULL

How to check for null in CASE(fieldname) WHEN ?

Have you considered using Coalesce? Coalesce(Note1 + DrAccount, Location + DrAccount)

COALESCE

Returns the first nonnull expression among its arguments.

Syntax

COALESCE(expression [,...n])

Check for no value in a local variable

How can I check the value of a local variable to see whether it reurned
a record after setting it to a SELECT Query?
I also need to be able use the value as an integer if it finds a match
Declare @.Found as Int
SET @.Found= ( SELECT TOP 1 ID FROM tbl WHERE col1=@.col1 AND col2=@.col2
)
IF @.Found ' -- how to test for no match found
Thanks.Read this article
http://vyaskn.tripod.com/difference..._and_select.htm
IF @.var IS NULL ?
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1143041257.585219.145960@.u72g2000cwu.googlegroups.com...
> How can I check the value of a local variable to see whether it reurned
> a record after setting it to a SELECT Query?
> I also need to be able use the value as an integer if it finds a match
> Declare @.Found as Int
> SET @.Found= ( SELECT TOP 1 ID FROM tbl WHERE col1=@.col1 AND col2=@.col2
> )
> IF @.Found ' -- how to test for no match found
> Thanks.
>|||Thanks!

check fields in select statement

hi,

I am getting some fields back from a select statement, how do you check one of the fields and display a string depending on what it is? Is there something like an if statement you can use? for example

select

field1,
field2 /*how do you check to see what it is here and display something depending on what it is*/

from
record

I am trying to see if field2 is a '' or empty string character

thxYou use a CASE statement.

SELECT field1, CASE field2 WHEN '' THEN 'BLANK' ELSE field2 END as Field2 FROM Table

If rather than '' the value might be null, you can use ISNULL

SELECT field1, ISNULL(field2,'BLANK') as field2 FROM Table

|||thank you for the help

CHECK CONTRAINT issue

I need something like this:
ALTER TABLE MatchResults
ADD CONSTRAINT ck_MatchResults
CHECK (
NOT EXISTS (
SELECT B1.Id, B2.Id
FROM MatchResults M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
)
)
But it yields following errors:
Server: Msg 8142, Level 16, State 1, Line 1
Subqueries are not supported in CHECK constraints, table 'MatchResults'.
Server: Msg 1759, Level 16, State 1, Line 1
Invalid column 'ProfielId' is specified in a constraint or computed-column d
efinition.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
I already tried this:
CREATE TRIGGER cti_MatchResults ON MatchResults
INSTEAD OF INSERT
AS
SET NOCOUNT ON
BEGIN
IF (NOT EXISTS (
SELECT B1.Id, B2.Id
FROM inserted M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
))
INSERT INTO MatchResults
SELECT ParentId, DatMatch, Id, Updated, Deleted
FROM inserted
ELSE
RAISERROR ('WARNING (Insert): you are inserting faulty data into table!', 0,
1) WITH NOWAIT
END
GO
CREATE TRIGGER ctu_MatchResults ON MatchResults
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
BEGIN
IF (NOT EXISTS (
SELECT B1.Id, B2.Id
FROM inserted M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
))
UPDATE M
SET M.ParentId = I.ParentId, M.DatMatch = I.DatMatch, M.Id = I.Id, M.Updated
= I.Updated, M.Deleted = I.Deleted
FROM MatchResults M, inserted I
WHERE M.ParentId = I.ParentId AND M.DatMatch = I.DatMatch AND M.Id = I.Id
ELSE
RAISERROR (''WARNING (Update): you are inserting faulty data into table!', 0
,1) WITH NOWAIT
END
GO
But for some reason this has no effect at all when I try to update a record
with faulty data (that violates the contraint).
I am updating it via a stored procedure:
CREATE PROCEDURE xsp_AddMatchResult
(
@.ParentId INT,
@.DatMatch DATETIME = NULL,
@.Id INT,
@.Updated DATETIME = NULL,
@.Deleted BIT = 0
) AS SET NOCOUNT ON
IF (@.Updated IS NULL) SET @.Updated = GETDATE()
IF (@.DatMatch IS NULL) SET @.DatMatch = @.Updated
IF EXISTS(SELECT ParentId FROM MatchResults
WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id)
UPDATE MatchResults SET Updated=@.Updated, Deleted=@.Deleted
WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id
ELSE
INSERT INTO MatchResults(ParentId,DatMatch,Id,Update
d,Deleted)
VALUES(@.ParentId,@.DatMatch,@.Id,@.Updated,
@.Deleted)
GO
Does anyone have a clue?
LisaHi Lisa
Please check if column ProfielId exists in the Table Bedrijven
thanks and regards
Chandra
"Lisa Pearlson" wrote:

> I need something like this:
> ALTER TABLE MatchResults
> ADD CONSTRAINT ck_MatchResults
> CHECK (
> NOT EXISTS (
> SELECT B1.Id, B2.Id
> FROM MatchResults M, Bedrijven B1, Bedrijven B2
> WHERE M.Deleted!=1
> AND B1.Id = M.ParentId
> AND B2.Id = M.Id
> AND (B1.ProfielId!=3 OR B2.ProfielId=3)
> )
> )
> But it yields following errors:
> Server: Msg 8142, Level 16, State 1, Line 1
> Subqueries are not supported in CHECK constraints, table 'MatchResults'.
> Server: Msg 1759, Level 16, State 1, Line 1
> Invalid column 'ProfielId' is specified in a constraint or computed-column
definition.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
>
> I already tried this:
> CREATE TRIGGER cti_MatchResults ON MatchResults
> INSTEAD OF INSERT
> AS
> SET NOCOUNT ON
> BEGIN
> IF (NOT EXISTS (
> SELECT B1.Id, B2.Id
> FROM inserted M, Bedrijven B1, Bedrijven B2
> WHERE M.Deleted!=1
> AND B1.Id = M.ParentId
> AND B2.Id = M.Id
> AND (B1.ProfielId!=3 OR B2.ProfielId=3)
> ))
> INSERT INTO MatchResults
> SELECT ParentId, DatMatch, Id, Updated, Deleted
> FROM inserted
> ELSE
> RAISERROR ('WARNING (Insert): you are inserting faulty data into table!',
0,1) WITH NOWAIT
> END
> GO
> CREATE TRIGGER ctu_MatchResults ON MatchResults
> INSTEAD OF UPDATE
> AS
> SET NOCOUNT ON
> BEGIN
> IF (NOT EXISTS (
> SELECT B1.Id, B2.Id
> FROM inserted M, Bedrijven B1, Bedrijven B2
> WHERE M.Deleted!=1
> AND B1.Id = M.ParentId
> AND B2.Id = M.Id
> AND (B1.ProfielId!=3 OR B2.ProfielId=3)
> ))
> UPDATE M
> SET M.ParentId = I.ParentId, M.DatMatch = I.DatMatch, M.Id = I.Id, M.Upd
ated = I.Updated, M.Deleted = I.Deleted
> FROM MatchResults M, inserted I
> WHERE M.ParentId = I.ParentId AND M.DatMatch = I.DatMatch AND M.Id = I.I
d
> ELSE
> RAISERROR (''WARNING (Update): you are inserting faulty data into table!'
, 0,1) WITH NOWAIT
> END
> GO
> But for some reason this has no effect at all when I try to update a recor
d with faulty data (that violates the contraint).
> I am updating it via a stored procedure:
> CREATE PROCEDURE xsp_AddMatchResult
> (
> @.ParentId INT,
> @.DatMatch DATETIME = NULL,
> @.Id INT,
> @.Updated DATETIME = NULL,
> @.Deleted BIT = 0
> ) AS SET NOCOUNT ON
> IF (@.Updated IS NULL) SET @.Updated = GETDATE()
> IF (@.DatMatch IS NULL) SET @.DatMatch = @.Updated
> IF EXISTS(SELECT ParentId FROM MatchResults
> WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id)
> UPDATE MatchResults SET Updated=@.Updated, Deleted=@.Deleted
> WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id
> ELSE
> INSERT INTO MatchResults(ParentId,DatMatch,Id,Update
d,Deleted)
> VALUES(@.ParentId,@.DatMatch,@.Id,@.Updated,
@.Deleted)
> GO
> Does anyone have a clue?
> Lisa|||Lisa
It's hard to suggest something without seeing the data. But if your stored p
rocedure does the job for you I would not change it to the trigger.
"Lisa Pearlson" <no@.spam.plz> wrote in message news:%23ETDzrEUFHA.2128@.TK2MS
FTNGP15.phx.gbl...
I need something like this:
ALTER TABLE MatchResults
ADD CONSTRAINT ck_MatchResults
CHECK (
NOT EXISTS (
SELECT B1.Id, B2.Id
FROM MatchResults M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
)
)
But it yields following errors:
Server: Msg 8142, Level 16, State 1, Line 1
Subqueries are not supported in CHECK constraints, table 'MatchResults'.
Server: Msg 1759, Level 16, State 1, Line 1
Invalid column 'ProfielId' is specified in a constraint or computed-column d
efinition.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
I already tried this:
CREATE TRIGGER cti_MatchResults ON MatchResults
INSTEAD OF INSERT
AS
SET NOCOUNT ON
BEGIN
IF (NOT EXISTS (
SELECT B1.Id, B2.Id
FROM inserted M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
))
INSERT INTO MatchResults
SELECT ParentId, DatMatch, Id, Updated, Deleted
FROM inserted
ELSE
RAISERROR ('WARNING (Insert): you are inserting faulty data into table!', 0,
1) WITH NOWAIT
END
GO
CREATE TRIGGER ctu_MatchResults ON MatchResults
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
BEGIN
IF (NOT EXISTS (
SELECT B1.Id, B2.Id
FROM inserted M, Bedrijven B1, Bedrijven B2
WHERE M.Deleted!=1
AND B1.Id = M.ParentId
AND B2.Id = M.Id
AND (B1.ProfielId!=3 OR B2.ProfielId=3)
))
UPDATE M
SET M.ParentId = I.ParentId, M.DatMatch = I.DatMatch, M.Id = I.Id, M.Updated
= I.Updated, M.Deleted = I.Deleted
FROM MatchResults M, inserted I
WHERE M.ParentId = I.ParentId AND M.DatMatch = I.DatMatch AND M.Id = I.Id
ELSE
RAISERROR (''WARNING (Update): you are inserting faulty data into table!', 0
,1) WITH NOWAIT
END
GO
But for some reason this has no effect at all when I try to update a record
with faulty data (that violates the contraint).
I am updating it via a stored procedure:
CREATE PROCEDURE xsp_AddMatchResult
(
@.ParentId INT,
@.DatMatch DATETIME = NULL,
@.Id INT,
@.Updated DATETIME = NULL,
@.Deleted BIT = 0
) AS SET NOCOUNT ON
IF (@.Updated IS NULL) SET @.Updated = GETDATE()
IF (@.DatMatch IS NULL) SET @.DatMatch = @.Updated
IF EXISTS(SELECT ParentId FROM MatchResults
WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id)
UPDATE MatchResults SET Updated=@.Updated, Deleted=@.Deleted
WHERE ParentId=@.ParentId AND DatMatch=@.DatMatch AND Id=@.Id
ELSE
INSERT INTO MatchResults(ParentId,DatMatch,Id,Update
d,Deleted)
VALUES(@.ParentId,@.DatMatch,@.Id,@.Updated,
@.Deleted)
GO
Does anyone have a clue?
Lisa|||I'm not sure why you couldn't use a standard After trigger for this. You wan
t
other check and unique constraints to fire. So why not something like:
Create Trigger trigMatchResultsIU
On dbo.MatchResults
For Insert, Update
As
If Not Exists(
Select *
From inserted As I, dbo.Bedrijven As B
Where I.Deleted <> 1
And (
(B.Id = I.ParentId And B.ProfielId <> 3)
Or (B.Id = I.Id And B.ProfielId = 3)
)
)
Begin
Raiserror('Warning Will Robenson! Danger! Danger!, 16, 1)
Rollback Tran
End
Thomas|||You can simply call "rollback tran" inside a trigger to undo the
insert/delete even if you didn't call "begin tran" yourself?
I didn't know that.
Can you attatch multiple triggers to insert/update on same table? Do they
get executed in the order the triggers were created?
Lisa
"Thomas Coleman" <thomas@.newsgroup.nospam> wrote in message
news:OezbXQLUFHA.3584@.TK2MSFTNGP14.phx.gbl...
> I'm not sure why you couldn't use a standard After trigger for this. You
> want other check and unique constraints to fire. So why not something
> like:
> Create Trigger trigMatchResultsIU
> On dbo.MatchResults
> For Insert, Update
> As
> If Not Exists(
> Select *
> From inserted As I, dbo.Bedrijven As B
> Where I.Deleted <> 1
> And (
> (B.Id = I.ParentId And B.ProfielId <> 3)
> Or (B.Id = I.Id And B.ProfielId = 3)
> )
> )
> Begin
> Raiserror('Warning Will Robenson! Danger! Danger!, 16, 1)
> Rollback Tran
> End
>
> Thomas
>
>|||Yes you can use Rollback Tran because each DML statement (Insert, Update,
Delete) is in an implicit transaction.
Yes, you can attach multiple Insert, Update and/or Delete triggers on the sa
me
table although you should do it with caution. In general, it is difficult to
determine any sort of firing order with multiple triggers. Thus, I would
recommend that you assume that the order is random when writing triggers.
That said, there is a system stored proc called sp_settriggerorder which wil
l
allow you to specify which trigger should fire first or last. That's about t
he
extent of the firing order.
HTH
Thomas
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:%23HXATgQUFHA.580@.TK2MSFTNGP15.phx.gbl...
> You can simply call "rollback tran" inside a trigger to undo the insert/de
lete
> even if you didn't call "begin tran" yourself?
> I didn't know that.
> Can you attatch multiple triggers to insert/update on same table? Do they
get
> executed in the order the triggers were created?
> Lisa
> "Thomas Coleman" <thomas@.newsgroup.nospam> wrote in message
> news:OezbXQLUFHA.3584@.TK2MSFTNGP14.phx.gbl...
>

Check Constraint Violation

I am getting a check constraint error on the following query.
INSERT TABLEA
( COL1, COL2, COL3)
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
returning 20000. All columns are of datatype INT as is the return value of
the function.
If i replace the function call with a literal 0, it works, but having the
function in there violates the constraint on COL3, despite the value still
being 0.
Does anyone have any idea why this is happening? I have tried making COL3 a
computed column, but can't have a constraint on a computed column, tried
making the select statement into a derived table and selecting from that int
o
my insert statement.
This is being done on SQL Server 2000 Enterprise.
Thank you
Clint ColefaxHi
What is the error that you received.
and what was displayed when you tried:
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Clint Colefax" wrote:

> I am getting a check constraint error on the following query.
> INSERT TABLEA
> ( COL1, COL2, COL3)
> SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
> There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
> returning 20000. All columns are of datatype INT as is the return value of
> the function.
> If i replace the function call with a literal 0, it works, but having the
> function in there violates the constraint on COL3, despite the value still
> being 0.
> Does anyone have any idea why this is happening? I have tried making COL3
a
> computed column, but can't have a constraint on a computed column, tried
> making the select statement into a derived table and selecting from that i
nto
> my insert statement.
> This is being done on SQL Server 2000 Enterprise.
> Thank you
> Clint Colefax
>
>|||The error received was a violation of check constraint.
INSERT statement conflicted with COLUMN CHECK constraint...
Execute that select statement returns as expected
20000, -20000, 0
Thank you
Clint Colefax|||Hi
Can you try as
INSERT INTO TABLEA
SELECT dbo.Function1(),-20000, dbo.Function1() + (-20000)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Clint Colefax" wrote:

> The error received was a violation of check constraint.
> INSERT statement conflicted with COLUMN CHECK constraint...
> Execute that select statement returns as expected
> 20000, -20000, 0
> Thank you
> Clint Colefax
>|||What data types are the columns and the function?
If they are not of an exact type, but e.g. "real", the third expression may
evaluate to a value slightly below 0, although it is displayed as 0. CAST to
integer to avoid this problem.
I hope this helps!
Martin
"Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
news:42D1D89D-9EA0-4F00-8F91-D8D55732C387@.microsoft.com...
>I am getting a check constraint error on the following query.
> INSERT TABLEA
> ( COL1, COL2, COL3)
> SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
> There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
> returning 20000. All columns are of datatype INT as is the return value of
> the function.
> If i replace the function call with a literal 0, it works, but having the
> function in there violates the constraint on COL3, despite the value still
> being 0.
> Does anyone have any idea why this is happening? I have tried making COL3
> a
> computed column, but can't have a constraint on a computed column, tried
> making the select statement into a derived table and selecting from that
> into
> my insert statement.
> This is being done on SQL Server 2000 Enterprise.
> Thank you
> Clint Colefax
>
>|||On Mon, 30 May 2005 19:19:41 -0700, Clint Colefax wrote:

>I am getting a check constraint error on the following query.
>INSERT TABLEA
>( COL1, COL2, COL3)
>SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
>There is a check constraint on COL3 ( COL3 >= 0 ) and dbo.Function() is
>returning 20000. All columns are of datatype INT as is the return value of
>the function.
Hi Clint,
I could not reproduce this behaviour (see repro script below). Could you
post a repro script for me to run and reproduce the error?

>I have tried making COL3 a
>computed column, but can't have a constraint on a computed column,
If Col3 is always equal to Col1 - Col2, you should make it a computed
column. You can replace the check constraint wiuth the following
equivalent:
CHECK (Col1 >= Col2)
Here's the script I used to try to reproduce your problem, and the
output I got from it:
create table TableA
(Col1 int not null,
Col2 int not null,
Col3 int not null,
PRIMARY KEY (Col1),
CHECK (Col3 >= 0)
)
go
create function dbo.Function1()
returns int
as
begin
return 20000
end
go
INSERT TableA
( Col1, Col2, Col3)
SELECT dbo.Function1(),-20000, (dbo.Function1() + (-20000) )
go
select * from TableA
go
drop function dbo.Function1
go
drop table TableA
go
Col1 Col2 Col3
-- -- --
20000 -20000 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I gave the earlier example as I didn't want to post all of the infrastructur
e
around this problem. With the following cut down version, I am still able to
replicate the problem.
CREATE TABLE test (
MOVEMENT_NO int IDENTITY (1, 1) NOT NULL ,
FACTORY_STATIONERY_NO int NOT NULL ,
INITIAL_VALUE int NOT NULL ,
MOVEMENT int NOT NULL ,
FINAL_VALUE int NOT NULL ,
CREATION_DATE smalldatetime not null
CONSTRAINT testPK PRIMARY KEY NONCLUSTERED ( MOVEMENT_NO ) ,
CHECK (FINAL_VALUE >= 0),
CHECK (INITIAL_VALUE >= 0)
)
INSERT TEST
VALUES( 46, 0, 50000, 50000, '2004-12-11 11:21:00' )
INSERT TEST
VALUES( 46, 50000, -30000, 20000, '2004-12-13 15:34:00' )
CREATE FUNCTION fntest( @.factory_stationery_no INT )
RETURNS INT AS
BEGIN
RETURN ISNULL( ( SELECT TOP 1 FINAL_VALUE
FROM dbo.test
WHERE FACTORY_STATIONERY_NO = @.factory_stationery_no
ORDER BY creation_date desc, MOVEMENT_NO DESC ), 0 )
END
INSERT TEST
SELECT 46 AS FACTORY_STATIONERY_NO,
DBO.FNTEST(46),
-20000 AS MOVEMENT,
DBO.FNTEST(46) + (-20000),
GETDATE()
DROP FUNCTION FNTEST
DROP TABLE TEST
Thank you
Clint Colefax|||Sorry, I didn't think out my example very well, the following is code that
should reproduce the error.
CREATE TABLE test (
MOVEMENT_NO int IDENTITY (1, 1) NOT NULL ,
FACTORY_STATIONERY_NO int NOT NULL ,
INITIAL_VALUE int NOT NULL ,
MOVEMENT int NOT NULL ,
FINAL_VALUE int NOT NULL ,
CREATION_DATE smalldatetime not null
CONSTRAINT testPK PRIMARY KEY NONCLUSTERED ( MOVEMENT_NO ) ,
CHECK (FINAL_VALUE >= 0),
CHECK (INITIAL_VALUE >= 0)
)
INSERT TEST
VALUES( 46, 0, 50000, 50000, '2004-12-11 11:21:00' )
INSERT TEST
VALUES( 46, 50000, -30000, 20000, '2004-12-13 15:34:00' )
CREATE FUNCTION fntest( @.factory_stationery_no INT )
RETURNS INT AS
BEGIN
RETURN ISNULL( ( SELECT TOP 1 FINAL_VALUE
FROM dbo.test
WHERE FACTORY_STATIONERY_NO = @.factory_stationery_no
ORDER BY creation_date desc, MOVEMENT_NO DESC ), 0 )
END
INSERT TEST
SELECT 46 AS FACTORY_STATIONERY_NO,
DBO.FNTEST(46),
-20000 AS MOVEMENT,
DBO.FNTEST(46) + (-20000),
GETDATE()
DROP FUNCTION FNTEST
DROP TABLE TEST|||Thank you but all datatype are of INT, all match, even using a CAST or
CONVERT statement does not get around the problem (had previouslty attempted
).
Thank you for your input
Clint Colefax|||It's a bug, and a surprising one:
CREATE TABLE TEST (
a int not null,
b smalldatetime not null,
constraint finalv CHECK (a >= 0)
)
go
INSERT TEST(a) SELECT 1
Gives this error:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.TEST';
column does not allow nulls. INSERT fails.
The statement has been terminated.
Can you post the result of
SELECT @.@.version
so we can see what version you're running?
I verified this on 8.00.2039, and will report it to Microsoft.
SK
Clint Colefax wrote:

>there error is a check constraint violation for the FINAL_VALUE constraint.
>Server: Msg 547, Level 16, State 1, Line 1
>INSERT statement conflicted with COLUMN CHECK constraint
>'CK__test__FINAL_VALU__3AA27A0F'. The conflict occurred in database
>'LIPSDev', table 'test', column 'FINAL_VALUE'.
>The statement has been terminated.
>Thank you
>

check box in parameters

How do I display check boxes in parameters.. I want the user to be able to
select multiple options that can be passed into the Query.. please help.
For example,
Select * from branch where branchregion in ('NORTHEAST','SOUTHWEST','
SOUTHEAST')..
RKSql Server 2000 Reporting Services does not directly support this
functionality. It is on our wish list for inclusion in a future release.
However there is an solution to this post on the GotDotNet.com web site:
http://www.gotdotnet.com/Community/Resources/Default.aspx?AFXPath=/Resource%5b@.ResourceId='2E882C0A-8D2B-4EAD-81BE-8E66C0941A18'%5d
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"RK Balaji" <rk.balaji@.gmail.com> wrote in message
news:ux5BwXHhEHA.4092@.TK2MSFTNGP10.phx.gbl...
> How do I display check boxes in parameters.. I want the user to be able to
> select multiple options that can be passed into the Query.. please help.
> For example,
> Select * from branch where branchregion in ('NORTHEAST','SOUTHWEST','
> SOUTHEAST')..
> RK
>
>
>

Check box in a Combo Box

Hi,
Iam working on a window application using .NET framework 2.0.
I want a check box + some text in a combobox.
User can select multiple items in combo box by using the check
box.
My requirement is : I have a report page. User selects some data
and click on 'Show Report'. Iam showing (I must show) the report also
in the same page. When user selects some other data, and click on 'Show
Report' button new data will be populated.
On this page I have an "Items" Combo box. User may select more than one
Item. Since multi selection property is not there for combo box, Iam
forced to go for ListBox. But Listbox is occupying more space and the
look is bad.
I have seen a CheckedComboBox article here:
http://www.codeproject.com/combobox...&forumid=114...
Its in VC++ I think. I need the same kind of functionality in .NET.
I hope its possible. But dont know how to start with it.
I tried creating a class that is inherited from ComboBox. The class
contains a CheckBox, ItemText Properties. But whats next? Iam unable to
proceed. Could you please help..........
Any ideas or code snippet........ Please............
Regards,
Bharathi Kumar.Please post this to a .Net programming group.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bharathi Kumar" <bharathidotnet@.gmail.com> wrote in message
news:1149768936.113891.259960@.u72g2000cwu.googlegroups.com...
Hi,
Iam working on a window application using .NET framework 2.0.
I want a check box + some text in a combobox.
User can select multiple items in combo box by using the check
box.
My requirement is : I have a report page. User selects some data
and click on 'Show Report'. Iam showing (I must show) the report also
in the same page. When user selects some other data, and click on 'Show
Report' button new data will be populated.
On this page I have an "Items" Combo box. User may select more than one
Item. Since multi selection property is not there for combo box, Iam
forced to go for ListBox. But Listbox is occupying more space and the
look is bad.
I have seen a CheckedComboBox article here:
http://www.codeproject.com/combobox...&forumid=114...
Its in VC++ I think. I need the same kind of functionality in .NET.
I hope its possible. But dont know how to start with it.
I tried creating a class that is inherited from ComboBox. The class
contains a CheckBox, ItemText Properties. But whats next? Iam unable to
proceed. Could you please help..........
Any ideas or code snippet........ Please............
Regards,
Bharathi Kumar.

chart problem

Hello everybody,
I have problem to make a simple line report:
My Query's dataset:
select VALUE_IND, DATE_IND
from uc.TB_INDI as TI, uc.TB_DEF_IND as TDI
where TDI.LIB_DEF_IND = @.param1 and
TDI.NO_DEF_IND = TI.NO_DEF_IND and
TI.NO_PRJ = @.param2
The result of this query is (from my test base):
VALUE_IND Date_IND
5.0 2005-01-02 00:00:00.000
15.0 2005-02-02 00:00:00.000
10.0 2005-03-02 00:00:00.000
25.0 2005-04-02 00:00:00.000
9.0 2005-05-02 00:00:00.000
33.0 2005-06-02 00:00:00.000
25.0 2005-07-02 00:00:00.000
10.0 2005-08-02 00:00:00.000
28.0 2005-09-02 00:00:00.000
55.0 2005-10-02 00:00:00.000
5.0 2005-11-02 00:00:00.000
10.0 2005-12-02 00:00:00.000
I want to make a chart 'Simple Line' with date in X axis and value in Y
axis. So, in 'Drop data fields here', i put VALUE_IND (which contains
Fields!VALUE_IND.Value like value) and in 'Drop category fields here', i
put DATE_IND (which contains =Fields!DATE_IND.Value like expression)
But when i make a preview, i have only one date record: January in X
axis (x5)and all the value are grouped at the right of the chart!
I don't understand what's happening, i need help please.
Thanks in advance,
aVravrama wrote:
> Hello everybody,
> I have problem to make a simple line report:
> My Query's dataset:
> select VALUE_IND, DATE_IND
> from uc.TB_INDI as TI, uc.TB_DEF_IND as TDI
> where TDI.LIB_DEF_IND = @.param1 and
> TDI.NO_DEF_IND = TI.NO_DEF_IND and
> TI.NO_PRJ = @.param2
> The result of this query is (from my test base):
> VALUE_IND Date_IND
> 5.0 2005-01-02 00:00:00.000
> 15.0 2005-02-02 00:00:00.000
> 10.0 2005-03-02 00:00:00.000
> 25.0 2005-04-02 00:00:00.000
> 9.0 2005-05-02 00:00:00.000
> 33.0 2005-06-02 00:00:00.000
> 25.0 2005-07-02 00:00:00.000
> 10.0 2005-08-02 00:00:00.000
> 28.0 2005-09-02 00:00:00.000
> 55.0 2005-10-02 00:00:00.000
> 5.0 2005-11-02 00:00:00.000
> 10.0 2005-12-02 00:00:00.000
>
> I want to make a chart 'Simple Line' with date in X axis and value in Y
> axis. So, in 'Drop data fields here', i put VALUE_IND (which contains
> Fields!VALUE_IND.Value like value) and in 'Drop category fields here', i
> put DATE_IND (which contains =Fields!DATE_IND.Value like expression)
> But when i make a preview, i have only one date record: January in X
> axis (x5)and all the value are grouped at the right of the chart!
> I don't understand what's happening, i need help please.
> Thanks in advance,
> aVr
I try again with no success... In fact, i dont't understand the
philosophie of RS.
aVr|||avrama wrote:
> avrama wrote:
>> Hello everybody,
>> I have problem to make a simple line report:
>> My Query's dataset:
>> select VALUE_IND, DATE_IND
>> from uc.TB_INDI as TI, uc.TB_DEF_IND as TDI
>> where TDI.LIB_DEF_IND = @.param1 and
>> TDI.NO_DEF_IND = TI.NO_DEF_IND and
>> TI.NO_PRJ = @.param2
>> The result of this query is (from my test base):
>> VALUE_IND Date_IND
>> 5.0 2005-01-02 00:00:00.000 15.0 2005-02-02
>> 00:00:00.000 10.0 2005-03-02 00:00:00.000 25.0
>> 2005-04-02 00:00:00.000 9.0 2005-05-02 00:00:00.000
>> 33.0 2005-06-02 00:00:00.000 25.0 2005-07-02
>> 00:00:00.000 10.0 2005-08-02 00:00:00.000 28.0
>> 2005-09-02 00:00:00.000 55.0 2005-10-02 00:00:00.000
>> 5.0 2005-11-02 00:00:00.000 10.0 2005-12-02
>> 00:00:00.000
>> I want to make a chart 'Simple Line' with date in X axis and value in Y
>> axis. So, in 'Drop data fields here', i put VALUE_IND (which contains
>> Fields!VALUE_IND.Value like value) and in 'Drop category fields here', i
>> put DATE_IND (which contains =Fields!DATE_IND.Value like expression)
>> But when i make a preview, i have only one date record: January in X
>> axis (x5)and all the value are grouped at the right of the chart!
>> I don't understand what's happening, i need help please.
>> Thanks in advance,
>> aVr
>
> I try again with no success... In fact, i dont't understand the
> philosophie of RS.
> aVr
Ok, it's good, it's working...
It was an X axis problem.
Thanks for you help
aVr

Chart Interlacing Colors

For a given axis I can select to enable interlaced colors.
How do I select what the second, interlacing color is?
It appears to always be gray.
This is not ideal for the custom colors we have selected.
Thank you in advance.
JerryThe interlacing color is automatically determined based on the chart's
backgroundcolor or plotarea color (if they are explicitly set).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jerry Nixon" <jerrynixon@.gmail.com> wrote in message
news:36f558cf.0410111518.7670c91f@.posting.google.com...
> For a given axis I can select to enable interlaced colors.
> How do I select what the second, interlacing color is?
> It appears to always be gray.
> This is not ideal for the custom colors we have selected.
> Thank you in advance.
> Jerry

Chart Help! I must be stupid!

OK, I have done 1000s of excel charts. Paste in the data, select the
data, insert chart and it works.
I am trying to do a simple line chart in RS and can't figure it out
after two hours. Maybe it is time to quir coding! My query returns the
following:
Product, Month1, Month2, Month3, Month4, Month5 (There Are Column
Names)
Proda 123 222 454 126 322 (this is the sales
data, row 1)
Prodb 333 526 447 631 262 (this is the sales
data, row 2)
Prodc 143 242 344 126 229 (this is the sales
data, row 3)
Prodd 153 123 467 116 122 (this is the sales
data, row 4)
I want a simple line chart with month1 thru month 5 at the bottom axis
and the
sales scale on the left axis. The series would be of course
proda,b,c,d. So I
want 4 lines on the chart from left to right representing month 1 thru
5 and the line would chart up and down based on the sales number.
In excel I paste in the table, hightlight it and insert a line chart
and it is 100% right. I'll email anyone the excel file who wants it.
What am I missing on this deal. I have looked every where and tried a
dozen combos. Thanks for your time!!
SheilaWhat kind of charts did you choose?
"George" <geomanks@.yahoo.com> wrote in message
news:e9dff8e4.0408171641.71a8b619@.posting.google.com...
> OK, I have done 1000s of excel charts. Paste in the data, select the
> data, insert chart and it works.
> I am trying to do a simple line chart in RS and can't figure it out
> after two hours. Maybe it is time to quir coding! My query returns the
> following:
> Product, Month1, Month2, Month3, Month4, Month5 (There Are Column
> Names)
> Proda 123 222 454 126 322 (this is the sales
> data, row 1)
> Prodb 333 526 447 631 262 (this is the sales
> data, row 2)
> Prodc 143 242 344 126 229 (this is the sales
> data, row 3)
> Prodd 153 123 467 116 122 (this is the sales
> data, row 4)
> I want a simple line chart with month1 thru month 5 at the bottom axis
> and the
> sales scale on the left axis. The series would be of course
> proda,b,c,d. So I
> want 4 lines on the chart from left to right representing month 1 thru
> 5 and the line would chart up and down based on the sales number.
> In excel I paste in the table, hightlight it and insert a line chart
> and it is 100% right. I'll email anyone the excel file who wants it.
> What am I missing on this deal. I have looked every where and tried a
> dozen combos. Thanks for your time!!
> Sheila

CHARINDEX in CASE Within SELECT Statement

Hello.
I need help with using CHARINDEX.
I have a column in a table (Discount_Specification) that could hold the
following values:
LF(I03U,CHA-14,ALL-0)
MR(I05U,I06U,CHA-5)
etc.
I'm inserting into another table and need to pick up the value following
"CHA-" in that column.
I've created a User Defined Function to do this but would like to make the
SQL code more efficient. The UDF has an argument which specifies which value
to pick up (separated by the delimiter). In example 1 it's the 2nd value. In
example 2 it's the 3rd value.
My SELECT code looks something like this:
select provider_id, last_name, first_name,
CASE
WHEN CHARINDEX(@.CHA,SM.Discount_Specification) > 0 THEN
CASE
WHEN Left(SM.Discount_Specification,2) IN(@.LF, @.LS, @.PF, @.PL) THEN
webcentral.dbo.udf_ConvertDecimalAllowance
(WebCentral.dbo.udf_GetNthDecimalValue(WebCentral.dbo.udf_GetNthTextValue(We
bCentral.dbo.udf_GetNthTextValue
(SubString(SM. Discount_Specification,4,DataLength(RTri
m(SM.Discount_Specific
ation))-4),@.Comma,2),@.Dash,2),@.Comma,1))
ELSE
CASE
WHEN Left(SM.Discount_Specification,3) = @.CHA THEN
-- Charge is all by itself
webcentral.dbo.udf_ConvertDecimalAllowance
(WebCentral.dbo.udf_GetNthDecimalValue(WebCentral.dbo.udf_GetNthTextValue
(RTrim(SM. Discount_Specification),@.Dash,2),@.Comma,
1))
ELSE 1
END
END
I would like to use a variable instead.
Something like:
DECLARE @.Pos SmallInt
SELECT provider_id, last_name, first_name,
@.Pos = CHARINDEX(@.CHA,SM.Discount_Specification)
CASE
WHEN @.POS > 0 THEN
webcentral.dbo.udf_ConvertDecimalAllowance etc. etc.
END
I get an error on the line where I'm setting @.Pos and I don't know what
syntax to use.
Any suggestions will be greatly appreciated.
Thanks,
RitaYou cannot set variables and return results to the client in the same SELECT
statement.
I'm not sure what it really is that you're trying to achieve, but I think
this function might help you parse those strings:
create function dbo.fnParse
(
@.charValue varchar(1000)
,@.findThis varchar(1000) = null
)
returns int
as
begin
declare @.result int
set @.findThis = isnull(@.findThis, 'CHA-')
select @.charValue
= substring(@.charValue, charindex(@.findThis, @.charValue) +
len(@.findThis), len(@.charValue))
select @.charValue
= substring(@.charValue, 1, patindex('%[^0-9]%', @.charValue) - 1)
select @.result
= case
when isnumeric(@.charValue) = 1
then cast(@.charValue as int)
else null
end
return @.result
end
go
Use like this:
select dbo.fnParse('LF(I03U,CHA-14,ALL-0)', 'CHA-')
,dbo.fnParse('MR(I05U,I06U,CHA-5)', 'CHA-')
ML
http://milambda.blogspot.com/|||Thanks so much for your response.
That's exactly what I'm playing around with - creating a UDF that uses the
CHARINDEX. Your example helps a lot!
Rita
"ML" wrote:

> You cannot set variables and return results to the client in the same SELE
CT
> statement.
> I'm not sure what it really is that you're trying to achieve, but I think
> this function might help you parse those strings:
> create function dbo.fnParse
> (
> @.charValue varchar(1000)
> ,@.findThis varchar(1000) = null
> )
> returns int
> as
> begin
> declare @.result int
> set @.findThis = isnull(@.findThis, 'CHA-')
> select @.charValue
> = substring(@.charValue, charindex(@.findThis, @.charValue) +
> len(@.findThis), len(@.charValue))
> select @.charValue
> = substring(@.charValue, 1, patindex('%[^0-9]%', @.charValue) - 1)
> select @.result
> = case
> when isnumeric(@.charValue) = 1
> then cast(@.charValue as int)
> else null
> end
> return @.result
> end
> go
> Use like this:
> select dbo.fnParse('LF(I03U,CHA-14,ALL-0)', 'CHA-')
> ,dbo.fnParse('MR(I05U,I06U,CHA-5)', 'CHA-')
>
> ML
> --
> http://milambda.blogspot.com/|||Gee, that sounds like a good deed from me. Hope Santa reads this newsgroup.
:)
At least one of the elves should. Or is Santa not using SQL...?
Anyway, just remember that this *is* the newsgroup with solutions. :)
ML
http://milambda.blogspot.com/|||>> Any suggestions will be greatly appreciated. <<
1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
2) Learn to normalize your schema and stop writing COBOL-style string
manipulations in procedures. You will waste an insane amount of time
and spce doing this.
I also watched code like this kill some children in Africa. The
programmer had used strings hold the package size quantity for drugs.
When the drug suppliers agreed to provide smaller packages (i.e.
quantity one), the string was changed, but not the front end. The
result was when you thought you had ordered a 5-unit package, you got
a 1-unit package instead.
My guess is that you need a table of Discounts (notice the plural name
to show it is a set) with the amount, the source and the code for each
of the discounts. You then do a simple join and get rid of all that
"pseudo-COBOL" field extractions.|||Ouch!
I think I should have posted everything here pertaining to my question so
there would be no misunderstanding.
I have no control of the data coming in. We are supplied this by our clients
and then we have to import the non standard data into our standard SQL table
s.
No matter what, I can't get passed having to parse out bits of information
all strung
together within 1 column separated by a delimiter.
"--CELKO--" wrote:

> 1) Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are.
> 2) Learn to normalize your schema and stop writing COBOL-style string
> manipulations in procedures. You will waste an insane amount of time
> and spce doing this.
> I also watched code like this kill some children in Africa. The
> programmer had used strings hold the package size quantity for drugs.
> When the drug suppliers agreed to provide smaller packages (i.e.
> quantity one), the string was changed, but not the front end. The
> result was when you thought you had ordered a 5-unit package, you got
> a 1-unit package instead.
> My guess is that you need a table of Discounts (notice the plural name
> to show it is a set) with the amount, the source and the code for each
> of the discounts. You then do a simple join and get rid of all that
> "pseudo-COBOL" field extractions.
>|||>> have no control of the data coming in. We are supplied this by our clien
ts and then we have to import the non standard data into our standard SQL ta
bles. No matter what, I can't get passed having to parse out bits of informa
tion all strung together wi
thin 1 column separated by a delimiter. <<
Just because the source data is a mess, you are not required to
propagate it in the schema. Parse it at load time and edit everything.
Have you looked into an ETL tool of some kind? You might be able to
write something in a small, fast scripting language likie AWK, Perl,
etc.|||Hmm.
I never thought to do that. The input files are always imported "as is" into
SQL staging tables using DTS packages. The reason being if there is ever a
question regarding the output data we have the original data in SQL format
against which queries can be run.
I use an ActiveX script to popultae the columns so that may be where I could
parse out the values. I've used AWK and Perl sparingly in the past. There is
a lot of logic going on when I get the value out of the string as to where t
o
place it so I think ActiveX is the best way to go.
I also need to consider speed since some of the files have millions of rows
in them.
Would it be faster to parse within the DTS ActiveX script or the Stored
Procedure (which I'm currently doing)? I heard that Stored Procedures are
much faster than DTS packages.
Thanks for the suggestion.
"--CELKO--" wrote:

within 1 column separated by a delimiter. <<
> Just because the source data is a mess, you are not required to
> propagate it in the schema. Parse it at load time and edit everything.
> Have you looked into an ETL tool of some kind? You might be able to
> write something in a small, fast scripting language likie AWK, Perl,
> etc.
>|||A set-based solution will be more efficient if run on the server. But that
will require accessing the source files through a linked server. So, the
question of the day is - what type are the source files?
ML
http://milambda.blogspot.com/|||They're fixed length text files.
"ML" wrote:

> A set-based solution will be more efficient if run on the server. But that
> will require accessing the source files through a linked server. So, the
> question of the day is - what type are the source files?
>
> ML
> --
> http://milambda.blogspot.com/