case command sql 2005

Hi fellows

I am running a command in sql, but I have an error message. The field that i am using has real values.

could someone help me with this isue?

CASE AQUILA.dbo.BI.BI WHEN AQUILA.dbo.BI.BI = 0 THEN 5 ELSE (AQUILA.dbo.BI.BI*25/100) END AS ROCK_FACTOR

cheers

Edwin

hi Edwin,

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL, BI real DEFAULT 0 ); GO INSERT INTO dbo.TestTB VALUES ( 1 , 0 ); INSERT INTO dbo.TestTB VALUES ( 2 , 5 ); INSERT INTO dbo.TestTB VALUES ( 3 , 10 ); GO SELECT Id, BI, CASE BI WHEN 0 THEN 5 ELSE (BI*25/100) END AS ROCK_FACTOR FROM dbo.TestTB; GO DROP TABLE dbo.TestTB; --<- Id BI ROCK_FACTOR -- - - 1 0 5 2 5 1,25 3 10 2,5

regards

|||

Hi Andrea

I have another problem. I have a table with consecutives values , but they are desorganized for example

DHID from to

45 50 40

45 40 30

45 0 10

45 10 10

45 20 30

I wan to organize them like this

DHID from to

45 0 10

45 10 20

45 20 30

45 30 40

45 40 50

That means to start with the minimum value and consecutivite values

Do you think that this is possible.

cheers

Edwin

|||

hi Edwin,

your data really is a mess ..

you've better take care of it as it will crash you, now or then..

anyway, you can write something similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( DHID int NOT NULL, [From] int NOT NULL, [To] int NOT NULL ); INSERT INTO dbo.TestTB VALUES ( 45, 50, 40 ); INSERT INTO dbo.TestTB VALUES ( 45, 40, 30 ); INSERT INTO dbo.TestTB VALUES ( 45, 0, 10 ); INSERT INTO dbo.TestTB VALUES ( 45, 10, 10 ); INSERT INTO dbo.TestTB VALUES ( 45, 20, 30 ); GO SELECT DHID , CASE WHEN [From] < [To] THEN [From] ELSE [To] END AS [From] , CASE WHEN [To] > [From] THEN [To] ELSE [From] END AS [To] FROM dbo.TestTB ORDER BY DHID, [From], [To] GO DROP TABLE dbo.TestTB; --<-- DHID From To -- -- -- 45 0 10 45 10 10 45 20 30 45 30 40 45 40 50

but you can not fill, this way, eventual gaps (like the missing 10-20 row) or remove unchanged states, like the 2nd row of the resultset, defined as

DHID from to

45 10 10

if you have this requirement you probably have to work with temporary tables to be populated and purged by bad/redundant data, with additional logic to fill "gaps"..

regards

|||

YOU ARE AWESOME

THANK YOU VERY MUCH

case command sql 2005

Hi fellows

I am running a command in sql, but I have an error message. The field that i am using has real values.

could someone help me with this isue?

CASE AQUILA.dbo.BI.BI WHEN AQUILA.dbo.BI.BI = 0 THEN 5 ELSE (AQUILA.dbo.BI.BI*25/100) END AS ROCK_FACTOR

cheers

Edwin

hi Edwin,

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL, BI real DEFAULT 0 ); GO INSERT INTO dbo.TestTB VALUES ( 1 , 0 ); INSERT INTO dbo.TestTB VALUES ( 2 , 5 ); INSERT INTO dbo.TestTB VALUES ( 3 , 10 ); GO SELECT Id, BI, CASE BI WHEN 0 THEN 5 ELSE (BI*25/100) END AS ROCK_FACTOR FROM dbo.TestTB; GO DROP TABLE dbo.TestTB; --<- Id BI ROCK_FACTOR -- - - 1 0 5 2 5 1,25 3 10 2,5

regards

|||

Hi Andrea

I have another problem. I have a table with consecutives values , but they are desorganized for example

DHID from to

45 50 40

45 40 30

45 0 10

45 10 10

45 20 30

I wan to organize them like this

DHID from to

45 0 10

45 10 20

45 20 30

45 30 40

45 40 50

That means to start with the minimum value and consecutivite values

Do you think that this is possible.

cheers

Edwin

|||

hi Edwin,

your data really is a mess ..

you've better take care of it as it will crash you, now or then..

anyway, you can write something similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( DHID int NOT NULL, [From] int NOT NULL, [To] int NOT NULL ); INSERT INTO dbo.TestTB VALUES ( 45, 50, 40 ); INSERT INTO dbo.TestTB VALUES ( 45, 40, 30 ); INSERT INTO dbo.TestTB VALUES ( 45, 0, 10 ); INSERT INTO dbo.TestTB VALUES ( 45, 10, 10 ); INSERT INTO dbo.TestTB VALUES ( 45, 20, 30 ); GO SELECT DHID , CASE WHEN [From] < [To] THEN [From] ELSE [To] END AS [From] , CASE WHEN [To] > [From] THEN [To] ELSE [From] END AS [To] FROM dbo.TestTB ORDER BY DHID, [From], [To] GO DROP TABLE dbo.TestTB; --<-- DHID From To -- -- -- 45 0 10 45 10 10 45 20 30 45 30 40 45 40 50

but you can not fill, this way, eventual gaps (like the missing 10-20 row) or remove unchanged states, like the 2nd row of the resultset, defined as

DHID from to

45 10 10

if you have this requirement you probably have to work with temporary tables to be populated and purged by bad/redundant data, with additional logic to fill "gaps"..

regards

|||

YOU ARE AWESOME

THANK YOU VERY MUCH

sql

Capturing the results from exec command

Hi,

I'm writing a small query where I have a dynamic table name and dynamic condition for the criteria. In order to execute this, I need Exec command.

exec(select count(*) from @.dynamictable where condition = @.dynamiccond)

But here I want to capture the count from the select statement. Could any of you help me capture the results from exec command?

Thanks

2 ways

USE pubs
GO

--sp_executesql
DECLARE @.chvTableName VARCHAR(100),
@.intTableCount INT,
@.chvSQL NVARCHAR(100)

SELECT @.chvTableName = 'Authors'
SELECT @.chvSQL = N'SELECT @.intTableCount = COUNT(*) FROM ' + @.chvTableName

EXEC sp_executesql @.chvSQL, N'@.intTableCount INT OUTPUT', @.intTableCount OUTPUT

SELECT @.intTableCount
GO

--EXEC (SQL)
DECLARE @.chvTableName VARCHAR(100),
@.intTableCount INT,
@.chvSQL NVARCHAR(100)

CREATE TABLE #temp (Totalcount INT)
SELECT @.chvTableName = 'Authors'
SELECT @.chvSQL = 'Insert into #temp Select Count(*) from ' + @.chvTableName

EXEC( @.chvSQL)

SELECT @.intTableCount = Totalcount from #temp

SELECT @.intTableCount

DROP TABLE #temp

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Or:

DECLARE @.chvTableName VARCHAR(100)

CREATE TABLE #temp (Totalcount INT)
SELECT @.chvTableName = 'sysobjects'

insert into #temp(totalCount)
EXEC( 'Select Count(*) from ' + @.chvTableName)

SELECT * from #temp

DROP TABLE #temp

Note: it is generally considered a bad practice to do this sort of thing unless you are building some sort of tool. If this is a production application, it would be better to build a procedure per table:

create procedures count_accounts
as
select count(*) from account
go

Yes, it sounds like a lot of maintenance, but unless you build a large quantity of tables, it shouldn't be a big deal.

Capture Current SQL Statements

There must be an adhoc command to capture these statements, like they do in
profiler. Does anyone know how?
Thanks
See if these help:
Using fn_get_sql
http://vyaskn.tripod.com/fn_get_sql.htm
Automating Server Side Tracing in SQL Server
http://vyaskn.tripod.com/server_side...sql_server.htm
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"David" <David@.discussions.microsoft.com> wrote in message
news:149937CB-151C-4E52-A001-DA4D052C86AC@.microsoft.com...
> There must be an adhoc command to capture these statements, like they do
in
> profiler. Does anyone know how?
> Thanks
|||Vyas,
Do you think I can run server side tracing SP 24/7 for auditing purposes?
Thanks.
"Narayana Vyas Kondreddi" wrote:

> See if these help:
> Using fn_get_sql
> http://vyaskn.tripod.com/fn_get_sql.htm
> Automating Server Side Tracing in SQL Server
> http://vyaskn.tripod.com/server_side...sql_server.htm
> --
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "David" <David@.discussions.microsoft.com> wrote in message
> news:149937CB-151C-4E52-A001-DA4D052C86AC@.microsoft.com...
> in
>
>

Capture a sql command

Hi All.

I have this project that I need help with. There are 9 tables that I need to capture everything that happens to them (update, insert, delete). I was thinking of creating triggers. If someone does any of these actions against them then I need to insert into another table the date, the table name, the command that was run, and the records that are affected by it. Now I know how to do the date and table name, that's easy. My question is how do I capture the command. Once I have the command I can get the records affected.

If anyone knows how to do this, please help.

Thanks,
ODanielsHave you considered trying SQL Profiler?|||I need to capture the records affected before they are affected first. Then execute the command to complete it. The profiler would give me everything that is going on. But will it give me the records affected?|||Don't think you can capture them that way, no.
Will be interesting to see if anyone can show a way to do it.|||Yes it would.
Thanks anyway.|||I need to capture the records affected before they are affected first. Then execute the command to complete it.Triggers operate after the command is executed, so if your requirements are strict about this (don't know why...) then triggers will not help you.
All your commands SHOULD come through stored procedures. You can easily put some code at the top of each procedure to log who ran it, when, and what parameters were supplied.