get 5 latest invoices (based on InvoiceDate) for each customer.

I have two tables:
- CustomerID(CustID,CustName)
- Invoice(CustID,InvoiceID,InvoiceDate)
How can i get a table that has 5 latest invoices (based on InvoiceDate) for each customer.
Example: The result has format like this:
CustID CustName InvoiceID InvoiceDate
0001 Ng V A 126121 20061028
0001 Ng V A 126128 20061026
0001 Ng V A 126130 20061022
0001 Ng V A 126132 20061019
0001 Ng V A 126140 20061018
0003 Ng V B 126050 20061024
0003 Ng V B 126046 20061016
0003 Ng V B 126038 20061012
0003 Ng V B 126012 20061010
Please help me to use query to solve this problem. Many thanks for your kind support.
Best regardscan't you just do the following

SELECT C.CustId, C.CustName, I.InvoiceId, I.InvoiceDate
FROM CustomerID C LEFT JOIN Invoice I on C.CustId = I.CustId
WHERE InvoivceDate in (Select Top 5 InvoiceDate from Invoice Order By InvoiceDate DESC)|||

Quote:

Originally Posted by Taftheman

can't you just do the following

SELECT C.CustId, C.CustName, I.InvoiceId, I.InvoiceDate
FROM CustomerID C LEFT JOIN Invoice I on C.CustId = I.CustId
WHERE InvoivceDate in (Select Top 5 InvoiceDate from Invoice Order By InvoiceDate DESC)

Thanks for your responding. But I think you misunderstand my idea. I want to retreive a table which contains a customer list with their corresponding latest invoice.

I'm appreciate for your helping.|||

Quote:

Originally Posted by thanhphong122

Thanks for your responding. But I think you misunderstand my idea. I want to retreive a table which contains a customer list with their corresponding latest invoice.

I'm appreciate for your helping.

Im not sure but try just joining the two tables.

SELECT C.CustId, C.CustName, I.InvoiceId, I.InvoiceDate
FROM CustomerID C LEFT JOIN Invoice I on C.CustId = I.CustId
WHERE InvoivceDate in (Select Top 5 I.InvoiceDate from Invoice I Left Join CustomerID C I.CustId = C.CustID Order By InvoiceDate DESC)

If that doesn't work then put custid in both where clauses|||You can use this, it gets all the top 5 dates from the customer id of QUICK, change to suit your needs... You can even run it on query analyzer as it uses the northwind database

Select C.CustomerId, C.ContactName, O.OrderDate
from Customers C left join Orders O on C.CustomerId = O.CustomerId
Where C.CustomerId = 'QUICK' and O.OrderDate in (Select top 5 OrderDate from Orders Where CustomerId = 'QUICK' order by OrderDate DESC)

Let us know if it works|||The query below runs very good.

SELECT c.Customer, c.Name1, Invoice,i.ILDATE
FROM Customer C join Invoice I on (c.Customer = i.MKH)
WHERE (i.Invoice IN (SELECT TOP 5 Invoice FROM Invoice j WHERE (c.Customer = j.MKH) ORDER BY j.ILDate DESC))
ORDER BY c.Customer, i.ILDate DESC

Thanks for your help.|||

Quote:

Originally Posted by thanhphong122

The query below runs very good.

SELECT c.Customer, c.Name1, Invoice,i.ILDATE
FROM Customer C join Invoice I on (c.Customer = i.MKH)
WHERE (i.Invoice IN (SELECT TOP 5 Invoice FROM Invoice j WHERE (c.Customer = j.MKH) ORDER BY j.ILDate DESC))
ORDER BY c.Customer, i.ILDate DESC

Thanks for your help.

would this not be simpler?

SELECT DISTINCT TOP 5 c.Customer, c.Name1, i.ILDATE
FROM Customer c
INNER JOIN Invoice i
ON c.Customer = i.MKH
ORDER BY i.ILDATE|||

Quote:

Originally Posted by willakawill

would this not be simpler?

SELECT DISTINCT TOP 5 c.Customer, c.Name1, i.ILDATE
FROM Customer c
INNER JOIN Invoice i
ON c.Customer = i.MKH
ORDER BY i.ILDATE

Oops!

ORDER BY i.ILDATE DESC

get @@identity from several records

Hi,

I was wondering if you can help.

I am running an sql query that creates several new records based on a select query. In other words duplicating some existing records in a table.

What I need to do is after I have created these new records update a field in each of them.

Any ideas how I can retrieve all of their ids at insertion and then use them to update a field in each of them?

Ideally I would like to use @.@.identity and datareaders with vb.net.

Cheers

Mark :)

You need to put all the TSQL into a stored procedure. After each insert use SCOPE_IDENTITY() to get the id of the record just inserted.

|||Decorate your table with a new column, let's call it "batchid". It could be a uniqueidentifier.

DECLARE @.batchid uniqueidentifier

SELECT @.batchid=newid()

--The insert

INSERT INTO table (column1,column2 ..... , batchid)
SELECT column1,column2......, @.batchid FROM table WHERE...

--Retrieve the new id:s

SELECT id FROM table WHERE batchid=@.batchid

But it's quite likely that you can insert the correct data directly, instead of having to update it afterwards.

INSERT INTO table (column1,column2 ..... , column15)
SELECT column1,column2......, @.thenewvalueforcolumn15 FROM table WHERE...|||

Although I like the solution of gunteman, I'm just wondering why you can't update the field in the INSERT statement?

|||

Hi,

Thanks for your help. But i would really like to update the field in the insert statement.

Any suggestions?

Cheers

Mark :)

|||

I described it above. If you want to set the new value directly, just include it in your insert statement.

INSERT INTO table (column1,column2 ..... , column15)
SELECT column1,column2......, @.thenewvalueforcolumn15 FROM table WHERE...

If you show us your INSERT and UPDATE statement, we could help you combine them.

Generating XMLdata based on the schema

How to generate XMLdata based on the schema in SQL SERVER 2005?

Are there any features in SQLServer2005 to achieve this using Xml datatype ,SQLXML 4.0?

Please suggest me in this regard.

Thanks

vaishu

SQL Server has the FOR XML xml construction modes which you can use to generate XML from your relational schema. Examples of this support are here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

In addition SQLXML 4.0 has mappings from XSD schema to relational schema, in case you need to map between these two types for schemas. An introduction to these can be found here:

http://msdn2.microsoft.com/en-us/library/ms171870(SQL.90).aspx

|||

Thanks for the reply

you can close treat this request as closed.

vaishu

Generating XMLdata based on the schema

How to generate XMLdata based on the schema in SQL SERVER 2005?

Are there any features in SQLServer2005 to achieve this using Xml datatype ,SQLXML 4.0?

Please suggest me in this regard.

Thanks

vaishu

SQL Server has the FOR XML xml construction modes which you can use to generate XML from your relational schema. Examples of this support are here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

In addition SQLXML 4.0 has mappings from XSD schema to relational schema, in case you need to map between these two types for schemas. An introduction to these can be found here:

http://msdn2.microsoft.com/en-us/library/ms171870(SQL.90).aspx

|||

Thanks for the reply

you can close treat this request as closed.

vaishu

Generating XML data based on the Schema

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Can a stored procedure in SQL Server 2005 generate XML data based on the schema?

Yes...

Create Proc dbo.GenXMLWithSchemaValidation
As
Begin
Set Nocount On

DECLARE @.x XML (HumanResources.HRResumeSchemaCollection)
SET @.x = (
SELECT ContactID,
FirstName,
LastName,
AdditionalContactInfo.query('
declare namespace aci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number') as MorePhoneNumbers
FROM Person.Contact
FOR XML AUTO, TYPE)
SELECT @.x
Set Nocount Off
End

Hope this helps,

Derek

|||

Thanks Derek

i appreciate your help.

Vaishu

Generating XML data based on the Schema

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Can a stored procedure in SQL Server 2005 generate XML data based on the schema?

Yes...

Create Proc dbo.GenXMLWithSchemaValidation
As
Begin
Set Nocount On

DECLARE @.x XML (HumanResources.HRResumeSchemaCollection)
SET @.x = (
SELECT ContactID,
FirstName,
LastName,
AdditionalContactInfo.query('
declare namespace aci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number') as MorePhoneNumbers
FROM Person.Contact
FOR XML AUTO, TYPE)
SELECT @.x
Set Nocount Off
End

Hope this helps,

Derek

|||

Thanks Derek

i appreciate your help.

Vaishu

Generating XML based on XMLSchema in a SQLSERVER2005 Stored procedure

Can a stored procedure in SQLSERVER 2005 generate XMLdata based on the schema?

Are there any features in SQLServer2005 to achieve this ?

Please suggest me in this regard.

Thanks

vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

|||

thanks for the reply.

vaishu

|||

Can sqlserver 2005 generete XML schema for the whole database ?

Can anyone suggest us on this.

thanks

oak-net

Generating XML based on XMLSchema in a SQLSERVER2005 Stored procedure

Can a stored procedure in SQLSERVER 2005 generate XMLdata based on the schema?

Are there any features in SQLServer2005 to achieve this ?

Please suggest me in this regard.

Thanks

vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

|||

thanks for the reply.

vaishu

|||

Can sqlserver 2005 generete XML schema for the whole database ?

Can anyone suggest us on this.

thanks

oak-net

sql

Generating XML based on Schema

Hi,

i need Info regarding this

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

Can i find any article on how to do this.

Thanks,

Vaishu

How about using "FOR XML AUTO" in your select!

select * from tbl1 FOR XML AUTO

Generating Surrogate Keys

I've been playing with a couple of ways of doing this, based on the ideas in
the article "The Cost of GUID's as Primary Keys" :
http://www.informit.com/articles/ar...2&seqNum=1&rl=1 and a
couple of other online sources
and I've put together the following:
create view builtins as
select getdate() the_date, newid() the_id
go
create function comb_id() returns uniqueidentifier
as
begin
declare @.out uniqueidentifier
select @.out = CAST(CAST(builtins.the_id AS BINARY(10))
+ CAST(builtins.the_date AS BINARY(6)) AS UNIQUEIDENTIFIER)
from builtins
return @.out
end
GO
create table x(a int, b uniqueidentifier DEFAULT ([dbo].[comb_id]()))
GO
Now, inserts to x result in a new "comb guid" being generated, which I can
trap in an insert trigger from the inserted table. I can also, if I wish,
generate ready-made IDs in the application, by reimplementing the same
algorithm.
Are there any "gotchas" of which I should be aware when using this approach?
Enviroment is SQL 2000, no immediate plans to upgrade.
Any observations welcome,
GCWhy not just use an auto-incrementing identity column?
"GC" <reply_to_group@.please> wrote in message
news:O8RKZr9XFHA.1044@.TK2MSFTNGP10.phx.gbl...
> I've been playing with a couple of ways of doing this, based on the ideas
in
> the article "The Cost of GUID's as Primary Keys" :
> http://www.informit.com/articles/ar...2&seqNum=1&rl=1 and a
> couple of other online sources
> and I've put together the following:
> create view builtins as
> select getdate() the_date, newid() the_id
> go
> create function comb_id() returns uniqueidentifier
> as
> begin
> declare @.out uniqueidentifier
> select @.out = CAST(CAST(builtins.the_id AS BINARY(10))
> + CAST(builtins.the_date AS BINARY(6)) AS UNIQUEIDENTIFIER)
> from builtins
> return @.out
> end
> GO
> create table x(a int, b uniqueidentifier DEFAULT ([dbo].[comb_id]()))
> GO
> Now, inserts to x result in a new "comb guid" being generated, which I can
> trap in an insert trigger from the inserted table. I can also, if I wish,
> generate ready-made IDs in the application, by reimplementing the same
> algorithm.
> Are there any "gotchas" of which I should be aware when using this
approach?
> Enviroment is SQL 2000, no immediate plans to upgrade.
> Any observations welcome,
> GC
>|||Why not create use a generic function to which you pass the current DateTime
and
a new Id. Like so:
Create Function dbo.CombID(@.SeedId UniqueIdentifier, @.CurDate DateTime)
As
Begin
Return Cast(Cast(@.SeedId As Binary(10))
+ Cast(@.CurDate As Binary(6)) As UniqueIdentifier)
End
Thus, you would do something like so in a table:
Create Table TableName
(
AColumn UniqueIdentifier Default (dbo.CombId(NewId(), Current_Timestamp))
)
Beyond the general gotchas of using a Guid, the only gotcha to this approach
would be that you are sacrificing a little "uniqueness" for better indexing
performance. That means that the odds are higher of a collision. Realistical
ly,
the odds are incredibly low even using the full eight bytes of the current d
ate
time.
Thomas
"GC" <reply_to_group@.please> wrote in message
news:O8RKZr9XFHA.1044@.TK2MSFTNGP10.phx.gbl...
> I've been playing with a couple of ways of doing this, based on the ideas
in
> the article "The Cost of GUID's as Primary Keys" :
> http://www.informit.com/articles/ar...2&seqNum=1&rl=1 and a
> couple of other online sources
> and I've put together the following:
> create view builtins as
> select getdate() the_date, newid() the_id
> go
> create function comb_id() returns uniqueidentifier
> as
> begin
> declare @.out uniqueidentifier
> select @.out = CAST(CAST(builtins.the_id AS BINARY(10))
> + CAST(builtins.the_date AS BINARY(6)) AS UNIQUEIDENTIFIER)
> from builtins
> return @.out
> end
> GO
> create table x(a int, b uniqueidentifier DEFAULT ([dbo].[comb_id]()))
> GO
> Now, inserts to x result in a new "comb guid" being generated, which I can
> trap in an insert trigger from the inserted table. I can also, if I wish,
> generate ready-made IDs in the application, by reimplementing the same
> algorithm.
> Are there any "gotchas" of which I should be aware when using this approac
h?
> Enviroment is SQL 2000, no immediate plans to upgrade.
> Any observations welcome,
> GC
>|||A couple of reasons, really. I figured a GUID-type is more "meaningless",
and less likely to be the subject of hard-coded assumptions in the future,
and I also quite liked the idea of being able to generate the GUID in the
application if I wanted to.
"JT" <someone@.microsoft.com> wrote in message
news:%23Ubds49XFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Why not just use an auto-incrementing identity column?
> "GC" <reply_to_group@.please> wrote in message
> news:O8RKZr9XFHA.1044@.TK2MSFTNGP10.phx.gbl...
> in
> approach?
>|||Thomas Coleman wrote:
> Why not create use a generic function to which you pass the current
> DateTime and a new Id. Like so:
Just because, for no particularly good reason, I felt the function should be
niladic.

> Beyond the general gotchas of using a Guid, the only gotcha to this
> approach would be that you are sacrificing a little "uniqueness" for
> better indexing performance. That means that the odds are higher of a
> collision. Realistically, the odds are incredibly low even using the
> full eight bytes of the current date time.
I guess I could test for this in @.@.ERROR and try again if there's been a
collision.
Thanks for your responses,
GC|||>> Why not create use a generic function to which you pass the current
> Just because, for no particularly good reason, I felt the function should
be
> niladic.
Understand. But wouldn't every call need to populate your little temp table
with
a guid and a datetime? Otherwise, you'll get duplicate results.

> I guess I could test for this in @.@.ERROR and try again if there's been a
> collision.
If the column is the primary key, of course this will not be necessary. If t
he
column in question is not the primary key, then you can simply create a uniq
ue
index on that column to ensure uniqueness.
Thomas|||Thomas Coleman wrote:
> Understand. But wouldn't every call need to populate your little temp
> table with a guid and a datetime? Otherwise, you'll get duplicate
> results.
It was a view, so newid() and getdate() would be called each time it was
selected from.

>
> If the column is the primary key, of course this will not be
> necessary. If the column in question is not the primary key, then you
> can simply create a unique index on that column to ensure uniqueness.
>
I was thinking about what to do in the (unlikely) event of the algorithm
giving rise to a collision.
If @.@.ERROR is 2627 (PK violation), then try again, on the basis that you'd
have to be seriously
unlucky to get two collisions in a row.

>
> Thomas|||An Integer is only 4 bytes long. GUIDs are 36 bytes long, and even a self
generated GUID would be at least twice as long as an integer. Not very
efficient for storage or performance reasons. Why is it important to
generate the IDs by the application, is this some sort of offline
application that periodically merges data to the database?
"GC" <reply_to_group@.please> wrote in message
news:ujmllH%23XFHA.2684@.TK2MSFTNGP09.phx.gbl...
> A couple of reasons, really. I figured a GUID-type is more "meaningless",
> and less likely to be the subject of hard-coded assumptions in the future,
> and I also quite liked the idea of being able to generate the GUID in the
> application if I wanted to.
> "JT" <someone@.microsoft.com> wrote in message
> news:%23Ubds49XFHA.3712@.TK2MSFTNGP09.phx.gbl...
ideas
a
wish,
>|||First of all this is not a surrogate key accordignt o Dr. Codd's
definition. It is an artifacial key. And it happens to be long,
unverifiable and a XXXXX to validate.
Here is an implementation of the additive congruential method of
generating values in pseudo-random order and is due to Roy Hann of
Rational Commerce Limited, a CA-Ingres consulting firm. It is based on
a shift-register and an XOR-gate, and it has its origins in
cryptography. While there are other ways to do this, this code is nice
because:
1) The algorithm can be written in C or another low level language for
speed. But math is fairly simple even in base ten.
2) The algorithm tends to generate successive values that are (usually)
"far apart", which is handy for improving the performance of tree
indexes. You will tend to put data on separate physical data pages in
storage.
3) The algorithm does not cycle until it has generated every possible
value, so we don't have to worry about duplicates. Just count how many
calls have been made to the generator.
4) The algorithm produces uniformly distributed values, which is a nice
mathematical property to have. It also does not include zero.
Generalizing the algorithm to arbitrary binary word sizes, and
therefore longer number sequences, is not as easy as you might think.
Finding the "tap" positions where bits are extracted for feedback
varies according to the word-size in an extremely non-obvious way.
Choosing incorrect tap positions results in an incomplete and usually
very short cycle, which is unusable. If you want the details and tap
positions for words of one to 100 bits, see E. J. Watson, "Primitive
Polynomials (Mod 2)", Mathematics of Computation, v.16, 1962,
p.368-369.
We need to tap bits 0 and 3 to construct the 31-bit random-order
generated value Generator (which is the one most people would want to
use in practice):
UPDATE Generator31
SET keyval =
keyval/2 + MOD(MOD(keyval, 2) + MOD(keyval/8, 2), 2)*2^30;
Or if you prefer, the algorithm in C:
int Generator31 ()
{static int n = 1;
n = n >> 1 | ((n^n >> 3) & 1) << 30;
return n;
}
Do not forget to add a check digit.|||Hello, JT
A GUID is stored on 16 bytes, not 36 bytes.
Sometimes it's better to generate the IDs by the application to avoid
the round trips (necessary to execute "SELECT SCOPE_IDENTITY()").
Sometimes a "less than perfect" environment (like Microsoft Access
ADP-s) is used to develop the application, that has "SELECT @.@.IDENTITY"
hardcoded (instead of "SELECT SCOPE_IDENTITY()"). Sometimes GUID-s are
needed for replication. Sometimes...
Regarding the performance reasons, did you read the article quoted by
GC in the first post? The author suggests that there is only a 10%
performance impact due to the 4 times increase of size (the bigger
impact in that test scenario is due to the randomness of GUIDs, and
this impact can be minimized with the converting of current date trick,
according to the article).
Razvan

Generating File names on the fly

Hi,

I want to create a package that can process a flat file based on the current data. i.e. name of the file contains current date and some predefined characters.

What is the best way to process it?Use a property expression on the ConnectionString property of your FlatFile connection manager to set it to the correct filename (containing the date).

-Jamie

Generating complicate WHERE clause via parameters

Hi,
I have a large number of parameters defined which can be null and I have to
generate WHERE clause based on valid parameters, this makes things complicated
becuse I have to add "AND" to the WHERE clause depending on availability of
the parameters, the number of validation I have to do increases gradually
because the 10th parametrs should check for availability of 10 previous
parameters
before it can add "AND" infront of it. Is there any slick ways to handle
this problem?
Thank you.COALESCE will do.
"JC" wrote:
> Hi,
> I have a large number of parameters defined which can be null and I have to
> generate WHERE clause based on valid parameters, this makes things complicated
> becuse I have to add "AND" to the WHERE clause depending on availability of
> the parameters, the number of validation I have to do increases gradually
> because the 10th parametrs should check for availability of 10 previous
> parameters
> before it can add "AND" infront of it. Is there any slick ways to handle
> this problem?
> Thank you.|||Thank you for you reply, I still do not see how CAOESECE can solve my problem.
My query looks like this.
SELECT column1, column2 FROM table1
WHERE
column3 = val1
AND
column4 = val2
with out val1 parameter supplied it should be:
SELECT column1, column2 FROM table1
WHERE
column4 = val2
So printing "AND" is the problem and only way to do it for me right now is
to check
if val1 was supplied or not.
can COALESCE help in this situation?
Thank you.
"Bing Bing Yu" wrote:
> COALESCE will do.
>
> "JC" wrote:
> > Hi,
> >
> > I have a large number of parameters defined which can be null and I have to
> > generate WHERE clause based on valid parameters, this makes things complicated
> > becuse I have to add "AND" to the WHERE clause depending on availability of
> > the parameters, the number of validation I have to do increases gradually
> > because the 10th parametrs should check for availability of 10 previous
> > parameters
> > before it can add "AND" infront of it. Is there any slick ways to handle
> > this problem?
> >
> > Thank you.|||WHERE column3 = COALESCE(val1, column3)
AND column4 = COALESCE(val2, column4)
"JC" wrote:
> Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> My query looks like this.
> SELECT column1, column2 FROM table1
> WHERE
> column3 = val1
> AND
> column4 = val2
> with out val1 parameter supplied it should be:
> SELECT column1, column2 FROM table1
> WHERE
> column4 = val2
> So printing "AND" is the problem and only way to do it for me right now is
> to check
> if val1 was supplied or not.
> can COALESCE help in this situation?
> Thank you.
>
> "Bing Bing Yu" wrote:
> >
> > COALESCE will do.
> >
> >
> > "JC" wrote:
> >
> > > Hi,
> > >
> > > I have a large number of parameters defined which can be null and I have to
> > > generate WHERE clause based on valid parameters, this makes things complicated
> > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > the parameters, the number of validation I have to do increases gradually
> > > because the 10th parametrs should check for availability of 10 previous
> > > parameters
> > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > this problem?
> > >
> > > Thank you.|||I eneded up doing it like this (using AdventureWorks) can this be enhanced?
(I have credited you by the way, thank you)
="DECLARE @.ContactID INT, " &
"@.FirstName NVARCHAR(50) " &
Iif(Len(Parameters!ContactID.Value) > 0, "SELECT @.ContactID = " &
Parameters!ContactID.Value, " SELECT @.ContactID = null ") &
Iif(Len(Parameters!FirstName.Value) > 0, "SELECT @.FirstName = '" &
Parameters!FirstName.Value & "' " , "SELECT @.FirstName = null ") &
" SELECT Title, FirstName, MiddleName, LastName from Person.Contact " &
"WHERE ContactID = COALESCE(@.ContactID, ContactID) AND FirstName =COALESCE(@.FirstName, FirstName)"
"Bing Bing Yu"ë'ì?´ ì'ì?±í' ë?´ì?©:
>
> WHERE column3 = COALESCE(val1, column3)
> AND column4 = COALESCE(val2, column4)
>
> "JC" wrote:
> >
> > Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> >
> > My query looks like this.
> >
> > SELECT column1, column2 FROM table1
> > WHERE
> > column3 = val1
> > AND
> > column4 = val2
> >
> > with out val1 parameter supplied it should be:
> >
> > SELECT column1, column2 FROM table1
> > WHERE
> > column4 = val2
> > So printing "AND" is the problem and only way to do it for me right now is
> > to check
> > if val1 was supplied or not.
> >
> > can COALESCE help in this situation?
> >
> > Thank you.
> >
> >
> > "Bing Bing Yu" wrote:
> >
> > >
> > > COALESCE will do.
> > >
> > >
> > > "JC" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a large number of parameters defined which can be null and I have to
> > > > generate WHERE clause based on valid parameters, this makes things complicated
> > > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > > the parameters, the number of validation I have to do increases gradually
> > > > because the 10th parametrs should check for availability of 10 previous
> > > > parameters
> > > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > > this problem?
> > > >
> > > > Thank you.|||Hi,
I think you need to build the query something like this.
declare @.str as nvarchar(1000)
set @.str = ''
set @.str = @.str + "select * from "...
if @.val is null
set @.str = @.str + "val = .."
exec sp_executesql @.str
you can use if conditions where ever required.
Amarnath
"JC" wrote:
> I eneded up doing it like this (using AdventureWorks) can this be enhanced?
> (I have credited you by the way, thank you)
> ="DECLARE @.ContactID INT, " &
> "@.FirstName NVARCHAR(50) " &
> Iif(Len(Parameters!ContactID.Value) > 0, "SELECT @.ContactID = " &
> Parameters!ContactID.Value, " SELECT @.ContactID = null ") &
> Iif(Len(Parameters!FirstName.Value) > 0, "SELECT @.FirstName = '" &
> Parameters!FirstName.Value & "' " , "SELECT @.FirstName = null ") &
> " SELECT Title, FirstName, MiddleName, LastName from Person.Contact " &
> "WHERE ContactID = COALESCE(@.ContactID, ContactID) AND FirstName => COALESCE(@.FirstName, FirstName)"
>
> "Bing Bing Yu"ë'ì?´ ì'ì?±í' ë?´ì?©:
> >
> >
> > WHERE column3 = COALESCE(val1, column3)
> > AND column4 = COALESCE(val2, column4)
> >
> >
> > "JC" wrote:
> >
> > >
> > > Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> > >
> > > My query looks like this.
> > >
> > > SELECT column1, column2 FROM table1
> > > WHERE
> > > column3 = val1
> > > AND
> > > column4 = val2
> > >
> > > with out val1 parameter supplied it should be:
> > >
> > > SELECT column1, column2 FROM table1
> > > WHERE
> > > column4 = val2
> > > So printing "AND" is the problem and only way to do it for me right now is
> > > to check
> > > if val1 was supplied or not.
> > >
> > > can COALESCE help in this situation?
> > >
> > > Thank you.
> > >
> > >
> > > "Bing Bing Yu" wrote:
> > >
> > > >
> > > > COALESCE will do.
> > > >
> > > >
> > > > "JC" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have a large number of parameters defined which can be null and I have to
> > > > > generate WHERE clause based on valid parameters, this makes things complicated
> > > > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > > > the parameters, the number of validation I have to do increases gradually
> > > > > because the 10th parametrs should check for availability of 10 previous
> > > > > parameters
> > > > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > > > this problem?
> > > > >
> > > > > Thank you.|||Well, I'm pretty sure the dynamic query works just fine. One concern would
be since it's dynamic, SQL server won't be able to optimize the query.
"Amarnath" wrote:
> Hi,
> I think you need to build the query something like this.
> declare @.str as nvarchar(1000)
> set @.str = ''
> set @.str = @.str + "select * from "...
> if @.val is null
> set @.str = @.str + "val = .."
> exec sp_executesql @.str
> you can use if conditions where ever required.
> Amarnath
>
> "JC" wrote:
> >
> > I eneded up doing it like this (using AdventureWorks) can this be enhanced?
> > (I have credited you by the way, thank you)
> >
> > ="DECLARE @.ContactID INT, " &
> > "@.FirstName NVARCHAR(50) " &
> > Iif(Len(Parameters!ContactID.Value) > 0, "SELECT @.ContactID = " &
> > Parameters!ContactID.Value, " SELECT @.ContactID = null ") &
> > Iif(Len(Parameters!FirstName.Value) > 0, "SELECT @.FirstName = '" &
> > Parameters!FirstName.Value & "' " , "SELECT @.FirstName = null ") &
> > " SELECT Title, FirstName, MiddleName, LastName from Person.Contact " &
> > "WHERE ContactID = COALESCE(@.ContactID, ContactID) AND FirstName => > COALESCE(@.FirstName, FirstName)"
> >
> >
> >
> > "Bing Bing Yu"ë'ì?´ ì'ì?±í' ë?´ì?©:
> >
> > >
> > >
> > > WHERE column3 = COALESCE(val1, column3)
> > > AND column4 = COALESCE(val2, column4)
> > >
> > >
> > > "JC" wrote:
> > >
> > > >
> > > > Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> > > >
> > > > My query looks like this.
> > > >
> > > > SELECT column1, column2 FROM table1
> > > > WHERE
> > > > column3 = val1
> > > > AND
> > > > column4 = val2
> > > >
> > > > with out val1 parameter supplied it should be:
> > > >
> > > > SELECT column1, column2 FROM table1
> > > > WHERE
> > > > column4 = val2
> > > > So printing "AND" is the problem and only way to do it for me right now is
> > > > to check
> > > > if val1 was supplied or not.
> > > >
> > > > can COALESCE help in this situation?
> > > >
> > > > Thank you.
> > > >
> > > >
> > > > "Bing Bing Yu" wrote:
> > > >
> > > > >
> > > > > COALESCE will do.
> > > > >
> > > > >
> > > > > "JC" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have a large number of parameters defined which can be null and I have to
> > > > > > generate WHERE clause based on valid parameters, this makes things complicated
> > > > > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > > > > the parameters, the number of validation I have to do increases gradually
> > > > > > because the 10th parametrs should check for availability of 10 previous
> > > > > > parameters
> > > > > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > > > > this problem?
> > > > > >
> > > > > > Thank you.|||One easy way would be to use 1=1 and
'Select col1 from table 1 where 1=1 and' + @.str_where
@. str_where can be populated from the parameters you accept in
"JC" wrote:
> Hi,
> I have a large number of parameters defined which can be null and I have to
> generate WHERE clause based on valid parameters, this makes things complicated
> becuse I have to add "AND" to the WHERE clause depending on availability of
> the parameters, the number of validation I have to do increases gradually
> because the 10th parametrs should check for availability of 10 previous
> parameters
> before it can add "AND" infront of it. Is there any slick ways to handle
> this problem?
> Thank you.|||Although you can do dynamic SQL I find this technique easier:
select somefields from sometable where (somefield = @.Param1 or @.Param1 ='All') and (anotherfield = @.Param2 or @.Param1 = 'All')
Note that if a number field you can check for a number that doesn't exist in
your database whether it is -1 or -99999 or whatever as long as you know it
doesn't exist in real life.
I prefer to have an All selection, however, you can use Null or Empty string
as well.
select somefields from sometable where (somefield = @.Param1 or
isnull(@.Param1)) and (anotherfield = @.Param2 or isnull(@.Param1))
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sunil" <Sunil@.discussions.microsoft.com> wrote in message
news:2666D2EB-03EA-47CF-A958-52EC722567FC@.microsoft.com...
> One easy way would be to use 1=1 and
> 'Select col1 from table 1 where 1=1 and' + @.str_where
> @. str_where can be populated from the parameters you accept in
>
> "JC" wrote:
>> Hi,
>> I have a large number of parameters defined which can be null and I have
>> to
>> generate WHERE clause based on valid parameters, this makes things
>> complicated
>> becuse I have to add "AND" to the WHERE clause depending on availability
>> of
>> the parameters, the number of validation I have to do increases gradually
>> because the 10th parametrs should check for availability of 10 previous
>> parameters
>> before it can add "AND" infront of it. Is there any slick ways to handle
>> this problem?
>> Thank you.

Generating and directing new rows in PostExecute.

I want to construct a dataset based on all rows passed in from a source and, upon running through all of them, push each dataset record to an output.

Unfortunately, upon calling the AddRow method, I get an "object reference not set to an instance of an object" error. Am I not allowed to create new rows in PostExecute?

Bill,

Can you post your code, indicate which line the error occurs on, and tell us how you have configured the outputs of the component.

-Jamie

|||It errors on reference to a row buffer.

With dsMaterialBuffer
.AddRow()
'Code for row values
End With

I've added the following to check if it's actually defined within PostExecute;
If dsMaterialBuffer Is Nothing Then MsgBox("dsMaterialBuffer is nothing.") 'My row buffer

dsMaterialBuffer, which is the row buffer I'm using, evaluates to "Nothing" and that is where my object reference errors are coming from.

Is it possible that the row buffers are being thrown out once PostExecute hits?

The outputs, by the way, are non-synchronous with standard columns.|||

What is the name of the output as defined in the inputs and outputs tab of the script component editor?

-Jamie

|||dsMaterial|||

Hmm, strange. if you want, drop me an email via here: http://blogs.conchango.com/jamiethomson/contact.aspx and I'll reply so you can send me the package. I can take a look and see if anything jumps out.

If you can build the package so that I am able to run it as well (i.e. not reliant on external data sources, just use a script source component instead) then that'd help.

-Jamie

Generating and directing new rows in PostExecute.

I want to construct a dataset based on all rows passed in from a source and, upon running through all of them, push each dataset record to an output.

Unfortunately, upon calling the AddRow method, I get an "object reference not set to an instance of an object" error. Am I not allowed to create new rows in PostExecute?

Bill,

Can you post your code, indicate which line the error occurs on, and tell us how you have configured the outputs of the component.

-Jamie

|||It errors on reference to a row buffer.

With dsMaterialBuffer
.AddRow()
'Code for row values
End With

I've added the following to check if it's actually defined within PostExecute;
If dsMaterialBuffer Is Nothing Then MsgBox("dsMaterialBuffer is nothing.") 'My row buffer

dsMaterialBuffer, which is the row buffer I'm using, evaluates to "Nothing" and that is where my object reference errors are coming from.

Is it possible that the row buffers are being thrown out once PostExecute hits?

The outputs, by the way, are non-synchronous with standard columns.|||

What is the name of the output as defined in the inputs and outputs tab of the script component editor?

-Jamie

|||dsMaterial|||

Hmm, strange. if you want, drop me an email via here: http://blogs.conchango.com/jamiethomson/contact.aspx and I'll reply so you can send me the package. I can take a look and see if anything jumps out.

If you can build the package so that I am able to run it as well (i.e. not reliant on external data sources, just use a script source component instead) then that'd help.

-Jamie

Generate Sequence Number based on other columns

Hi,
Please help me to find a solution.

My requirement is similar to this.
Example:

TABLEA(pk_id, pol_mas_id,pol_seq_id)

values are
pk_id pol_mas_id pol_seq_id
1 1
2 1
3 2
4 2
5 3
6 3
7 3

Now i need to update the column "pol_seq_id" as below.

pk_id pol_mas_id pol_seq_id
1 1 1
2 1 2
3 2 1
4 2 2
5 3 1
6 3 2
7 3 3

Currently i am using cursor and you know performance sucks.

Is there any way to increment the data in the column(pol_seq_id) based on other column (pol_mas_id).

Thanks in Advance!!

Try:

select

pk_id, pol_mas_id, row_number() over(partition by pol_mas_id order by pk_id) as pol_seq_id

from

dbo.t1

go

It is helpful to have an index by (pol_mas_id, pk_id)

AMB

|||Thanks a lot for the response !!!
It works for select as when i am trying to update the same i am getting this error
"Windowed functions can only appear in the SELECT or ORDER BY clauses."
so i have populated the same into #temp table and then updated with join statement.

It works
|||

Try using a CTE.

;with cte

as

(

select

pk_id, pol_mas_id, pol_seq_id, row_number() over(partition by pol_mas_id order by pk_id) as rn

from

dbo.t1

go

)

update cte

set pol_seq_id = rn;

AMB

Generate chart dynamically

Can sombody tell me how to generate cahrt dynamically in reporting service?
I need to generate a number of charts based on the number specified.
Thanks
--
WeiNot sure what you exactly mean with dynamically "generate a number of
charts" - but did you look into the Visibility.Hidden property of the chart?
You can define several charts at design time and hide them using an
expression at runtime (dependent on e.g. a parameter value).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"David" <weiliu00@.hotmail.com> wrote in message
news:D2828717-5299-43FF-B838-F29269B9D713@.microsoft.com...
> Can sombody tell me how to generate cahrt dynamically in reporting
service?
> I need to generate a number of charts based on the number specified.
> Thanks
> --
> Wei|||I 've got a chart (2 actually) inside a list. It creates the charts for
each element in the list. Works like a charm. (Except that you cannot
control the Y-scale, but that will be solved in the next release, I hear).
Hth,
Tom
"David" wrote:
> Can sombody tell me how to generate cahrt dynamically in reporting service?
> I need to generate a number of charts based on the number specified.
> Thanks
> --
> Wei|||Hi, Robert :
Thank you for your kind help. As my project, I do not know how many charts
will be generated. It depends on the number that a user specifies. What I am
planning to do is to use the following codes to generate chart dynamically:
Response.Write("<cc1:ReportViewer id='ReportViewer1' style='Z-INDEX: 101;
LEFT: 8px; POSITION: absolute; TOP: 8px' runat=server Width=680px
Height=632px ServerUrl='http://localhost/reportserver' ReportPath='/Sample
Report/Report1'" & _
"Toolbar=Default></cc1:ReportViewer>")
Of course I need to pass a parameter to the report so that the chart will be
different. However, it looks like the the above codes does not work.
Robert. Could you give me some suggestions?
Thanks a lot,
David
"Robert Bruckner [MSFT]" wrote:
> Not sure what you exactly mean with dynamically "generate a number of
> charts" - but did you look into the Visibility.Hidden property of the chart?
> You can define several charts at design time and hide them using an
> expression at runtime (dependent on e.g. a parameter value).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "David" <weiliu00@.hotmail.com> wrote in message
> news:D2828717-5299-43FF-B838-F29269B9D713@.microsoft.com...
> > Can sombody tell me how to generate cahrt dynamically in reporting
> service?
> >
> > I need to generate a number of charts based on the number specified.
> >
> > Thanks
> > --
> > Wei
>
>

Generarting XML based on Schema in SQLSERVER 2005

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

The section entitled "Integration with the Xml Datatype" would most likely address your scenario.

|||

Todd Pfleiger wrote:

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

The section entitled "Integration with the Xml Datatype" would most likely address your scenario.

thanks for your help

Generarting XML based on Schema in SQLSERVER 2005

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

The section entitled "Integration with the Xml Datatype" would most likely address your scenario.

|||

Todd Pfleiger wrote:

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

The section entitled "Integration with the Xml Datatype" would most likely address your scenario.

thanks for your help