SQLAgentMail: rows

Showing posts with label rows. Show all posts

Wednesday, March 28, 2012

Question regarding use of a join to the same table

I have a table, BOOK, which uses BU and Asset and BOOK as a combined unique
identifier. A BU/Asset combination can have up to 4 rows, with different
BOOK values (CORP, FED, AMT - all of which are required, and LOCAL which is
situational).
Due to bad data entry, some of the Assets were not given an AMT row. I have
to identify those BU/Asset combinations.
I know I need to do a join back to the BOOK table, but I can't seem to get
the right statement. Here is my SQL:
select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
from PS_BOOK A
right outer join PS_BOOK B
on A.BUSINESS_UNIT = B.BUSINESS_UNIT and
A.ASSET_ID = B.ASSET_ID
where B.BOOK <> 'AMT'
order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
This gives me a double lisitng of rows (of course) and I can visually scan
for those missing 'AMT', but that's way too clunky. Where am I shooting
myself in the foot? What is the correct statement to use to get just a list
of the BU/Asset combinations that are missing 'AMT'?
TIA,
jej1216I tried a simpler stetment:
select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
from PS_BOOK A
where EXISTS (select 'x'
from PS_BOOK B
where B.BOOK <> 'AMT')
order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
But I still get both the BU/Asset combination that has 'AMT' as well as the
combination that does not have 'AMT'.
- jej1216
"Joe" wrote:
> I have a table, BOOK, which uses BU and Asset and BOOK as a combined unique
> identifier. A BU/Asset combination can have up to 4 rows, with different
> BOOK values (CORP, FED, AMT - all of which are required, and LOCAL which is
> situational).
> Due to bad data entry, some of the Assets were not given an AMT row. I have
> to identify those BU/Asset combinations.
> I know I need to do a join back to the BOOK table, but I can't seem to get
> the right statement. Here is my SQL:
> select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
> from PS_BOOK A
> right outer join PS_BOOK B
> on A.BUSINESS_UNIT = B.BUSINESS_UNIT and
> A.ASSET_ID = B.ASSET_ID
> where B.BOOK <> 'AMT'
> order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
> This gives me a double lisitng of rows (of course) and I can visually scan
> for those missing 'AMT', but that's way too clunky. Where am I shooting
> myself in the foot? What is the correct statement to use to get just a list
> of the BU/Asset combinations that are missing 'AMT'?
> TIA,
> jej1216
>|||On Tue, 16 Oct 2007 08:42:02 -0700, Joe wrote:
>I tried a simpler stetment:
>select A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
>from PS_BOOK A
>where EXISTS (select 'x'
> from PS_BOOK B
> where B.BOOK <> 'AMT')
>order by A.BUSINESS_UNIT, A.ASSET_ID, A.BOOK
>But I still get both the BU/Asset combination that has 'AMT' as well as the
>combination that does not have 'AMT'.
Hi Joe,
I think you want this one:
SELECT a.BusinessUnit, a.AssetID, a.Book
FROM PS_Book AS a
WHERE NOT EXISTS
(SELECT *
FROM PS_Book AS b
WHERE b.BusienssUnit = a.BusinessUnit
AND b.AssetID = a.AssetID
AND b.Book <> 'AMT');
If that is not it, then see www.aspfaq.com/5006 to find the information
you need to post in order to make it possible for us to help you.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Question regarding SP

Hi guys
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
ThanksPatrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com|||Hello Patrick,
Inside of T-SQL if you want to get the results back from a stored procedure
you need to insert them into a table as part of running the query.
The syntax for this is INSERT <<table>> (<<cols>> ) EXECUTE <<procedure>>
So for example
INSERT myTable ([Row1], [Row1]) EXECUTE myProcedure
The table you insert these records into can be a temp table, table var, or
even a normal table. Once you have your results in a table you can then crea
te
a cursor using that table.
Aaron Weiker
http://aaronweiker.com/

> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows.
> I want to define cursor on the result which is sent by stored
> procedure.
> How can i do that ?
> Thanks
>|||Seeing as you are asking this in the DTS group, amongst others, you may wan
t to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl.
.
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows
. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>

Question regarding SP

Hi guys
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
Thanks
Patrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com
|||Seeing as you are asking this in the DTS group, amongst others, you may want to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>
|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com
|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>
|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>
|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>

Question regarding SP

Hi guys
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
ThanksPatrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com|||Seeing as you are asking this in the DTS group, amongst others, you may wan
t to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl..
.
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows
. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>

Question regarding SP

Hi guys
I have written one stored procedure. At the end it returns the set of rows.
I want to define cursor on the result which is sent by stored procedure.
How can i do that ?
ThanksPatrick wrote:
> Hi guys
> I have written one stored procedure. At the end it returns the set of
> rows. I want to define cursor on the result which is sent by stored
> procedure. How can i do that ?
> Thanks
How do you mean? You want to use a cursor from the application? Or are
you saying you want to use the results from one stored procedure in
another stored procedure? Or are you saying you want to define a cursor
on the results from within the procedure so you can perform row-by-row
processing? Or are you saying something else altogether?
Please provide some details about what you are doing. Cursors on SQL
Server are very slow and there may be other set-based solutions you can
use.
David Gugick
Imceda Software
www.imceda.com|||Seeing as you are asking this in the DTS group, amongst others, you may want to look at
How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com
"Patrick" <kingofusa@.juno.com> wrote in message news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of rows. I want to define cursor on the result which is sent by
> stored procedure.
> How can i do that ?
> Thanks
>|||I Want to use cursor with in the stored procedure itself.
My main problem is how can I catch the result returned by one procedure in
another procedure.
Marmik
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
> Patrick wrote:
>> Hi guys
>> I have written one stored procedure. At the end it returns the set of
>> rows. I want to define cursor on the result which is sent by stored
>> procedure. How can i do that ?
>> Thanks
> How do you mean? You want to use a cursor from the application? Or are you
> saying you want to use the results from one stored procedure in another
> stored procedure? Or are you saying you want to define a cursor on the
> results from within the procedure so you can perform row-by-row
> processing? Or are you saying something else altogether?
> Please provide some details about what you are doing. Cursors on SQL
> Server are very slow and there may be other set-based solutions you can
> use.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Patrick wrote:
> I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one
> procedure in another procedure.
> Marmik
>
Those are two different things. Do you want to use the cursor from
within the procedure? if so, see DECLARE CURSOR in the help file.
Do you want to catch the results of one procedure in another? You'll
have to use a temp table (or a real table) to store the results.
David Gugick
Imceda Software
www.imceda.com|||Here you go...
create table TestTable
( id int not null,
name varchar(50) not null)
go
Insert into TestTable values (1,'Test Name 1')
Insert into TestTable values (2,'Test Name 2')
Insert into TestTable values (3,'Test Name 3')
Insert into TestTable values (4,'Test Name 4')
go
Create Proc TestProc1
as
Select * from TestTable
go
Create Proc TestProc2
as
Declare @.tmpTable table
( id int not null,
name varchar(50) not null)
Insert into @.tmpTable Exec TestProc1
Select *,'Result From Proc2' from @.tmpTable
Run this example script ... I hope it anser your question.
-Sarav
"Patrick" <kingofusa@.juno.com> wrote in message
news:emOql5lAFHA.1396@.tk2msftngp13.phx.gbl...
> Hi guys
> I have written one stored procedure. At the end it returns the set of
rows.
> I want to define cursor on the result which is sent by stored procedure.
> How can i do that ?
> Thanks
>|||Hello,
What kind of result are you trying to get in your stored procedure that is
calling another stored procedure?
Are you looking for a scalar datatype or a record set? And if you're looking
for a record set how much data are you going to return (for example a
single, a few rows, or a lot of rows)?
If you're looking for a small recordset, I've had great success using User
Defined Functions to return TABLE Variables into my stored procedures. For a
lot more data I use temp tables or regular tables.
Chris
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>> Patrick wrote:
>> Hi guys
>> I have written one stored procedure. At the end it returns the set of
>> rows. I want to define cursor on the result which is sent by stored
>> procedure. How can i do that ?
>> Thanks
>> How do you mean? You want to use a cursor from the application? Or are
>> you saying you want to use the results from one stored procedure in
>> another stored procedure? Or are you saying you want to define a cursor
>> on the results from within the procedure so you can perform row-by-row
>> processing? Or are you saying something else altogether?
>> Please provide some details about what you are doing. Cursors on SQL
>> Server are very slow and there may be other set-based solutions you can
>> use.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>|||You could store the result of a SP in a Temp Table and build a Cursor based
on that Temp Table.
Gopi
"Patrick" <kingofusa@.juno.com> wrote in message
news:u9gcCImAFHA.3492@.TK2MSFTNGP12.phx.gbl...
>I Want to use cursor with in the stored procedure itself.
> My main problem is how can I catch the result returned by one procedure in
> another procedure.
> Marmik
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OmXyc$lAFHA.3664@.TK2MSFTNGP14.phx.gbl...
>> Patrick wrote:
>> Hi guys
>> I have written one stored procedure. At the end it returns the set of
>> rows. I want to define cursor on the result which is sent by stored
>> procedure. How can i do that ?
>> Thanks
>> How do you mean? You want to use a cursor from the application? Or are
>> you saying you want to use the results from one stored procedure in
>> another stored procedure? Or are you saying you want to define a cursor
>> on the results from within the procedure so you can perform row-by-row
>> processing? Or are you saying something else altogether?
>> Please provide some details about what you are doing. Cursors on SQL
>> Server are very slow and there may be other set-based solutions you can
>> use.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>

Monday, March 26, 2012

Question on using ROW_NUMBER to remove duplicate rows in SQL 2005

Hi all--Given a table with the following definition in SQL Server 2005 SP2:

[DBA_AUDIT_SESSION](

[INSTANCE_NAME] [varchar](16) NULL,

[HOST_NAME] [varchar](64) NULL,

[OS_USERNAME] [varchar](255) NULL,

[USERNAME] [varchar](30) NULL,

[USERHOST] [varchar](128) NULL,

[TERMINAL] [varchar](255) NULL,

[TIMESTAMP] [datetime] NOT NULL,

[ACTION_NAME] [varchar](28) NULL,

[LOGOFF_TIME] [datetime] NULL,

[LOGOFF_LREAD] [float] NULL,

[LOGOFF_PREAD] [float] NULL,

[LOGOFF_LWRITE] [float] NULL,

[LOGOFF_DLOCK] [varchar](40) NULL,

[SESSIONID] [float] NOT NULL,

[RETURNCODE] [float] NOT NULL,

[CLIENT_ID] [varchar](64) NULL,

[SESSION_CPU] [float] NULL,

[Import_Date] [datetime] NULL

I am trying to remove duplicate rows using the Import_Date and SESSIONID fields as my identifying fields. Since I am new to the ROW_NUMBER() function, I am having problems with aggregate querying while trying to build up to a CTE and resolve this issue. For example, when I query with this:

SELECT ROW_NUMBER() OVER (order BY SESSIONID asc) AS ROWID, instance_name, sessionid, Import_date

FROM dba_audit_session

group by sessionid, instance_name, import_date

HAVING COUNT(sessionid)>1

I get zero rows, or a complaint about fields not being coupled with an aggregate function or not in a GROUPED BY clause if I use an * with the ROW_NUMBER() function here. Any thoughts about how I should proceed?

Thanks in advance,

Jonathan

I am not saying it will fix your code but it is the place to start, all aggregate functions in SQL Server ignores NULLs except COUNT(*); it counts all rows of the table. Run a search for COUNT(*) in the BOL. Hope this helps.|||Hey,

I am a noob.. but.. have a look at this..

http://support.microsoft.com/kb/139444

NOx
|||

Hmmm, that query worked fine for me.

What do you get when you run this:

Code Snippet

SELECT instance_name, sessionid, Import_date, count(*) as tally

FROM dba_audit_session

group by sessionid, instance_name, import_date

HAVING COUNT(sessionid)>1

|||You can delete all but the latest (in Import_Date, SESSIONID order) for each instance_name this way:

Code Snippet

with T_ranked as (
select
*,
rank() over (
partition by instance_name
order by Import_Date desc, SESSIONID desc
) as rk

from dba_audit_session
)
delete from T_ranked
where rk > 1

Steve Kass
Drew University
http://www.stevekass.com
|||

Hi--Found this one, liked how it got to the basics, but wanted to try working with SQL Server 2005's row_number() function based on this article:

http://www.databasejournal.com/features/mssql/article.php/3572301

The author of this article makes it look easy, but looks at a specific row in a resultset instead of a range of data.

|||

Use the following query. It will help you.

Code Snippet

;WITH CTE

(

SELECT

ROW_NUMBER() OVER (Partition By sessionid, import_date Order BY sessionid) AS ROWID

, instance_name

, sessionid

, Import_date

FROM

dba_audit_session

)

--Select * From CTE

Delete From CTE Where ROWID > 1

|||What if we don't know which rows are duplicates, meaning.. imagine a table with say 10000 rows and by a simple query you find out there are about 100 duplicate records.

What's the approach you would take in deleting these records?

Thanks
|||

Thank you both, Steve and Manivannan, for the tips on how to handle these with CTEs. These are exactly what I was looking for, and more. I've got several instances' worth of data to play with, so the partitioning by sessionid and the ranking by instance_name in both examples will come in handy. I will try them both out with sample data and let you know how they work out.

- Jonathan

|||

Hi Steve--I populated the test table with two copies of each row from two data sources, so I could check how your formula works. I just tried your ranking formula, substituting "select *" instead of the DELETE FROM statement so I could see the final result set before deletion of duplicates. The result set returns over 2.4 million rows, looking like this sample of duplicate data:

InstanceOS_UsernameHostSessionidImport_Date Rank

dwsmithhost11272912007-07-10 15:18:45.267 2

dwsmithhost11272912007-07-10 15:10:42.867 3

…

From what I can tell, the CTE as originally written will remove all but one row from the ranked and sorted data. Is there a way to tweak this CTE so that it checks for the sessionid of the row preceding the currently checked row for a duplicate entry and then removing the duplicate entry if one is found? (I tried tweaking it, but started to get into trouble with aggregate query messages when I attempted to do selects where the delete statement is written.)

Thanks in advance--Jonathan

|||

Hi--I think we're halfway there. I looked at your code, ran it with a sample of my data, and came up with a conclusion that I have an extra condition to handle. Given rowid > 1, I have to look at the Import_date and sessionid, compare the values from both rows, and keep the row given the latest Import_date. So, for example, if I have data like this:

1 dw 1053976 2007-07-10 15:10:42.867
1 dw 1053976 2007-07-10 15:18:45.267
1 dw 1053977 2007-07-10 15:10:42.867
1 dw 1053977 2007-07-10 15:18:45.267

...

I have to find the latest entry using a query like this:

SELECT

ROW_NUMBER() OVER (Partition By sessionid, import_date Order BY sessionid) AS ROWID,

instance_name,

sessionid,

Import_date

FROM dba_audit_session

where Import_Date < (select max(a.Import_Date) from DBA_AUDIT_SESSION as a

where a.sessionid = DBA_AUDIT_SESSION.sessionid);

Am I looking at another CTE for this?

|||

Hi--I think this is a good start point, and found I have an extra condition to meet.Given data like this:

Rowid instance_name sessionidImport_date

1 dw 1053976 2007-07-10 15:10:42.867

1 dw 1053976 2007-07-10 15:18:45.267

1 dw 1053977 2007-07-10 15:10:42.867

1 dw 1053977 2007-07-10 15:18:45.267

I have to keep the latest Import_date and remove prior entries.Given this query, I can make the selection:

SELECT ROW_NUMBER() OVER (Partition By sessionid, import_date Order BY sessionid) AS ROWID,

instance_name,

sessionid,

Import_date

FROM dba_audit_session

where Import_Date < (select max(a.Import_Date) from DBA_AUDIT_SESSION as a

where a.sessionid = DBA_AUDIT_SESSION.sessionid)

Do I need to nest this into a second CTE?

|||

Hi--I think this is a good start point, and found I have an extra condition to meet.Given data like this:

Rowid instance_name sessionidImport_date

1 dw 1053976 2007-07-10 15:10:42.867

1 dw 1053976 2007-07-10 15:18:45.267

1 dw 1053977 2007-07-10 15:10:42.867

1 dw 1053977 2007-07-10 15:18:45.267

I have to keep the latest Import_date and remove prior entries.Given this query, I can make the selection:

SELECT ROW_NUMBER() OVER (Partition By sessionid, import_date Order BY sessionid) AS ROWID,

instance_name,

sessionid,

Import_date

FROM dba_audit_session

where Import_Date < (select max(a.Import_Date) from DBA_AUDIT_SESSION as a

where a.sessionid = DBA_AUDIT_SESSION.sessionid)

Do I need to nest this into a second CTE?

|||I think you're missing the point. With rank(), you don't need any of this < (select max...) business. Just set the partition by and order by lists correctly so that the rows you want to keep are exactly the ones that get ranked #1.

Here's a wizard:

1. Fill in the blank. I want to keep one row for each .
2. Fill in the blank. The row I want to keep is first if I order by __.

Answer to 1 is your partition by list; answer to 2 is your order by list. My guess now is you want one row for each (instance_name, sessionid) combination, so it might be this (sessionid was in the order by list in my first try)

with T_ranked as (
select
*,
rank() over (
partition by instance_name, sessionid
order by Import_Date desc
) as rk

from dba_audit_session
)
delete from T_ranked
where rk > 1

If this still isn't it, it would be a huge help if you gave your desired answer along with sample data from which you want that answer.

SK
sql

Friday, March 23, 2012

Question on Top N clause

Top N clause retrieve the first N rows of the table. How can I retrieve the second N rows? Generally , is there any solution to retrieve the nth N rows?Daniel Anderson has an article at 4GuysFromRolla which contains a general solution:Paging through Records using a Stored Procedure.

You could also do it non-generically and unattractively and probably inefficiently like this (will select records 11-20):

SELECT TOP 10 * FROM
(SELECTTOP 10 * FROM
(SELECT TOP 20 * FROM division ORDER BY div_code ASC ) AS myTop20
ORDER BY div_code DESC) AS myTop10Desc
ORDER BY div_code ASC

Terri|||Thank you very much.|||tmorton.. that's just sexy!

Tuesday, March 20, 2012

question on query

Hi,
I need a query which returns all rows from one table and rows from another
table if they are present.
I also need to restrict the right hand table to certain rows.
When I do this on;y the matching ros from RHS show.
How d I get all rows from LHS and only atching rows in RHS?
Many thanks,
NeilYou didn't provide DDL. Here's an example from Northwind:
select
*
from
Customers c
left
join
Orders o on o.CustomerID = c.CustomerID
and o.OrderDate >= '19970101'
This shows all customers and any orders placed on or after 1 JAN 1997.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:d6gdu1$bro$1$8300dec7@.news.demon.co.uk...
Hi,
I need a query which returns all rows from one table and rows from another
table if they are present.
I also need to restrict the right hand table to certain rows.
When I do this on;y the matching ros from RHS show.
How d I get all rows from LHS and only atching rows in RHS?
Many thanks,
Neil|||Hi,
Go for LEFT OUTER JOIN. refer books online
Thanks
Hari
SQL Server MVP
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:d6gdu1$bro$1$8300dec7@.news.demon.co.uk...
> Hi,
> I need a query which returns all rows from one table and rows from another
> table if they are present.
> I also need to restrict the right hand table to certain rows.
> When I do this on;y the matching ros from RHS show.
> How d I get all rows from LHS and only atching rows in RHS?
> Many thanks,
> Neil
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Please read what you wrote. Does that sound like a spec that anyone
can use for programming? A vague question gets a vague answer -- look
up OUTER JOIN syntax in BOL.|||Sorry.
if I use
SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
ngjarma_general.aycs_v_TICList.MediaID,
ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
Expr1
FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
ngjarma_general.aycs_tblTICDeliveryLine ON
ngjarma_general.aycs_v_TICList.MediaID =
ngjarma_general.aycs_tblTICDeliveryLine.MediaID
ORDER BY ngjarma_general.aycs_v_TICList.TICname
I get a list of all MediaIDs from TICList and can see if there are any
orders in tblDeliveryLine.
But I need to restrict my view of tblDeliveryLine to a certain order. When I
use
SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
ngjarma_general.aycs_v_TICList.MediaID,
ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID
FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
ngjarma_general.aycs_tblTICDeliveryLine ON
ngjarma_general.aycs_v_TICList.MediaID =
ngjarma_general.aycs_tblTICDeliveryLine.MediaID
WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID = 67)
ORDER BY ngjarma_general.aycs_v_TICList.TICname
I no longer get the full list from the TICList.
NEIL|||On Thu, 19 May 2005 08:01:17 +0100, Neil Jarman wrote:

>Sorry.
>if I use
>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>ngjarma_general.aycs_v_TICList.MediaID,
> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>Expr1
>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
> ngjarma_general.aycs_tblTICDeliveryLine ON
>ngjarma_general.aycs_v_TICList.MediaID =
>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>I get a list of all MediaIDs from TICList and can see if there are any
>orders in tblDeliveryLine.
>But I need to restrict my view of tblDeliveryLine to a certain order. When
I
>use
>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>ngjarma_general.aycs_v_TICList.MediaID,
> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID
>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
> ngjarma_general.aycs_tblTICDeliveryLine ON
>ngjarma_general.aycs_v_TICList.MediaID =
>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID = 67)
>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>I no longer get the full list from the TICList.
Hi Neil,
First, get rid of the TOP 100 PERCENT. This is just useless extra text
to make the query harder to read. Returning all rows is the default, so
you don't need to specify that you want all the rows. (And frankly, I'm
not sure if the optimizer is smart enough to ignore the TOP 100 PERCENT
clause, so you might even end up with a sub-optimal execution plan!).
The reason that your modification doesn't work, is that you filter on
the outer table in the where clause. This effectively changes the outer
join to an inner join. All rows from the first table without matching
rows from the second table get a set of NULLS as a result of the outer
join; since this NULL is never equal to 67, those rows are removed by
the WHERE filter. The solution is to move the filtering condition to the
JOIN condition.
Also, please start using table aliases instead of endlessly repeating
the same table name. Make them short and mnemonic for best effect.
Here's a version of your query that's not only corrected, but much
easier to read and maintain as well:
SELECT list.TICname, list.MediaID,
line.MediaID, line.TICDeliveryHeadID
FROM ngjarma_general.aycs_v_TICList AS list
LEFT OUTER JOIN ngjarma_general.aycs_tblTICDeliveryLine AS line
ON list.MediaID = line.MediaID
AND line.TICDeliveryHeadID = 67
ORDER BY list.TICname
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
I'm obliged for your help.
In fact I had already solved the problem with:
SELECT TICList.*, CurOrder.iOrder
FROM ngjarma_general.aycs_v_TICList TICList LEFT OUTER JOIN
(SELECT MediaID, iOrder
FROM ngjarma_general.aycs_tblTICDeliveryLine
WHERE (TICDeliveryHeadID = 67)) CurOrder ON TICList.MediaID =
CurOrder.MediaID
However, yours in much nicer and I'm changing my solution right now.
Thanks for the tips on shortening the code - I admit that I posted these
directly from EM, but I do usually tidy them up before using them in
Dreamweaver.
Once again, many thanks for the code and also the general tips.
NEIL
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:sj0q81puksr46e9l3qa2lqrqrjv0orkpi3@.
4ax.com...
> On Thu, 19 May 2005 08:01:17 +0100, Neil Jarman wrote:
>
> Hi Neil,
> First, get rid of the TOP 100 PERCENT. This is just useless extra text
> to make the query harder to read. Returning all rows is the default, so
> you don't need to specify that you want all the rows. (And frankly, I'm
> not sure if the optimizer is smart enough to ignore the TOP 100 PERCENT
> clause, so you might even end up with a sub-optimal execution plan!).
> The reason that your modification doesn't work, is that you filter on
> the outer table in the where clause. This effectively changes the outer
> join to an inner join. All rows from the first table without matching
> rows from the second table get a set of NULLS as a result of the outer
> join; since this NULL is never equal to 67, those rows are removed by
> the WHERE filter. The solution is to move the filtering condition to the
> JOIN condition.
> Also, please start using table aliases instead of endlessly repeating
> the same table name. Make them short and mnemonic for best effect.
> Here's a version of your query that's not only corrected, but much
> easier to read and maintain as well:
> SELECT list.TICname, list.MediaID,
> line.MediaID, line.TICDeliveryHeadID
> FROM ngjarma_general.aycs_v_TICList AS list
> LEFT OUTER JOIN ngjarma_general.aycs_tblTICDeliveryLine AS line
> ON list.MediaID = line.MediaID
> AND line.TICDeliveryHeadID = 67
> ORDER BY list.TICname
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

question on query

Hi,
I need a query which returns all rows from one table and rows from another
table if they are present.
I also need to restrict the right hand table to certain rows.
When I do this on;y the matching ros from RHS show.
How d I get all rows from LHS and only atching rows in RHS?
Many thanks,
Neil
You didn't provide DDL. Here's an example from Northwind:
select
*
from
Customers c
left
join
Orders o on o.CustomerID = c.CustomerID
and o.OrderDate >= '19970101'
This shows all customers and any orders placed on or after 1 JAN 1997.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:d6gdu1$bro$1$8300dec7@.news.demon.co.uk...
Hi,
I need a query which returns all rows from one table and rows from another
table if they are present.
I also need to restrict the right hand table to certain rows.
When I do this on;y the matching ros from RHS show.
How d I get all rows from LHS and only atching rows in RHS?
Many thanks,
Neil
|||Hi,
Go for LEFT OUTER JOIN. refer books online
Thanks
Hari
SQL Server MVP
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:d6gdu1$bro$1$8300dec7@.news.demon.co.uk...
> Hi,
> I need a query which returns all rows from one table and rows from another
> table if they are present.
> I also need to restrict the right hand table to certain rows.
> When I do this on;y the matching ros from RHS show.
> How d I get all rows from LHS and only atching rows in RHS?
> Many thanks,
> Neil
>
|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Please read what you wrote. Does that sound like a spec that anyone
can use for programming? A vague question gets a vague answer -- look
up OUTER JOIN syntax in BOL.
|||Sorry.
if I use
SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
ngjarma_general.aycs_v_TICList.MediaID,
ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
Expr1
FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
ngjarma_general.aycs_tblTICDeliveryLine ON
ngjarma_general.aycs_v_TICList.MediaID =
ngjarma_general.aycs_tblTICDeliveryLine.MediaID
ORDER BY ngjarma_general.aycs_v_TICList.TICname
I get a list of all MediaIDs from TICList and can see if there are any
orders in tblDeliveryLine.
But I need to restrict my view of tblDeliveryLine to a certain order. When I
use
SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
ngjarma_general.aycs_v_TICList.MediaID,
ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliver yHeadID
FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
ngjarma_general.aycs_tblTICDeliveryLine ON
ngjarma_general.aycs_v_TICList.MediaID =
ngjarma_general.aycs_tblTICDeliveryLine.MediaID
WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDelive ryHeadID = 67)
ORDER BY ngjarma_general.aycs_v_TICList.TICname
I no longer get the full list from the TICList.
NEIL
|||On Thu, 19 May 2005 08:01:17 +0100, Neil Jarman wrote:

>Sorry.
>if I use
>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>ngjarma_general.aycs_v_TICList.MediaID,
> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>Expr1
>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
> ngjarma_general.aycs_tblTICDeliveryLine ON
>ngjarma_general.aycs_v_TICList.MediaID =
>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>I get a list of all MediaIDs from TICList and can see if there are any
>orders in tblDeliveryLine.
>But I need to restrict my view of tblDeliveryLine to a certain order. When I
>use
>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>ngjarma_general.aycs_v_TICList.MediaID,
> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliver yHeadID
>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
> ngjarma_general.aycs_tblTICDeliveryLine ON
>ngjarma_general.aycs_v_TICList.MediaID =
>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDelive ryHeadID = 67)
>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>I no longer get the full list from the TICList.
Hi Neil,
First, get rid of the TOP 100 PERCENT. This is just useless extra text
to make the query harder to read. Returning all rows is the default, so
you don't need to specify that you want all the rows. (And frankly, I'm
not sure if the optimizer is smart enough to ignore the TOP 100 PERCENT
clause, so you might even end up with a sub-optimal execution plan!).
The reason that your modification doesn't work, is that you filter on
the outer table in the where clause. This effectively changes the outer
join to an inner join. All rows from the first table without matching
rows from the second table get a set of NULLS as a result of the outer
join; since this NULL is never equal to 67, those rows are removed by
the WHERE filter. The solution is to move the filtering condition to the
JOIN condition.
Also, please start using table aliases instead of endlessly repeating
the same table name. Make them short and mnemonic for best effect.
Here's a version of your query that's not only corrected, but much
easier to read and maintain as well:
SELECT list.TICname, list.MediaID,
line.MediaID, line.TICDeliveryHeadID
FROM ngjarma_general.aycs_v_TICList AS list
LEFT OUTER JOIN ngjarma_general.aycs_tblTICDeliveryLine AS line
ON list.MediaID = line.MediaID
AND line.TICDeliveryHeadID = 67
ORDER BY list.TICname
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
I'm obliged for your help.
In fact I had already solved the problem with:
SELECT TICList.*, CurOrder.iOrder
FROM ngjarma_general.aycs_v_TICList TICList LEFT OUTER JOIN
(SELECT MediaID, iOrder
FROM ngjarma_general.aycs_tblTICDeliveryLine
WHERE (TICDeliveryHeadID = 67)) CurOrder ON TICList.MediaID =
CurOrder.MediaID
However, yours in much nicer and I'm changing my solution right now.
Thanks for the tips on shortening the code - I admit that I posted these
directly from EM, but I do usually tidy them up before using them in
Dreamweaver.
Once again, many thanks for the code and also the general tips.
NEIL
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:sj0q81puksr46e9l3qa2lqrqrjv0orkpi3@.4ax.com...
> On Thu, 19 May 2005 08:01:17 +0100, Neil Jarman wrote:
>
> Hi Neil,
> First, get rid of the TOP 100 PERCENT. This is just useless extra text
> to make the query harder to read. Returning all rows is the default, so
> you don't need to specify that you want all the rows. (And frankly, I'm
> not sure if the optimizer is smart enough to ignore the TOP 100 PERCENT
> clause, so you might even end up with a sub-optimal execution plan!).
> The reason that your modification doesn't work, is that you filter on
> the outer table in the where clause. This effectively changes the outer
> join to an inner join. All rows from the first table without matching
> rows from the second table get a set of NULLS as a result of the outer
> join; since this NULL is never equal to 67, those rows are removed by
> the WHERE filter. The solution is to move the filtering condition to the
> JOIN condition.
> Also, please start using table aliases instead of endlessly repeating
> the same table name. Make them short and mnemonic for best effect.
> Here's a version of your query that's not only corrected, but much
> easier to read and maintain as well:
> SELECT list.TICname, list.MediaID,
> line.MediaID, line.TICDeliveryHeadID
> FROM ngjarma_general.aycs_v_TICList AS list
> LEFT OUTER JOIN ngjarma_general.aycs_tblTICDeliveryLine AS line
> ON list.MediaID = line.MediaID
> AND line.TICDeliveryHeadID = 67
> ORDER BY list.TICname
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

question on query

Hi,
I need a query which returns all rows from one table and rows from another
table if they are present.
I also need to restrict the right hand table to certain rows.
When I do this on;y the matching ros from RHS show.
How d I get all rows from LHS and only atching rows in RHS?
Many thanks,
NeilYou didn't provide DDL. Here's an example from Northwind:
select
*
from
Customers c
left
join
Orders o on o.CustomerID = c.CustomerID
and o.OrderDate >= '19970101'
This shows all customers and any orders placed on or after 1 JAN 1997.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:d6gdu1$bro$1$8300dec7@.news.demon.co.uk...
Hi,
I need a query which returns all rows from one table and rows from another
table if they are present.
I also need to restrict the right hand table to certain rows.
When I do this on;y the matching ros from RHS show.
How d I get all rows from LHS and only atching rows in RHS?
Many thanks,
Neil|||Hi,
Go for LEFT OUTER JOIN. refer books online
Thanks
Hari
SQL Server MVP
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:d6gdu1$bro$1$8300dec7@.news.demon.co.uk...
> Hi,
> I need a query which returns all rows from one table and rows from another
> table if they are present.
> I also need to restrict the right hand table to certain rows.
> When I do this on;y the matching ros from RHS show.
> How d I get all rows from LHS and only atching rows in RHS?
> Many thanks,
> Neil
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Please read what you wrote. Does that sound like a spec that anyone
can use for programming? A vague question gets a vague answer -- look
up OUTER JOIN syntax in BOL.|||Sorry.
if I use
SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
ngjarma_general.aycs_v_TICList.MediaID,
ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
Expr1
FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
ngjarma_general.aycs_tblTICDeliveryLine ON
ngjarma_general.aycs_v_TICList.MediaID =ngjarma_general.aycs_tblTICDeliveryLine.MediaID
ORDER BY ngjarma_general.aycs_v_TICList.TICname
I get a list of all MediaIDs from TICList and can see if there are any
orders in tblDeliveryLine.
But I need to restrict my view of tblDeliveryLine to a certain order. When I
use
SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
ngjarma_general.aycs_v_TICList.MediaID,
ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID
FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
ngjarma_general.aycs_tblTICDeliveryLine ON
ngjarma_general.aycs_v_TICList.MediaID =ngjarma_general.aycs_tblTICDeliveryLine.MediaID
WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID = 67)
ORDER BY ngjarma_general.aycs_v_TICList.TICname
I no longer get the full list from the TICList.
NEIL|||On Thu, 19 May 2005 08:01:17 +0100, Neil Jarman wrote:
>Sorry.
>if I use
>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>ngjarma_general.aycs_v_TICList.MediaID,
> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>Expr1
>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
> ngjarma_general.aycs_tblTICDeliveryLine ON
>ngjarma_general.aycs_v_TICList.MediaID =>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>I get a list of all MediaIDs from TICList and can see if there are any
>orders in tblDeliveryLine.
>But I need to restrict my view of tblDeliveryLine to a certain order. When I
>use
>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>ngjarma_general.aycs_v_TICList.MediaID,
> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID
>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
> ngjarma_general.aycs_tblTICDeliveryLine ON
>ngjarma_general.aycs_v_TICList.MediaID =>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID = 67)
>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>I no longer get the full list from the TICList.
Hi Neil,
First, get rid of the TOP 100 PERCENT. This is just useless extra text
to make the query harder to read. Returning all rows is the default, so
you don't need to specify that you want all the rows. (And frankly, I'm
not sure if the optimizer is smart enough to ignore the TOP 100 PERCENT
clause, so you might even end up with a sub-optimal execution plan!).
The reason that your modification doesn't work, is that you filter on
the outer table in the where clause. This effectively changes the outer
join to an inner join. All rows from the first table without matching
rows from the second table get a set of NULLS as a result of the outer
join; since this NULL is never equal to 67, those rows are removed by
the WHERE filter. The solution is to move the filtering condition to the
JOIN condition.
Also, please start using table aliases instead of endlessly repeating
the same table name. Make them short and mnemonic for best effect.
Here's a version of your query that's not only corrected, but much
easier to read and maintain as well:
SELECT list.TICname, list.MediaID,
line.MediaID, line.TICDeliveryHeadID
FROM ngjarma_general.aycs_v_TICList AS list
LEFT OUTER JOIN ngjarma_general.aycs_tblTICDeliveryLine AS line
ON list.MediaID = line.MediaID
AND line.TICDeliveryHeadID = 67
ORDER BY list.TICname
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
I'm obliged for your help.
In fact I had already solved the problem with:
SELECT TICList.*, CurOrder.iOrder
FROM ngjarma_general.aycs_v_TICList TICList LEFT OUTER JOIN
(SELECT MediaID, iOrder
FROM ngjarma_general.aycs_tblTICDeliveryLine
WHERE (TICDeliveryHeadID = 67)) CurOrder ON TICList.MediaID =CurOrder.MediaID
However, yours in much nicer and I'm changing my solution right now.
Thanks for the tips on shortening the code - I admit that I posted these
directly from EM, but I do usually tidy them up before using them in
Dreamweaver.
Once again, many thanks for the code and also the general tips.
NEIL
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:sj0q81puksr46e9l3qa2lqrqrjv0orkpi3@.4ax.com...
> On Thu, 19 May 2005 08:01:17 +0100, Neil Jarman wrote:
>>Sorry.
>>if I use
>>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>>ngjarma_general.aycs_v_TICList.MediaID,
>> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>>Expr1
>>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
>> ngjarma_general.aycs_tblTICDeliveryLine ON
>>ngjarma_general.aycs_v_TICList.MediaID =>>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>>I get a list of all MediaIDs from TICList and can see if there are any
>>orders in tblDeliveryLine.
>>But I need to restrict my view of tblDeliveryLine to a certain order. When
>>I
>>use
>>SELECT TOP 100 PERCENT ngjarma_general.aycs_v_TICList.TICname,
>>ngjarma_general.aycs_v_TICList.MediaID,
>> ngjarma_general.aycs_tblTICDeliveryLine.MediaID AS
>>Expr1, ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID
>>FROM ngjarma_general.aycs_v_TICList LEFT OUTER JOIN
>> ngjarma_general.aycs_tblTICDeliveryLine ON
>>ngjarma_general.aycs_v_TICList.MediaID =>>ngjarma_general.aycs_tblTICDeliveryLine.MediaID
>>WHERE (ngjarma_general.aycs_tblTICDeliveryLine.TICDeliveryHeadID = 67)
>>ORDER BY ngjarma_general.aycs_v_TICList.TICname
>>I no longer get the full list from the TICList.
> Hi Neil,
> First, get rid of the TOP 100 PERCENT. This is just useless extra text
> to make the query harder to read. Returning all rows is the default, so
> you don't need to specify that you want all the rows. (And frankly, I'm
> not sure if the optimizer is smart enough to ignore the TOP 100 PERCENT
> clause, so you might even end up with a sub-optimal execution plan!).
> The reason that your modification doesn't work, is that you filter on
> the outer table in the where clause. This effectively changes the outer
> join to an inner join. All rows from the first table without matching
> rows from the second table get a set of NULLS as a result of the outer
> join; since this NULL is never equal to 67, those rows are removed by
> the WHERE filter. The solution is to move the filtering condition to the
> JOIN condition.
> Also, please start using table aliases instead of endlessly repeating
> the same table name. Make them short and mnemonic for best effect.
> Here's a version of your query that's not only corrected, but much
> easier to read and maintain as well:
> SELECT list.TICname, list.MediaID,
> line.MediaID, line.TICDeliveryHeadID
> FROM ngjarma_general.aycs_v_TICList AS list
> LEFT OUTER JOIN ngjarma_general.aycs_tblTICDeliveryLine AS line
> ON list.MediaID = line.MediaID
> AND line.TICDeliveryHeadID = 67
> ORDER BY list.TICname
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

question on query

Monday, February 20, 2012

Question on analysis services cube partiton duration

Hi, all experts here,

It is really strange as I only got a small partition for my cube which only got around 150 rows records. But when I processed the cueb, it took a long while to finish? Why is that? Any advices?

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

How long does it take to run the query the AS executes in the relational database?|||

I think that is because the low space on my local system drive.

Thanks a lot.

With best regards,

Yours sincerely,

Wednesday, March 28, 2012

Question regarding use of a join to the same table

Question regarding SP

Question regarding SP

Question regarding SP

Question regarding SP

Monday, March 26, 2012

Question on using ROW_NUMBER to remove duplicate rows in SQL 2005

Friday, March 23, 2012

Question on Top N clause

Tuesday, March 20, 2012

question on query

question on query

question on query

question on query

Monday, February 20, 2012

Question on analysis services cube partiton duration

SQLAgentMail

Blog Archive

About Me