Hi,
When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
This code works :
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
SELECT * FROM @.OldProposal
and this code doesn't work:
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
SELECT * FROM @.OldProposal
The difference is only "WHERE ProposalID=9;" at the end of update query
and OUTPUT query
Hi
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
SELECT * FROM @.OldProposal
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegro ups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>
|||?
|||?
Have you ran my soultion?
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127155.623078.225040@.u72g2000cwu.googlegr oups.com...
> ?
>
|||Yes this works but my update query is having WHERE Clause...
Thanks Uri.
|||I'm confused , try run this code and see
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127994.920723.283170@.u72g2000cwu.googlegr oups.com...
> Yes this works but my update query is having WHERE Clause...
> Thanks Uri.
>
|||Yes this works...
But please try running this:
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1 where i = 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
Note: I have added "where i = 1" in update query
|||Adarsh
You don't need to put a WHERE condition twice. Does my example return a
right result, doesn't it?
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141129109.654224.266210@.j33g2000cwa.googlegr oups.com...
> Yes this works...
> But please try running this:
> create table t ( i int not null );
>
> create table table_audit ( old_i int not null, new_i int null );
> insert into t (i) values( 1 );
> insert into t (i) values( 2 );
>
> update t
> set i = i + 1 where i = 1
> output deleted.i, inserted.i into table_audit
> where i = 1;
>
> delete from t
> output deleted.i, NULL into table_audit
> where i = 2;
>
> select * from t;
> select * from table_audit;
>
> drop table t, table_audit;
> go
>
> Note: I have added "where i = 1" in update query
>
|||The OUTPUT clause doesn't have a WHERE clause. Check out the syntax in Books Online for the OUTPUT
clause:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-08fdc19f5fe4.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegro ups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>
|||Yes but I want to update the row only which has i = 1. What can I do in
that situation .
Becoz without Where Clause it will update all the records in t table.
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Friday, March 30, 2012
OUTPUT Clause SQL Server 2005
Hi,
When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
This code works :
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
SELECT * FROM @.OldProposal
and this code doesn't work:
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
SELECT * FROM @.OldProposal
The difference is only "WHERE ProposalID=9;" at the end of update query
and OUTPUT queryHi
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
SELECT * FROM @.OldProposal
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||'|||'
Have you ran my soultion?
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127155.623078.225040@.u72g2000cwu.googlegroups.com...
> '
>|||Yes this works but my update query is having WHERE Clause...
Thanks Uri.|||I'm confused , try run this code and see
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127994.920723.283170@.u72g2000cwu.googlegroups.com...
> Yes this works but my update query is having WHERE Clause...
> Thanks Uri.
>|||Yes this works...
But please try running this:
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1 where i = 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
Note: I have added "where i = 1" in update query|||Adarsh
You don't need to put a WHERE condition twice. Does my example return a
right result, doesn't it?
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141129109.654224.266210@.j33g2000cwa.googlegroups.com...
> Yes this works...
> But please try running this:
> create table t ( i int not null );
>
> create table table_audit ( old_i int not null, new_i int null );
> insert into t (i) values( 1 );
> insert into t (i) values( 2 );
>
> update t
> set i = i + 1 where i = 1
> output deleted.i, inserted.i into table_audit
> where i = 1;
>
> delete from t
> output deleted.i, NULL into table_audit
> where i = 2;
>
> select * from t;
> select * from table_audit;
>
> drop table t, table_audit;
> go
>
> Note: I have added "where i = 1" in update query
>|||The OUTPUT clause doesn't have a WHERE clause. Check out the syntax in Books Online for the OUTPUT
clause:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-08fdc19f5fe4.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||Yes but I want to update the row only which has i = 1. What can I do in
that situation .
Becoz without Where Clause it will update all the records in t table.|||Adarsh, I think you are confused about the OUTPUT clause. Both the OUTPUT
clause and the WHERE clause are part of the same UPDATE statement. The
following will update only those rows with ProposalID=9, not all rows in the
table. Only the before image of the updated rows will be inserted into
@.OldProposal.
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141132215.382713.261120@.e56g2000cwe.googlegroups.com...
> Yes but I want to update the row only which has i = 1. What can I do in
> that situation .
> Becoz without Where Clause it will update all the records in t table.
>|||The update has a WHERE clause. Just as usual. But the OUTPUT clause doesn't have a WHERE clause.
This is what it should look like:
UPDATE tblname
OUTPUT ...
WHERE...
The WHERE clause above belong to the UPDATE statement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141132215.382713.261120@.e56g2000cwe.googlegroups.com...
> Yes but I want to update the row only which has i = 1. What can I do in
> that situation .
> Becoz without Where Clause it will update all the records in t table.
>|||#$%&*(..... Oh ok.... I got it now..;)
Thanks a lot Dan, Tibor and Uri...
When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
This code works :
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
SELECT * FROM @.OldProposal
and this code doesn't work:
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
SELECT * FROM @.OldProposal
The difference is only "WHERE ProposalID=9;" at the end of update query
and OUTPUT queryHi
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
SELECT * FROM @.OldProposal
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||'|||'
Have you ran my soultion?
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127155.623078.225040@.u72g2000cwu.googlegroups.com...
> '
>|||Yes this works but my update query is having WHERE Clause...
Thanks Uri.|||I'm confused , try run this code and see
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127994.920723.283170@.u72g2000cwu.googlegroups.com...
> Yes this works but my update query is having WHERE Clause...
> Thanks Uri.
>|||Yes this works...
But please try running this:
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1 where i = 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
Note: I have added "where i = 1" in update query|||Adarsh
You don't need to put a WHERE condition twice. Does my example return a
right result, doesn't it?
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141129109.654224.266210@.j33g2000cwa.googlegroups.com...
> Yes this works...
> But please try running this:
> create table t ( i int not null );
>
> create table table_audit ( old_i int not null, new_i int null );
> insert into t (i) values( 1 );
> insert into t (i) values( 2 );
>
> update t
> set i = i + 1 where i = 1
> output deleted.i, inserted.i into table_audit
> where i = 1;
>
> delete from t
> output deleted.i, NULL into table_audit
> where i = 2;
>
> select * from t;
> select * from table_audit;
>
> drop table t, table_audit;
> go
>
> Note: I have added "where i = 1" in update query
>|||The OUTPUT clause doesn't have a WHERE clause. Check out the syntax in Books Online for the OUTPUT
clause:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-08fdc19f5fe4.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||Yes but I want to update the row only which has i = 1. What can I do in
that situation .
Becoz without Where Clause it will update all the records in t table.|||Adarsh, I think you are confused about the OUTPUT clause. Both the OUTPUT
clause and the WHERE clause are part of the same UPDATE statement. The
following will update only those rows with ProposalID=9, not all rows in the
table. Only the before image of the updated rows will be inserted into
@.OldProposal.
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141132215.382713.261120@.e56g2000cwe.googlegroups.com...
> Yes but I want to update the row only which has i = 1. What can I do in
> that situation .
> Becoz without Where Clause it will update all the records in t table.
>|||The update has a WHERE clause. Just as usual. But the OUTPUT clause doesn't have a WHERE clause.
This is what it should look like:
UPDATE tblname
OUTPUT ...
WHERE...
The WHERE clause above belong to the UPDATE statement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141132215.382713.261120@.e56g2000cwe.googlegroups.com...
> Yes but I want to update the row only which has i = 1. What can I do in
> that situation .
> Becoz without Where Clause it will update all the records in t table.
>|||#$%&*(..... Oh ok.... I got it now..;)
Thanks a lot Dan, Tibor and Uri...
OUTPUT Clause SQL Server 2005
Hi,
When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
This code works :
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
SELECT * FROM @.OldProposal
and this code doesn't work:
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
SELECT * FROM @.OldProposal
The difference is only "WHERE ProposalID=9;" at the end of update query
and OUTPUT queryHi
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
SELECT * FROM @.OldProposal
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||'|||'
Have you ran my soultion?
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127155.623078.225040@.u72g2000cwu.googlegroups.com...
> '
>|||Yes this works but my update query is having WHERE Clause...
Thanks Uri.|||I'm confused , try run this code and see
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127994.920723.283170@.u72g2000cwu.googlegroups.com...
> Yes this works but my update query is having WHERE Clause...
> Thanks Uri.
>|||Yes this works...
But please try running this:
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1 where i = 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
Note: I have added "where i = 1" in update query|||Adarsh
You don't need to put a WHERE condition twice. Does my example return a
right result, doesn't it?
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141129109.654224.266210@.j33g2000cwa.googlegroups.com...
> Yes this works...
> But please try running this:
> create table t ( i int not null );
>
> create table table_audit ( old_i int not null, new_i int null );
> insert into t (i) values( 1 );
> insert into t (i) values( 2 );
>
> update t
> set i = i + 1 where i = 1
> output deleted.i, inserted.i into table_audit
> where i = 1;
>
> delete from t
> output deleted.i, NULL into table_audit
> where i = 2;
>
> select * from t;
> select * from table_audit;
>
> drop table t, table_audit;
> go
>
> Note: I have added "where i = 1" in update query
>|||The OUTPUT clause doesn't have a WHERE clause. Check out the syntax in Books
Online for the OUTPUT
clause:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-
08fdc19f5fe4.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||Yes but I want to update the row only which has i = 1. What can I do in
that situation .
Becoz without Where Clause it will update all the records in t table.
When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
This code works :
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
SELECT * FROM @.OldProposal
and this code doesn't work:
DECLARE @.OldProposal TABLE
(ProposalDesc varchar(200))
UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
SELECT * FROM @.OldProposal
The difference is only "WHERE ProposalID=9;" at the end of update query
and OUTPUT queryHi
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
SELECT * FROM @.OldProposal
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||'|||'
Have you ran my soultion?
UPDATE tbl_Proposal SET ProposalDesc = 'yu'
OUTPUT Deleted.ProposalDesc INTO @.OldProposal
WHERE ProposalID=9;
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127155.623078.225040@.u72g2000cwu.googlegroups.com...
> '
>|||Yes this works but my update query is having WHERE Clause...
Thanks Uri.|||I'm confused , try run this code and see
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141127994.920723.283170@.u72g2000cwu.googlegroups.com...
> Yes this works but my update query is having WHERE Clause...
> Thanks Uri.
>|||Yes this works...
But please try running this:
create table t ( i int not null );
create table table_audit ( old_i int not null, new_i int null );
insert into t (i) values( 1 );
insert into t (i) values( 2 );
update t
set i = i + 1 where i = 1
output deleted.i, inserted.i into table_audit
where i = 1;
delete from t
output deleted.i, NULL into table_audit
where i = 2;
select * from t;
select * from table_audit;
drop table t, table_audit;
go
Note: I have added "where i = 1" in update query|||Adarsh
You don't need to put a WHERE condition twice. Does my example return a
right result, doesn't it?
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141129109.654224.266210@.j33g2000cwa.googlegroups.com...
> Yes this works...
> But please try running this:
> create table t ( i int not null );
>
> create table table_audit ( old_i int not null, new_i int null );
> insert into t (i) values( 1 );
> insert into t (i) values( 2 );
>
> update t
> set i = i + 1 where i = 1
> output deleted.i, inserted.i into table_audit
> where i = 1;
>
> delete from t
> output deleted.i, NULL into table_audit
> where i = 2;
>
> select * from t;
> select * from table_audit;
>
> drop table t, table_audit;
> go
>
> Note: I have added "where i = 1" in update query
>|||The OUTPUT clause doesn't have a WHERE clause. Check out the syntax in Books
Online for the OUTPUT
clause:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/41b9962c-0c71-4227-80a0-
08fdc19f5fe4.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1141125419.472041.94320@.v46g2000cwv.googlegroups.com...
> Hi,
> When I add WHERE CLAUSE IN UPDATE query the OUTPUT Clause doesn't work.
>
> This code works :
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu'
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal
> SELECT * FROM @.OldProposal
>
> and this code doesn't work:
>
> DECLARE @.OldProposal TABLE
> (ProposalDesc varchar(200))
>
> UPDATE tbl_Proposal SET ProposalDesc = 'yu' WHERE ProposalID=9;
> OUTPUT Deleted.ProposalDesc INTO @.OldProposal WHERE ProposalID=9;
> SELECT * FROM @.OldProposal
> The difference is only "WHERE ProposalID=9;" at the end of update query
> and OUTPUT query
>|||Yes but I want to update the row only which has i = 1. What can I do in
that situation .
Becoz without Where Clause it will update all the records in t table.
OUTPUT Clause
We have a table, which is partitioned on a computed column, based on a UDF function (custom logic). We populate this computed column in an INSTEAD OF Trigger. But when we try to "output" the computed column using OUTPUT caluse, NULL values are returned for computed column. SQL BOL is not clear on this part of using output clause in instead of triggers. This does not even work for a table that is not partitioned. Here is an example of what we are trying to do...
I would appreciate any help on this...
IF OBJECT_ID('dbo.Test') IS NOT NULL
DROP TABLE dbo.Test
GO
CREATE TABLE dbo.Test
( IdCol INT IDENTITY(1,1) NOT NULL,
Date DATETIME NOT NULL,
CalcCol CHAR(10) NOT NULL
)
GO
CREATE TRIGGER trgTest_InsUpd ON dbo.Test
INSTEAD OF INSERT AS
BEGIN
INSERT INTO dbo.Test
( Date, CalcCol ) SELECT Date, CONVERT(CHAR(10), Date, 101) FROM INSERTED
END
GO
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
GO
CREATE TABLE #tmp(IdCol INT, Date DATETIME, CalcCol VARCHAR(10))
TRUNCATE TABLE #tmp
INSERT INTO dbo.Test(Date) OUTPUT INSERTED.IdCol, INSERTED.Date, INSERTED.CalcCol INTO #tmp
VALUES(GETDATE())
--Here the identity and Computed column are returned as NULL
SELECT * FROM #tmp
SELECT * FROM dbo.Test
Instead of trigger would not have data in INSERTED and DELETED tables, because instead of the original insert statement your INSTEAD OF tRIGGER is getting fired. The tables will be populated if you write a AFTER TRIGGER.output 2 table data to a text file
hi, how to output 2 table data to a text file?Not specifing what you want...
You can use bcp, osql with the -o switch or if you just wanna put out the
result of a query in QA you can send the result to a fiel rather than to the
result pane.
Is it that what you need ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:uCTvwdPTFHA.632@.TK2MSFTNGP10.phx.gbl...
> hi, how to output 2 table data to a text file?
>|||can I do it in a query: select * from tb1 output my.txt?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OoINvkPTFHA.1148@.tk2msftngp13.phx.gbl...
> Not specifing what you want...
> You can use bcp, osql with the -o switch or if you just wanna put out the
> result of a query in QA you can send the result to a fiel rather than to
> the result pane.
> Is it that what you need ?
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:uCTvwdPTFHA.632@.TK2MSFTNGP10.phx.gbl...
>|||Yes of course, here is an example:
osql -E -Q"Select TOP 1 * from Northwind..Orders" -o "C:\Output.txt"
will produuce the following output:
OrderID CustomerID EmployeeID OrderDate
RequiredDate ShippedDate ShipVia
Freight ShipName
ShipAddress
ShipCity ShipRegion ShipPostalCode ShipCountry
-- -- -- --
-- -- --
-- ---
----
-- -- -- --
10248 VINET 5 1996-07-04 00:00:00.000
1996-08-01 00:00:00.000 1996-07-16 00:00:00.000 3
32.3800 Vins et al
s Chevalier
59 rue de l'Abbaye
Reims NULL 51100 France
(1 row affected)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:uoTJioPTFHA.2336@.TK2MSFTNGP12.phx.gbl...
> can I do it in a query: select * from tb1 output my.txt?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OoINvkPTFHA.1148@.tk2msftngp13.phx.gbl...
>|||Thanks Jens...|||how about this:
osql -E -Q"backup Northwind to disk='c:\output1.txt " -o "C:\Output2.txt" -S
myServer
'c:\output1.txt : Server Path
'C:\Output2.txt : Local Path
can I run it in another worksation? and want to 'c:\output1.txt' located in
the workdation?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eEF4GuPTFHA.3392@.TK2MSFTNGP12.phx.gbl...
> Yes of course, here is an example:
> osql -E -Q"Select TOP 1 * from Northwind..Orders" -o "C:\Output.txt"
> will produuce the following output:
> OrderID CustomerID EmployeeID OrderDate
> RequiredDate ShippedDate ShipVia
> Freight ShipName
> ShipAddress
> ShipCity ShipRegion ShipPostalCode ShipCountry
> -- -- -- --
> -- -- --
> -- ---
> ----
> -- -- -- --
> 10248 VINET 5 1996-07-04 00:00:00.000
> 1996-08-01 00:00:00.000 1996-07-16 00:00:00.000 3
> 32.3800 Vins et als Chevalier
> 59 rue de l'Abbaye
> Reims NULL 51100 France
> (1 row affected)
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:uoTJioPTFHA.2336@.TK2MSFTNGP12.phx.gbl...
>|||Output will be redirected to the workstation due to the output of osql,
backup will be made to the server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:eOlPq3PTFHA.228@.TK2MSFTNGP12.phx.gbl...
> how about this:
> osql -E -Q"backup Northwind to disk='c:\output1.txt " -o
> "C:\Output2.txt" -S myServer
> 'c:\output1.txt : Server Path
> 'C:\Output2.txt : Local Path
> can I run it in another worksation? and want to 'c:\output1.txt' located
> in the workdation?
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:eEF4GuPTFHA.3392@.TK2MSFTNGP12.phx.gbl...
>|||can backup to disk=unc format?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uGRII%23PTFHA.1152@.tk2msftngp13.phx.gbl...
> Output will be redirected to the workstation due to the output of osql,
> backup will be made to the server.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:eOlPq3PTFHA.228@.TK2MSFTNGP12.phx.gbl...
>|||YEah that works,
Jens Suessmeyer.
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:OdHsAJQTFHA.2812@.TK2MSFTNGP09.phx.gbl...
> can backup to disk=unc format?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uGRII%23PTFHA.1152@.tk2msftngp13.phx.gbl...
>
You can use bcp, osql with the -o switch or if you just wanna put out the
result of a query in QA you can send the result to a fiel rather than to the
result pane.
Is it that what you need ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:uCTvwdPTFHA.632@.TK2MSFTNGP10.phx.gbl...
> hi, how to output 2 table data to a text file?
>|||can I do it in a query: select * from tb1 output my.txt?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OoINvkPTFHA.1148@.tk2msftngp13.phx.gbl...
> Not specifing what you want...
> You can use bcp, osql with the -o switch or if you just wanna put out the
> result of a query in QA you can send the result to a fiel rather than to
> the result pane.
> Is it that what you need ?
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:uCTvwdPTFHA.632@.TK2MSFTNGP10.phx.gbl...
>|||Yes of course, here is an example:
osql -E -Q"Select TOP 1 * from Northwind..Orders" -o "C:\Output.txt"
will produuce the following output:
OrderID CustomerID EmployeeID OrderDate
RequiredDate ShippedDate ShipVia
Freight ShipName
ShipAddress
ShipCity ShipRegion ShipPostalCode ShipCountry
-- -- -- --
-- -- --
-- ---
----
-- -- -- --
10248 VINET 5 1996-07-04 00:00:00.000
1996-08-01 00:00:00.000 1996-07-16 00:00:00.000 3
32.3800 Vins et al
s Chevalier59 rue de l'Abbaye
Reims NULL 51100 France
(1 row affected)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:uoTJioPTFHA.2336@.TK2MSFTNGP12.phx.gbl...
> can I do it in a query: select * from tb1 output my.txt?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OoINvkPTFHA.1148@.tk2msftngp13.phx.gbl...
>|||Thanks Jens...|||how about this:
osql -E -Q"backup Northwind to disk='c:\output1.txt " -o "C:\Output2.txt" -S
myServer
'c:\output1.txt : Server Path
'C:\Output2.txt : Local Path
can I run it in another worksation? and want to 'c:\output1.txt' located in
the workdation?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eEF4GuPTFHA.3392@.TK2MSFTNGP12.phx.gbl...
> Yes of course, here is an example:
> osql -E -Q"Select TOP 1 * from Northwind..Orders" -o "C:\Output.txt"
> will produuce the following output:
> OrderID CustomerID EmployeeID OrderDate
> RequiredDate ShippedDate ShipVia
> Freight ShipName
> ShipAddress
> ShipCity ShipRegion ShipPostalCode ShipCountry
> -- -- -- --
> -- -- --
> -- ---
> ----
> -- -- -- --
> 10248 VINET 5 1996-07-04 00:00:00.000
> 1996-08-01 00:00:00.000 1996-07-16 00:00:00.000 3
> 32.3800 Vins et al
s Chevalier> 59 rue de l'Abbaye
> Reims NULL 51100 France
> (1 row affected)
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:uoTJioPTFHA.2336@.TK2MSFTNGP12.phx.gbl...
>|||Output will be redirected to the workstation due to the output of osql,
backup will be made to the server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:eOlPq3PTFHA.228@.TK2MSFTNGP12.phx.gbl...
> how about this:
> osql -E -Q"backup Northwind to disk='c:\output1.txt " -o
> "C:\Output2.txt" -S myServer
> 'c:\output1.txt : Server Path
> 'C:\Output2.txt : Local Path
> can I run it in another worksation? and want to 'c:\output1.txt' located
> in the workdation?
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:eEF4GuPTFHA.3392@.TK2MSFTNGP12.phx.gbl...
>|||can backup to disk=unc format?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uGRII%23PTFHA.1152@.tk2msftngp13.phx.gbl...
> Output will be redirected to the workstation due to the output of osql,
> backup will be made to the server.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
> news:eOlPq3PTFHA.228@.TK2MSFTNGP12.phx.gbl...
>|||YEah that works,
Jens Suessmeyer.
"js" <js@.someone@.hotmail.com> schrieb im Newsbeitrag
news:OdHsAJQTFHA.2812@.TK2MSFTNGP09.phx.gbl...
> can backup to disk=unc format?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:uGRII%23PTFHA.1152@.tk2msftngp13.phx.gbl...
>
out-of-range smalldatatime value
I am trying to insert a rown into a table with this line in my program :
INSERT INTO MYTABLE (Usuario, Datahora, COD_PROG, Tabela, TipoMov, Registro, Campo, ValAnt, ValAtu, Motivo, Sequencial) VALUES (23, '06/28/2007 19:53:45', '002101', 'Contribuinte', 'A', '1006626', ' ', 'Isentou Tx 2a Via 062007', ' ', ' ', ' ')
and I am receiving this message :The conversion of char data type to smalldatatime data type resulted in an out-of-range smalldatatime value.
ThanksHello
I suppose that the smalldatetime has the value 06/28/2007 19:53:45
what is the language used for the server ?
the format of the date is corresponding to month/day/year ( american format ).Maybe it's the origin of the problem.
For the french format ( dd/mm/yyyy ), you have an error ( 28 does not correspond to a month )
Have a good day
|||The smalldatetime datatype does NOT include seconds. But it truncates the seconds. -That isn't the problem.
I suspect that your server is expecting a date in the form of 'dd/mm/yyyy', and you are providing 'mm/dd/yyyy'.
If you changed the date format on the INSERT data to the ISO standard of 'yyyy/mm/dd', you wouldn't have any problem.
|||Thank you, that's ok now.Out-of_process error when trying to process a dimension
In my dsv I have a table called OrderDetail. This table has a salescodeid column in it. I have a second table called SalesCodeDetail. There is a relationship between these two tables based on the salescodeid. The Id is the primary key in the SalesCodeDetail table. In my cube I have a dimension of OrderDetail. When I try to add the salescode description from the salescodeDetail table as an attribute in my OrderDetail dimension I get this error.
Here is a more detailed description of this same problem.
I have a fact table that is joined to a dimension table 'Customer' by the customer number. Customer Number is the primary key in the 'Customer' view. I have a second Dimension table called 'SalesRep' which is referenced to the fact table through the customer. The join is SalesRepId, which is primary key on the SalesRep view, and SalesRepId which is on the 'Customer' view but not a key. Also, this relationship between SalesRep and customer does exist in the DSV.
When I try to process this relationship I get the following error: 'OLE DB error: OLE DB or ODBC error: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.; 42000.'
The SQL behind the processing is doing an 'OPENROWSET' which is causing the problem. If I take the 'OPENROWSET' out of the query when I run in Query Analyzer, the query runs fine.
So, I need to modify the way AS is creating this processing query.
|||So, let me check my understanding of the problem. You have a fact table that references the Customer dimension. The Customer dimension holds a SalesRep key which points to the SalesRep dimension. The fact table is assigned a referenced relationship to the SalesRep dimension using the Customer dimension as the intermediate dimension in that relationship. Is that correct?
If so, do you have the SalesRep dimension flagged to be materialized?
Either way, could you post the SQL generated that is causing the error?
Thanks,
Bryan Smith
Yes, you're understanding the problem correctly. Yes, the SalesRep dimension is flagged to be materialized.
Here is the SQL
SELECT [dbo_vw_OrdersAndSalesDetailFact].[ICAndExtExtendedPrice] AS [dbo_vw_OrdersAndSalesDetailFactICAndExtExtendedPrice0_0],[dbo_vw_OrdersAndSalesDetailFact].[ICAndExtOrderQtyInRptUOM] AS [dbo_vw_OrdersAndSalesDetailFactICAndExtOrderQtyInRptUOM0_1],[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] AS [dbo_vw_OrdersAndSalesDetailFactSoldToAccountId0_2],[dbo_vw_OMSBPCustomer_2].[PrimarySalesRepAccountID] AS [dbo_vw_OMSBPCustomerPrimarySalesRepAccountID2_0]
FROM [dbo].[vw_OrdersAndSalesDetailFact] AS [dbo_vw_OrdersAndSalesDetailFact],
OPENROWSET
(
N'SQLNCLI.1',
N'',
N'[dbo].[vw_OMSBPCustomer]'
)
AS [dbo_vw_OMSBPCustomer_2]
WHERE
(
(
[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] = [dbo_vw_OMSBPCustomer_2].[CustomerNumber]
)
)
Now if I take this exact SQL and run it in query analyzer I get basically the same error:
Msg 7430, Level 16, State 3, Line 1
Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.
If I remove the OPENROWSET section as below
SELECT [dbo_vw_OrdersAndSalesDetailFact].[ICAndExtExtendedPrice]
AS [dbo_vw_OrdersAndSalesDetailFactICAndExtExtendedPrice0_0],
[dbo_vw_OrdersAndSalesDetailFact].[ICAndExtOrderQtyInRptUOM]
AS [dbo_vw_OrdersAndSalesDetailFactICAndExtOrderQtyInRptUOM0_1],
[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId]
AS [dbo_vw_OrdersAndSalesDetailFactSoldToAccountId0_2],
[dbo_vw_OMSBPCustomer_2].[PrimarySalesRepAccountID] AS
[dbo_vw_OMSBPCustomerPrimarySalesRepAccountID2_0]
FROM [dbo].[vw_OrdersAndSalesDetailFact] AS [dbo_vw_OrdersAndSalesDetailFact],
-- OPENROWSET
-- (
-- N'SQLNCLI.1',
-- N'',
-- N'[dbo].[vw_OMSBPCustomer]'
-- )
--
[omswrite].[dbo].[vw_OMSBPCustomer] AS [dbo_vw_OMSBPCustomer_2] ****different database here
WHERE
(
(
[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] = [dbo_vw_OMSBPCustomer_2].[CustomerNumber]
)
)
The customer table and the fact table are in different databases, not sure if that is causing some problems, but don't think it should.
|||
Deselecting the Materialize option should eliminate the problem becase the join will not be performed. If performance drops when querying the Sales Rep data, you may want to try using the .NET provider on your data sources.
B.
|||This worked. Thanks very much for taking the time to help!
sqlOut-of_process error when trying to process a dimension
In my dsv I have a table called OrderDetail. This table has a salescodeid column in it. I have a second table called SalesCodeDetail. There is a relationship between these two tables based on the salescodeid. The Id is the primary key in the SalesCodeDetail table. In my cube I have a dimension of OrderDetail. When I try to add the salescode description from the salescodeDetail table as an attribute in my OrderDetail dimension I get this error.
Here is a more detailed description of this same problem.
I have a fact table that is joined to a dimension table 'Customer' by the customer number. Customer Number is the primary key in the 'Customer' view. I have a second Dimension table called 'SalesRep' which is referenced to the fact table through the customer. The join is SalesRepId, which is primary key on the SalesRep view, and SalesRepId which is on the 'Customer' view but not a key. Also, this relationship between SalesRep and customer does exist in the DSV.
When I try to process this relationship I get the following error: 'OLE DB error: OLE DB or ODBC error: Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.; 42000.'
The SQL behind the processing is doing an 'OPENROWSET' which is causing the problem. If I take the 'OPENROWSET' out of the query when I run in Query Analyzer, the query runs fine.
So, I need to modify the way AS is creating this processing query.
|||So, let me check my understanding of the problem. You have a fact table that references the Customer dimension. The Customer dimension holds a SalesRep key which points to the SalesRep dimension. The fact table is assigned a referenced relationship to the SalesRep dimension using the Customer dimension as the intermediate dimension in that relationship. Is that correct?
If so, do you have the SalesRep dimension flagged to be materialized?
Either way, could you post the SQL generated that is causing the error?
Thanks,
Bryan Smith
Yes, you're understanding the problem correctly. Yes, the SalesRep dimension is flagged to be materialized.
Here is the SQL
SELECT [dbo_vw_OrdersAndSalesDetailFact].[ICAndExtExtendedPrice] AS [dbo_vw_OrdersAndSalesDetailFactICAndExtExtendedPrice0_0],[dbo_vw_OrdersAndSalesDetailFact].[ICAndExtOrderQtyInRptUOM] AS [dbo_vw_OrdersAndSalesDetailFactICAndExtOrderQtyInRptUOM0_1],[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] AS [dbo_vw_OrdersAndSalesDetailFactSoldToAccountId0_2],[dbo_vw_OMSBPCustomer_2].[PrimarySalesRepAccountID] AS [dbo_vw_OMSBPCustomerPrimarySalesRepAccountID2_0]
FROM [dbo].[vw_OrdersAndSalesDetailFact] AS [dbo_vw_OrdersAndSalesDetailFact],
OPENROWSET
(
N'SQLNCLI.1',
N'',
N'[dbo].[vw_OMSBPCustomer]'
)
AS [dbo_vw_OMSBPCustomer_2]
WHERE
(
(
[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] = [dbo_vw_OMSBPCustomer_2].[CustomerNumber]
)
)
Now if I take this exact SQL and run it in query analyzer I get basically the same error:
Msg 7430, Level 16, State 3, Line 1
Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.
If I remove the OPENROWSET section as below
SELECT [dbo_vw_OrdersAndSalesDetailFact].[ICAndExtExtendedPrice]
AS [dbo_vw_OrdersAndSalesDetailFactICAndExtExtendedPrice0_0],
[dbo_vw_OrdersAndSalesDetailFact].[ICAndExtOrderQtyInRptUOM]
AS [dbo_vw_OrdersAndSalesDetailFactICAndExtOrderQtyInRptUOM0_1],
[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId]
AS [dbo_vw_OrdersAndSalesDetailFactSoldToAccountId0_2],
[dbo_vw_OMSBPCustomer_2].[PrimarySalesRepAccountID] AS
[dbo_vw_OMSBPCustomerPrimarySalesRepAccountID2_0]
FROM [dbo].[vw_OrdersAndSalesDetailFact] AS [dbo_vw_OrdersAndSalesDetailFact],
-- OPENROWSET
-- (
-- N'SQLNCLI.1',
-- N'',
-- N'[dbo].[vw_OMSBPCustomer]'
-- )
--
[omswrite].[dbo].[vw_OMSBPCustomer] AS [dbo_vw_OMSBPCustomer_2] ****different database here
WHERE
(
(
[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] = [dbo_vw_OMSBPCustomer_2].[CustomerNumber]
)
)
The customer table and the fact table are in different databases, not sure if that is causing some problems, but don't think it should.
|||
Deselecting the Materialize option should eliminate the problem becase the join will not be performed. If performance drops when querying the Sales Rep data, you may want to try using the .NET provider on your data sources.
B.
|||This worked. Thanks very much for taking the time to help!
Wednesday, March 28, 2012
Outer table inside stored procedure
Hi,
I am using SQL Server 2000
I need to select values from a table that belongs to other database from
within a stored procedure. Hardcoding table's fully qualified name is not
appropriate. Table's database name must be a parameter of a stored procedure
.
Is there any better way than using dynamic query string generation? If query
is a dynamic string it is difficult to mainain it.
Thanks in advance.You could create a view which includes the database name - then when you nee
d
to change the database just change the view and all the SPs will reference
tthe correct database.
"Alexander Korol" wrote:
> Hi,
> I am using SQL Server 2000
> I need to select values from a table that belongs to other database from
> within a stored procedure. Hardcoding table's fully qualified name is not
> appropriate. Table's database name must be a parameter of a stored procedu
re.
> Is there any better way than using dynamic query string generation? If que
ry
> is a dynamic string it is difficult to mainain it.
> Thanks in advance.|||Sorry I made a multipost. Was not intended to just missed the window :) .
From now on let's refer to "How to refer from a stored procedure to a table
in another database"
Thanks for your reply.
I can not do anything beforehand. In the runtime my procedure can get only
the name of a database of guaranteed structure. Thereis a way to change the
database name in the view from stored procedure in the runtime ALTER VIEW. I
s
that what you ment? I will try.|||Dynamic altering the view helped. Thanks.
A very simple example:
exec('ALTER VIEW ViewName AS SELECT * FROM ' + @.NM_DATABASE +
'.dbo.TableName')
I am using SQL Server 2000
I need to select values from a table that belongs to other database from
within a stored procedure. Hardcoding table's fully qualified name is not
appropriate. Table's database name must be a parameter of a stored procedure
.
Is there any better way than using dynamic query string generation? If query
is a dynamic string it is difficult to mainain it.
Thanks in advance.You could create a view which includes the database name - then when you nee
d
to change the database just change the view and all the SPs will reference
tthe correct database.
"Alexander Korol" wrote:
> Hi,
> I am using SQL Server 2000
> I need to select values from a table that belongs to other database from
> within a stored procedure. Hardcoding table's fully qualified name is not
> appropriate. Table's database name must be a parameter of a stored procedu
re.
> Is there any better way than using dynamic query string generation? If que
ry
> is a dynamic string it is difficult to mainain it.
> Thanks in advance.|||Sorry I made a multipost. Was not intended to just missed the window :) .
From now on let's refer to "How to refer from a stored procedure to a table
in another database"
Thanks for your reply.
I can not do anything beforehand. In the runtime my procedure can get only
the name of a database of guaranteed structure. Thereis a way to change the
database name in the view from stored procedure in the runtime ALTER VIEW. I
s
that what you ment? I will try.|||Dynamic altering the view helped. Thanks.
A very simple example:
exec('ALTER VIEW ViewName AS SELECT * FROM ' + @.NM_DATABASE +
'.dbo.TableName')
Outer Joins
I have a set of data which needs to be put in a table in addition to dates
(daily). There are some dates where a location could be closed and hence no
data values but i still want to see the date row in the table with location
and all other associated details but NULL for visit counts.
I have tried LEFT OUTER / RIGHT OUTER but failed. Is there an easy way to do
this ?
Thanks
AsimAsim wrote:
> I have a set of data which needs to be put in a table in addition to
> dates (daily). There are some dates where a location could be closed
> and hence no data values but i still want to see the date row in the
> table with location and all other associated details but NULL for
> visit counts.
> I have tried LEFT OUTER / RIGHT OUTER but failed. Is there an easy
> way to do this ?
> Thanks
> Asim
You need to post table DDL and sample data.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||It is very hard to debug code that you cannot see :)|||It sounds like you want the output to include information (dates)
that are nowhere to be found in your data, as if you were to query
an empty sales table and expect a result with some dates.
If you want data in your result, the data has to be in the source
of the query. A calendar table might be what you want in this
case:
select
C.theDate,
A.Location,
A.otherStuff
from Calendar as C
left outer join AsimTable as A
on A.dt >= C.theDate
and A.dt < C.theDate + 1
See http://www.aspfaq.com/show.asp?id=2519
Steve Kass
Drew University
Asim wrote:
>I have a set of data which needs to be put in a table in addition to dates
>(daily). There are some dates where a location could be closed and hence no
>data values but i still want to see the date row in the table with location
>and all other associated details but NULL for visit counts.
>I have tried LEFT OUTER / RIGHT OUTER but failed. Is there an easy way to d
o
>this ?
>Thanks
>Asim
>|||Sorry guys!
Here is the code, the first temp table gets all the cost center, procedures
and etc and then the second table gets each date (D_DateMonth) .
DECLARE @.Procs TABLE
(ProcedureCode varchar(11), CostCtr varchar(16), CcName varchar(31),
StatCode varchar(11), LocationID varchar(30),
SubGrouping varchar(30), [Grouping] varchar(30), Service varchar(50), Campus
varchar(30), SiteGroup varchar(50))
INSERT INTO @.Procs
SELECT ProcedureCode, CostCtr, CcName, StatCode, LocationID, SubGrouping,
[Grouping], Service, Campus, SiteGroup
FROM D_Procedures
GROUP BY ProcedureCode, CostCtr, CcName, StatCode, LocationID,
SubGrouping, [Grouping], Service, Campus, SiteGroup
SELECT dbo.D_Date_month.[Date] as DOM, dbo.TitleCase(a.Service) as
Service,a.Campus as Hospital,dbo.TitleCase(a.SiteGroup) as Location,
a.CostCtr as CostCtr,dbo.TitleCase(a.CcName) as CcName,
SUM(CASE WHEN OC1_Txns.TransactionCount < 0 THEN -1 ELSE 1 END) as
Visits,NULL AS BudgetVisits,
NULL AS VisitVar, NULL AS LastFYVisits, NULL AS LastFYVar, NULL AS Charges,
NULL AS LastFYCharges,
NULL AS ChargesVar,Convert(smalldatetime, GetDate()) as RowUpdatedatetime
FROM dbo.D_Date_month LEFT OUTER JOIN OC1_Txns ON OC1_Txns.BatchDateTime
= dbo.D_Date_month.[Date]
INNER JOIN @.Procs a ON OC1_Txns.TransactionProcedure = a.ProcedureCode
WHERE (Left(a.StatCode,1) = 'V'
OR a.StatCode='FAMPLAN' OR a.StatCode='MIDWIFE' OR a.StatCode='ORTHOOTH')
AND (OC1_Txns.BatchDateTime >= '04/01/2005' AND OC1_Txns.BatchDateTime <
'04/03/2005')
GROUP BY dbo.D_Date_month.[Date],a.Service, a.Campus, a.SiteGroup,
a.CostCtr, a.CcName
ORDER BY dbo.D_Date_month.[Date],a.Service, a.Campus, a.SiteGroup,
a.CostCtr, a.CcName
"--CELKO--" wrote:
> It is very hard to debug code that you cannot see :)
>|||The C table has no dates.. The information is based on visit level in that
table and I need to build the daily visits grouped by service, location and
etc and even if there was no visit on a particular date for a service or
locaiton, i want to see that service or location with a null for visits but
the date.
"Steve Kass" wrote:
> It sounds like you want the output to include information (dates)
> that are nowhere to be found in your data, as if you were to query
> an empty sales table and expect a result with some dates.
> If you want data in your result, the data has to be in the source
> of the query. A calendar table might be what you want in this
> case:
> select
> C.theDate,
> A.Location,
> A.otherStuff
> from Calendar as C
> left outer join AsimTable as A
> on A.dt >= C.theDate
> and A.dt < C.theDate + 1
> See http://www.aspfaq.com/show.asp?id=2519
> Steve Kass
> Drew University
> Asim wrote:
>
>|||Why not fold the first query into the second one instead of creating a
physical table? Why are you doiing formatting in the query instead of
enforcing this in the DDL? Why do data elements keep changing names
from table to table?
My quick guess is that it might look like this:
SELECT D.foobar_date, A.service, A.campus, A.site_group), A.cost_ctr,
A.cc_name,
SUM(CASE WHEN T.transaction_count < 0
THEN -1 ELSE 1 END) AS visits,
CURRENT_TIMESTAMP
FROM D_Date_Month AS M
LEFT OUTER JOIN
Oc1_Txns AS T
ON T.batch_datetime = D.foobar_date
INNER JOIN
(SELECT DISTINCT procedure_code, cost_ctr, cc_name, stat_code,
location_id, subgrouping, grouping, service, campus, site_group
FROM D_Procedures) AS A
ON T.procedure_code = A.procedure_code
WHERE (LEFT(A.stat_code, 1) = 'V'
OR A.stat_code IN ('FAMPLAN', 'MIDWIFE', 'ORTHOOTH'))
AND T.batch_datetime BETWEEN '2005-04-01 00:00:00' AND '2005-04-03
23:59:59.9999'
GROUP BY D.foobar_date, A.service, A.campus, A.site_group, A.cost_ctr,
A.cc_name;|||On 27 May 2005 11:34:09 -0700, --CELKO-- wrote:
(snip)
>AND T.batch_datetime BETWEEN '2005-04-01 00:00:00' AND '2005-04-03
>23:59:59.9999'
Hi Joe,
This is a bad modification from the original code, for several reasons.
First, the constant '2005-04-03 23:59:59.9999' will not convert to a
datetime value, since you specify one decimal place too much.
Second, the format you used is not safe. It could be interpreted as
yyyy-mm-dd or yyyy-dd-mm, depending on regional settings. The only safe
formats in SQL Server are:
* yyyymmdd (date only - note: no interpunction)
* yyyy-mm-ddThh:mm:ss.ttt (date plus time - note the dashes in the date
part, the colons and decimal point in the time part and the capital T
that seperates the parts. Also note that the milliseconds (the .ttt
part) is optional).
Third, if you change it to '2005-04-03T23:59:59.999', it will be
converted to the fourth of april, midnight. You should use either
'2005-04-03T23:59:59.997' (if the column has datatype datetime) or
'2005-04-03T23:59:00' (if it is smalldatetime). And you must change it
if the column's datatype changes, or if MS changes the precision of
either the datetime or the smalldatetime datatype.
Of course, just using T.batch_datetime < '20050404' (almost the same as
the original code, only the date format is changed!) is much easier and
much safer.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
(daily). There are some dates where a location could be closed and hence no
data values but i still want to see the date row in the table with location
and all other associated details but NULL for visit counts.
I have tried LEFT OUTER / RIGHT OUTER but failed. Is there an easy way to do
this ?
Thanks
AsimAsim wrote:
> I have a set of data which needs to be put in a table in addition to
> dates (daily). There are some dates where a location could be closed
> and hence no data values but i still want to see the date row in the
> table with location and all other associated details but NULL for
> visit counts.
> I have tried LEFT OUTER / RIGHT OUTER but failed. Is there an easy
> way to do this ?
> Thanks
> Asim
You need to post table DDL and sample data.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||It is very hard to debug code that you cannot see :)|||It sounds like you want the output to include information (dates)
that are nowhere to be found in your data, as if you were to query
an empty sales table and expect a result with some dates.
If you want data in your result, the data has to be in the source
of the query. A calendar table might be what you want in this
case:
select
C.theDate,
A.Location,
A.otherStuff
from Calendar as C
left outer join AsimTable as A
on A.dt >= C.theDate
and A.dt < C.theDate + 1
See http://www.aspfaq.com/show.asp?id=2519
Steve Kass
Drew University
Asim wrote:
>I have a set of data which needs to be put in a table in addition to dates
>(daily). There are some dates where a location could be closed and hence no
>data values but i still want to see the date row in the table with location
>and all other associated details but NULL for visit counts.
>I have tried LEFT OUTER / RIGHT OUTER but failed. Is there an easy way to d
o
>this ?
>Thanks
>Asim
>|||Sorry guys!
Here is the code, the first temp table gets all the cost center, procedures
and etc and then the second table gets each date (D_DateMonth) .
DECLARE @.Procs TABLE
(ProcedureCode varchar(11), CostCtr varchar(16), CcName varchar(31),
StatCode varchar(11), LocationID varchar(30),
SubGrouping varchar(30), [Grouping] varchar(30), Service varchar(50), Campus
varchar(30), SiteGroup varchar(50))
INSERT INTO @.Procs
SELECT ProcedureCode, CostCtr, CcName, StatCode, LocationID, SubGrouping,
[Grouping], Service, Campus, SiteGroup
FROM D_Procedures
GROUP BY ProcedureCode, CostCtr, CcName, StatCode, LocationID,
SubGrouping, [Grouping], Service, Campus, SiteGroup
SELECT dbo.D_Date_month.[Date] as DOM, dbo.TitleCase(a.Service) as
Service,a.Campus as Hospital,dbo.TitleCase(a.SiteGroup) as Location,
a.CostCtr as CostCtr,dbo.TitleCase(a.CcName) as CcName,
SUM(CASE WHEN OC1_Txns.TransactionCount < 0 THEN -1 ELSE 1 END) as
Visits,NULL AS BudgetVisits,
NULL AS VisitVar, NULL AS LastFYVisits, NULL AS LastFYVar, NULL AS Charges,
NULL AS LastFYCharges,
NULL AS ChargesVar,Convert(smalldatetime, GetDate()) as RowUpdatedatetime
FROM dbo.D_Date_month LEFT OUTER JOIN OC1_Txns ON OC1_Txns.BatchDateTime
= dbo.D_Date_month.[Date]
INNER JOIN @.Procs a ON OC1_Txns.TransactionProcedure = a.ProcedureCode
WHERE (Left(a.StatCode,1) = 'V'
OR a.StatCode='FAMPLAN' OR a.StatCode='MIDWIFE' OR a.StatCode='ORTHOOTH')
AND (OC1_Txns.BatchDateTime >= '04/01/2005' AND OC1_Txns.BatchDateTime <
'04/03/2005')
GROUP BY dbo.D_Date_month.[Date],a.Service, a.Campus, a.SiteGroup,
a.CostCtr, a.CcName
ORDER BY dbo.D_Date_month.[Date],a.Service, a.Campus, a.SiteGroup,
a.CostCtr, a.CcName
"--CELKO--" wrote:
> It is very hard to debug code that you cannot see :)
>|||The C table has no dates.. The information is based on visit level in that
table and I need to build the daily visits grouped by service, location and
etc and even if there was no visit on a particular date for a service or
locaiton, i want to see that service or location with a null for visits but
the date.
"Steve Kass" wrote:
> It sounds like you want the output to include information (dates)
> that are nowhere to be found in your data, as if you were to query
> an empty sales table and expect a result with some dates.
> If you want data in your result, the data has to be in the source
> of the query. A calendar table might be what you want in this
> case:
> select
> C.theDate,
> A.Location,
> A.otherStuff
> from Calendar as C
> left outer join AsimTable as A
> on A.dt >= C.theDate
> and A.dt < C.theDate + 1
> See http://www.aspfaq.com/show.asp?id=2519
> Steve Kass
> Drew University
> Asim wrote:
>
>|||Why not fold the first query into the second one instead of creating a
physical table? Why are you doiing formatting in the query instead of
enforcing this in the DDL? Why do data elements keep changing names
from table to table?
My quick guess is that it might look like this:
SELECT D.foobar_date, A.service, A.campus, A.site_group), A.cost_ctr,
A.cc_name,
SUM(CASE WHEN T.transaction_count < 0
THEN -1 ELSE 1 END) AS visits,
CURRENT_TIMESTAMP
FROM D_Date_Month AS M
LEFT OUTER JOIN
Oc1_Txns AS T
ON T.batch_datetime = D.foobar_date
INNER JOIN
(SELECT DISTINCT procedure_code, cost_ctr, cc_name, stat_code,
location_id, subgrouping, grouping, service, campus, site_group
FROM D_Procedures) AS A
ON T.procedure_code = A.procedure_code
WHERE (LEFT(A.stat_code, 1) = 'V'
OR A.stat_code IN ('FAMPLAN', 'MIDWIFE', 'ORTHOOTH'))
AND T.batch_datetime BETWEEN '2005-04-01 00:00:00' AND '2005-04-03
23:59:59.9999'
GROUP BY D.foobar_date, A.service, A.campus, A.site_group, A.cost_ctr,
A.cc_name;|||On 27 May 2005 11:34:09 -0700, --CELKO-- wrote:
(snip)
>AND T.batch_datetime BETWEEN '2005-04-01 00:00:00' AND '2005-04-03
>23:59:59.9999'
Hi Joe,
This is a bad modification from the original code, for several reasons.
First, the constant '2005-04-03 23:59:59.9999' will not convert to a
datetime value, since you specify one decimal place too much.
Second, the format you used is not safe. It could be interpreted as
yyyy-mm-dd or yyyy-dd-mm, depending on regional settings. The only safe
formats in SQL Server are:
* yyyymmdd (date only - note: no interpunction)
* yyyy-mm-ddThh:mm:ss.ttt (date plus time - note the dashes in the date
part, the colons and decimal point in the time part and the capital T
that seperates the parts. Also note that the milliseconds (the .ttt
part) is optional).
Third, if you change it to '2005-04-03T23:59:59.999', it will be
converted to the fourth of april, midnight. You should use either
'2005-04-03T23:59:59.997' (if the column has datatype datetime) or
'2005-04-03T23:59:00' (if it is smalldatetime). And you must change it
if the column's datatype changes, or if MS changes the precision of
either the datetime or the smalldatetime datatype.
Of course, just using T.batch_datetime < '20050404' (almost the same as
the original code, only the date format is changed!) is much easier and
much safer.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
OUTER JOIN with multiple tables and a plus sign?
I am trying to select specific columns from multiple tables based on a
common identifier found in each table.
common identifier found in each table.
For example, the three tables:
PUBACC_AC
PUBACC_AM
PUBACC_AN
each have a common column:
PUBACC_AC.unique_system_identifier
PUBACC_AM.unique_system_identifier
PUBACC_AN.unique_system_identifier
What I am trying to select, for example:
PUBACC_AC.name
PUBACC_AM.phone_number
PUBACC_AN.zip
where the TABLE.unique_system_identifier is common.
For example:
--------------
PUBACC_AC
=========
unique_system_identifier name
1234 JONES
--------------
PUBACC_AM
=========
unique_system_identifier phone_number
1234 555-1212
--------------
PUBACC_AN
=========
unique_system_identifier zip
1234 90210
When I run my query, I would like to see the following returned as one
blob, rather than the separate tables:
--------------------
unique_system_identifier name phone_number zip
1234 JONES 555-1212 90210
--------------------
I think this is an OUTER JOIN? I see examples on the net using a plus
sign, with mention of Oracle. I'm not running Oracle...I am using
Microsoft SQL Server 2000.
Help, please?
P. S. Will this work with several tables? I actually have about 15
tables in this mess, but I tried to keep it simple (!??!) for the above
example.
Thanks in advance for your help!
NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAIL
ADDRESS.
Who actually BUYS the cr@.p that the spammers advertise, anyhow?!!!
(Rhetorical question only.)You do the following:
SELECT PUBACC_AC.Name + PUBACC_AM.Phone_number + PUBACC_AN.zip
FROM PUBACC_AC
INNER JOIN PUBACC_AM
ON PUBACC_AM.unique_col = PUBACC_AC.unique_col
INNER JOIN PUBACC_AN
ON PUBACC_AN.unique_col = PUBACC_AC.unique_col
That's it - you do inner join, if you want only those records, for which
unique_col value exists in all 3 tables.
Or, if you replace "INNER JOIN" with "FULL JOIN", which is the same as OUTER
JOIN in Oracle, you will get
as meny records as number of unique_col values.
Thats it!
Hope it helped,
Andrey aka Muzzy
"TeleTech1212" <tele_tech1212DELETE_THIS@.yahoo.com> wrote in message
news:Xns9556E99BEE9B4teletech1212DELETETH@.207.115. 63.158...
> I am trying to select specific columns from multiple tables based on a
> common identifier found in each table.
> For example, the three tables:
> PUBACC_AC
> PUBACC_AM
> PUBACC_AN
> each have a common column:
> PUBACC_AC.unique_system_identifier
> PUBACC_AM.unique_system_identifier
> PUBACC_AN.unique_system_identifier
>
> What I am trying to select, for example:
> PUBACC_AC.name
> PUBACC_AM.phone_number
> PUBACC_AN.zip
> where the TABLE.unique_system_identifier is common.
>
> For example:
> --------------
> PUBACC_AC
> =========
> unique_system_identifier name
> 1234 JONES
> --------------
> PUBACC_AM
> =========
> unique_system_identifier phone_number
> 1234 555-1212
> --------------
> PUBACC_AN
> =========
> unique_system_identifier zip
> 1234 90210
>
> When I run my query, I would like to see the following returned as one
> blob, rather than the separate tables:
> --------------------
> unique_system_identifier name phone_number zip
> 1234 JONES 555-1212 90210
> --------------------
>
> I think this is an OUTER JOIN? I see examples on the net using a plus
> sign, with mention of Oracle. I'm not running Oracle...I am using
> Microsoft SQL Server 2000.
> Help, please?
> P. S. Will this work with several tables? I actually have about 15
> tables in this mess, but I tried to keep it simple (!??!) for the above
> example.
> Thanks in advance for your help!
> NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAIL
> ADDRESS.
> Who actually BUYS the cr@.p that the spammers advertise, anyhow?!!!
> (Rhetorical question only.)
outer join vs. =*
I'm using SQL Server 2000 (and C#). I have a table of locations, a table of people and a table of locations in which the people are willing to work, like this (with sample data):
Terri|||Thank you.
etc.
create table Person (PersonID int, PerName varchar(20))
create table Location (LocationID int, LocName varchar(10))
create table PersonLocation (PersonID int, LocationID int)
insert into Person value (1, 'Joe')
insert into Person value (2, 'Mary')
insert into Person value (3, 'Pat')
insert into Location value (10, 'Home')
insert into Location value (20, 'Office')
insert into Location value (30, 'The moon')
insert into PersonLocation (1, 10)
insert into PersonLocation (1, 30)
insert into PersonLocation (2, 20)
I want to create a CheckBoxList with all the items from the Location table and check off the items which exist in the PersonLocation table. I thought it would be good to do this in one query of the database. Here's the results I'd to populate the list:
select LocationID, Selected from SelectedLocations where PersonID = 1
10 10
20 null
30 30
select LocationID, Selected from SelectedLocations where PersonID = 2
10 null
20 2
30 null
select LocationID, Selected from SelectedLocations where PersonID = 3
10 null
20 null
30 null
That second column could also be true/false (bit 0/1) or whatever. I'm going to subclass the CheckBoxList control and add a DataSelectedField property which would be the name of the field which indicates if the item is selected. I will need to override the databinding, so any value that distinguishes selected locations from unselected locations if fine.
I like to use views for queries so that I can add in arbitrary filtering and sorting. So I made this view:
create view SelectedLocations
as
select l.LocationID, pl.LocationID Selected
from PersonLocation pl, Location l
where l.LocationID *= pl.LocationID
But it isn't standard SQL. I'm confused about why it works. Is there a standardized way to do the query I want in SQL? I think it should have an outer join, but I tried various outer join syntax with no luck.You need something like this:
SELECT
L.LocationID,
PL.LocationID AS Selected
FROM
Location L
LEFT OUTER JOIN
PersonLocation PL ON PL.LocationID = L.LocationID AND PL.PersonID = '1'
Terri|||Thank you.
Is there any way to turn that query into a view where PersonID is a column in the view? I suppose this is an example of a place where I would need to use a stored procedure instead of a view. This is too bad, because I'm then prevented from using arbitrary sorting.|||You should be able to create a view using this approach:
CREATE VIEW dbo.View1
AS
SELECT
L.LocationID,
PL.LocationID AS Selected,
P.PersonID
FROM
Location L
FULL JOIN
Person P ON 1=1
LEFT OUTER JOIN
PersonLocation PL ON PL.LocationID = L.LocationID AND PL.PersonID = P.PersonID
So, you'd use it like this:
SELECT * FROM View1 WHERE PersonID = 1
SELECT * FROM View1 WHERE PersonID = 2
SELECT * FROM View1 WHERE PersonID = 3
etc.
Terri|||Thank you very much, indeed!
Monday, March 26, 2012
OUTER JOIN table limit?
I came across this statement from ASP.NET forum : "..There is a limit to the level for OUTER JOIN ANSI SQL limit is four after that you may get strange results. ..." . I did a little research but without getting clear answer from the SQL92 standard itself. I am wondering whether I can get help about this in SQL Server 2005 implementation here.
I put this question in another way, How many tables can we use in OUTER JOIN(or INNER JOIN) in SQL Server 2005?
Thanks.
There is no limit in the ANSI SQL standard or SQL Server. In fact, the ANSI SQL standard doesn't talk about such limits anywhere. They provide specifications on the syntax and how it should work etc. Note that a particular database implementation can have limits imposed. SQL Server 2005 for example has a maximum limit of 256 table references in a SELECT statement. So you can end up in some situation where the query optimizer cannot produce a plan due to insufficient resources in the system if the query is too complex and contains large number of table references. See below example for SQL Server 2005 which allows more than 4 outer joins:
with t(i)
as (
select 1
)
select *
from t as t1
left join t as t2
left join t as t3
left join t as t4
left join t as t5
left join t as t6
left join t as t7
left join t as t8
left join t as t9
left join t as t10
on t10.i = t9.i
on t9.i = t8.i
on t8.i = t7.i
on t7.i = t6.i
on t6.i = t5.i
on t5.i = t4.i
on t4.i = t3.i
on t3.i = t2.i
on t2.i = t1.i
as far as sql server is concerned there is no limitation.
it could have been a limitation from data access tier
e.g. dataset
|||Hello:
I was pointed to this "Three-Way Joins and Beyond" in SQL Pperformance Tuning by Peter Gulutzan and Trudy Pelzer.
Can I get some explaination for this statement: "You can expect the DBMS optimizer to start going wrong if five or more joins are in the query (until recently Microsoft's admitted limit was four). "
Is there anything I miss here?
This book was published on September 10, 2002.
Thank you.
|||That article makes several incorrect assumptions and looks like the authors are not well informed about SQL Server. For example, the support for recognizing transitive predicates has been in the product since SQL Server 7.0. See link below:
http://www.microsoft.com/technet/prodtechnol/sql/70/reskit/part9/sqc13.mspx?mfr=true
I didn't go through all the chapters but the first page itself has several errors and doesn't apply to SQL Server. Performance of joins involving large number of tables can be a problem depending on the resources and the query plan. This has also been improved considerably in the product for every release starting from SQL 6x. If you are looking for querying tips for SQL Server, you may want to look at some of the new Inside SQL Server 2005 series for example.
|||Hi Umachandar:
Thank you for answering this question. I used multiple outer joins to extract a dataset from my database. That table limit statement could pose a serious problem to my query if it were true for SQL Server 2000 or 2005.
Outer join runs differently on SQL Server and Oracle
Oracle 9i:
create table t(i integer)
Table created
insert into t values(1)
1 row inserted
select t1.i i1, t2.i i2
from t t1 left join t t2 on 1=0
0 rows selected
-- I beleive this is wrong
drop table t
Table dropped
the same query against MS SQL Server 2000:
create table t(i integer)
insert into t values(1)
select t1.i i1, t2.i i2
from t t1 left join t t2 on 1=0
i1 i2
-- --
1 NULL
-- I think this is correct
(1 row(s) affected)
drop table t
What do you thinkAK wrote:
> Oracle 9i:
> create table t(i integer)
> Table created
> insert into t values(1)
> 1 row inserted
>
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> 0 rows selected
> -- I beleive this is wrong
> drop table t
> Table dropped
> the same query against MS SQL Server 2000:
> create table t(i integer)
> insert into t values(1)
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> i1 i2
> -- --
> 1 NULL
> -- I think this is correct
> (1 row(s) affected)
> drop table t
> What do you think
I think you're using an unpatched release of Oracle 9i, whichever
release that may be (9i says NOTHING about which release or patch level
you're using):
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> create table t(i integer);
Table created.
SQL>
SQL> insert into t values(1);
1 row created.
SQL>
SQL>
SQL> select t1.i i1, t2.i i2
2 from t t1 left join t t2 on 1=0;
I1 I2
-- --
1
SQL>
The results look exactly the same to me between the two. Care to try
again, this time with a patched release of Oracle?
David Fitzjarrell|||AK schrieb:
> Oracle 9i:
> create table t(i integer)
> Table created
> insert into t values(1)
> 1 row inserted
>
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> 0 rows selected
> -- I beleive this is wrong
> drop table t
> Table dropped
> the same query against MS SQL Server 2000:
> create table t(i integer)
> insert into t values(1)
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> i1 i2
> -- --
> 1 NULL
> -- I think this is correct
> (1 row(s) affected)
> drop table t
> What do you think
>
Oracle ansi join implementation had some bugs ( much of them fixed in
recent versions/patches ), but in your case i definitely can't reproduce
your behaviour ( both on 9iR2 and 10gR1/R2)
oracle@.col-fc1-02:~/sql >sqlplus scott/tiger
SQL*Plus: Release 9.2.0.6.0 - Production on Thu Aug 11 19:19:29 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> create table t(i integer);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> select t1.i i1,t2.i i2
2 from t t1 left join t t2 on 1=0;
I1 I2
-- --
1
SQL>
Here is output for 10gR1
oracle@.wks01:~> sqlplus scott/tiger
SQL*Plus: Release 10.1.0.3.0 - Production on Do Aug 11 18:05:22 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table t(i integer);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> select t1.i i1,t2.i i2
2 from t t1 left join t t2 on 1=0
3 /
I1 I2
-- --
1
SQL>
Best regards
Maxim|||yep, one more reason to patch up:
Connected to:
Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
Thanks for the feedback
create table t(i integer)
Table created
insert into t values(1)
1 row inserted
select t1.i i1, t2.i i2
from t t1 left join t t2 on 1=0
0 rows selected
-- I beleive this is wrong
drop table t
Table dropped
the same query against MS SQL Server 2000:
create table t(i integer)
insert into t values(1)
select t1.i i1, t2.i i2
from t t1 left join t t2 on 1=0
i1 i2
-- --
1 NULL
-- I think this is correct
(1 row(s) affected)
drop table t
What do you thinkAK wrote:
> Oracle 9i:
> create table t(i integer)
> Table created
> insert into t values(1)
> 1 row inserted
>
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> 0 rows selected
> -- I beleive this is wrong
> drop table t
> Table dropped
> the same query against MS SQL Server 2000:
> create table t(i integer)
> insert into t values(1)
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> i1 i2
> -- --
> 1 NULL
> -- I think this is correct
> (1 row(s) affected)
> drop table t
> What do you think
I think you're using an unpatched release of Oracle 9i, whichever
release that may be (9i says NOTHING about which release or patch level
you're using):
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> create table t(i integer);
Table created.
SQL>
SQL> insert into t values(1);
1 row created.
SQL>
SQL>
SQL> select t1.i i1, t2.i i2
2 from t t1 left join t t2 on 1=0;
I1 I2
-- --
1
SQL>
The results look exactly the same to me between the two. Care to try
again, this time with a patched release of Oracle?
David Fitzjarrell|||AK schrieb:
> Oracle 9i:
> create table t(i integer)
> Table created
> insert into t values(1)
> 1 row inserted
>
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> 0 rows selected
> -- I beleive this is wrong
> drop table t
> Table dropped
> the same query against MS SQL Server 2000:
> create table t(i integer)
> insert into t values(1)
> select t1.i i1, t2.i i2
> from t t1 left join t t2 on 1=0
> i1 i2
> -- --
> 1 NULL
> -- I think this is correct
> (1 row(s) affected)
> drop table t
> What do you think
>
Oracle ansi join implementation had some bugs ( much of them fixed in
recent versions/patches ), but in your case i definitely can't reproduce
your behaviour ( both on 9iR2 and 10gR1/R2)
oracle@.col-fc1-02:~/sql >sqlplus scott/tiger
SQL*Plus: Release 9.2.0.6.0 - Production on Thu Aug 11 19:19:29 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
SQL> create table t(i integer);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> select t1.i i1,t2.i i2
2 from t t1 left join t t2 on 1=0;
I1 I2
-- --
1
SQL>
Here is output for 10gR1
oracle@.wks01:~> sqlplus scott/tiger
SQL*Plus: Release 10.1.0.3.0 - Production on Do Aug 11 18:05:22 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table t(i integer);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> select t1.i i1,t2.i i2
2 from t t1 left join t t2 on 1=0
3 /
I1 I2
-- --
1
SQL>
Best regards
Maxim|||yep, one more reason to patch up:
Connected to:
Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production
Thanks for the feedback
outer join quesiont, pls help!
I have two tables. One (table1) is look up table that has 48 records for tim
e
interval. They are:
interval
0:00 - 0:30
0:30 - 1:00
1:00 - 1:30
:
:
23:00 - 23:30
23:30 - 24:00
The other table (table2) has real data. It looks like that:
Interval user value
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
15:00 - 15:30 user2 3
17:00 - 17:30 user2 2
23:00 - 23:30 user3 2
The result I need is
interval user value
00:00 - 00:30 user1 Null (since no data for user1
in this interval)
00:30 - 01:00 user1 Null
:
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
:
23:00 - 23:30 user1 Null
23:30 - 24:00 user1 Null
I am trying to use left outer join to do it but it only return me:
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
my query is
select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
t2 on
t1.interval = t2.itnerval where user='user1'
Can somebody tell me what wrong is with my query. How to modify it to get
the result I need.
Thanks in advance!>> Can somebody tell me what wrong is with my query. How to modify it to get
Most likely moving the predicate in WHERE clause to ON clause will fix it.
Other wise, please read www.aspfaq.com/5006 and post DDL, sample data &
expected results in a usable format so that others can repro your problem
scenario.
Anith|||The problem is the where clause - you basically turn the outer join into an
inner join (not literally, but in effect).
Change it like this:
where (user = 'user1' or user is null)
ML|||you could try:
select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
t2 on
t1.interval = t2.itnerval AND t2.user='user1'
"Jean" wrote:
> I have two tables. One (table1) is look up table that has 48 records for t
ime
> interval. They are:
> interval
> 0:00 - 0:30
> 0:30 - 1:00
> 1:00 - 1:30
> :
> :
> 23:00 - 23:30
> 23:30 - 24:00
> The other table (table2) has real data. It looks like that:
> Interval user value
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> 15:00 - 15:30 user2 3
> 17:00 - 17:30 user2 2
> 23:00 - 23:30 user3 2
> The result I need is
> interval user value
> 00:00 - 00:30 user1 Null (since no data for user1
> in this interval)
> 00:30 - 01:00 user1 Null
> :
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> :
> 23:00 - 23:30 user1 Null
> 23:30 - 24:00 user1 Null
> I am trying to use left outer join to do it but it only return me:
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> my query is
> select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
> t2 on
> t1.interval = t2.itnerval where user='user1'
> Can somebody tell me what wrong is with my query. How to modify it to get
> the result I need.
> Thanks in advance!
>|||It works! Thanks to you all!!!
e
interval. They are:
interval
0:00 - 0:30
0:30 - 1:00
1:00 - 1:30
:
:
23:00 - 23:30
23:30 - 24:00
The other table (table2) has real data. It looks like that:
Interval user value
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
15:00 - 15:30 user2 3
17:00 - 17:30 user2 2
23:00 - 23:30 user3 2
The result I need is
interval user value
00:00 - 00:30 user1 Null (since no data for user1
in this interval)
00:30 - 01:00 user1 Null
:
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
:
23:00 - 23:30 user1 Null
23:30 - 24:00 user1 Null
I am trying to use left outer join to do it but it only return me:
6:00 - 6:30 user1 1
7:00 - 7:30 user1 1
my query is
select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
t2 on
t1.interval = t2.itnerval where user='user1'
Can somebody tell me what wrong is with my query. How to modify it to get
the result I need.
Thanks in advance!>> Can somebody tell me what wrong is with my query. How to modify it to get
Most likely moving the predicate in WHERE clause to ON clause will fix it.
Other wise, please read www.aspfaq.com/5006 and post DDL, sample data &
expected results in a usable format so that others can repro your problem
scenario.
Anith|||The problem is the where clause - you basically turn the outer join into an
inner join (not literally, but in effect).
Change it like this:
where (user = 'user1' or user is null)
ML|||you could try:
select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
t2 on
t1.interval = t2.itnerval AND t2.user='user1'
"Jean" wrote:
> I have two tables. One (table1) is look up table that has 48 records for t
ime
> interval. They are:
> interval
> 0:00 - 0:30
> 0:30 - 1:00
> 1:00 - 1:30
> :
> :
> 23:00 - 23:30
> 23:30 - 24:00
> The other table (table2) has real data. It looks like that:
> Interval user value
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> 15:00 - 15:30 user2 3
> 17:00 - 17:30 user2 2
> 23:00 - 23:30 user3 2
> The result I need is
> interval user value
> 00:00 - 00:30 user1 Null (since no data for user1
> in this interval)
> 00:30 - 01:00 user1 Null
> :
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> :
> 23:00 - 23:30 user1 Null
> 23:30 - 24:00 user1 Null
> I am trying to use left outer join to do it but it only return me:
> 6:00 - 6:30 user1 1
> 7:00 - 7:30 user1 1
> my query is
> select t1.inteval, t2.user, t2.value from table1 t1 left outer join table2
> t2 on
> t1.interval = t2.itnerval where user='user1'
> Can somebody tell me what wrong is with my query. How to modify it to get
> the result I need.
> Thanks in advance!
>|||It works! Thanks to you all!!!
Outer join query
Hi!
I have a problem with a query:
Two tables:
CREATE TABLE Emp (empno INT, depno INT)
CREATE TABLE Work (empno INT, depno INT, date DATETIME)
I have a problem with a query:
Two tables:
CREATE TABLE Emp (empno INT, depno INT)
CREATE TABLE Work (empno INT, depno INT, date DATETIME)
I want a list of all employees that belongs to a department (from Emp
table), together with ("union") all employeees WORKING on that department a
spescial day (An employee can have been borrowed from another department
which he does not belong)
Sample data
INSERT INTO Emp (empno, depno) VALUES (1,10)
INSERT INTO Emp (empno, depno) VALUES (2,10)
INSERT INTO Emp (empno, depno) VALUES (3,20)
INSERT INTO Work (empno, depno, date) VALUES (1,10,'2003-10-17')
INSERT INTO Work (empno, depno, date) VALUES (3,10,'2003-10-17')
INSERT INTO Work (empno, depno, date) VALUES (3,10,'2003-10-18')
Note that Employee 3 works on a department to which he does not belong (he
is borrowed to another department)
The following query
SELECT empno, depno, date FROM work WHERE depno = 10 AND date = '2003-10-17'
gives me this result set:
empno depno date
1 10 2003-10-17 00:00:00.000
3 10 2003-10-17 00:00:00.000
But I want employee 2 to appear in the result set as well, because he
belongs to department 10 (eaven thoug he is not working this particular day)
The result set should look like this
empno depno date
1 10 2003-10-01 00:00:00.000
2 10 NULL
3 10 2003-10-01 00:00:00.000
I have tried different approaches, but none of them is good.
Could someone please help me?
Thanks in advance
Regards,
Gunnar Vyenli
EDB-konsulent as
NORWAYSELECT empno, depno,
CASE [date] WHEN '20031017' THEN [date] END AS [date]
FROM Work
WHERE depno = 10
Date is a reserved word and shouldn't be used as a column name (it's a
pretty meaningless name for a column anyway - Date of what?)
--
David Portas
----
Please reply only to the newsgroup
--|||Thanks for your reply, but am afraid this will not do.
I need data from BOTH the tables, not only from Work.
With your query, employee 2 will not be included in the result set, because
he belongs to the Employee table.
In other words: I want a list of all employees who belongs to depno 10,
TOGETHER with all employees which does not belong to depno 10, but work on
depno 10 this particular day.
We are talking about two categories of employees:
1) All the employees who belong to depno 10 (whether they work this day or
not)
2) Those employees who does NOT belong to depno 10, BUT is working at depno
10 this date.
A new suggestion would be apprechiated.
-Gunnar
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:yrednQ5UUuzGQxKiRVn-vQ@.giganews.com...
> SELECT empno, depno,
> CASE [date] WHEN '20031017' THEN [date] END AS [date]
> FROM Work
> WHERE depno = 10
> Date is a reserved word and shouldn't be used as a column name (it's a
> pretty meaningless name for a column anyway - Date of what?)
> --
> David Portas
> ----
> Please reply only to the newsgroup
> --|||OK. Your DDL was missing any keys. Assuming the PK in Emp is empno and in
Work is (empno, date) and that there is an FK constraint on Work (empno NOT
NULL REFERENCES Emp (empno)):
SELECT COALESCE(E.empno,W.empno) AS empno, 10 AS depno, W.[date]
FROM Emp AS E
LEFT JOIN Work AS W
ON W.empno = E.empno AND W.[date] = '20031017'
WHERE E.depno = 10 OR W.depno = 10
--
David Portas
----
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:cNednSW9WZJGaBKiRVn-vQ@.giganews.com...
> OK. Your DDL was missing any keys. Assuming the PK in Emp is empno and in
> Work is (empno, date) and that there is an FK constraint on Work (empno NOT
> NULL REFERENCES Emp (empno)):
> SELECT COALESCE(E.empno,W.empno) AS empno, 10 AS depno, W.[date]
> FROM Emp AS E
> LEFT JOIN Work AS W
> ON W.empno = E.empno AND W.[date] = '20031017'
> WHERE E.depno = 10 OR W.depno = 10
Hi David, minor aside but the call to COALESCE is unnecessary as E.empno
will do. Nice solution.
So as to not be taking up bandwidth with total triviality, here's another take.
SELECT depno, empno, MAX(work_date) AS work_date
FROM (SELECT empno, depno, "date" AS work_date
FROM Work
UNION ALL
SELECT empno, depno, NULL AS work_date
FROM Emp) AS W
WHERE depno = 10 AND
(work_date = '20031017' OR work_date IS NULL)
GROUP BY depno, empno
Regards,
jag
> --
> David Portas
> ----
> Please reply only to the newsgroup
> --
Outer Join qry help!
The first query returns 46 rows (correct result set) while the second one
returns all of the records from app_type table. The second query is similar
(atleast thats what I'm trying to accomplish) to the first one, difference
being non-ansii statement.
Can someone help me figure out whats wrong with the second query?
Thanks.
qry1
select distinct ap.type,ap.subsystem
from app_type ap left outer join app_code ac on ap.type=ac.type
where ac.type is null
qry2
select distinct ap.type, ap.subsystem
from app_type ap , app_code ac
where ap.type*=ac.type
and ac.type is null
Arul,
you just found out why you should not use the depricated outer join
syntax.
Check out this posting, and see if that helps:
http://groups.google.nl/groups?selm=...utput=g plain
Gert-Jan
Arul wrote:
> The first query returns 46 rows (correct result set) while the second one
> returns all of the records from app_type table. The second query is similar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is null
returns all of the records from app_type table. The second query is similar
(atleast thats what I'm trying to accomplish) to the first one, difference
being non-ansii statement.
Can someone help me figure out whats wrong with the second query?
Thanks.
qry1
select distinct ap.type,ap.subsystem
from app_type ap left outer join app_code ac on ap.type=ac.type
where ac.type is null
qry2
select distinct ap.type, ap.subsystem
from app_type ap , app_code ac
where ap.type*=ac.type
and ac.type is null
Arul,
you just found out why you should not use the depricated outer join
syntax.
Check out this posting, and see if that helps:
http://groups.google.nl/groups?selm=...utput=g plain
Gert-Jan
Arul wrote:
> The first query returns 46 rows (correct result set) while the second one
> returns all of the records from app_type table. The second query is similar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is null
Outer Join qry help!
The first query returns 46 rows (correct result set) while the second one
returns all of the records from app_type table. The second query is similar
(atleast thats what I'm trying to accomplish) to the first one, difference
being non-ansii statement.
Can someone help me figure out whats wrong with the second query?
Thanks.
qry1
select distinct ap.type,ap.subsystem
from app_type ap left outer join app_code ac on ap.type=ac.type
where ac.type is null
qry2
select distinct ap.type, ap.subsystem
from app_type ap , app_code ac
where ap.type*=ac.type
and ac.type is nullArul,
you just found out why you should not use the depricated outer join
syntax.
Check out this posting, and see if that helps:
[url]http://groups.google.nl/groups?selm=eAtctS7DBHA.968%40tkmsftngp07&output=gplain[/u
rl]
Gert-Jan
Arul wrote:
> The first query returns 46 rows (correct result set) while the second one
> returns all of the records from app_type table. The second query is simil
ar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is null
returns all of the records from app_type table. The second query is similar
(atleast thats what I'm trying to accomplish) to the first one, difference
being non-ansii statement.
Can someone help me figure out whats wrong with the second query?
Thanks.
qry1
select distinct ap.type,ap.subsystem
from app_type ap left outer join app_code ac on ap.type=ac.type
where ac.type is null
qry2
select distinct ap.type, ap.subsystem
from app_type ap , app_code ac
where ap.type*=ac.type
and ac.type is nullArul,
you just found out why you should not use the depricated outer join
syntax.
Check out this posting, and see if that helps:
[url]http://groups.google.nl/groups?selm=eAtctS7DBHA.968%40tkmsftngp07&output=gplain[/u
rl]
Gert-Jan
Arul wrote:
> The first query returns 46 rows (correct result set) while the second one
> returns all of the records from app_type table. The second query is simil
ar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is null
Outer Join qry help!
The first query returns 46 rows (correct result set) while the second one
returns all of the records from app_type table. The second query is similar
(atleast thats what I'm trying to accomplish) to the first one, difference
being non-ansii statement.
Can someone help me figure out whats wrong with the second query?
Thanks.
qry1
select distinct ap.type,ap.subsystem
from app_type ap left outer join app_code ac on ap.type=ac.type
where ac.type is null
qry2
select distinct ap.type, ap.subsystem
from app_type ap , app_code ac
where ap.type*=ac.type
and ac.type is nullArul,
you just found out why you should not use the depricated outer join
syntax.
Check out this posting, and see if that helps:
http://groups.google.nl/groups?selm=eAtctS7DBHA.968%40tkmsftngp07&output=gplain
Gert-Jan
Arul wrote:
> The first query returns 46 rows (correct result set) while the second one
> returns all of the records from app_type table. The second query is similar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is nullsql
returns all of the records from app_type table. The second query is similar
(atleast thats what I'm trying to accomplish) to the first one, difference
being non-ansii statement.
Can someone help me figure out whats wrong with the second query?
Thanks.
qry1
select distinct ap.type,ap.subsystem
from app_type ap left outer join app_code ac on ap.type=ac.type
where ac.type is null
qry2
select distinct ap.type, ap.subsystem
from app_type ap , app_code ac
where ap.type*=ac.type
and ac.type is nullArul,
you just found out why you should not use the depricated outer join
syntax.
Check out this posting, and see if that helps:
http://groups.google.nl/groups?selm=eAtctS7DBHA.968%40tkmsftngp07&output=gplain
Gert-Jan
Arul wrote:
> The first query returns 46 rows (correct result set) while the second one
> returns all of the records from app_type table. The second query is similar
> (atleast thats what I'm trying to accomplish) to the first one, difference
> being non-ansii statement.
> Can someone help me figure out whats wrong with the second query?
> Thanks.
> qry1
> select distinct ap.type,ap.subsystem
> from app_type ap left outer join app_code ac on ap.type=ac.type
> where ac.type is null
> qry2
> select distinct ap.type, ap.subsystem
> from app_type ap , app_code ac
> where ap.type*=ac.type
> and ac.type is nullsql
Outer join problem
Hi.
I mean left outer join must return all rows in the left table.
Why?
SELECT DISTINCT a.cliente, a.Expositor
FROM prm_Exp_x_PV a
LEFT OUTER JOIN prm_paneles_x_pv c on a.id_expv = c.id_expv
LEFT OUTER JOIN prompaneles d on c.panel = d.codigo
WHERE (a.cliente = '4306500007')
Returns 22 Rows. and
SELECT DISTINCT a.cliente, a.Expositor
FROM prm_Exp_x_PV a
LEFT OUTER JOIN prm_paneles_x_pv c on a.id_expv = c.id_expv
LEFT OUTER JOIN prompaneles d on c.panel = d.codigo
WHERE (a.cliente = '4306500007')
and d.grupo is null
Returns only 9 rows
I need a query that returns all rows in the prm_Exp_x_PV table and only the
rows with null grupo in the prm_paneles_x_pv.
Table a and b have a master detail relation with cero or more rows in the
detail table.
Any help?
Thanks.
Pau.Having not seen your data (and DDL for that matter) I cannot see anything
unexpected in those queries.
And if you want rows where prm_paneles_x_pv.grupo is null then use the
appropriate alias - c instead of d.
For better help post DDL and sample data.
ML|||You keep mentioning table "b". Where is it? It's not referenced by any query
.
I don't know what happened to the DDL/sample data, but I can't see it.
Please read more on this here:
http://www.aspfaq.com/etiquette.asp?id=5006
ML|||Pau, if I'm correctly guessing what you want, you need to apply the
condition in the join condition rather than the where clause ...
...
FROM
a
LEFT JOIN b ON a.x = b.x AND b.y IS NULL
...
"Pau Dom_nguez" wrote:
> Ok. here it is the DDL and data.
> The field grupo is a field of table d used to filter the matching rows in
> table b.
> I need a query that returns all rows in the prm_Exp_x_PV table and only th
e
> rows from the prm_paneles_x_pv that have null grupo in prompaneles.
> Table a and b have a master detail relation with cero or more rows in the
> detail table.
> SELECT DISTINCT a.cliente, a.Expositor
> FROM prm_Exp_x_PV a
> LEFT OUTER JOIN prm_paneles_x_pv c on a.id_expv = c.id_ex
pv
> LEFT OUTER JOIN prompaneles d on c.panel = d.codigo
> WHERE a.cliente = '4306500007'
> and d.grupo is null
> What is wrong in this select?
> Thanks ML.
> Pau.
> "ML" <ML@.discussions.microsoft.com> escribió en el mensaje
> news:54B5E0ED-DD74-4A75-B4BD-F95FE6915DF5@.microsoft.com...
>
>|||Thanks you very much KH.
Now it works fine.
Pau.
"KH" <KH@.discussions.microsoft.com> escribi en el mensaje
news:3989ADA9-5111-4927-AD1A-64192018DCC9@.microsoft.com...
> Pau, if I'm correctly guessing what you want, you need to apply the
> condition in the join condition rather than the where clause ...
> ...
> FROM
> a
> LEFT JOIN b ON a.x = b.x AND b.y IS NULL
> ...
>
>
> "Pau Domnguez" wrote:
>|||Thank you for nothing ML.
You are burned, this work is not for you.
Pau.
"ML" <ML@.discussions.microsoft.com> escribi en el mensaje
news:93A4AF26-5094-4C10-863D-AC5C7E39A597@.microsoft.com...
> You keep mentioning table "b". Where is it? It's not referenced by any
> query.
> I don't know what happened to the DDL/sample data, but I can't see it.
> Please read more on this here:
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> ML
I mean left outer join must return all rows in the left table.
Why?
SELECT DISTINCT a.cliente, a.Expositor
FROM prm_Exp_x_PV a
LEFT OUTER JOIN prm_paneles_x_pv c on a.id_expv = c.id_expv
LEFT OUTER JOIN prompaneles d on c.panel = d.codigo
WHERE (a.cliente = '4306500007')
Returns 22 Rows. and
SELECT DISTINCT a.cliente, a.Expositor
FROM prm_Exp_x_PV a
LEFT OUTER JOIN prm_paneles_x_pv c on a.id_expv = c.id_expv
LEFT OUTER JOIN prompaneles d on c.panel = d.codigo
WHERE (a.cliente = '4306500007')
and d.grupo is null
Returns only 9 rows
I need a query that returns all rows in the prm_Exp_x_PV table and only the
rows with null grupo in the prm_paneles_x_pv.
Table a and b have a master detail relation with cero or more rows in the
detail table.
Any help?
Thanks.
Pau.Having not seen your data (and DDL for that matter) I cannot see anything
unexpected in those queries.
And if you want rows where prm_paneles_x_pv.grupo is null then use the
appropriate alias - c instead of d.
For better help post DDL and sample data.
ML|||You keep mentioning table "b". Where is it? It's not referenced by any query
.
I don't know what happened to the DDL/sample data, but I can't see it.
Please read more on this here:
http://www.aspfaq.com/etiquette.asp?id=5006
ML|||Pau, if I'm correctly guessing what you want, you need to apply the
condition in the join condition rather than the where clause ...
...
FROM
a
LEFT JOIN b ON a.x = b.x AND b.y IS NULL
...
"Pau Dom_nguez" wrote:
> Ok. here it is the DDL and data.
> The field grupo is a field of table d used to filter the matching rows in
> table b.
> I need a query that returns all rows in the prm_Exp_x_PV table and only th
e
> rows from the prm_paneles_x_pv that have null grupo in prompaneles.
> Table a and b have a master detail relation with cero or more rows in the
> detail table.
> SELECT DISTINCT a.cliente, a.Expositor
> FROM prm_Exp_x_PV a
> LEFT OUTER JOIN prm_paneles_x_pv c on a.id_expv = c.id_ex
pv
> LEFT OUTER JOIN prompaneles d on c.panel = d.codigo
> WHERE a.cliente = '4306500007'
> and d.grupo is null
> What is wrong in this select?
> Thanks ML.
> Pau.
> "ML" <ML@.discussions.microsoft.com> escribió en el mensaje
> news:54B5E0ED-DD74-4A75-B4BD-F95FE6915DF5@.microsoft.com...
>
>|||Thanks you very much KH.
Now it works fine.
Pau.
"KH" <KH@.discussions.microsoft.com> escribi en el mensaje
news:3989ADA9-5111-4927-AD1A-64192018DCC9@.microsoft.com...
> Pau, if I'm correctly guessing what you want, you need to apply the
> condition in the join condition rather than the where clause ...
> ...
> FROM
> a
> LEFT JOIN b ON a.x = b.x AND b.y IS NULL
> ...
>
>
> "Pau Domnguez" wrote:
>|||Thank you for nothing ML.
You are burned, this work is not for you.
Pau.
"ML" <ML@.discussions.microsoft.com> escribi en el mensaje
news:93A4AF26-5094-4C10-863D-AC5C7E39A597@.microsoft.com...
> You keep mentioning table "b". Where is it? It's not referenced by any
> query.
> I don't know what happened to the DDL/sample data, but I can't see it.
> Please read more on this here:
> http://www.aspfaq.com/etiquette.asp?id=5006
>
> ML
Subscribe to:
Posts (Atom)