Orphaned Sessions and locks: query

Showing posts with label query. Show all posts

Friday, March 30, 2012

Output column size limit on query analyzer

We are using SQL 2K with Sp4.
I am doing a Select statement where it contains a column that composes of a
concatenation of various strings and many fields. At time, the output of this
column will contain a very very long output string that part the output data
is being truncated. I assume it exceeds the limit of an output column in
the result panel would allow. Is there a setting in SQL I can change to
imcrease the size of a column in the result panel in query anaylzer?
WingmanA way to get around this is to use Excel and exeucte a database query and
return the results to a sheet. The data will not be truncated. It may
work by sending query results to a file from query analyzer but I haven't
tried that.
Walter
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:F41B65CC-8CF7-465A-8F75-81D0BE273AC7@.microsoft.com...
> We are using SQL 2K with Sp4.
> I am doing a Select statement where it contains a column that composes of
> a
> concatenation of various strings and many fields. At time, the output of
> this
> column will contain a very very long output string that part the output
> data
> is being truncated. I assume it exceeds the limit of an output column in
> the result panel would allow. Is there a setting in SQL I can change to
> imcrease the size of a column in the result panel in query anaylzer?
> Wingman|||Thank for the idea but I can't use Excel. Acutally the SQL statement is
coming from our web application so we need to solve this within SQL or
adjusting SQL settings.
"Walter Mallon" wrote:
> A way to get around this is to use Excel and exeucte a database query and
> return the results to a sheet. The data will not be truncated. It may
> work by sending query results to a file from query analyzer but I haven't
> tried that.
> Walter
> "Wingman" <Wingman@.discussions.microsoft.com> wrote in message
> news:F41B65CC-8CF7-465A-8F75-81D0BE273AC7@.microsoft.com...
> > We are using SQL 2K with Sp4.
> >
> > I am doing a Select statement where it contains a column that composes of
> > a
> > concatenation of various strings and many fields. At time, the output of
> > this
> > column will contain a very very long output string that part the output
> > data
> > is being truncated. I assume it exceeds the limit of an output column in
> > the result panel would allow. Is there a setting in SQL I can change to
> > imcrease the size of a column in the result panel in query anaylzer?
> >
> > Wingman
>
>|||In Query analyzer, go to the menu to Tools, then Options.
On the results tab, increase the Maximum characters per
column setting.
-Sue
On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
<Wingman@.discussions.microsoft.com> wrote:
>We are using SQL 2K with Sp4.
>I am doing a Select statement where it contains a column that composes of a
>concatenation of various strings and many fields. At time, the output of this
>column will contain a very very long output string that part the output data
>is being truncated. I assume it exceeds the limit of an output column in
>the result panel would allow. Is there a setting in SQL I can change to
>imcrease the size of a column in the result panel in query anaylzer?
>Wingman|||Thanks. It is getting closer.
The select statement is coming from a web application, how do I change the
settings you mention in a server-wise setup so all connections to that
database has the same setting.
Wingman
"Sue Hoegemeier" wrote:
> In Query analyzer, go to the menu to Tools, then Options.
> On the results tab, increase the Maximum characters per
> column setting.
> -Sue
> On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
> <Wingman@.discussions.microsoft.com> wrote:
> >We are using SQL 2K with Sp4.
> >
> >I am doing a Select statement where it contains a column that composes of a
> >concatenation of various strings and many fields. At time, the output of this
> >column will contain a very very long output string that part the output data
> >is being truncated. I assume it exceeds the limit of an output column in
> >the result panel would allow. Is there a setting in SQL I can change to
> >imcrease the size of a column in the result panel in query anaylzer?
> >
> >Wingman
>|||Those settings are for query analyzer only - it won't apply
to other applications. If data is being truncated on the
application end, you need to look at the code in the
application.
-Sue
On Thu, 27 Apr 2006 14:56:01 -0700, Wingman
<Wingman@.discussions.microsoft.com> wrote:
>Thanks. It is getting closer.
>The select statement is coming from a web application, how do I change the
>settings you mention in a server-wise setup so all connections to that
>database has the same setting.
>Wingman
>"Sue Hoegemeier" wrote:
>> In Query analyzer, go to the menu to Tools, then Options.
>> On the results tab, increase the Maximum characters per
>> column setting.
>> -Sue
>> On Thu, 27 Apr 2006 10:00:01 -0700, Wingman
>> <Wingman@.discussions.microsoft.com> wrote:
>> >We are using SQL 2K with Sp4.
>> >
>> >I am doing a Select statement where it contains a column that composes of a
>> >concatenation of various strings and many fields. At time, the output of this
>> >column will contain a very very long output string that part the output data
>> >is being truncated. I assume it exceeds the limit of an output column in
>> >the result panel would allow. Is there a setting in SQL I can change to
>> >imcrease the size of a column in the result panel in query anaylzer?
>> >
>> >Wingman
>>

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 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.

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...

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.

Output an SQL Query to a text file

Hi,

How would I go about writing the results of an SQL query to a text
file?

I cannot find any info in the Online help files.

For example, I would like the results of:

SELECT * FROM TableA

To be written to the file result.txt

AlexOpen query analyzer. Go to Query and select Result to file... and
during execution, it will prompt a file name...|||Thanks but I cannot do that.

This must query must be invisible to the end user.

For example, if a user performs some function on my system, I wish to
execute a query on what they have selected and output the results to a
file. I do not want my user to be selecting Save As etc..|||First your question is not clear. I am still not sure what you are
trying to accomplish.

First if you have a query that you dont want your user to know, wrap it
up in a stored proc and schedule it in a job and let the user execute
the job.

Another easy way is to create a dts and let the user run the dts that
should run the query and output the file.

Hope this helps..!|||Ok, I have no idea what a dts is!!

Is it possible to output the results from a stored procedure to a
specified file.

I do not want to use a tool or anything, just simply write the results
from a query to a file.|||Alex,
You might try adding this to your connection string: -o '<path
name>\results.txt.

such as: isql -SMyserver -Uusera -Puserapassword -i mysqlquery.sql -o
results.tx

hth

"Alex" <alex_seymour@.lineone.net> wrote in message
news:1108412990.821013.237700@.g14g2000cwa.googlegr oups.com...
> Hi,
> How would I go about writing the results of an SQL query to a text
> file?
> I cannot find any info in the Online help files.
> For example, I would like the results of:
> SELECT * FROM TableA
> To be written to the file result.txt
>
> Alex|||On 2/14/05 4:24 PM, in article
1108416279.303119.278950@.z14g2000cwz.googlegroups. com, "Alex"
<alex_seymour@.lineone.net> wrote:

> Ok, I have no idea what a dts is!!
> Is it possible to output the results from a stored procedure to a
> specified file.
> I do not want to use a tool or anything, just simply write the results
> from a query to a file.

There is no OUTPUT TO FILE argument in a SELECT clause. You can SELECT INTO
a temporary table but this doesn't help you. The simplest thing to do (path
of least resistance) is to use a program in the middle to handle the query
and writing the recordset into a file. For example, if you wanted to create
an .ASP web form you could simply ask for the customer's email address, run
a query and send them the results. You could write a VB app or VB script to
run locally and generate a file from your query.

-Greg|||There isn't a direct method. Two possible workarounds are OSQL and
BCP. I tend to use BCP. BCP only lets you output from a permanent
table, though.

I would create a table called "CACHE" where one of the columns is "IP"
and the other cols match the SELECT query. Then store the output into
CACHE along with the user's IP. Then run BCP to output from CACHE.|||Alex (alex_seymour@.lineone.net) writes:
> Ok, I have no idea what a dts is!!
> Is it possible to output the results from a stored procedure to a
> specified file.
> I do not want to use a tool or anything, just simply write the results
> from a query to a file.

No, you don't want to do that. Yes, you can invoke xp_cmdshell and from
there do all sorts of things. But then your users would have to have
permission to use xp_cmdshell, and that could be a security issue.

It's not clear why you want to write to a file, but if you want to
log some stuff for debug reasons - write to a table instead. You are
in a database, not in a file system.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Ok this is great, the tables I need data outputting from are permanent.

Basically it is for creating a log file. The database is huge, but the
log only needs to contain the contents of a few tables. It has to go to
a file because I want to be able to save the results of the query to
disk or print it out.

Can I BCP out from several tables into the same file?

i.e. I need SELECT * from TableA, TableB, TableC to result.txt

Security is not an issue.|||Alex wrote:
> Ok this is great, the tables I need data outputting from are
permanent.
> Basically it is for creating a log file. The database is huge, but
the
> log only needs to contain the contents of a few tables. It has to go
to
> a file because I want to be able to save the results of the query to
> disk or print it out.
> Can I BCP out from several tables into the same file?
> i.e. I need SELECT * from TableA, TableB, TableC to result.txt
> Security is not an issue.

It's a little involved but you can write a select statement joining
several tables. Each time BCP executes, I believe it overwrites the
text file.

Check out this article:
http://www.sqlteam.com/item.asp?ItemID=4722

Wednesday, March 28, 2012

outerjoint

Hi all - I am new to MsSQL and need a bit of help.
I have a query that works perfectly in Oracle but not in MsSQL. I understand that there are different commands in MsSQL and for the life of me I cannot find them. Can someone help? Here is the query.

Select c.*, ins.id from CASES c, INSPECTOR ins
where c.id = &intID
and ins.id (+) = decode(c.inspector_id,1,null,c.inspector_id)

Cheers
HekiSelect c.*, ins.id
from CASES c
LEFT OUTER JOIN
INSPECTOR ins ON
ins.id = decode(c.inspector_id,1,null,c.inspector_id) AND
c.id = &intID;|||sql server doesn't support DECODE

select c.*
, ins.id
from CASES c
left outer
join INSPECTOR ins
on case when c.inspector_id = 1
then null
else c.inspector_id end = ins.id
and c.id = &intID

Outer Joins

I am trying to create an outer join between two tables in a query that
includes several other tables.

When I double-click on the Join line, it presents three join options:

1) ONLY records from table1 and table2 where join fields are equal
2) ALL values from table1 and ONLY records from table2 where join
fields are equal
3) ALL values from table2 and ONLY records from table1 where join
fields are equal

In my case, I want option 2 - all values from table1, and if there is
no match to table2, I want a blank to appear in the output.

When I select this option, I get the following error:

"Can't have outer joins if there are more than two tables in the
query."

How can I get around this, since there are other tables in my query?

Thanks.

Dennis Hancy
Eaton Corporation
Cleveland, OHThis sounds like a limitation of the GUI application you are using to create
the query. Use Query Analyzer instead. Perhaps you can paste your SQL query
into QA and edit it.

Alternatively, if you are creating a view, Enterprise Manager's view
designer doesn't suffer from this particular restriction but it does have
other limitations so Query Analyzer is probably the best choice if you're
willing and able to write the query yourself.

--
David Portas
SQL Server MVP
--

Outer Join?

I am having troubles with a query that may have to use an Outer Join.
I would appreciate any help with this problem.
I will pass two parameters to the stored procedure.
@.OldStnType and @.NewStnType.
I want to see which records exist in @.OldStnType and not in
@.NewStnType. In this example:
set @.OldStnType='BUILD'
set @.NewStnType='SCHED'
Since only optionnbr 4 exists in Build and Not in Sched, I want to
return the record the following:
StnType OptionNbr
========== =============
BUILD 4
CREATE TABLE [dbo].[cfgStnOptionList] (
[StnType] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OptionNbr] [int] NOT NULL ,
[Description] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdated] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert into cfgStnOptionList values ('BUILD',3,'Station 1310
Option','Jan 2, 2005')
Insert into cfgStnOptionList values ('BUILD',4,'Test 4','Jan 1, 2005')
Insert into cfgStnOptionList values ('SCHED',1,'Marquee CommPort','Jan
1, 2005')
Insert into cfgStnOptionList values ('SCHED',2,'Scanner CommPort','Jan
1, 2005')
Insert into cfgStnOptionList values ('SCHED',3,'Station 12313','Jan 1,
2005')
Thank You,
Dave.Hi
These are untested, but try:
SELECT A.[StnType] , A.[OptionNbr]
FROM [dbo].[cfgStnOptionList] A
LEFT JOIN [dbo].[cfgStnOptionList] B ON A.[OptionNbr] = B.[OptionNbr] AND
B.[StnType] = @.NewStnType
WHERE B.[OptionNbr] IS NULL
AND A.[StnType] = @.OldStnType
Or
SELECT A.[StnType] , A.[OptionNbr]
FROM [dbo].[cfgStnOptionList] A
WHERE NOT EXISTS ( SELECT * FROM [dbo].[cfgStnOptionList] B WHERE
A.[OptionNbr] = B.[OptionNbr] AND B.[StnType] = @.NewStnType )
AND A.[StnType] = @.OldStnType
John
"Dave" wrote:

> I am having troubles with a query that may have to use an Outer Join.
> I would appreciate any help with this problem.
> I will pass two parameters to the stored procedure.
> @.OldStnType and @.NewStnType.
> I want to see which records exist in @.OldStnType and not in
> @.NewStnType. In this example:
> set @.OldStnType='BUILD'
> set @.NewStnType='SCHED'
> Since only optionnbr 4 exists in Build and Not in Sched, I want to
> return the record the following:
>
> StnType OptionNbr
> ========== =============
> BUILD 4
>
>
> CREATE TABLE [dbo].[cfgStnOptionList] (
> [StnType] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [OptionNbr] [int] NOT NULL ,
> [Description] [varchar] (25) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [LastUpdated] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> Insert into cfgStnOptionList values ('BUILD',3,'Station 1310
> Option','Jan 2, 2005')
> Insert into cfgStnOptionList values ('BUILD',4,'Test 4','Jan 1, 2005')
> Insert into cfgStnOptionList values ('SCHED',1,'Marquee CommPort','Jan
> 1, 2005')
> Insert into cfgStnOptionList values ('SCHED',2,'Scanner CommPort','Jan
> 1, 2005')
> Insert into cfgStnOptionList values ('SCHED',3,'Station 12313','Jan 1,
> 2005')
> Thank You,
> Dave.
>|||That worked great John!
Thank you for your help.
Dave.
On Tue, 10 May 2005 04:36:02 -0700, John Bell
<jbellnewsposts@.h0tmail.com> wrote:
>Hi
>These are untested, but try:
>SELECT A.[StnType] , A.[OptionNbr]
>FROM [dbo].[cfgStnOptionList] A
>LEFT JOIN [dbo].[cfgStnOptionList] B ON A.[OptionNbr] = B.[OptionNbr] AND
>B.[StnType] = @.NewStnType
>WHERE B.[OptionNbr] IS NULL
>AND A.[StnType] = @.OldStnType
>Or
>SELECT A.[StnType] , A.[OptionNbr]
>FROM [dbo].[cfgStnOptionList] A
>WHERE NOT EXISTS ( SELECT * FROM [dbo].[cfgStnOptionList] B WHERE
>A.[OptionNbr] = B.[OptionNbr] AND B.[StnType] = @.NewStnType )
>AND A.[StnType] = @.OldStnType
>John
>"Dave" wrote:
>sql

Monday, March 26, 2012

outer join similar to Access

Ok fellas, I need your help. I have a crosstab query that I wrote in Access
that lists all my marketers regardless of their referrals. The outer joins
selects all rows from marketer table. Now in SSRS I recreated the same query,
setup the matrix and populated it correctly. Problem is, that if the marketer
had no referrals, their name does not show up. I assumed that the outer join
would take care of that. Do I need to alter the query in some way to treat
nulls as zeros? Its a very simple query but I cannot determine where to put
the conditioning. Many thanks.isnull(numofreferralscolumn, 0) as numofreferralscolumn
in the select would be the easiest way.
--
Michael Abair
Programmer / Analyst
Chicos FAS Inc.
"Brian L" <BrianL@.discussions.microsoft.com> wrote in message
news:D5DBC2BE-55FA-413E-97A9-99A262C48EAC@.microsoft.com...
> Ok fellas, I need your help. I have a crosstab query that I wrote in
> Access
> that lists all my marketers regardless of their referrals. The outer joins
> selects all rows from marketer table. Now in SSRS I recreated the same
> query,
> setup the matrix and populated it correctly. Problem is, that if the
> marketer
> had no referrals, their name does not show up. I assumed that the outer
> join
> would take care of that. Do I need to alter the query in some way to treat
> nulls as zeros? Its a very simple query but I cannot determine where to
> put
> the conditioning. Many thanks.|||or you might need to do some more hocus-pocus.. i have to do things
like this to get 0 members showing up in some cubes for example
say you got a table that lists OrderTotals.. right?
Select CustomerID, OrderID, OrderTotalA, OrderTotalB From OrderTotals
UNION ALL
Select CustomerID, 0,0,0 From Customers
Michael Abair wrote:
> isnull(numofreferralscolumn, 0) as numofreferralscolumn
> in the select would be the easiest way.
> --
> Michael Abair
> Programmer / Analyst
> Chicos FAS Inc.
>
> "Brian L" <BrianL@.discussions.microsoft.com> wrote in message
> news:D5DBC2BE-55FA-413E-97A9-99A262C48EAC@.microsoft.com...
> > Ok fellas, I need your help. I have a crosstab query that I wrote in
> > Access
> > that lists all my marketers regardless of their referrals. The outer joins
> > selects all rows from marketer table. Now in SSRS I recreated the same
> > query,
> > setup the matrix and populated it correctly. Problem is, that if the
> > marketer
> > had no referrals, their name does not show up. I assumed that the outer
> > join
> > would take care of that. Do I need to alter the query in some way to treat
> > nulls as zeros? Its a very simple query but I cannot determine where to
> > put
> > the conditioning. Many thanks.

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 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

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

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

Outer Join Problem - hardest query ever?

Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.
If you post your DDL and sample data, we can test a solution.
http://www.aspfaq.com/etiquette.asp?id=5006
When you post your DDL and sample data, I'll have a go at it.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"BlackKnight" <BlackKnight@.discussions.microsoft.com> wrote in message
news:980AE47D-8CA7-42A1-BFAC-1139F550DD76@.microsoft.com...
Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.
|||On May 3, 4:19 pm, BlackKnight <BlackKni...@.discussions.microsoft.com>
wrote:
> Hi - I'm struggling with a query, which is as follows.
> (I have changed the context slightly for simplicity)
> I have 4 tables: users, scores, trials, tests
> Each pair of users takes a series of upto 4 tests in 1 trial, getting a
> score for each test.
> There are a different numbers of trials for each pair of users.
> In detail the tables are:
> Users - userid(primary,int), name(varchar)
> Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
> testid(int), score(int)
> Trials - trialid(primary,int), attempt(int), location(varchar)
> Tests - testid(primary,int), testname(varchar)
> Important: Users do not take all tests.
> EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
> test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
> userB's 1st attempt.
> TrialId 2 may be the same, but their 2nd attempt.
> TrialId 3 may be the 1st attempt for 2 different users etc.
> Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
> There are always 2 users for each trial id.
> I want a query which will return all scores for all users for all trials,
> BUT must include NULLs if a user did not take a test on that trial.
> I thought it may involve a cross join between the Tests table and the Trials
> table.
> Any help greatly appreciated.
I think you are looking for this . Not tested
Select a.name,a.userid,a.testid,a.testname,
b.score,c.attempt,c.location
from
( select userid,name from users
cross join
select testid,testname from tests ) as a
left outer join scores b
on a.userid = b.userid
and a.testid = b.testid
left outer join trials c
on b.trialid = c.trialid

Outer Join Problem - hardest query ever?

Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.If you post your DDL and sample data, we can test a solution.
http://www.aspfaq.com/etiquette.asp?id=5006
When you post your DDL and sample data, I'll have a go at it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"BlackKnight" <BlackKnight@.discussions.microsoft.com> wrote in message
news:980AE47D-8CA7-42A1-BFAC-1139F550DD76@.microsoft.com...
Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.|||On May 3, 4:19 pm, BlackKnight <BlackKni...@.discussions.microsoft.com>
wrote:
> Hi - I'm struggling with a query, which is as follows.
> (I have changed the context slightly for simplicity)
> I have 4 tables: users, scores, trials, tests
> Each pair of users takes a series of upto 4 tests in 1 trial, getting a
> score for each test.
> There are a different numbers of trials for each pair of users.
> In detail the tables are:
> Users - userid(primary,int), name(varchar)
> Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
> testid(int), score(int)
> Trials - trialid(primary,int), attempt(int), location(varchar)
> Tests - testid(primary,int), testname(varchar)
> Important: Users do not take all tests.
> EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
> test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
> userB's 1st attempt.
> TrialId 2 may be the same, but their 2nd attempt.
> TrialId 3 may be the 1st attempt for 2 different users etc.
> Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
> There are always 2 users for each trial id.
> I want a query which will return all scores for all users for all trials,
> BUT must include NULLs if a user did not take a test on that trial.
> I thought it may involve a cross join between the Tests table and the Trials
> table.
> Any help greatly appreciated.
I think you are looking for this . Not tested
Select a.name,a.userid,a.testid,a.testname,
b.score,c.attempt,c.location
from
( select userid,name from users
cross join
select testid,testname from tests ) as a
left outer join scores b
on a.userid = b.userid
and a.testid = b.testid
left outer join trials c
on b.trialid = c.trialidsql

Outer Join Problem - hardest query ever?

Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.If you post your DDL and sample data, we can test a solution.
http://www.aspfaq.com/etiquette.asp?id=5006
When you post your DDL and sample data, I'll have a go at it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"BlackKnight" <BlackKnight@.discussions.microsoft.com> wrote in message
news:980AE47D-8CA7-42A1-BFAC-1139F550DD76@.microsoft.com...
Hi - I'm struggling with a query, which is as follows.
(I have changed the context slightly for simplicity)
I have 4 tables: users, scores, trials, tests
Each pair of users takes a series of upto 4 tests in 1 trial, getting a
score for each test.
There are a different numbers of trials for each pair of users.
In detail the tables are:
Users - userid(primary,int), name(varchar)
Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
testid(int), score(int)
Trials - trialid(primary,int), attempt(int), location(varchar)
Tests - testid(primary,int), testname(varchar)
Important: Users do not take all tests.
EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA &
userB's 1st attempt.
TrialId 2 may be the same, but their 2nd attempt.
TrialId 3 may be the 1st attempt for 2 different users etc.
Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4)
There are always 2 users for each trial id.
I want a query which will return all scores for all users for all trials,
BUT must include NULLs if a user did not take a test on that trial.
I thought it may involve a cross join between the Tests table and the Trials
table.
Any help greatly appreciated.|||On May 3, 4:19 pm, BlackKnight <BlackKni...@.discussions.microsoft.com>
wrote:
> Hi - I'm struggling with a query, which is as follows.
> (I have changed the context slightly for simplicity)
> I have 4 tables: users, scores, trials, tests
> Each pair of users takes a series of upto 4 tests in 1 trial, getting a
> score for each test.
> There are a different numbers of trials for each pair of users.
> In detail the tables are:
> Users - userid(primary,int), name(varchar)
> Scores - scoreid(primary,int), userid(int), trialid(int), userid(int),
> testid(int), score(int)
> Trials - trialid(primary,int), attempt(int), location(varchar)
> Tests - testid(primary,int), testname(varchar)
> Important: Users do not take all tests.
> EG TrialId 1 contains userA & userB with userA scoring 10 on test1, 20 on
> test2 and userB scoring 30 on test2, 40 on test3, 50 on test4 and is userA
&
> userB's 1st attempt.
> TrialId 2 may be the same, but their 2nd attempt.
> TrialId 3 may be the 1st attempt for 2 different users etc.
> Suppose the Tests table has 4 tests (1,test1),(2,test2),(3,test3),(4,test4
)
> There are always 2 users for each trial id.
> I want a query which will return all scores for all users for all trials,
> BUT must include NULLs if a user did not take a test on that trial.
> I thought it may involve a cross join between the Tests table and the Tria
ls
> table.
> Any help greatly appreciated.
I think you are looking for this . Not tested
Select a.name,a.userid,a.testid,a.testname,
b.score,c.attempt,c.location
from
( select userid,name from users
cross join
select testid,testname from tests ) as a
left outer join scores b
on a.userid = b.userid
and a.testid = b.testid
left outer join trials c
on b.trialid = c.trialid

Outer join problem

This is the query I m executing.

SELECT M.MetroName Metro, R.Description Bank,
B.BranchCode + ' - ' + B.BranchName Branch, E.EmployeeLastName
Name FROM Metro M, Region R, Branch B, Employee E where
E.BranchID *= B.BranchID AND B.RegionID *= R.RegionID AND
R.MetroID *= M.MetroID AND E.ProductionNumber = '0000' AND E.ProductionNumber
Is Not Null

The error message I m getting is
Server: Msg 301, Level 16, State 1, Line 1
Query contains an outer-join request that is not permitted.

Try to help me asap.

Thx in advance
KKPTry to replace *= with LEFT OUTER JOIN. The issue there deals with the optimizer complaining about your request to LEFT JOIN a table that has already been LEFT JOINed.|||Originally posted by rdjabarov
Try to replace *= with LEFT OUTER JOIN. The issue there deals with the optimizer complaining about your request to LEFT JOIN a table that has already been LEFT JOINed.

Hi Rdja,

I tried that I made this as it is

SELECT M.MetroName Metro, R.Description Bank,
B.BranchCode + ' - ' + B.BranchName Branch, E.EmployeeLastName
Name FROM Metro M, Region R, Branch B, Employee E where
E.BranchID LEFT OUTER JOIN B.BranchID AND B.RegionID LEFT OUTER JOIN R.RegionID AND
R.MetroID LEFT OUTER JOIN M.MetroID AND E.ProductionNumber = '0000' AND E.ProductionNumber
Is Not Null

It gives back me the error
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'LEFT'.

Can u solve this?

Thx
KKP|||You need to look up and review the syntax of SELECT statements in Books Online.

SELECT M.MetroName Metro,
R.Description Bank,
B.BranchCode + ' - ' + B.BranchName Branch,
E.EmployeeLastName Name
FROM Metro M
left outer join Region R on M.MetroID = R.MetroID
left outer join Branch B on R.RegionID = B.RegionID
left outer join Employee E on B.BranchID = E.BranchID
where E.ProductionNumber = '0000'
AND E.ProductionNumber Is Not Null

blindman|||You'd code it like this

SELECT M.MetroName Metro,
R.Description 'Bank',
B.BranchCode + ' - ' + B.BranchName Branch,
E.EmployeeLastName 'Name'
FROM Metro AS M
LEFT OUTER JOIN Region AS R ON R.MetroID = M.MetroID
LEFT OUTER JOIN Branch AS B ON B.RegionID = R.RegionID
LEFT OUTER JOIN Employee AS E ON E.BranchID = B.BranchID
WHERE E.ProductionNumber = '0000'
AND E.ProductionNumber IS NOT NULL

But that WHERE clause basically undoes the effect of all those LEFT OUTER JOINS so I not sure what you're trying to accomplish here. I mean it seems pointless to use LEFT OUTER to preserve the rows in the Metro, Region, and Branch tables that don't have matching Employee rows if you're going to turn around and throw them away with the where clause. Besides, the two conditions E.ProductionNumber = '0000' and E.ProductionNumber IS NOT NULL are redundant. You don't need the later, as if E.ProductionNumber is '0000', it is of necessity not null.

Can you explain your desired result? That might help us translate it into the correct SQL, as we really don't know what it is you're trying to get.|||Yes, logically you should either convert your outer joins to inner joins, or move the criteria to the join as well:

SELECT M.MetroName Metro,
R.Description Bank,
B.BranchCode + ' - ' + B.BranchName Branch,
E.EmployeeLastName Name
FROM Metro M
left outer join Region R on M.MetroID = R.MetroID
left outer join Branch B on R.RegionID = B.RegionID
left outer join Employee E on B.BranchID = E.BranchID
and E.ProductionNumber = '0000'
and E.ProductionNumber Is Not Null|||Steve and Blindman

Thx for the reply.
Steve : My objective of the query is to select Metroname, Description,
Lastname and Branch code , name from four diff tables based on the
criterias MetroIds, RegionIds and BranchIds. Reg the production number
it is a four digit number in Employee table with diff values.
I should take both factors '0000' and null so I added both the constraints in the where clause.

You see my first query and try to tell me the error.

SELECT M.MetroName Metro, R.Description Bank, B.BranchCode + ' - ' + B.BranchName Branch,
E.EmployeeLastName Name FROM Metro M, Region R, Branch B, Employee E WHERE
E.BranchID *= join B.BranchID AND B.RegionID *= R.RegionID AND R.MetroID *= M.MetroID AND E.ProductionNumber = '0000' AND E.ProductionNumber Is Not Null

Error mesg : Server: Msg 301, Level 16, State 1, Line 1
Query contains an outer-join request that is not permitted.

so I added left outer joins. Lemme know if I have confused you more.
waiting for ur reply.

Thx
KK|||AH, OK, I think I have it now.

I think you need IS NULL instead of IS NOT NULL. Also, since you're using LEFT OUTER joins, those selection criteria need to be in the JOIN clause. I think you want something like tihis

SELECT M.MetroName Metro,
R.Description 'Bank',
B.BranchCode + ' - ' + B.BranchName Branch,
E.EmployeeLastName 'Name'
FROM Metro AS M
LEFT OUTER JOIN Region AS R ON R.MetroID = M.MetroID
LEFT OUTER JOIN Branch AS B ON B.RegionID = R.RegionID
LEFT OUTER JOIN Employee AS E ON E.BranchID = B.BranchID
AND (E.ProductionNumber = '0000'
OR E.ProductionNumber IS NULL)

Whether to put a condition like column IS NULL in the WHERE or JOIN clause depends on what you're trying to achieve. To use an OUTER JOIN to find rows that don't have matches in another table, you typically put the condition in a WHERE clause and use a column that won't naturally have NULLs. If, as I think you are saying you need, you only want to consider joining rows that have NULL (or possibly other values as well) but still need to preserve the previous tables even if there isn't a qualifying match, then you need to put the condition in the JOIN clause. This will make the OUTER JOIN function correctly and return nulls for the final table, even if it has rows that would typically match a normal join, but don't match you extra criteria.

outer join oracle query translate in sql server

Hi,
I have this oracle query with outer join situation. how can i convert it into sql server query.

SELECT distinct ae.dB_CONTRACT,CP.PC_CODE,BID_ITEM.ITEM_NO,
'N',BID_ITEM.PRICE_WORDS,OFF_ITEM.DESCPT,
OFF_ITEM.UNITS,OFF_ITEM.TYPE_ITEM,
PRES_ITEM.RET_PERC
FROM BID_TOTAL,BID_ITEM,OFF_ITEM,PRES_ITEM, AE_CONTRACT AE, CONTRACT_PC CP
WHERE RANK_NUMB = 1
AND BID_TOTAL.DB_CONTRACT = 37044
AND BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
AND BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
AND OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO (+)
AND AE.DB_CONTRACT=BID_TOTAL.DB_cONTRACT
AND CP.DB_CONTRACT = AE.DB_CONTRACT
AND CP.pc_code = 1

Any Help will be appreciated.I thhink this is it

SELECT DISTINCT
ae.dB_CONTRACT
, CP.PC_CODE
, BID_ITEM.ITEM_NO
, 'N'
, BID_ITEM.PRICE_WORDS
, OFF_ITEM.DESCPT
, OFF_ITEM.UNITS
, OFF_ITEM.TYPE_ITEM
, PRES_ITEM.RET_PERC
FROM BID_TOTAL
JOIN BID_ITEM
ON BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
JOIN OFF_ITEM
ON BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
LEFT JOIN PRES_ITEM
ON OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO
JOIN AE_CONTRACT AE
ON AE.DB_CONTRACT = BID_TOTAL.DB_cONTRACT
JOIN CONTRACT_PC CP
ON CP.DB_CONTRACT = AE.DB_CONTRACT
WHERE RANK_NUMB = 1
AND BID_TOTAL.DB_CONTRACT = 37044
AND CP.pc_code = 1

Test it out...|||Hi,
thank you very much..it worked excellent

Originally posted by Brett Kaiser
I thhink this is it

SELECT DISTINCT
ae.dB_CONTRACT
, CP.PC_CODE
, BID_ITEM.ITEM_NO
, 'N'
, BID_ITEM.PRICE_WORDS
, OFF_ITEM.DESCPT
, OFF_ITEM.UNITS
, OFF_ITEM.TYPE_ITEM
, PRES_ITEM.RET_PERC
FROM BID_TOTAL
JOIN BID_ITEM
ON BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT
AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR
JOIN OFF_ITEM
ON BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO
AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT
LEFT JOIN PRES_ITEM
ON OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO
JOIN AE_CONTRACT AE
ON AE.DB_CONTRACT = BID_TOTAL.DB_cONTRACT
JOIN CONTRACT_PC CP
ON CP.DB_CONTRACT = AE.DB_CONTRACT
WHERE RANK_NUMB = 1
AND BID_TOTAL.DB_CONTRACT = 37044
AND CP.pc_code = 1

Test it out...|||if your oracle install is 9i
the ansi join syntax actually works
after 20 years!!!!!

Friday, March 30, 2012

Output column size limit on query analyzer

Output column size limit on query analyzer

OUTPUT Clause SQL Server 2005

OUTPUT Clause SQL Server 2005

OUTPUT Clause SQL Server 2005

Output an SQL Query to a text file

Wednesday, March 28, 2012

outerjoint

Outer Joins

Outer Join?

Monday, March 26, 2012

outer join similar to Access

Outer join query

Outer Join qry help!

Outer Join qry help!

Outer Join qry help!

Outer Join Problem - hardest query ever?

Outer Join Problem - hardest query ever?

Outer Join Problem - hardest query ever?

Outer Join Problem - hardest query ever?

Outer join problem

outer join oracle query translate in sql server

Orphaned Sessions and locks

Blog Archive

About Me