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.
Showing posts with label write. Show all posts
Showing posts with label write. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
Additional Information
Also, some people do not have middle names, how would I write the query to
pull this out? Thanks again.
"Mike Collins" wrote:
> In the following query, I will be making 6 joins for each ID in the Proble
ms
> table to get a person's full name from our personnel table. Is there anoth
er
> way to do this than with the query I have below?
> Select TPRTitle, p1.FirstName + ' ' + p1.LastName As Originator,
> p2.FirstName + ' ' + p2.MiddleName + ' ' + p2.LastName As Screener
> From TPRs t
> Join common..personnel p1 On p1.PersonnelID = t.OriginatorID
> Join common..personnel p2 On p2.PersonnelID = t.ScreenerID
> ...
> CREATE TABLE [Personnel] (
> [PersonnelID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
> [DF_Personnel_PersonnelID] DEFAULT (newid()),
> [OrganizationID] [uniqueidentifier] NOT NULL ,
> [Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MiddleName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Service] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OfficeSymbol] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastModified] [datetime] NOT NULL ,
> CONSTRAINT [PK_Personnel] PRIMARY KEY CLUSTERED
> (
> [PersonnelID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [Problems] (
> [ProblemID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
> [DF_TPRs_TPRID] DEFAULT (newid()),
> [OriginatorID] [uniqueidentifier] NOT NULL ,
> [ClassifiedByID] [uniqueidentifier] NOT NULL ,
> [ScreenerID] [uniqueidentifier] NOT NULL ,
> [SubjectMatterExpertID] [uniqueidentifier] NOT NULL ,
> [TestDirectorID] [uniqueidentifier] NOT NULL ,
> [TestEventID] [uniqueidentifier] NOT NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Location] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Function] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OS] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ProblemSource] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SequenceOfEvents] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProblemDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WorkAround] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_tblTPRs] PRIMARY KEY CLUSTERED
> (
> [ProblemID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [FK_TPRs_TestEvents] FOREIGN KEY
> (
> [TestEventID]
> ) REFERENCES [TestEvents] (
> [TestEventID]
> )
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:5C6CE19B-3FCE-422E-B546-3A1FDC7DD28B@.microsoft.com...
> Also, some people do not have middle names, how would I write the query to
> pull this out? Thanks again.
>
> "Mike Collins" wrote:
>
From where I sit, you will need to do the joins. A couple of suggestions
however.
1. Use ISNULL for the MiddleName column. If it is empty, you will not get
a row back because by default NULL Concatenation returns NULL. So
something like: ISNULL(p1.FirstName, '') + ' ' + ISNULL(p1.MiddleName,
'')...
2. Since I don't see any FK constraints in the Problem table that will
guarantee that Originators, Screeners and so forth exist in the Personnel
table, you may want to use LEFT JOINS to ensure that you get rows back.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks...that helps a lot. I forgot about using the IsNull function. It
greatly simplifies the query. One question I have since you mentioned foreig
n
keys. The personnel table is located in another database. Is there a way to
create a foreign key that will span databases? Thanks for your help.
"Rick Sawtell" wrote:
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:5C6CE19B-3FCE-422E-B546-3A1FDC7DD28B@.microsoft.com...
>
> From where I sit, you will need to do the joins. A couple of suggestions
> however.
> 1. Use ISNULL for the MiddleName column. If it is empty, you will not ge
t
> a row back because by default NULL Concatenation returns NULL. So
> something like: ISNULL(p1.FirstName, '') + ' ' + ISNULL(p1.MiddleName,
> '')...
> 2. Since I don't see any FK constraints in the Problem table that will
> guarantee that Originators, Screeners and so forth exist in the Personnel
> table, you may want to use LEFT JOINS to ensure that you get rows back.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:CBAC9424-506B-40A3-85DD-8945A4AC61B5@.microsoft.com...
> Thanks...that helps a lot. I forgot about using the IsNull function. It
> greatly simplifies the query. One question I have since you mentioned
> foreign
> keys. The personnel table is located in another database. Is there a way
> to
> create a foreign key that will span databases? Thanks for your help.
>
You can't do it across DB's AFAIK. You can use sprocs and/or triggers for
this however, but it may slow your system down. Then again, you will
guarantee integrity that way. It's up to you.
Rick
pull this out? Thanks again.
"Mike Collins" wrote:
> In the following query, I will be making 6 joins for each ID in the Proble
ms
> table to get a person's full name from our personnel table. Is there anoth
er
> way to do this than with the query I have below?
> Select TPRTitle, p1.FirstName + ' ' + p1.LastName As Originator,
> p2.FirstName + ' ' + p2.MiddleName + ' ' + p2.LastName As Screener
> From TPRs t
> Join common..personnel p1 On p1.PersonnelID = t.OriginatorID
> Join common..personnel p2 On p2.PersonnelID = t.ScreenerID
> ...
> CREATE TABLE [Personnel] (
> [PersonnelID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
> [DF_Personnel_PersonnelID] DEFAULT (newid()),
> [OrganizationID] [uniqueidentifier] NOT NULL ,
> [Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MiddleName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Service] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OfficeSymbol] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastModified] [datetime] NOT NULL ,
> CONSTRAINT [PK_Personnel] PRIMARY KEY CLUSTERED
> (
> [PersonnelID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> CREATE TABLE [Problems] (
> [ProblemID] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
> [DF_TPRs_TPRID] DEFAULT (newid()),
> [OriginatorID] [uniqueidentifier] NOT NULL ,
> [ClassifiedByID] [uniqueidentifier] NOT NULL ,
> [ScreenerID] [uniqueidentifier] NOT NULL ,
> [SubjectMatterExpertID] [uniqueidentifier] NOT NULL ,
> [TestDirectorID] [uniqueidentifier] NOT NULL ,
> [TestEventID] [uniqueidentifier] NOT NULL ,
> [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Location] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Function] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OS] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ProblemSource] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SequenceOfEvents] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ProblemDescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WorkAround] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_tblTPRs] PRIMARY KEY CLUSTERED
> (
> [ProblemID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [FK_TPRs_TestEvents] FOREIGN KEY
> (
> [TestEventID]
> ) REFERENCES [TestEvents] (
> [TestEventID]
> )
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:5C6CE19B-3FCE-422E-B546-3A1FDC7DD28B@.microsoft.com...
> Also, some people do not have middle names, how would I write the query to
> pull this out? Thanks again.
>
> "Mike Collins" wrote:
>
From where I sit, you will need to do the joins. A couple of suggestions
however.
1. Use ISNULL for the MiddleName column. If it is empty, you will not get
a row back because by default NULL Concatenation returns NULL. So
something like: ISNULL(p1.FirstName, '') + ' ' + ISNULL(p1.MiddleName,
'')...
2. Since I don't see any FK constraints in the Problem table that will
guarantee that Originators, Screeners and so forth exist in the Personnel
table, you may want to use LEFT JOINS to ensure that you get rows back.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks...that helps a lot. I forgot about using the IsNull function. It
greatly simplifies the query. One question I have since you mentioned foreig
n
keys. The personnel table is located in another database. Is there a way to
create a foreign key that will span databases? Thanks for your help.
"Rick Sawtell" wrote:
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:5C6CE19B-3FCE-422E-B546-3A1FDC7DD28B@.microsoft.com...
>
> From where I sit, you will need to do the joins. A couple of suggestions
> however.
> 1. Use ISNULL for the MiddleName column. If it is empty, you will not ge
t
> a row back because by default NULL Concatenation returns NULL. So
> something like: ISNULL(p1.FirstName, '') + ' ' + ISNULL(p1.MiddleName,
> '')...
> 2. Since I don't see any FK constraints in the Problem table that will
> guarantee that Originators, Screeners and so forth exist in the Personnel
> table, you may want to use LEFT JOINS to ensure that you get rows back.
> HTH
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:CBAC9424-506B-40A3-85DD-8945A4AC61B5@.microsoft.com...
> Thanks...that helps a lot. I forgot about using the IsNull function. It
> greatly simplifies the query. One question I have since you mentioned
> foreign
> keys. The personnel table is located in another database. Is there a way
> to
> create a foreign key that will span databases? Thanks for your help.
>
You can't do it across DB's AFAIK. You can use sprocs and/or triggers for
this however, but it may slow your system down. Then again, you will
guarantee integrity that way. It's up to you.
Rick
additional data files not filing
We have a quad sql server that runs OLTP transactions at the rate of
100's per second (read & Write).
We used to have all the tables on 1 file but started to notice high contention on this file. We added 3 more files to match the processor number. The problem is that the 3 additional files are not filling with data. Does anyone know why this happens or can reccommend a fix?
--
willVerify that the newly added files are in the appropriate file group (probably PRIMARY) and are of the appropriate file type (ie, they were created as data files, not log files). Also, your new file(s) may not get data written to them if the old file still has space available.
Your logic for reducing contention by adding files to match the number of processors does not make sense. Contention is caused by physical IO going to the same physical disk and getting backed up because the pipe to the physical disk is too small. You would add files to a database filegroup to ease contention by creating the files on separate physical disks. It has no relation the number of CPUs.
Regards,
hmscott|||Yes they are all the same file type (data files).
Microsoft claims that you should have a seperate file per processor for high contention OLTP sql servers. Parallelism is the goal here. Sql server can only access 1 file per cpu at any one time. If it was a dual processor machine It would have less impact considering the OS & tempdb usually have something going on in parallel.
Considering we have a quad sql server = 4 files per file group.
As an FYI we are running a raid 10 with 14 disks.|||Hmm, not doubting you, but could you send a link with the MS info? I had not seen that before.
As for your issue, what's the state of the original data file? Full, nearly full or not even close? Also, can you verify that the new files you added were updateable (ie, not read-only)? Beyond that, I would try testing some scenarios out on a development server somewhere.
Regards,
hmscott
100's per second (read & Write).
We used to have all the tables on 1 file but started to notice high contention on this file. We added 3 more files to match the processor number. The problem is that the 3 additional files are not filling with data. Does anyone know why this happens or can reccommend a fix?
--
willVerify that the newly added files are in the appropriate file group (probably PRIMARY) and are of the appropriate file type (ie, they were created as data files, not log files). Also, your new file(s) may not get data written to them if the old file still has space available.
Your logic for reducing contention by adding files to match the number of processors does not make sense. Contention is caused by physical IO going to the same physical disk and getting backed up because the pipe to the physical disk is too small. You would add files to a database filegroup to ease contention by creating the files on separate physical disks. It has no relation the number of CPUs.
Regards,
hmscott|||Yes they are all the same file type (data files).
Microsoft claims that you should have a seperate file per processor for high contention OLTP sql servers. Parallelism is the goal here. Sql server can only access 1 file per cpu at any one time. If it was a dual processor machine It would have less impact considering the OS & tempdb usually have something going on in parallel.
Considering we have a quad sql server = 4 files per file group.
As an FYI we are running a raid 10 with 14 disks.|||Hmm, not doubting you, but could you send a link with the MS info? I had not seen that before.
As for your issue, what's the state of the original data file? Full, nearly full or not even close? Also, can you verify that the new files you added were updateable (ie, not read-only)? Beyond that, I would try testing some scenarios out on a development server somewhere.
Regards,
hmscott
Monday, March 19, 2012
Adding Serial No in the Query Resultset
Hi to All!
Can any one help me in generating serial no in the query result?
e.g. i write a query it gives me 500 rows.
select name,fathername from abc
name FatherName
Harry David
Sarah Nenry
.
.
.
.
i want that reslut should come like that
S-no name FatherName
1 Harry David
2 Sarah Nenry
.
.
.
.
500 Farid Masood
how can i add this sequence no in query i have no S-no column?
Regards
Thanx
*** Sent via Developersdex http://www.examnotes.net ***1. Create a temp table with an Identity column
2. Insert your values into the temp table
3. Select * From the temp table and return that resultset.
Greg Jackson
PDX, Oregon|||http://www.aspfaq.com/2427
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:uBPoaM2IGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Hi to All!
> Can any one help me in generating serial no in the query result?
> e.g. i write a query it gives me 500 rows.
> select name,fathername from abc
> name FatherName
> Harry David
> Sarah Nenry
> .
> .
> .
> .
> i want that reslut should come like that
> S-no name FatherName
> 1 Harry David
> 2 Sarah Nenry
> .
> .
> .
> .
> 500 Farid Masood
> how can i add this sequence no in query i have no S-no column?
> Regards
> Thanx
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Line numbering is an issue for the front end and has nothing to do with
the RDBMS. You can use a stinking dirty kludge with a proprietary
IDENTITY if you do not care about proper coding.
Have you ever had a software engineering course or read a book on
Software Engineering?|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23YwLvk5IGHA.3176@.TK2MSFTNGP12.phx.gbl...
> http://www.aspfaq.com/2427
'Be sure to read KB #186133 for Microsoft's official word'
(How to dynamically number rows in a SELECT Transact-SQL statement)
Applies to 2005...?..well do the people that write this stuff read BOL?
Perhaps they want to keep some things a secret:)
www.rac4sql.net|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138413114.379715.161790@.g47g2000cwa.googlegroups.com...
> Line numbering is an issue for the front end and has nothing to do with
> the RDBMS. You can use a stinking dirty kludge with a proprietary
> IDENTITY if you do not care about proper coding.
> Have you ever had a software engineering course or read a book on
> Software Engineering?
Would you feel better substituting ranking for line numbering?
And I was having a hard time using 'stinking dirty kludge' in
a sentence.
Did you write for 'Laugh In'? :)|||>> Would you feel better substituting ranking for line numbering? <<
That is fine; ranking as a rule while putting a number on the output of
an un-ordred cursor is absurd and unrepeatable.
But do you have a bigger problem using it in a program :)? You
should.
Two gags only. And I do not remember what they were. They paid $50
for the two.|||I've posted something in the private groups so hopefully they will update to
reflect all the new bits that they brought in for SQL Server 2005.
Eg...
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"05ponyGT" <nospam@.nospam> wrote in message
news:ejYR1SHJGHA.1188@.TK2MSFTNGP14.phx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message
> news:%23YwLvk5IGHA.3176@.TK2MSFTNGP12.phx.gbl...
>
> 'Be sure to read KB #186133 for Microsoft's official word'
> (How to dynamically number rows in a SELECT Transact-SQL statement)
> Applies to 2005...?..well do the people that write this stuff read BOL?
> Perhaps they want to keep some things a secret:)
> www.rac4sql.net
>|||select row_number() over ( order by name ), name
from sys.objects
order by name
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:ui7ET6LJGHA.3408@.TK2MSFTNGP12.phx.gbl...
> I've posted something in the private groups so hopefully they will update
> to reflect all the new bits that they brought in for SQL Server 2005.
> Eg...
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "05ponyGT" <nospam@.nospam> wrote in message
> news:ejYR1SHJGHA.1188@.TK2MSFTNGP14.phx.gbl...
>
Can any one help me in generating serial no in the query result?
e.g. i write a query it gives me 500 rows.
select name,fathername from abc
name FatherName
Harry David
Sarah Nenry
.
.
.
.
i want that reslut should come like that
S-no name FatherName
1 Harry David
2 Sarah Nenry
.
.
.
.
500 Farid Masood
how can i add this sequence no in query i have no S-no column?
Regards
Thanx
*** Sent via Developersdex http://www.examnotes.net ***1. Create a temp table with an Identity column
2. Insert your values into the temp table
3. Select * From the temp table and return that resultset.
Greg Jackson
PDX, Oregon|||http://www.aspfaq.com/2427
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:uBPoaM2IGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Hi to All!
> Can any one help me in generating serial no in the query result?
> e.g. i write a query it gives me 500 rows.
> select name,fathername from abc
> name FatherName
> Harry David
> Sarah Nenry
> .
> .
> .
> .
> i want that reslut should come like that
> S-no name FatherName
> 1 Harry David
> 2 Sarah Nenry
> .
> .
> .
> .
> 500 Farid Masood
> how can i add this sequence no in query i have no S-no column?
> Regards
> Thanx
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Line numbering is an issue for the front end and has nothing to do with
the RDBMS. You can use a stinking dirty kludge with a proprietary
IDENTITY if you do not care about proper coding.
Have you ever had a software engineering course or read a book on
Software Engineering?|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23YwLvk5IGHA.3176@.TK2MSFTNGP12.phx.gbl...
> http://www.aspfaq.com/2427
'Be sure to read KB #186133 for Microsoft's official word'
(How to dynamically number rows in a SELECT Transact-SQL statement)
Applies to 2005...?..well do the people that write this stuff read BOL?
Perhaps they want to keep some things a secret:)
www.rac4sql.net|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138413114.379715.161790@.g47g2000cwa.googlegroups.com...
> Line numbering is an issue for the front end and has nothing to do with
> the RDBMS. You can use a stinking dirty kludge with a proprietary
> IDENTITY if you do not care about proper coding.
> Have you ever had a software engineering course or read a book on
> Software Engineering?
Would you feel better substituting ranking for line numbering?
And I was having a hard time using 'stinking dirty kludge' in
a sentence.
Did you write for 'Laugh In'? :)|||>> Would you feel better substituting ranking for line numbering? <<
That is fine; ranking as a rule while putting a number on the output of
an un-ordred cursor is absurd and unrepeatable.
But do you have a bigger problem using it in a program :)? You
should.
Two gags only. And I do not remember what they were. They paid $50
for the two.|||I've posted something in the private groups so hopefully they will update to
reflect all the new bits that they brought in for SQL Server 2005.
Eg...
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"05ponyGT" <nospam@.nospam> wrote in message
news:ejYR1SHJGHA.1188@.TK2MSFTNGP14.phx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message
> news:%23YwLvk5IGHA.3176@.TK2MSFTNGP12.phx.gbl...
>
> 'Be sure to read KB #186133 for Microsoft's official word'
> (How to dynamically number rows in a SELECT Transact-SQL statement)
> Applies to 2005...?..well do the people that write this stuff read BOL?
> Perhaps they want to keep some things a secret:)
> www.rac4sql.net
>|||select row_number() over ( order by name ), name
from sys.objects
order by name
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:ui7ET6LJGHA.3408@.TK2MSFTNGP12.phx.gbl...
> I've posted something in the private groups so hopefully they will update
> to reflect all the new bits that they brought in for SQL Server 2005.
> Eg...
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "05ponyGT" <nospam@.nospam> wrote in message
> news:ejYR1SHJGHA.1188@.TK2MSFTNGP14.phx.gbl...
>
Thursday, March 8, 2012
Adding optional criteria in a select proc
I would like to write 1 proc that can take additional criteria if its sent in. An example is:
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
(
select VendorPackageId from ValidVendorPackages
where Vendor = @.VENDOR
and Sitecode = @.SITECODE
and PackageType = @.PACKAGETYPE
)HB on HA.VendorPackageId = HB.VendorPackageId
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @.DESTINATION
and LengthOfStay = @.LENGTHOFSTAY
and Ages = @.AGE
and ComponentType = @.COMPONENTTYPE_1
and ValidItemType = @.VALIDITEMTYPE_1
and ItemValue = @.ITEMVALUE_1
)
Multiple @.COMPONENTTYPE, @.VALIDITEMTYPE,@.ITEMVALUE can be sent in.
Instead of making multiple procs or copying the proc multiple times with an if statement at the top checking the number of parameters that aren't =''. Is there a way to exectue:
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @.DESTINATION
and LengthOfStay = @.LENGTHOFSTAY
and Ages = @.AGE
and ComponentType = @.COMPONENTTYPE_1
and ValidItemType = @.VALIDITEMTYPE_1
and ItemValue = @.ITEMVALUE_1
)
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @.DESTINATION
and LengthOfStay = @.LENGTHOFSTAY
and Ages = @.AGE
and ComponentType = @.COMPONENTTYPE_2
and ValidItemType = @.VALIDITEMTYPE_2
and ItemValue = @.ITEMVALUE_2
)
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @.DESTINATION
and LengthOfStay = @.LENGTHOFSTAY
and Ages = @.AGE
and ComponentType = @.COMPONENTTYPE_3
and ValidItemType = @.VALIDITEMTYPE_3
and ItemValue = @.ITEMVALUE_3
)
Ignoring the 2nd 2 selects if @.COMPONENTTYPE_2, @.VALIDITEMTYPE_2,@.ITEMVALUE_2 and @.COMPONENTTYPE_3, @.VALIDITEMTYPE_3,@.ITEMVALUE_3 are = ''
Thanks for your help in advance.Yes, there are ways to do this, but I haven't seen any generic way. It requires knowledge of the conditions that apply (especially how your code needs to handle incomplete sets of criteria, such as when only the second value for @.COMPONENTTYPE is supplied without the second @.VALIDITEMTYPE or @.ITEMVALUE ).
I've never found a satisfactory generic way to handle this kind of problem.
-PatP|||Yes, there are ways to do this, but I haven't seen any generic way. It requires knowledge of the conditions that apply (especially how your code needs to handle incomplete sets of criteria, such as when only the second value for @.COMPONENTTYPE is supplied without the second @.VALIDITEMTYPE or @.ITEMVALUE ).
I've never found a satisfactory generic way to handle this kind of problem.
-PatP
You will never have a 2nd and not a first.
You'll either have 1, 2 or 3 of
@.COMPONENTTYPE
@.VALIDITEMTYPE
@.ITEMVALUE
if they send in all 3 @.ITEMVALUE_1 thourgh 3 will be populated and so will @.VALIDITEMTYPE & @.ITEMVALUE|||Is this close to what you want? I hope you are not expecting any conditional search such as this to run particularly fast...
select Distinct Criteria.PriceId, Criteria.VendorPackageId
from Criteria
Inner Join ValidVendorPackages
on Criteria.VendorPackageId = ValidVendorPackages.VendorPackageId
and ValidVendorPackages.Vendor = @.VENDOR
and ValidVendorPackages.Sitecode = @.SITECODE
and ValidVendorPackages.PackageType = @.PACKAGETYPE
Left Outer Join ValidItemCriteria VIC_1
on Criteria.CriteriaID = VIC_1.CriteriaID
and VIC_1.Destination = @.DESTINATION
and VIC_1.LengthOfStay = @.LENGTHOFSTAY
and VIC_1.Ages = @.AGE
and VIC_1.ComponentType = @.COMPONENTTYPE_1
and VIC_1.ValidItemType = @.VALIDITEMTYPE_1
and VIC_1.ItemValue = @.ITEMVALUE_1
Left Outer Join ValidItemCriteria VIC_2
on Criteria.CriteriaID = VIC_2.CriteriaID
and VIC_2.Destination = @.DESTINATION
and VIC_2.LengthOfStay = @.LENGTHOFSTAY
and VIC_2.Ages = @.AGE
and VIC_2.ComponentType = @.COMPONENTTYPE_2
and VIC_2.ValidItemType = @.VALIDITEMTYPE_2
and VIC_2.ItemValue = @.ITEMVALUE_2
Left Outer Join ValidItemCriteria VIC_3
on Criteria.CriteriaID = VIC_1.CriteriaID
and VIC_3.Destination = @.DESTINATION
and VIC_3.LengthOfStay = @.LENGTHOFSTAY
and VIC_3.Ages = @.AGE
and VIC_3.ComponentType = @.COMPONENTTYPE_3
and VIC_3.ValidItemType = @.VALIDITEMTYPE_3
and VIC_3.ItemValue = @.ITEMVALUE_3
where VIC_1.CriteriaID is not null
or VIC_2.CriteriaID is not null
or VIC_3.CriteriaID is not null|||Are the matches against collections of criteria (for example, a given row needs to match any one of the vendors, any one of the site codes, and any one of the item values in order to qualify), or are the matches against sets of criteria (a row needs to match on vendor N, site code N, and criteria N in order to qualify)? That makes a considerable difference in how the code needs to work.
Do NULL values matter (do you ever need to search for a NULL criteria)? That's a really nasty twist from a performance perspective.
-PatP|||SELECT
...
WHERE
...
and CriteriaID in
(
select CriteriaID from ValidItemCriteria
where Destination = @.DESTINATION
and LengthOfStay = @.LENGTHOFSTAY
and Ages = @.AGE
and
(
coalesce(@.COMPONENTTYPE_1, @.VALIDITEMTYPE_1, @.ITEMVALUE_1) is null
OR
(ComponentType = @.COMPONENTTYPE_1
and ValidItemType = @.VALIDITEMTYPE_1
and ItemValue = @.ITEMVALUE_1)
)
and
(
coalesce(@.COMPONENTTYPE_2, @.VALIDITEMTYPE_2, @.ITEMVALUE_2) is null
OR
(ComponentType = @.COMPONENTTYPE_2
and ValidItemType = @.VALIDITEMTYPE_2
and ItemValue = @.ITEMVALUE_2)
)
and
(
coalesce(@.COMPONENTTYPE_3, @.VALIDITEMTYPE_3, @.ITEMVALUE_3) is null
OR
(ComponentType = @.COMPONENTTYPE_3
and ValidItemType = @.VALIDITEMTYPE_3
and ItemValue = @.ITEMVALUE_3)
)
)|||I'm just curious, but would you please explain what you think that SQL will do?
-PatP|||I'm just curious, but would you please explain what you think that SQL will do?
-PatP
Its useless, I noticed the flaw myself. I realized that I was negating the first criteria if I found criteria on the second. I have changed the question to a new post with a query I belive will complish this:Optional Inner Joins.
Please help if you can.
select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
(
select VendorPackageId from ValidVendorPackages
where Vendor = @.VENDOR
and Sitecode = @.SITECODE
and PackageType = @.PACKAGETYPE
)HB on HA.VendorPackageId = HB.VendorPackageId
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @.DESTINATION
and LengthOfStay = @.LENGTHOFSTAY
and Ages = @.AGE
and ComponentType = @.COMPONENTTYPE_1
and ValidItemType = @.VALIDITEMTYPE_1
and ItemValue = @.ITEMVALUE_1
)
Multiple @.COMPONENTTYPE, @.VALIDITEMTYPE,@.ITEMVALUE can be sent in.
Instead of making multiple procs or copying the proc multiple times with an if statement at the top checking the number of parameters that aren't =''. Is there a way to exectue:
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @.DESTINATION
and LengthOfStay = @.LENGTHOFSTAY
and Ages = @.AGE
and ComponentType = @.COMPONENTTYPE_1
and ValidItemType = @.VALIDITEMTYPE_1
and ItemValue = @.ITEMVALUE_1
)
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @.DESTINATION
and LengthOfStay = @.LENGTHOFSTAY
and Ages = @.AGE
and ComponentType = @.COMPONENTTYPE_2
and ValidItemType = @.VALIDITEMTYPE_2
and ItemValue = @.ITEMVALUE_2
)
and CriteriaId in
(
select CriteriaID from ValidItemCriteria
where Destination = @.DESTINATION
and LengthOfStay = @.LENGTHOFSTAY
and Ages = @.AGE
and ComponentType = @.COMPONENTTYPE_3
and ValidItemType = @.VALIDITEMTYPE_3
and ItemValue = @.ITEMVALUE_3
)
Ignoring the 2nd 2 selects if @.COMPONENTTYPE_2, @.VALIDITEMTYPE_2,@.ITEMVALUE_2 and @.COMPONENTTYPE_3, @.VALIDITEMTYPE_3,@.ITEMVALUE_3 are = ''
Thanks for your help in advance.Yes, there are ways to do this, but I haven't seen any generic way. It requires knowledge of the conditions that apply (especially how your code needs to handle incomplete sets of criteria, such as when only the second value for @.COMPONENTTYPE is supplied without the second @.VALIDITEMTYPE or @.ITEMVALUE ).
I've never found a satisfactory generic way to handle this kind of problem.
-PatP|||Yes, there are ways to do this, but I haven't seen any generic way. It requires knowledge of the conditions that apply (especially how your code needs to handle incomplete sets of criteria, such as when only the second value for @.COMPONENTTYPE is supplied without the second @.VALIDITEMTYPE or @.ITEMVALUE ).
I've never found a satisfactory generic way to handle this kind of problem.
-PatP
You will never have a 2nd and not a first.
You'll either have 1, 2 or 3 of
@.COMPONENTTYPE
@.VALIDITEMTYPE
@.ITEMVALUE
if they send in all 3 @.ITEMVALUE_1 thourgh 3 will be populated and so will @.VALIDITEMTYPE & @.ITEMVALUE|||Is this close to what you want? I hope you are not expecting any conditional search such as this to run particularly fast...
select Distinct Criteria.PriceId, Criteria.VendorPackageId
from Criteria
Inner Join ValidVendorPackages
on Criteria.VendorPackageId = ValidVendorPackages.VendorPackageId
and ValidVendorPackages.Vendor = @.VENDOR
and ValidVendorPackages.Sitecode = @.SITECODE
and ValidVendorPackages.PackageType = @.PACKAGETYPE
Left Outer Join ValidItemCriteria VIC_1
on Criteria.CriteriaID = VIC_1.CriteriaID
and VIC_1.Destination = @.DESTINATION
and VIC_1.LengthOfStay = @.LENGTHOFSTAY
and VIC_1.Ages = @.AGE
and VIC_1.ComponentType = @.COMPONENTTYPE_1
and VIC_1.ValidItemType = @.VALIDITEMTYPE_1
and VIC_1.ItemValue = @.ITEMVALUE_1
Left Outer Join ValidItemCriteria VIC_2
on Criteria.CriteriaID = VIC_2.CriteriaID
and VIC_2.Destination = @.DESTINATION
and VIC_2.LengthOfStay = @.LENGTHOFSTAY
and VIC_2.Ages = @.AGE
and VIC_2.ComponentType = @.COMPONENTTYPE_2
and VIC_2.ValidItemType = @.VALIDITEMTYPE_2
and VIC_2.ItemValue = @.ITEMVALUE_2
Left Outer Join ValidItemCriteria VIC_3
on Criteria.CriteriaID = VIC_1.CriteriaID
and VIC_3.Destination = @.DESTINATION
and VIC_3.LengthOfStay = @.LENGTHOFSTAY
and VIC_3.Ages = @.AGE
and VIC_3.ComponentType = @.COMPONENTTYPE_3
and VIC_3.ValidItemType = @.VALIDITEMTYPE_3
and VIC_3.ItemValue = @.ITEMVALUE_3
where VIC_1.CriteriaID is not null
or VIC_2.CriteriaID is not null
or VIC_3.CriteriaID is not null|||Are the matches against collections of criteria (for example, a given row needs to match any one of the vendors, any one of the site codes, and any one of the item values in order to qualify), or are the matches against sets of criteria (a row needs to match on vendor N, site code N, and criteria N in order to qualify)? That makes a considerable difference in how the code needs to work.
Do NULL values matter (do you ever need to search for a NULL criteria)? That's a really nasty twist from a performance perspective.
-PatP|||SELECT
...
WHERE
...
and CriteriaID in
(
select CriteriaID from ValidItemCriteria
where Destination = @.DESTINATION
and LengthOfStay = @.LENGTHOFSTAY
and Ages = @.AGE
and
(
coalesce(@.COMPONENTTYPE_1, @.VALIDITEMTYPE_1, @.ITEMVALUE_1) is null
OR
(ComponentType = @.COMPONENTTYPE_1
and ValidItemType = @.VALIDITEMTYPE_1
and ItemValue = @.ITEMVALUE_1)
)
and
(
coalesce(@.COMPONENTTYPE_2, @.VALIDITEMTYPE_2, @.ITEMVALUE_2) is null
OR
(ComponentType = @.COMPONENTTYPE_2
and ValidItemType = @.VALIDITEMTYPE_2
and ItemValue = @.ITEMVALUE_2)
)
and
(
coalesce(@.COMPONENTTYPE_3, @.VALIDITEMTYPE_3, @.ITEMVALUE_3) is null
OR
(ComponentType = @.COMPONENTTYPE_3
and ValidItemType = @.VALIDITEMTYPE_3
and ItemValue = @.ITEMVALUE_3)
)
)|||I'm just curious, but would you please explain what you think that SQL will do?
-PatP|||I'm just curious, but would you please explain what you think that SQL will do?
-PatP
Its useless, I noticed the flaw myself. I realized that I was negating the first criteria if I found criteria on the second. I have changed the question to a new post with a query I belive will complish this:Optional Inner Joins.
Please help if you can.
Friday, February 24, 2012
Adding expression in Data Flow Task
Hi
I am trying to develop a data flow task in C#. I need to add an expression tab in the task where i can write expressions on the input columns and map it to outputs. Please let me know how to go about it. I am new to SSIS coding so dnt have much idea abt it.
Thanks in advance,
Vipul
Try using the Derived Column tool.|||Hi TGnat
I am looking for some apis which will help me to write a code. I am not sure abt Derive Column Tool as there are some of the things which are not supported in it and i am looking to enhance the functionality for my use.
Thanks
Vipul
|||Can you give an example of what you are trying to do.
When I first started out I thought I would need a script task or a custom control for everything... Now I find that the stock components can meet most of my needs!
|||I am trying to build a task where i want to embed the logic of Derived column and Lookup in the same task. Also i want to hard code a particular table in the lookup. Its will be used to resolve the surrogate key in the facts for the dimmensions.
Hope this makes sense. I didnt knew abt stock components. Thanks for the pointer. Please let me know if you have any more updates on this.
Thanks
Vipul
|||Vipul123 wrote:
I am trying to build a task where i want to embed the logic of Derived column and Lookup in the same task. Also i want to hard code a particular table in the lookup. Its will be used to resolve the surrogate key in the facts for the dimmensions.
Hope this makes sense. I didnt knew abt stock components. Thanks for the pointer. Please let me know if you have any more updates on this.
Thanks
Vipul
"Stock components" simply means the stuff that is already in the toolbox.
If you want to build something that does the same as a LOOKUP and a DERIVED COLUMN, why not just use the LOOKUP and the DERIVED COLUMN?
-Jamie
|||
Also... Your lookup can return multiple columns, one or more for the look up match, and then you can select other columns to be added to your data flow. Follow this up with a derived column that implements your logic and you may have the functionality you need.
|||Jamie:
Thanks for the reply. I have few more queries. In the OLEDB Command, there is a tab called "Column Mapping". In here we can map the input columns to the output columns.
I am writing this code in the function :
public override void ProvideComponentProperties()
{
ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
RemoveAllInputsOutputsAndCustomProperties();
ComponentMetaData.Name = "Test";
ComponentMetaData.Description = "Test";
ComponentMetaData.ContactInfo = "Test";
IDTSInput90 input = ComponentMetaData.InputCollection.New();
input.Name = "Input";
input.Description = "Input Desc";
input.ExternalMetadataColumnCollection.IsUsed = true;
IDTSRuntimeConnection90 rtc = ComponentMetaData.RuntimeConnectionCollection.New();
rtc.Name = "Source";
rtc.Description = "Source Desc";
IDTSOutput90 output = ComponentMetaData.OutputCollection.New();
output.Name = "Output";
output.Description = "Output desc";
output.ExternalMetadataColumnCollection.IsUsed = true;
}
I am not able to connect(link) the input to the output ports. I am able to add new output columns in "Input and Output Properties" tab. The output ports which i am adding in this tab are not visible in "Column Mapping" tab. Please let me know if i am missing something in the component properties which will enable the linking of the input to output ports.
Thanks,
Vipul
|||TGnat,
Yes i understand that part. But my aim is to make all this as a part of Task so that i can drag and drop it as a task and reuse it instead of making the same thing for all the packages.
Thanks
Vipul
|||TGnat,
Yes i understand that part. But my aim is to make all this as a part of Task so that i can drag and drop it as a task and reuse it instead of making the same thing for all the packages.
Thanks
Vipul
So you want to be able to reuse functionality? Currently the only way to do this is to write your own component (very difficult) or good old copy-and-paste (very easy). I know which I would choose.
Incidentally, there is functionality probably coming in Vnext that would allow you to do exactly what you want to do (i.e. build reusable collections of components) without writing code.
-Jamie
Subscribe to:
Posts (Atom)