Sql Activity log backup: value

Showing posts with label value. Show all posts

Thursday, March 29, 2012

Adjust values in db base in the value modified on the form?

Hi everyone,

Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.

Thanks for your help!Hi everyone,

Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.

Thanks for your help!

Paste your query or the tables involved and explain what you want in new one?|||I don't have any query yet, I need to come up with the query that will check which value has been changed and do update approprietly.

Ex. #1 - Discount value is changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (changed to) 10%
TotalSellPrice $0 (hasn't been changed) -> I need to calculate it then. it becomes $180

Ex. #2 - Discount and TotalSellPrice values are changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (changed to) 10%
TotalSellPrice $0 (changed to) $150
Then I need to calculate TotalSellPrice since both Discount and TotalSellPrice values have been changed. TotalSellPrice becomes $180

Ex. #3 - TotalSellPrcie value is changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (hasn't been changed) -> I need to calculate it. Discount is 25%
TotalSellPrice $0 (changed to) $150

Ex. #4 - TotalSellPrice value is changed to number bigger then it's original one then I have to keep Discount at 0% don't go negative
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (hasn't been changed) -> I need to keep Discount at 0%
TotalSellPrice $0 (changed to) $250|||I don't have any query yet, I need to come up with the query that will check which value has been changed and do update approprietly.

Whatever I make out from the above problem is, you want to adjust the data according to the changes in the other fields.I think you can easily do it at the front end and send the update query to the database according to its invoice no.
An user can see the store data and update it anytime by changing the different fields.You can do the neccessary calculation at the front end.And pass a simple update query to the database.That will do the trick..|||I think you can easily do it at the front end and send the update query to the database according to its invoice no.
The thing is it would not be efficient from a programming stand point, because I have to grab all the information from the database and store it somewhere in the session, then compare it to what user enters, then create a query dynamically instead of using stored procedure. I need a back end query that will handle all these stuff for me.

Any idea?|||Any idea?

And you need not have to create a stored procedure for such a small calculation.I think so...;)|||Maybe I explained it incorrectly, I am sorry.
What I do I initially populate those fields with the default data from the database

Ex. - Initial page load
Price -> "$100"
Quantity -> "0"
Discount -> "0%"
TotalSellPrice -> "$0"

After user updates any of writable fields I do update and populate that form again but base on the rules I described before, I hope that explains everything.|||Maybe I explained it incorrectly, I am sorry.
What I do I initially populate those fields with the default data from the database

Ex. - Initial page load
Price -> "$100"
Quantity -> "0"
Discount -> "0%"
TotalSellPrice -> "$0"

After user updates any of writable fields I do update and populate that form again but base on the rules I described before, I hope that explains everything.
So...?? You can write the adjustment code in asp page,and simply update the record.Mind it you are not doing anything more than that..so I don't think on the point of efficiency ,use of stored procedure will make any difference...|||After user updates any of writable fields I do update and populate that form again but base on the rules I described before...You think it is efficient or good programming practice to make a call to the database every time a user changes a value on a form?

It's not, and you certainly wouldn't design scalable enterprise applications this way.

Your form should get complete recordsets from the database (even defaults), and should submit complete recordsets to the database.

And frankly, you don't have to store all the detail data to keep track of the new total. NewTotal = OldTotal - OldValue + NewValue. That is just three variables.

Tuesday, March 27, 2012

Ad-Hoc Matrix report calculations

Is there a way to change the value in an ad-hoc matrix report to be the percent of the row an not just the raw number?

unfortunatly not

Ad-hoc INSERT of new Identity Value

Hello,
I have a table with a primary key having IDENTITY (ID).
In case of an INSERT I want to fill another Column C1
with the new Identity value.
How can I achive this? By a Trigger maybe?
Thank you very much
JoachimTry
create table #t (c1 int not null identity(1,1),c2 char(1),c3 as c1)
insert into #t (c2) values ('a')
insert into #t (c2) values ('b')
insert into #t (c2) values ('c')
select * from #t
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:OysGn1beGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table with a primary key having IDENTITY (ID).
> In case of an INSERT I want to fill another Column C1
> with the new Identity value.
> How can I achive this? By a Trigger maybe?
> Thank you very much
> Joachim|||What would be the point of having redundant data in the table? Anyway, much
easier with a computed column than with a trigger, but this won't work if
your intention is to later update C1.
CREATE TABLE dbo.foo
(
id INT IDENTITY(1,1),
blat VARCHAR(32),
C1 AS id
);
INSERT dbo.foo(blat) SELECT 'bar';
SELECT * FROM dbo.foo;
DROP TABLE dbo.foo;
Or just use a view:
CREATE VIEW dbo.View_foo
AS
SELECT id, C1 = id
FROM dbo.foo;
But it's tough to provide a good answer if we have no idea. It's usually
better to describe the intended goal than to ask us how to accomplish the
solution you've already determined is the only way to do it...
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:OysGn1beGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table with a primary key having IDENTITY (ID).
> In case of an INSERT I want to fill another Column C1
> with the new Identity value.
> How can I achive this? By a Trigger maybe?
> Thank you very much
> Joachim

Thursday, March 22, 2012

Adding value in a query

I am trying to insert a value numeric + 1 in to db table but i get error when i do this

this is the code

Const SQLAsString ="INSERT INTO [PageHits] ([DefaultPage]) VALUES (@.defaultP)"

Dim myCommandAsNew Data.SqlClient.SqlCommand(SQL, myConnection)myCommand.Parameters.AddWithValue("@.DefaultP" +"1", DefaultP.Text.Trim())

myConnection.Open()

myCommand.ExecuteNonQuery()

myConnection.Close()

The Error:

Must declare the variable '@.defaultP'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Must declare the variable '@.defaultP'.

The code you wrote will send a parameter called @.DefaultP1 to the database.

Did you want to increase an existing value in the database? If so, you must use an UPDATE query.sql

Tuesday, March 20, 2012

Adding text to a value?

Hello!

Hoping this is simple. I have a age SP that does the age from getdate and DOB. I also have it that does months if the year is zero. What I would like to do if the value is 10, and it's a month value, how would I add the "mo" to it. So it reads 10mo. Is this possible?

Thanks!

Rudy

use the "+" operator

@.Months_string = @.months + 'mo'

if @.months is a number field then cast it first:

@.Months_string = cast(@.months as varchar)+ 'mo'|||

You need to cast the number to a character and then just concatenate

selectconvert(varchar(3), @.age)+'mo.'

|||

Code Snippet

select case when yr > 0
then cast(yr as varchar) + ' Yr'
else cast(mo as varchar) + ' Mo'
end as theValue
from ( select 3 as yr, 3 as mo union all
select 1, 5 union all
select 0, 10
) a

/*
theValue
3 Yr
1 Yr
10 Mo
*/

|||

WOW!!

Thank you all! Great suggestions!

Rudy

|||

Ok!

I spoke way too soon! The first mistake I made was to trust I actually wrote the store procedure correct. LOL. It works, but it calculates the age wrong. I guess I was so excited it fiinally worked with out any errors, I didn't check if it was correct. My DOB field is date time, my Age field in nvarchar. He is my feeble attempt. I'm sure there is a better way to write this.

UPDATE Active_Orders

SET Age =CASEWHENdateadd(year,datediff(year, DOB,GetDate()), DOB)

<GetDate()THEN(datediff(year, DOB,GetDate()))- 1 ELSEdatediff(month, DOB,getdate())

% 12 ENDFROM Active_Orders

Any help on this would be greatly appreciated!

Thanks!!

Jim

|||

Jim:

Would something like this work for you:

Code Snippet

UPDATE Active_Orders
SET Age = CASE WHEN year(getdate() - cast(@.dob as datetime)) > 1900
THEN (datediff (year, DOB, GetDate())) - 1
ELSE datediff(month, DOB, getdate()) % 12
END
FROM Active_Orders

|||

Code Snippet

createtable #t1 (namevarchar(25), dob datetime)

insertinto #t1

select'Gomez','02/29/1908'union

select'Morticia','05/01/1936'union

select'Wednesday','10/31/1959'union

select'Friday','12/25/2006'

select*,

age =casewhendatediff(mm, dob,getdate())< 12

--months

thencasewhendatediff(mm, dob,getdate())< 0

then 0

elsedatediff(mm, dob,getdate())

end

else

-- years

datediff(mm, dob,getdate())/12

end

from #t1

So, the update statement would be:

Code Snippet

UPDATE Active_Orders

SET Age =casewhendatediff(mm, dob,getdate())< 12

--months

thencasewhendatediff(mm, dob,getdate())< 0
then 0
elsedatediff(mm, dob,getdate())

end

else
-- years

datediff(mm, dob,getdate())/12

end

FROM Active_Orders

Adding subreport values

I have a _very_ simple table with just one row and three columns.
The first column of the row contains a subreport that retrieves a single
value.
The second column of the row contains a subreport that retrieves a single
value.
...and heres where I lose it... :-)
The third column should contain the sum of column1 and column2. Period.
Please help. Thanks :-)
jdjespersenIf all you are doing is returning one value from the sub-report then why
use the sub-report at all in the first place?
Secondly, you can't add two sub-reports together. Just because your
sub-reports only return one value does not mean that the main report will
see it as such. If you plugged a sub-report in to that area that returned
40 rows of data and then tried to add those together with another
sub-reports output what would you expect to see?
So in essence you can't do what you are trying to do since you can't
reference the subreport the way you are trying.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Reply-To: "Jeppe Jespersen" <jdj@.jdj.dk>
> From: "Jeppe Jespersen" <jdj@.jdj.dk>
> Subject: Adding subreport values
> Date: Fri, 26 Oct 2007 13:41:07 +0200
> Lines: 17
> Message-ID: <50E85932-E653-4C37-A096-567C497B9B78@.microsoft.com>
> MIME-Version: 1.0
> Content-Type: text/plain;
> format=flowed;
> charset="iso-8859-1";
> reply-type=original
> Content-Transfer-Encoding: 7bit
> I have a _very_ simple table with just one row and three columns.
> The first column of the row contains a subreport that retrieves a single
> value.
> The second column of the row contains a subreport that retrieves a single
> value.
> ...and heres where I lose it... :-)
> The third column should contain the sum of column1 and column2. Period.
> Please help. Thanks :-)
> jdjespersen
>
>|||Hi Chris, and thanks for replying...
> If all you are doing is returning one value from the sub-report then why
> use the sub-report at all in the first place?
In short, 'cause i suck. :-( I'll try to explain.
Data is in an Analysis Services database, and MDX is not my strongpoint.
I was hoping that by splitting my queries into seperate datasources, I could
get away with much simpler queries. But, not being able to use data from
two datasets within a single table control, i figured i could do it with
subreports.
Imagine a desired report table layout like this. Not the most complex, i
admit:
Company Last Years Sales Current Sales
Total
Adv.Works 10000 4000
14000
Northwind 3200 2000
5200
Designing the above query may not be rocket science, but as far as MDX goes,
i'm more of a soapbox-car scientist. And not even a good one... :-/
FYI, I do have a time dimension on my datasource.
Any help greatly appreciated.
Jeppe Jespersen
Denmark
> Secondly, you can't add two sub-reports together. Just because your
> sub-reports only return one value does not mean that the main report will
> see it as such. If you plugged a sub-report in to that area that returned
> 40 rows of data and then tried to add those together with another
> sub-reports output what would you expect to see?
> So in essence you can't do what you are trying to do since you can't
> reference the subreport the way you are trying.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
>> Reply-To: "Jeppe Jespersen" <jdj@.jdj.dk>
>> From: "Jeppe Jespersen" <jdj@.jdj.dk>
>> Subject: Adding subreport values
>> Date: Fri, 26 Oct 2007 13:41:07 +0200
>> Lines: 17
>> Message-ID: <50E85932-E653-4C37-A096-567C497B9B78@.microsoft.com>
>> MIME-Version: 1.0
>> Content-Type: text/plain;
>> format=flowed;
>> charset="iso-8859-1";
>> reply-type=original
>> Content-Transfer-Encoding: 7bit
>> I have a _very_ simple table with just one row and three columns.
>> The first column of the row contains a subreport that retrieves a single
>> value.
>> The second column of the row contains a subreport that retrieves a single
>> value.
>> ...and heres where I lose it... :-)
>> The third column should contain the sum of column1 and column2. Period.
>> Please help. Thanks :-)
>> jdjespersen
>>
>|||Trust me Analysis Services is something I try to stay away from so I feel
your pain. When it comes to MDX I have almost no idea. Try the analysis
services newsgroup and see if they can give you a hand on creating a query
that will pull back the data you need in one dataset. That way you can
avoid the subreports altogether.
Good luck. You'll need it writing those queries ;)
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: "Jeppe Jespersen" <jdj@.jdj.dk>
> References: <50E85932-E653-4C37-A096-567C497B9B78@.microsoft.com>
<FQVdkw$FIHA.360@.TK2MSFTNGHUB02.phx.gbl>
> Subject: Re: Adding subreport values
> Date: Fri, 26 Oct 2007 21:31:32 +0200
> Lines: 87
>
> Hi Chris, and thanks for replying...
> > If all you are doing is returning one value from the sub-report then why
> > use the sub-report at all in the first place?
> In short, 'cause i suck. :-( I'll try to explain.
> Data is in an Analysis Services database, and MDX is not my strongpoint.
> I was hoping that by splitting my queries into seperate datasources, I
could
> get away with much simpler queries. But, not being able to use data from
> two datasets within a single table control, i figured i could do it with
> subreports.
> Imagine a desired report table layout like this. Not the most complex, i
> admit:
> Company Last Years Sales Current Sales
> Total
> Adv.Works 10000 4000
> 14000
> Northwind 3200 2000
> 5200
> Designing the above query may not be rocket science, but as far as MDX
goes,
> i'm more of a soapbox-car scientist. And not even a good one... :-/
> FYI, I do have a time dimension on my datasource.
> Any help greatly appreciated.
> Jeppe Jespersen
> Denmark
>
>
> >
> > Secondly, you can't add two sub-reports together. Just because your
> > sub-reports only return one value does not mean that the main report
will
> > see it as such. If you plugged a sub-report in to that area that
returned
> > 40 rows of data and then tried to add those together with another
> > sub-reports output what would you expect to see?
> >
> > So in essence you can't do what you are trying to do since you can't
> > reference the subreport the way you are trying.
> >
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > --
> >> Reply-To: "Jeppe Jespersen" <jdj@.jdj.dk>
> >> From: "Jeppe Jespersen" <jdj@.jdj.dk>
> >> Subject: Adding subreport values
> >> Date: Fri, 26 Oct 2007 13:41:07 +0200
> >> Lines: 17
> >> Message-ID: <50E85932-E653-4C37-A096-567C497B9B78@.microsoft.com>
> >> MIME-Version: 1.0
> >> Content-Type: text/plain;
> >> format=flowed;
> >> charset="iso-8859-1";
> >> reply-type=original
> >> Content-Transfer-Encoding: 7bit
> >>
> >> I have a _very_ simple table with just one row and three columns.
> >>
> >> The first column of the row contains a subreport that retrieves a
single
> >> value.
> >>
> >> The second column of the row contains a subreport that retrieves a
single
> >> value.
> >>
> >> ...and heres where I lose it... :-)
> >>
> >> The third column should contain the sum of column1 and column2. Period.
> >>
> >> Please help. Thanks :-)
> >>
> >> jdjespersen
> >>
> >>
> >>
> >
>
>|||> Trust me Analysis Services is something I try to stay away from so I feel
> your pain. When it comes to MDX I have almost no idea. Try the analysis
> services newsgroup and see if they can give you a hand on creating a query
> that will pull back the data you need in one dataset. That way you can
> avoid the subreports altogether.
> Good luck. You'll need it writing those queries ;)
Eeeek. And this is one of the simpler queries I'll need.
Thanks for trying to help.
Jeppe Jespersen
Denmark

Monday, March 19, 2012

adding some rows to a select

Hi folks,

I've a sql query problem I was wondering if you all had a quick and
dirty solution for. I've a query:

Select code, value from table_a where date in
(2004) and a_code in ('1000','2000') and b_code in ('01000','02000')

This returns a table that looks like:

A_CODE B_CODE VALUE
-- -- --

1000 01000 $500
1000 02000 $750

What I'd like to see is:

A_CODE B_CODE VALUE
-- -- --

1000 01000 $500
1000 02000 $750
2000 01000 $0
2000 02000 $0

Any suggestions on how to rewrite my query so the results show A_CODE
2000 with a VALUE of 0 or null?

Thank much in advance!

MarcMarc (brownjenkn@.aol.com) writes:
> I've a sql query problem I was wondering if you all had a quick and
> dirty solution for. I've a query:
> Select code, value from table_a where date in
> (2004) and a_code in ('1000','2000') and b_code in ('01000','02000')
> This returns a table that looks like:
> A_CODE B_CODE VALUE
> -- -- --
> 1000 01000 $500
> 1000 02000 $750
> What I'd like to see is:
> A_CODE B_CODE VALUE
> -- -- --
> 1000 01000 $500
> 1000 02000 $750
> 2000 01000 $0
> 2000 02000 $0
> Any suggestions on how to rewrite my query so the results show A_CODE
> 2000 with a VALUE of 0 or null?

CREATE TABLE a_code (a_code char(4) NOT NULL
CREATE TABLE b_code (b_code char(5) NOT NULL

go
INSERT a_code (a_code) VALUES ('1000')
INSERT a_code (a_code) VALUES ('2000')
INSERT b_code (b_code) VALUES ('01000')
INSERT b_code (b_code) VALUES ('02000')
go
SELECT a.a_code, b.b_code, coalesce(t.value, 0)
FROM (a_code a
CROSS JOIN b_code b)
LEFT JOIN table_a t ON a.a_code = t.a_code
AND b.b_code = t.b_code
ABD t.date = '2004'

Here I am handling a_code and b_code in the same way, so you will
get output for missing b_codes as well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Sunday, March 11, 2012

Adding Report Filter on Float field

I am adding a filter on a float datatype field in my dataset.
Example:
=Fields!SharePrice.Value > 50
When running the report, I get the following error:
"...the processing of filter for the data set 'dataset' cannot be performed.
The comparision failed. Please check the data type returned by the data
expression."
I've tried casting the field as decimal with mixed results. Does anyone
know why this error occurs?You'd have to explicitly cast the return value using CSng() function.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am adding a filter on a float datatype field in my dataset.
> Example:
> =Fields!SharePrice.Value > 50
> When running the report, I get the following error:
> "...the processing of filter for the data set 'dataset' cannot be
performed.
> The comparision failed. Please check the data type returned by the data
> expression."
> I've tried casting the field as decimal with mixed results. Does anyone
> know why this error occurs?
>|||Try this:
Filter expression: =CDbl(Fields!SharePrice.Value)
Operator: >
Filter value: =CDbl(50)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am adding a filter on a float datatype field in my dataset.
> Example:
> =Fields!SharePrice.Value > 50
> When running the report, I get the following error:
> "...the processing of filter for the data set 'dataset' cannot be
performed.
> The comparision failed. Please check the data type returned by the data
> expression."
> I've tried casting the field as decimal with mixed results. Does anyone
> know why this error occurs?
>|||Thanks. That did the trick.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eNogP1WWEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Try this:
> Filter expression: =CDbl(Fields!SharePrice.Value)
> Operator: >
> Filter value: =CDbl(50)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Rod Bautista" <rod.bautista@.adam-us.com> wrote in message
> news:uZ9L9oWWEHA.3716@.TK2MSFTNGP11.phx.gbl...
> > I am adding a filter on a float datatype field in my dataset.
> >
> > Example:
> >
> > =Fields!SharePrice.Value > 50
> >
> > When running the report, I get the following error:
> >
> > "...the processing of filter for the data set 'dataset' cannot be
> performed.
> > The comparision failed. Please check the data type returned by the data
> > expression."
> >
> > I've tried casting the field as decimal with mixed results. Does anyone
> > know why this error occurs?
> >
> >
>

Saturday, February 25, 2012

Adding Leading Zeros to a char value - TSQL Question

Hi all:

I have save stored procedure which does an insert and update. Before the insert or update statements, I have the following SQL to adding leading zeroes to in my input params:

DECLARE @.SchoolCode_Modified char(6)

SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + '123',6))

SELECT @.SchoolCode_Modified AS Col1

The problem is that this works on by itself on SSMS, but when incorporated in the SPROC, it doesnt add the leading zeros. The datatype for both of the colum is char(6) as well. If I run the above query in SQL Server Management Studio, it displays Col1 with 000123 in it, which is what I want, from the sproc, but that doesnt happen. Why? Can someone please help me? The following is how it is implemented in the SPROC:

CREATE PROCEDURE [LAF].[uspSaveLoanApplication]

...more params here

@.SchoolCode char(6),

@.BranchCode char(2),

@.PK int OUTPUT,

@.PreviousLoanApplicationStatus char(3) OUTPUT

SET NOCOUNT OFF

SET @.PreviousLoanApplicationStatus = 0

IF EXISTS(SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

BEGIN

SET @.PreviousLoanApplicationStatus = (SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

SELECT @.PreviousLoanApplicationStatus PreviousLoanApplicationStatus

END

-- Add Leading Zeroes to School Code and Branch Code to conform with CLIPS Formatting

DECLARE @.SchoolCode_Modified char(6)

DECLARE @.BranchCode_Modified char(2)

SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + @.SchoolCode,6))

SET @.BranchCode_Modified = (SELECT Right(Replicate('0',2) + @.BranchCode,2))

IF EXISTS(SELECT [LoanApplicationID] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

BEGIN

UPDATE [LAF].[LoanApplication] SET

....more here

[SchoolCode] = @.SchoolCode_Modified,

[BranchCode] = @.BranchCode_Modified,

...more here

WHERE

[LoanApplicationID] = @.LoanApplicationID

AND [UpdatedOn] = @.LastUpdated

SELECT @.LoanApplicationID PK

SET @.PK = @.LoanApplicationID

END

ELSE

BEGIN

INSERT INTO [LAF].[LoanApplication] (

.....more here

[SchoolCode],

[BranchCode],

.....more here

) VALUES (

....more here

@.SchoolCode_Modified,

@.BranchCode_Modified,

....more here

)

SET @.PK = SCOPE_IDENTITY()

SELECT @.PK PK

END

With a quick scan, your code appears correct.

How is it that you are finding that the values of @.SchoolCode_Modified and @.BranchCode_Modified do NOT have the leading zeros?

|||

It appears that your problem is that

@.SchoolCode is declared as char(6).

So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.

It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.

Dan

|||

DanR1 wrote:

It appears that your problem is that
@.SchoolCode is declared as char(6).
So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.
It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.
Dan

Good catch...

Actually, I don't think that it would be ' 123', but instead 'should' be '123 '. In either case though, adding preceding characters and then taking the rightmost 6 characters would not effect any change in the value.

|||

Arnie,

Yes, you are right about the trailing blanks, instead of leading blanks as I suggested. (I checked on it after I made my post, and figured it wasn't worth the trouble to make the edit.)

So it seems like "ltrim(rtrim(@.SchoolCode))" is what should be preceded by "000000" before taking the 6 rightmost characters.

Dan

|||

DECLARE @.SchoolCode_Modified char(6)

SET @.SchoolCode_Modified = '123'

SET @.SchoolCode_Modified = REPLICATE('0',6 -LEN(@.SchoolCode_Modified))+ @.SchoolCode_Modified

SELECT @.SchoolCode_Modified AS Col1

Adding Integers

The code below has this line
SET @.SOGallons = @.ODTGallons

I need it to add the Current value of @.SOGallons to the newly selected value of @.ODTGallons and set that as the new value of @.SOGallons.

I've tried
SET @.SOGallons = @.SOGallons + @.ODTGallons

SET @.SOGalTemp = @.SOGallons
SET @.SOGallons= @.SOGalTemp + @.ODTGallons

Neither Worked

<CODE>
FROM [CSITSS].[dbo].[Orderdt] as ODT LEFT OUTER JOIN [CSITSS].[dbo].[Orddtcom] as OCOM
ON ODT.[Companydiv] = OCOM.[Companydiv] AND ODT.[OrderNumber] = OCOM.[OrderNumber] AND
ODT.[Sequence] = OCOM.[Sequence] WHERE ODT.[Companydiv]= 'GLPC-TRANS' AND ODT.[OrderNumber] = @.OrdNum AND
([LineType] = 'IP' OR [LineType] = 'SO' OR [LineType] = 'DL' OR [LineType] = 'PU')

OPEN TC1

FETCH NEXT FROM TC1 INTO @.LT, @.ODTGallons, @.ODTComm
WHILE @.@.FETCH_STATUS=0
BEGIN
IF @.LT = 'SO'
BEGIN
SET @.SplitTest = 1
SET @.SOGallons = @.ODTGallons
IF @.SOGallons > 0
BEGIN
SET @.SOGalTest = 1
END
ELSE
BEGIN
SET @.SOGalTest = 0
END
IF @.SplitTest <> @.SOGalTest
BEGIN
SET @.SOGalTest = 0
END
END
ELSE
BEGIN
SET @.SOGalTest = 1
END
FETCH NEXT FROM TC1 INTO @.LT, @.ODTGallons, @.ODTComm
END
CLOSE TC1
DEALLOCATE TC1</CODE>LineType Gallons Commodity
DL 4000 #2 ULSD DYED
IP 7000 87 NL / ETH
PU 4000 #2 ULSD DYED
SO 7000 87 NL / ETH

There may be multiple lines of any of the above line types.

IP = Initial Pickup
PU = Additional Pickup
SO = Stop Off
DL = Final Delivery

I need to know if all of the gallons that were picked up where delivered

IP + PU = SO + DL

I'm doing a check on the validity of the commodity type as well but with the forums helps we figured that one out yesterday.|||First, your issue is NULL issue.
You said
I've tried
SET @.SOGallons = @.SOGallons + @.ODTGallons

SET @.SOGalTemp = @.SOGallons
SET @.SOGallons= @.SOGalTemp + @.ODTGallons

But before you use @.SOGallons, you need to initialise it. Otherwise it stays as NULL. And whatever value added to it becomes NULL. This is why it failed. Say, insert this line "SET @.SOGallons = 0" before "SET @.SOGallons = @.SOGallons + @.ODTGallons".

Next, in your WHILE Loop, put "SET @.SplitTest = 1" before the loop started as it is a constant.|||Hey,

Thanks.

I know VB fairly well but am completely new to SQL as of about 3 weeks ago. I always seem to know what I want to do but am continually making small syntax errox that trip me up.

Friday, February 24, 2012

Adding empty space

I want to add some spaces on the starting feild value. Please see the below example

" " & Fields!Name.Value

This is working on my BIDS. But the spaces are removed automatically when I deployed the report to the report manager. What should be the problem.

Rather than inserting spaces, can you adjust the padding property on the textbox? If you goal is to have the field indented, that should work.|||

Try using StrDup(3, Chr(32)) & Fields!Name.Value or Space(3) & Fields!Name.Value

Shyam

|||

I used padding property to indent the field. its working fine.

Thanks jwelch.

|||

Hi Folks,

I'm attempting to add empty spaces to format my report. I have one subreport that's a part of my report. I used the "Space()" function and it worked nicely within VS2005, however when I moved my report to the Report Server all my heading and detail data had one space in between them. I also tried "strDUP(3, " ")" and it too worked fine in VS2005 but not on the Report Server. Below is a sample of my code in what I trying to accomplish.

="DIR #" & StrDup(5, " ") & "ST" & StrDup(4, " ") &

"DIR NAME" & StrDup(20, " ") & "PUBCO" &

StrDup(7, " ") & "CLOSE" & StrDup(10, " ") & "ISSUE"

|||Spaces don't work very well when you are rendering as HTML, as browsers tend to ignore repeated white space. Any reason you couldn't use a table instead?|||

Hi John,

I'm currently using a table and it consist of one large column because my subreport is a part of it.

Best regards

|||How about using seperate columns for displaying this info, and merging the cells for the subreport?|||

John - I followed your advice and added a table within my table and it works fine now.

Thanks for your help.

Sunday, February 19, 2012

Adding empty space

I want to add some spaces on the starting feild value. Please see the below example

" " & Fields!Name.Value

This is working on my BIDS. But the spaces are removed automatically when I deployed the report to the report manager. What should be the problem.

Rather than inserting spaces, can you adjust the padding property on the textbox? If you goal is to have the field indented, that should work.|||

Try using StrDup(3, Chr(32)) & Fields!Name.Value or Space(3) & Fields!Name.Value

Shyam

|||

I used padding property to indent the field. its working fine.

Thanks jwelch.

|||

Hi Folks,

="DIR #" & StrDup(5, " ") & "ST" & StrDup(4, " ") &

"DIR NAME" & StrDup(20, " ") & "PUBCO" &

StrDup(7, " ") & "CLOSE" & StrDup(10, " ") & "ISSUE"

|||Spaces don't work very well when you are rendering as HTML, as browsers tend to ignore repeated white space. Any reason you couldn't use a table instead?|||

Hi John,

I'm currently using a table and it consist of one large column because my subreport is a part of it.

Best regards

|||How about using seperate columns for displaying this info, and merging the cells for the subreport?|||

John - I followed your advice and added a table within my table and it works fine now.

Thanks for your help.

Adding default value to an already created table using query analyzer

Hello,

How can I give default value to a field in a table which is already created, i.e. there is a table test and it have field test1 which is int(4). Now, I want to give a default value 0 to this field. As I am not able to access Enterprise Manager, I want to do it using Query Analyzer. How can I do this using Query Analyzer?

Thanks in advance,
Uday.just run a query saying

 update yourtable set yourcol=0 where yourcol is null

hth|||Hi,

That is OK for the data that is entered but what about the new data that will get entered, i.e. I want to set default value as 0 for that particular field. So if someone enter new data and the value for that particular field is not entered, it should take that value as 0.

Best Regards,
Uday.|||ALTER TableName
ALTER COLUMN column_name
DEFAULT 0 WITH VALUES|||Hi,

I tried the above but it is giving me the following error:
Incorrect syntax near the keyword 'DEFAULT'

Best Regards,
Uday|||yea i thgt you already set the default value for the column as 0 and want to modify xisting rows with null values to default to 0. if you havent already you can do it now. so for any new records added if the value is not supplied it will default to 0.

hth|||ALTER TABLE MyTable
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT getdate() WITH VALUES
-------------
That's books online says. Usually works fine with adding columns, but I don't see why it's not letting me alter. I guess you have to drop the current constraint, and recreate one.

Thursday, February 16, 2012

Adding custom property to custom component

What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.

I thought this would involve adding a IDTSCustomProperty to the ComponentMetaData.CustomPropertyCollection and the right place to do this to mee seemed be in the ProvideComponentProperties() method:

IDTSCustomProperty90 property = ComponentMetaData.CustomPropertyCollection.New();
property.Name = "MyLittleProperty";

However, after compiling and redeploying the custom component and adding it to a package, the property does not show up in the editor.

Would anyone have suggestions on how what I want to do should be done right?

Thnx in advance,
Henk

Well, luckily it works after all. Closing down VS and restarting it did the trick. Probably kept reference to an old version of my custom component...|||You will need to restart VS everytime you recompile a component or task. You can set your project as the startup command for debugging the component, but it seems to take ages to load.

When testing the component execution side, set DTExec and a ready buildt package as the debug, as this is much faster.|||We've added a few tidbits that you might find interesting to the BOL topic on the subject of Custom Properties since the last CTP.

Creating Custom Properties

The call to the ProvideComponentProperties method is where component developers should add custom properties (IDTSCustomProperty90) to the component.

You can indicate that your custom property supports property expressions by setting the value of its ExpressionType property to CPET_NOTIFY from the DTSCustomPropertyExpressionType enumeration, as shown in the following example:

C# Copy Code IDTSCustomProperty90 myCustomProperty; ... myCustomProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

Visual Basic Copy Code Dim myCustomProperty As IDTSCustomProperty90 ... myCustomProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY

You can limit users to selecting a custom property value from an enumeration by using the TypeConverter property, as shown in the following example, which assumes that you have defined a public enumeration named MyValidValues:

C# Copy Code IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the type with the custom property. customProperty.TypeConverter = typeof(MyValidValues).AssemblyQualifiedName; // Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne;

Visual Basic Copy Code Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the type with the custom property. customProperty.TypeConverter = GetType(MyValidValues).AssemblyQualifiedName ' Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne

For more information, see "Generalized Type Conversion" and "Implementing a Type Converter" in the MSDN Library.

You can specify a custom editor dialog box for the value of your custom property by using the UITypeEditor property, as shown in the following example. First, you must create a custom type editor that inherits from System.Drawing.Design.UITypeEditor, if you are unable to locate an existing UI type editor class that fits your needs.

C# Copy Code public class MyCustomTypeEditor : UITypeEditor { ... }

Visual Basic Copy Code Public Class MyCustomTypeEditor Inherits UITypeEditor ... End Class

Then specify this class as the value of the UITypeEditor property of your custom property.

C# Copy Code IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the editor with the custom property. customProperty.UITypeEditor = typeof(MyCustomTypeEditor).AssemblyQualifiedName;

Visual Basic Copy Code Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the editor with the custom property. customProperty.UITypeEditor = GetType(MyCustomTypeEditor).AssemblyQualifiedName

For more information, see "Implementing a UI Type Editor" in the MSDN Library.

|||Great, thanks!|||

I have a question though...I followed those directions and I can get the enumerated list to show up in my transformation when added through SSIS.

My question now is, how do I access the property within the "ProcessInput" function?

|||

I would read the property value in PreExecute and store in a class level variable, but the code is the same regardless of location-

IDTSCustomProperty90 property = componentMetaData.CustomPropertyCollection[propertyName];
object o = property.Value;

|||Ahh, makes sense. Thank you.

Adding custom property to custom component

What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.

I thought this would involve adding a IDTSCustomProperty to the ComponentMetaData.CustomPropertyCollection and the right place to do this to mee seemed be in the ProvideComponentProperties() method:

IDTSCustomProperty90 property = ComponentMetaData.CustomPropertyCollection.New();
property.Name = "MyLittleProperty";

However, after compiling and redeploying the custom component and adding it to a package, the property does not show up in the editor.

Would anyone have suggestions on how what I want to do should be done right?

Thnx in advance,
Henk

Creating Custom Properties

The call to the ProvideComponentProperties method is where component developers should add custom properties (IDTSCustomProperty90) to the component.

C# Copy Code IDTSCustomProperty90 myCustomProperty; ... myCustomProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;