Sql Stop Logging: column

Showing posts with label column. Show all posts

Friday, March 30, 2012

How to trim leading zeros from a varchar column?

My table has a column named [Liability] varchar datatype which has the data in the format

(

3535.00,

00393.99,

00Loan,

0.00,

.00

*.00

)

I want to trim the leading zeros so that the output should be(trim only the leading zeros)

(

3535.00,

393.99,

Loan,

0.00,

.00

*.00

)

Can someone show my the sql statement for this?

Thanks.

Code Snippet

--To ignore leading '0' and <SPACE> characters:

select substring(Liability,patindex('%[^0 ]%',Liability),8000)

from <MyTable>

--To ignore ONLY leading '0' characters:

select substring(Liability,patindex('%[^0]%',Liability),8000)

from <MyTable>

|||

rusag2,

I think that your suggested solution will also remove the leading zero from [ 0.00 ] which 'should' be retained.

Perhaps something like this would be closer to the desired output:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyValue varchar(20)
)

INSERT INTO @.MyTable VALUES ( '3535.00' )
INSERT INTO @.MyTable VALUES ( '00393.99' )
INSERT INTO @.MyTable VALUES ( '00Loan' )
INSERT INTO @.MyTable VALUES ( '0.00' )
INSERT INTO @.MyTable VALUES ( '00.00' )
INSERT INTO @.MyTable VALUES ( '.00' )
INSERT INTO @.MyTable VALUES ( '*.00' )

SELECT

CASE
WHEN isnumeric( MyValue ) = 1 THEN cast( cast( MyValue AS decimal(18,2)) AS varchar(20))
ELSE substring( MyValue, patindex('%[^0]%', MyValue ), 20 )
END
FROM @.MyTable

3535.00
393.99
Loan
0.00
0.00
0.00
*.00

|||

Thanks Arnie.

That worked!!!!!

sql

How to trap the results of constraints in SQL Server 2005from Visual studio C# Code?

Hi all,

Suppose I have set a CHECK constraint to a column where Salary field is not permitted to be less than 1000 or greater than 10000.

In this situation, when I insert a new record with salary as 10, using a stored procedure from Visual Studio, how will I trap the error from C# Code?

Thanks

Tomy

Your best bet would be to have layered constraints. Your business logic should also make sure that the data being entered is between 1000 and 10000, so that the violation would never reach the database. Database constraints should be left in place for people who like to edit the database directly.

While you can get a violated constraint error from the database, I don't believe it would be phrased properly to display to your users, which would mean lots of parsing in order to make the error presentable and user-friendly. It would be better to use a range validator to prevent the user from entering incorrect information.

As for trapping the error, use Try/Catch blocks

try{}catch (System.Data.SqlClient.SqlException ex){// Handle SQL Exceptions here; // all sql exceptions fall under this exception type, // but the errorcode/errors will be different.}catch (Exception ex){// handle all other errors here.}

|||

Thanks a lot.

How to trap an error from insert ?

When inserting rows from a staging table to a production one, I need to
convert a column of type varchar to type int. Often there're rows that
have junk data in this column and that makes convert() fail. Is it
possible to know that such junk rows exist without getting an error
message ? In other words, is there a way to prevent the insert query
from throwing an error msg, but I still know that it fails ?
thanks,
TamYou can use the ISNUMERIC() to determine whether an
expression is a valid numeric type.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1124509954.485547.268620@.o13g2000cwo.googlegroups.com...
> When inserting rows from a staging table to a production one, I need to
> convert a column of type varchar to type int. Often there're rows that
> have junk data in this column and that makes convert() fail. Is it
> possible to know that such junk rows exist without getting an error
> message ? In other words, is there a way to prevent the insert query
> from throwing an error msg, but I still know that it fails ?
> thanks,
> Tam
>|||I've tried isnumeric() - indeed this was the first thing I did. Yet
strings that have character like 'd', 'e', '.' also pass isnumeric()
but are not convertible to int, and these characters happen quite
commonly in the junk rows in my DB. Any other suggestions ?|||You can check for all digits with:
CASE WHEN
REPLACE(
REPLACE(
.
REPLACE (num '0',''),
1, ''),
.
9, '') = '' THEN CAST(num AS INTEGER) ELSE NULL END|||On 20 Aug 2005 18:30:21 -0700, --CELKO-- wrote:

>You can check for all digits with:
>CASE WHEN
>REPLACE(
> REPLACE(
> ..
> REPLACE (num '0',''),
> 1, ''),
> ..
> 9, '') = '' THEN CAST(num AS INTEGER) ELSE NULL END
Hi Joe,
Never rely on implicit conversion if you don't have to. Use quotes
around 1, 2, ..., 9 as well to prevent conversions.
And of course, this is lots more complicated then encessary:
CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||these are interesting solutions. btw, is there any "try-catch"
structure in sql server as I'm concerned there're cases that exceptions
are not known before hand ?
thanks,
Tam|||>> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
Should be
> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CAST(num AS INTEGER) END
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:kgrgg11r74k425gau3958b7dgjjtcsu9vu@.
4ax.com...
> On 20 Aug 2005 18:30:21 -0700, --CELKO-- wrote:
>
> Hi Joe,
> Never rely on implicit conversion if you don't have to. Use quotes
> around 1, 2, ..., 9 as well to prevent conversions.
> And of course, this is lots more complicated then encessary:
> CASE WHEN num NOT LIKE '%^[0-9]%' THEN CASE(num AS INTEGER) END
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Yep. But in SQL Server 2005
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1124668962.565306.81090@.g43g2000cwa.googlegroups.com...
> these are interesting solutions. btw, is there any "try-catch"
> structure in sql server as I'm concerned there're cases that exceptions
> are not known before hand ?
> thanks,
> Tam
>|||On Mon, 22 Aug 2005 11:06:30 +0530, Roji. P. Thomas wrote:
>Should be
Hi Roji,
You are correct that I made a mistake. But your correction is wrong too
(since it's an exact same copy - you obviously forgot to correct the
mistake before posting).
For others reading this discussion: the correct statement is
(Note how the caret has sneaked one position to the right)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> You are correct that I made a mistake. But your correction is wrong too
> (since it's an exact same copy - you obviously forgot to correct the
> mistake before posting).
:)
In a hurry to correct you asap, I refuse to look at the finer details :)
Infact I have'nt noticed the misplaced caret, but only the mispelled CAST.
Lets keep correcting each other :p
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:m1mkg19d2vmgq7vvus5ncer3di83gdvhut@.
4ax.com...
> On Mon, 22 Aug 2005 11:06:30 +0530, Roji. P. Thomas wrote:
>
> Hi Roji,
>
> You are correct that I made a mistake. But your correction is wrong too
> (since it's an exact same copy - you obviously forgot to correct the
> mistake before posting).
> For others reading this discussion: the correct statement is
>
> (Note how the caret has sneaked one position to the right)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

how to transpose col values into row values?

Hello SQL people,

I need a way to transpose the values of a column into values in a row in a SqlServer7 table. Here is the problem:

I have a table employeeattendance.
Here is the script -

if exists (select * from sysobjects where id = object_id(N'[dbo].[EmployeeAttendance]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EmployeeAttendance]
GO

CREATE TABLE [dbo].[EmployeeAttendance] (
[EmployeeID] [varchar] (20) NULL ,
[AttendanceDate] [varchar] (10) NULL ,
[Status] [char] (1) NULL
) ON [PRIMARY]
GO

insert into [EmployeeAttendance] Values ( '20001', '2003-03-28' , 'Y')
insert into [EmployeeAttendance] Values ( '20001', '2003-03-29' , 'N')
insert into [EmployeeAttendance] Values ( '20001', '2003-03-30' , 'N')
insert into [EmployeeAttendance] Values ( '20002', '2003-03-28' , 'Y')
insert into [EmployeeAttendance] Values ( '20002', '2003-03-29' , 'N')

Now if i say
select * from employeeattendance
Output is -
EmployeeID AttendanceDate Status
20001 2003-03-28 Y
20001 2003-03-29 N
20001 2003-03-30 N
20002 2003-03-28 Y
20002 2003-03-29 N

But i want the output some thing like this-

20001 2003-03-28 Y 2003-03-29 N 2003-03-30 N
20002 2003-03-28 Y 2003-03-29 N

Please, any help would be greatly appreciated. Thanks, GolaI had answer a similar question before. See my replay at view and link table thread on 14 March 2003

ionut|||Originally posted by ionut calin
I had answer a similar question before. See my replay at view and link table thread on 14 March 2003

ionut

Thanks ionut for reply, but i am using MS sql server 7.0 and i have only one table.

Thanks once again.

Gola Munjal|||you could do it in your application program, or else use transact-sql

see
http://sqlteam.com/item.asp?ItemID=11021
and
http://sqlteam.com/item.asp?ItemID=2368

rudy

how to transpose a table using sql statement

as caption, I want to use sql statement to transponse a table (row to column
and column to row), is this possible and how to script it?
e.g.
1,2,3
4,5,6
(1,2,3) is column name
after transponse, the table become
1,4
2,5
3,6
(1,4) is column name
Thx!!"kei" <kei@.discussions.microsoft.com> wrote in message
news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
If you're using SQL 2005, look up pivot()
> as caption, I want to use sql statement to transponse a table (row to
column
> and column to row), is this possible and how to script it?
> e.g.
> 1,2,3
> 4,5,6
> (1,2,3) is column name
> after transponse, the table become
> 1,4
> 2,5
> 3,6
> (1,4) is column name
> Thx!!|||Oh! I am using SQL2000,is there any method using SQL Server 2000?
"Greg D. Moore (Strider)" wrote:
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
> If you're using SQL 2005, look up pivot()
>
> > as caption, I want to use sql statement to transponse a table (row to
> column
> > and column to row), is this possible and how to script it?
> > e.g.
> > 1,2,3
> > 4,5,6
> > (1,2,3) is column name
> > after transponse, the table become
> > 1,4
> > 2,5
> > 3,6
> > (1,4) is column name
> >
> > Thx!!
>
>|||This is a multi-part message in MIME format.
--020008060109080504060608
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
With SQL Server 2000, you do it with a GROUP BY clause and a series of
<AggregateFunction>(CASE...) columns in the select list, where
AggregateFunction is SUM() or MAX() or MIN(), etc. depending on your needs.
See http://www.aspfaq.com/2462
--
*mike hodgson*
http://sqlnerd.blogspot.com
kei wrote:
>Oh! I am using SQL2000,is there any method using SQL Server 2000?
>"Greg D. Moore (Strider)" wrote:
>
>>"kei" <kei@.discussions.microsoft.com> wrote in message
>>news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
>>If you're using SQL 2005, look up pivot()
>>
>>
>>as caption, I want to use sql statement to transponse a table (row to
>>
>>column
>>
>>and column to row), is this possible and how to script it?
>>e.g.
>>1,2,3
>>4,5,6
>>(1,2,3) is column name
>>after transponse, the table become
>>1,4
>>2,5
>>3,6
>>(1,4) is column name
>>Thx!!
>>
>>
--020008060109080504060608
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>With SQL Server 2000, you do it with a GROUP BY clause and a series
of <AggregateFunction>(CASE...) columns in the select list, where
AggregateFunction is SUM() or MAX() or MIN(), etc. depending on your
needs. 
 
See <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.aspfaq.com/2462</a></tt> ">http://www.aspfaq.com/2462">http://www.aspfaq.com/2462</a></tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
kei wrote:
<blockquote cite="mid723F76A5-9F57-42B1-B787-DF454C95361D@.microsoft.com"
type="cite">
<pre wrap="">Oh! I am using SQL2000,is there any method using SQL Server 2000?
"Greg D. Moore (Strider)" wrote:
</pre>
<blockquote type="cite">
<pre wrap="">"kei" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:kei@.discussions.microsoft.com"><kei@.discussions.microsoft.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com">news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com</a>...
If you're using SQL 2005, look up pivot()
</pre>
<blockquote type="cite">
<pre wrap="">as caption, I want to use sql statement to transponse a table (row to
</pre>
</blockquote>
<pre wrap="">column
</pre>
<blockquote type="cite">
<pre wrap="">and column to row), is this possible and how to script it?
e.g.
1,2,3
4,5,6
(1,2,3) is column name
after transponse, the table become
1,4
2,5
3,6
(1,4) is column name
Thx!!
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</blockquote>
</body>
</html>
--020008060109080504060608--|||Just curious: why do you want to do this?
What kind of software environment do you work in?|||may be i can help you in this regard.
can u publish some sample data with scripts to create object?
kay
"kei" <kei@.discussions.microsoft.com> wrote in message
news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
> as caption, I want to use sql statement to transponse a table (row to
> column
> and column to row), is this possible and how to script it?
> e.g.
> 1,2,3
> 4,5,6
> (1,2,3) is column name
> after transponse, the table become
> 1,4
> 2,5
> 3,6
> (1,4) is column name
> Thx!!

Wednesday, March 28, 2012

How to translate net_address

Does anyone know who to translate the column net_address
from master..sysprocess into it's corresponding ip address?
Thanks,
David.The net_address is the mac address of the machine
It corresponds to the Physical address returned from ipconfig /all
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Is there a way to find the host, from the net_address? How?
>--Original Message--
>The net_address is the mac address of the machine
>It corresponds to the Physical address returned from
ipconfig /all
>Rand
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>|||Not that I am aware of. There is no correlation between the mac address and
the ip address.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||You can correlate the IP address back from hostname
select spid, dbo.fn_getIPaddress(hostname) as IPaddress
from sysprocesses
where spid > 50
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"David Velasco" <anonymous@.discussions.microsoft.com> wrote in message
news:f02701c40c61$7cb7ae30$a301280a@.phx.gbl...
> Does anyone know who to translate the column net_address
> from master..sysprocess into it's corresponding ip address?
> Thanks,
> David.sql

Monday, March 26, 2012

how to total the values in a numeric column?

I'm building a report using the matrix control. I have financial data in
different line items (Salary, Rental Expense etc) that needs to be shown by
month (so the columns have months). I have it pretty much working but can't
figure out how to sum up the numbers in each column at the bottom so I can
have a total for every month. Could anyone help?
Thanks a lot.
BobRight-click on the row field that you wish to total, in your case it
would be the Salary field. Choose Sub-total from the menu.
To format the subtotals you need to right-click->properties on the tiny
green triangle that appears on the Total cell.|||ahhh. I was thinking this must be something really simple since it's such a
common function. You can't believe how much time I had spent trying to
figure this out. Thanks a lot.
Bob
"grahamiec" <grahamrichter@.gmail.com> wrote in message
news:1123769689.664448.10490@.g14g2000cwa.googlegroups.com...
> Right-click on the row field that you wish to total, in your case it
> would be the Salary field. Choose Sub-total from the menu.
> To format the subtotals you need to right-click->properties on the tiny
> green triangle that appears on the Total cell.
>

how to total a column in a matrix?

How do you total a column in a matrix? It seems like it should be simple, but I am not getting anywhere.
- Here is some of my matrix as seen in Layout view. The Total column shown was created when I added a subtotal to the group Fields!MonthName.Value.
=Fields!MonthName.Value Total
Travel =Sum(Fields!Travel.Value)
Supplies =Sum(Fields!Supplies.Value)
Services =Sum(Fields!Services.Value)
- Here it is in Preview:
Jan Feb Total
Travel 100 200 300
Supplies 200 300 500
Services 100 200 300
- Here is what I WANT, notice column totals:
Jan Feb Total
Travel 100 200 300
Supplies 200 300 500
Services 100 200 300
Month Totals 400 700 1100
Thanks!can you create this formula:
=Sum(Fields!Travel.Value) + Sum(Fields!Supplies.Value) +
Sum(Fields!Services.Value)
If you have 1 value by column, then its an easy solution.
=Fields!MonthName.Value Total
Travel =Sum(Fields!Travel.Value)
Supplies =Sum(Fields!Supplies.Value)
Services =Sum(Fields!Services.Value)
Month total s =Sum(Fields!Travel.Value) + Sum(Fields!Supplies.Value) +
Sum(Fields!Services.Value)
"jacobryce" <jacobryce@.discussions.microsoft.com> a écrit dans le message de
news:4CD61168-A0DD-43CA-AE53-A6429D9CD63C@.microsoft.com...
> How do you total a column in a matrix? It seems like it should be simple,
but I am not getting anywhere.
> - Here is some of my matrix as seen in Layout view. The Total column
shown was created when I added a subtotal to the group
Fields!MonthName.Value.
> =Fields!MonthName.Value Total
> Travel =Sum(Fields!Travel.Value)
> Supplies =Sum(Fields!Supplies.Value)
> Services =Sum(Fields!Services.Value)
> - Here it is in Preview:
> Jan Feb Total
> Travel 100 200 300
> Supplies 200 300 500
> Services 100 200 300
> - Here is what I WANT, notice column totals:
> Jan Feb Total
> Travel 100 200 300
> Supplies 200 300 500
> Services 100 200 300
> Month Totals 400 700 1100
> Thanks!|||Yes that works, but I just thought there would be a more 'automatic' way, like something you would do in Excel. Thanks, though.
"JÃ©jÃ©" wrote:
> can you create this formula:
> =Sum(Fields!Travel.Value) + Sum(Fields!Supplies.Value) +
> Sum(Fields!Services.Value)
> If you have 1 value by column, then its an easy solution.
> =Fields!MonthName.Value Total
> Travel =Sum(Fields!Travel.Value)
> Supplies =Sum(Fields!Supplies.Value)
> Services =Sum(Fields!Services.Value)
> Month total s =Sum(Fields!Travel.Value) + Sum(Fields!Supplies.Value) +
> Sum(Fields!Services.Value)
>
> "jacobryce" <jacobryce@.discussions.microsoft.com> a Ã©crit dans le message de
> news:4CD61168-A0DD-43CA-AE53-A6429D9CD63C@.microsoft.com...
> > How do you total a column in a matrix? It seems like it should be simple,
> but I am not getting anywhere.
> > - Here is some of my matrix as seen in Layout view. The Total column
> shown was created when I added a subtotal to the group
> Fields!MonthName.Value.
> >
> > =Fields!MonthName.Value Total
> > Travel =Sum(Fields!Travel.Value)
> > Supplies =Sum(Fields!Supplies.Value)
> > Services =Sum(Fields!Services.Value)
> >
> > - Here it is in Preview:
> > Jan Feb Total
> > Travel 100 200 300
> > Supplies 200 300 500
> > Services 100 200 300
> >
> > - Here is what I WANT, notice column totals:
> > Jan Feb Total
> > Travel 100 200 300
> > Supplies 200 300 500
> > Services 100 200 300
> >
> > Month Totals 400 700 1100
> >
> > Thanks!
>
>|||So you have 3 static rows and no row groups? You could probably add a row
group with an expression =1 and then do a grand total.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"jacobryce" <jacobryce@.discussions.microsoft.com> wrote in message
news:773526EC-6041-4EFA-ABC3-F8193E88EB04@.microsoft.com...
> Yes that works, but I just thought there would be a more 'automatic' way,
> like something you would do in Excel. Thanks, though.
> "Jéjé" wrote:
>> can you create this formula:
>> =Sum(Fields!Travel.Value) + Sum(Fields!Supplies.Value) +
>> Sum(Fields!Services.Value)
>> If you have 1 value by column, then its an easy solution.
>> =Fields!MonthName.Value Total
>> Travel =Sum(Fields!Travel.Value)
>> Supplies =Sum(Fields!Supplies.Value)
>> Services =Sum(Fields!Services.Value)
>> Month total s =Sum(Fields!Travel.Value) + Sum(Fields!Supplies.Value) +
>> Sum(Fields!Services.Value)
>>
>> "jacobryce" <jacobryce@.discussions.microsoft.com> a écrit dans le message
>> de
>> news:4CD61168-A0DD-43CA-AE53-A6429D9CD63C@.microsoft.com...
>> > How do you total a column in a matrix? It seems like it should be
>> > simple,
>> but I am not getting anywhere.
>> > - Here is some of my matrix as seen in Layout view. The Total column
>> shown was created when I added a subtotal to the group
>> Fields!MonthName.Value.
>> >
>> > =Fields!MonthName.Value Total
>> > Travel =Sum(Fields!Travel.Value)
>> > Supplies =Sum(Fields!Supplies.Value)
>> > Services =Sum(Fields!Services.Value)
>> >
>> > - Here it is in Preview:
>> > Jan Feb Total
>> > Travel 100 200 300
>> > Supplies 200 300 500
>> > Services 100 200 300
>> >
>> > - Here is what I WANT, notice column totals:
>> > Jan Feb Total
>> > Travel 100 200 300
>> > Supplies 200 300 500
>> > Services 100 200 300
>> >
>> > Month Totals 400 700 1100
>> >
>> > Thanks!
>>|||interesting idea.
I'll test it.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> a écrit dans le
message de news:ekXD0nmXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> So you have 3 static rows and no row groups? You could probably add a row
> group with an expression =1 and then do a grand total.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "jacobryce" <jacobryce@.discussions.microsoft.com> wrote in message
> news:773526EC-6041-4EFA-ABC3-F8193E88EB04@.microsoft.com...
> > Yes that works, but I just thought there would be a more 'automatic'
way,
> > like something you would do in Excel. Thanks, though.
> >
> > "Jéjé" wrote:
> >
> >> can you create this formula:
> >> =Sum(Fields!Travel.Value) + Sum(Fields!Supplies.Value) +
> >> Sum(Fields!Services.Value)
> >>
> >> If you have 1 value by column, then its an easy solution.
> >>
> >> =Fields!MonthName.Value Total
> >> Travel =Sum(Fields!Travel.Value)
> >> Supplies =Sum(Fields!Supplies.Value)
> >> Services =Sum(Fields!Services.Value)
> >> Month total s =Sum(Fields!Travel.Value) + Sum(Fields!Supplies.Value)
+
> >> Sum(Fields!Services.Value)
> >>
> >>
> >>
> >> "jacobryce" <jacobryce@.discussions.microsoft.com> a écrit dans le
message
> >> de
> >> news:4CD61168-A0DD-43CA-AE53-A6429D9CD63C@.microsoft.com...
> >> > How do you total a column in a matrix? It seems like it should be
> >> > simple,
> >> but I am not getting anywhere.
> >> > - Here is some of my matrix as seen in Layout view. The Total column
> >> shown was created when I added a subtotal to the group
> >> Fields!MonthName.Value.
> >> >
> >> > =Fields!MonthName.Value Total
> >> > Travel =Sum(Fields!Travel.Value)
> >> > Supplies =Sum(Fields!Supplies.Value)
> >> > Services =Sum(Fields!Services.Value)
> >> >
> >> > - Here it is in Preview:
> >> > Jan Feb Total
> >> > Travel 100 200 300
> >> > Supplies 200 300 500
> >> > Services 100 200 300
> >> >
> >> > - Here is what I WANT, notice column totals:
> >> > Jan Feb Total
> >> > Travel 100 200 300
> >> > Supplies 200 300 500
> >> > Services 100 200 300
> >> >
> >> > Month Totals 400 700 1100
> >> >
> >> > Thanks!
> >>
> >>
> >>
>sql

Friday, March 23, 2012

How to test a string for numeric?

How do I test if a varchar column contains a numeric value?What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390
AMB
"Snake" wrote:

> How do I test if a varchar column contains a numeric value?
>|||CREATE TABLE #foo
(
bar VARCHAR(12)
)
SET NOCOUNT ON
INSERT #foo SELECT 'aaaaa5aaaa'
INSERT #foo SELECT '5aaaa'
INSERT #foo SELECT 'aaaa5'
INSERT #foo SELECT 'xxxxx'
INSERT #foo SELECT '.'
SELECT bar, ContainsNumeric = CASE
WHEN bar LIKE '%[0-9]%' THEN 'Yes'
ELSE 'No'
END
FROM #foo
DROP TABLE #foo
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:EF4FDF27-7F2B-4BF9-A8CE-6EE1E5C9C74F@.microsoft.com...
> How do I test if a varchar column contains a numeric value?
>

Wednesday, March 21, 2012

How to tell if I can convert a varchar to int

Hi,
I am attempting to write a procedure whereby I can get the maximum numeric
value in a varchar column (which may contain a mix of words and number
strings).
I'm trying to use:
SELECT MAX ( case isnumeric(value) when 1 then cast (value as int) else 0
end ) FROM table
However, given that entries in the value column can be up to 255 characters,
I obviously have a problem when the number is too long to be cast as an int
(or indeed a bigint or whatever).
In the case of this happening, I'd like to ignore these values, so what I
need is a means of telling whether a value can be cast as an int before
attempting to do it. Using isnumeric(..) isn't enough because it can't
confirm that I can actually convert it.
Any help would be very welcome,
Thanks in advance,
Chris.Maybe try datalength(column) which gives you the number of characters...
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
news:LnD3c.3274$re1.2931@.newsfe1-win...
> Hi,
> I am attempting to write a procedure whereby I can get the maximum numeric
> value in a varchar column (which may contain a mix of words and number
> strings).
> I'm trying to use:
> SELECT MAX ( case isnumeric(value) when 1 then cast (value as int) else 0
> end ) FROM table
> However, given that entries in the value column can be up to 255
characters,
> I obviously have a problem when the number is too long to be cast as an
int
> (or indeed a bigint or whatever).
> In the case of this happening, I'd like to ignore these values, so what I
> need is a means of telling whether a value can be cast as an int before
> attempting to do it. Using isnumeric(..) isn't enough because it can't
> confirm that I can actually convert it.
> Any help would be very welcome,
> Thanks in advance,
> Chris.
>|||To add to Wayne's response, consider using LIKE instead of ISNUMERIC. The
ISNUMERIC function will return 1 for some obscure numeric expressions.
SELECT
MAX(CAST(Value as decimal(38)))
FROM MyTable
WHERE
Value NOT LIKE '%[^0-9]%' AND
DATALENGTH(Value) < 39 AND
Value <> ''
Also, the need to do this sort of thing may be symptomatic of a database
design issue. It's usually not a good idea to store different types of data
in the same column in a relational database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris Lacey" <chris.lacey@.bigfoot.com> wrote in message
news:LnD3c.3274$re1.2931@.newsfe1-win...
> Hi,
> I am attempting to write a procedure whereby I can get the maximum numeric
> value in a varchar column (which may contain a mix of words and number
> strings).
> I'm trying to use:
> SELECT MAX ( case isnumeric(value) when 1 then cast (value as int) else 0
> end ) FROM table
> However, given that entries in the value column can be up to 255
characters,
> I obviously have a problem when the number is too long to be cast as an
int
> (or indeed a bigint or whatever).
> In the case of this happening, I'd like to ignore these values, so what I
> need is a means of telling whether a value can be cast as an int before
> attempting to do it. Using isnumeric(..) isn't enough because it can't
> confirm that I can actually convert it.
> Any help would be very welcome,
> Thanks in advance,
> Chris.
>

Monday, March 19, 2012

How to Take a Column Value to Make it a Field ?

if a customer can have up to 10 acounts and each account has a balance,

in customer_account table, the same custID repeats and relates to an

accountID and each account has its own balance.

How to display each customer in ONE row and list his account one next to another ?

for example:

custID, acct#1, acct#1Balance, acct#2, acct#2Balance, acct#3, acct#3Balance

how to do write SQl to achive it ?

This is chanllenging to me...

Your advise is greatly appreciated !

Most likely, you will find value in the new PIVOT keyword.

This previous thread may help direct you.

|||

just to add what Arnie has said.. PIVIOT is only available in SQL Server 2005.

Madhu

|||

Since you have to pivot more than one column, I recommend to use the Case..When (legacy approach).

Here the sample, (this sample is for SQL Server 2005 only – let me know if you SQL Server 2000)

Code Snippet

Create Table #accounts (

[CustomerId] int ,

[AccountNo] int ,

[Balance] money

);

Insert Into #accounts Values('1','272837282','100000');

Insert Into #accounts Values('1','272837298','20000');

Insert Into #accounts Values('1','272837390','1000');

Insert Into #accounts Values('1','272937282','100000');

Insert Into #accounts Values('2','272437282','10000');

Insert Into #accounts Values('3','572837282','83734937');

Insert Into #accounts Values('3','572837285','28292');

;With Grouped

(

Select * ,Row_Number() Over(Partition By CustomerId Order BY CustomerId) [Acct#] from #accounts

)

Select

CustomerId

,Max(Case When [Acct#] =1 Then [AccountNo] End) [acct#1]

,Max(Case When [Acct#] =1 Then [Balance] End) [acct#1Balance]

,Max(Case When [Acct#] =2 Then [AccountNo] End) [acct#2]

,Max(Case When [Acct#] =2 Then [Balance] End) [acct#2Balance]

,Max(Case When [Acct#] =3 Then [AccountNo] End) [acct#3]

,Max(Case When [Acct#] =3 Then [Balance] End) [acct#3Balance]

,Max(Case When [Acct#] =4 Then [AccountNo] End) [acct#4]

,Max(Case When [Acct#] =4 Then [Balance] End) [acct#4Balance]

,Max(Case When [Acct#] =5 Then [AccountNo] End) [acct#5]

,Max(Case When [Acct#] =5 Then [Balance] End) [acct#5Balance]

--You can add upto n accounts as per your data

From

Grouped

Group By

CustomerId

Monday, March 12, 2012

How to suppress warnings from a data flow task

Hi,

I am getting unusable warnings from dataflow task while running. Say for example I am getting column A, B, C from source and I am using only column C in destination. Whenever I run the package, I am getting a warning saying that

"The output column "A" (67) on output "Output0" (5) and component "Data Flow Task" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance."

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

"Get rid of unused columns" means modifying components with async outputs (sources, union all, merge, merge join, sort, aggregation) by eliminating unused output columns. Classic example is to use a sproc or select statement rather than a Table/View, which will often clog the dataflow with unused output columns.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

So, in your case, in the transforme task after your datasource, uncheck the columns that will not be used in the dataflow. If you dont insert this columns on the destination, and dont have any transformations depending on these columns, you can delete it from teh dataflow..

Understood?

Regards!!!

|||

Thanks for ur reply.

But After the source component, I have only lookup component which will looks up for the value. If it finds a row, update will happen or else insert will happen. In lookup we can not unselect the columns.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:
Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

Understood?

Regards!!!

|||

Thanks for ur reply.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:
Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

Understood?

Regards!!!

|||

Thanks for ur reply.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:
Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

Understood?

Regards!!!

|||

Thanks for ur reply.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:
Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

Understood?

Regards!!!

|||

Thanks for ur reply.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:
Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

Understood?

Regards!!!

|||

Thanks for ur reply.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:
Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

How to suppress warnings from a data flow task

Hi,

Can any one help me how to resolve these warnings?

Thanks in advance.

You can ignore the warning, or do what it says, and remove the unused column from the data flow.|||

Get rid of the unused columns by not having them present on a given output.

That "unused output columns" warning is a very useful, and use-able, for that matter, warning message.

|||

Dear Friend,

In order to improve the performance in your dataflow, it's better to clean all the warning that the system return.

Understood?

Regards!!!

|||

Thanks for ur reply.

Shall I delete the columns in the "Inputs and Outputs" editor for source component?

|||

Your datasource has a SQL Statment inside, no?

If yes, delete the fields in the SQL

If not, explain me how your data comes into dataflow!

Regards!

|||Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.|||

S Venkat wrote:
Our source does not have any query. It will just read the data from file. We have to read all columns. But in data flow I will just use only one column.

Then in the data source, only select the column you need.|||

So, select only the column you need to use in your dataflow...

Understood? If not, ahow us an image or something with more detail!

regards!

Friday, March 9, 2012

How to sum Salary column with a condition

Hi,
I'd like to sum the salary for the PayDate<'5/1/1985'. Can I use one SQL
statement to get it? Please show me the SQL statement. Thank a lot.
EmployeeNo PayDate Salary
1 1/31/1985 $1,000.00
1 2/28/1985 $1,000.00
1 3/31/1985 $1,000.00
1 4/30/1985 $1,000.00
1 5/31/1985 $1,000.00
1 6/30/1985 $1,000.00"chris" <yma16@.kicon.com> wrote in message
news:eYNiCgqGFHA.3916@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'd like to sum the salary for the PayDate<'5/1/1985'. Can I use one SQL
> statement to get it? Please show me the SQL statement. Thank a lot.
> EmployeeNo PayDate Salary
> 1 1/31/1985 $1,000.00
> 1 2/28/1985 $1,000.00
> 1 3/31/1985 $1,000.00
> 1 4/30/1985 $1,000.00
> 1 5/31/1985 $1,000.00
> 1 6/30/1985 $1,000.00
>
I assume that you want to do some grouping, or else you could simply use a
WHERE clause.
select EmployeeNo,
sum(case when PayDate<'5/1/1985' then Salary else 0 end) Salary
from MyTable
group by EmployeeNo
David|||Chris,
Try....
Select Sum(Salary) as 'Total Salary', EmployeeNo
from YourTable
where PayDate < '5/1/1985'
Group By EmployeeNo
Thanks
Barry|||Thank you both for the quick help.
"chris" <yma16@.kicon.com> wrote in message
news:eYNiCgqGFHA.3916@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'd like to sum the salary for the PayDate<'5/1/1985'. Can I use one
SQL
> statement to get it? Please show me the SQL statement. Thank a lot.
> EmployeeNo PayDate Salary
> 1 1/31/1985 $1,000.00
> 1 2/28/1985 $1,000.00
> 1 3/31/1985 $1,000.00
> 1 4/30/1985 $1,000.00
> 1 5/31/1985 $1,000.00
> 1 6/30/1985 $1,000.00
>

How to Sum certain fields in a column

Hi all,
I am new to reporting services. What I am trying to do is display some of
our accounting data in a debit and credit fields. I am using this expression
to display the correct data in the fields:
=Iif(Fields!credit.value,0,Fields!Amount.Value), however at the bottom I want
to sum only the information that was displayed. Does anyone know how to best
achieve this?Hi there Cynthias,
good old days of access ;-)
AFAIK, -- > No there is no way to aggregate the report fields, you should do
this for best practice in your query, to minimize the rendering time, though
the Database Server (if you are using one) is in most cases the smartest
solution.
HTH, Jens Süßmeyer.
"cynthia" <cynthia@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BEA9F4E0-4BCC-40DA-AD06-13F7ECB2537A@.microsoft.com...
> Hi all,
> I am new to reporting services. What I am trying to do is display some of
> our accounting data in a debit and credit fields. I am using this
> expression
> to display the correct data in the fields:
> =Iif(Fields!credit.value,0,Fields!Amount.Value), however at the bottom I
> want
> to sum only the information that was displayed. Does anyone know how to
> best
> achieve this?|||There is more than one way to do it.
One way that I think is particularly clean is to do the following. In your
field list (where you drag a field onto your form) do a right mouse click,
add field. The field can be a field that is not coming from the database but
is an expression. Put in the expression you have below and call it whatever
you want. This field is now indistinguishable from a field that came from
the database. You can sum, group, whatever you want on it.
The second way is to put in this:
=sum(iif(Fields!credit.value,0,Fields!Amount.Value)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jens Süßmeyer" <Jens@.sqlserver2005.de> wrote in message
news:%23ThjqK6PFHA.1528@.TK2MSFTNGP09.phx.gbl...
> Hi there Cynthias,
> good old days of access ;-)
> AFAIK, -- > No there is no way to aggregate the report fields, you should
do
> this for best practice in your query, to minimize the rendering time,
though
> the Database Server (if you are using one) is in most cases the smartest
> solution.
> HTH, Jens Süßmeyer.
>
> "cynthia" <cynthia@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:BEA9F4E0-4BCC-40DA-AD06-13F7ECB2537A@.microsoft.com...
> > Hi all,
> > I am new to reporting services. What I am trying to do is display some
of
> > our accounting data in a debit and credit fields. I am using this
> > expression
> > to display the correct data in the fields:
> > =Iif(Fields!credit.value,0,Fields!Amount.Value), however at the bottom I
> > want
> > to sum only the information that was displayed. Does anyone know how to
> > best
> > achieve this?
>

Friday, March 30, 2012

How to trim leading zeros from a varchar column?

How to trap the results of constraints in SQL Server 2005from Visual studio C# Code?

How to trap an error from insert ?

how to transpose col values into row values?

how to transpose a table using sql statement

Wednesday, March 28, 2012

How to translate net_address

Monday, March 26, 2012

how to total the values in a numeric column?

how to total a column in a matrix?

Friday, March 23, 2012

How to test a string for numeric?

Wednesday, March 21, 2012

How to tell if I can convert a varchar to int

Monday, March 19, 2012

How to Take a Column Value to Make it a Field ?

Monday, March 12, 2012

How to suppress warnings from a data flow task

How to suppress warnings from a data flow task

How to suppress warnings from a data flow task

How to suppress warnings from a data flow task

How to suppress warnings from a data flow task

How to suppress warnings from a data flow task

How to suppress warnings from a data flow task

Friday, March 9, 2012

How to sum Salary column with a condition

How to Sum certain fields in a column

Sql Stop Logging

Blog Archive

About Me