HELP! Unable to select database engine in management studio

Hi All,

I am new to SQL Server and having trouble using SQL Server Management Studio. I am unable to select the Database Engine in management studio.

I am able to see the instance of default database engine (MSSQLServer) running in Reporting Services manager as well as in Surface area configuration manager, but it is not visible in the drop down list in Management Studio's "Select Database Engine" menu.

I had removed Sql server 2005 earlier ( I was able to select the database engine in Management Studio then). But when I installed it again, I was unable to install the Sql Server Tools (it said that my Upgrade is blocked). So, I cleaned the Windows Registry of all keys containing 'Sql'. After this I tried installing it again and successfully installed Sql Server 2005 + ALL TOOLS. But this time I am unable to select the database engine in management studio.

Thanks and Regards to ALL

You may need to rebuild the machine from scratch. Did you install any beta version of the 2.0 framework? If so a complete rebuild of the machine may be required.|||

Hi TATWORTH,

Thanks for the reply.

I may not wish to rebuild the machine as it is a triple boot system with 2000/xp/suse 10.1. If I reinstall XP, I may have to reinstall linux...

Is there any alternative?

Regards

|||Try installing SQL Server again - not as a reinstall of the default instance but as a new instance.|||

Ok.

Will reinstall as a seperate instance and tell you.

Regards

HELP! Dynamic binding of schema element names

Hi all,
I'm trying to achieve the following using SQL and SQLServer2000 is the db
I'm using.
Here's a simple select statement
SELECT column1, column2 FROM my_table WHERE some_condition = 1
What I want to be able to do is bind the name my_table to an actual
tablename during runtime, i.e. when the query executes.
The equivalent effect of what I'd like can be represented as below:
SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition = 1.
Here get_my_table_name( ) is a function that evaluates and returns the table
name. Things don't work this way however.
Is there a way to accomplish this?
Any replies are greatly appreciated.
Thanks in advance,
--Abhi[posted and mailed, please reply in news]
Abhijith Das (adas@.expeditevcs.com) writes:
> I'm trying to achieve the following using SQL and SQLServer2000 is the db
> I'm using.
> Here's a simple select statement
> SELECT column1, column2 FROM my_table WHERE some_condition = 1
> What I want to be able to do is bind the name my_table to an actual
> tablename during runtime, i.e. when the query executes.
> The equivalent effect of what I'd like can be represented as below:
> SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition => 1.
> Here get_my_table_name( ) is a function that evaluates and returns the
> table name. Things don't work this way however. Is there a way to
> accomplish this?
Yes. But it is unlikely that it is the right thing to do. Since I don't
know your underlying problem, I cannot suggest a solution here and now.
But this article on my web site, both describes on how you can achieve
this - and why you most probably should not do it anyway.
http://www.sommarskog.se/dynamic_sql.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Hi ,
No you can't do that that way. You must use dynamic SQL something like this
Declare @.SQL varchar(1000)
set @.SQL = 'SELECT column1, column2 FROM ' + 'my_table' + ' WHERE
some_condition = 1'
exec @.SQL
--
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Abhijith Das" <adas@.expeditevcs.com> wrote in message
news:u400jnbqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I'm trying to achieve the following using SQL and SQLServer2000 is the db
> I'm using.
> Here's a simple select statement
> SELECT column1, column2 FROM my_table WHERE some_condition = 1
> What I want to be able to do is bind the name my_table to an actual
> tablename during runtime, i.e. when the query executes.
> The equivalent effect of what I'd like can be represented as below:
> SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition => 1.
> Here get_my_table_name( ) is a function that evaluates and returns the
> table name. Things don't work this way however.
> Is there a way to accomplish this?
> Any replies are greatly appreciated.
> Thanks in advance,
> --Abhi
>

HELP! Dynamic binding of schema element names

Hi all,
I'm trying to achieve the following using SQL and SQLServer2000 is the db
I'm using.
Here's a simple select statement
SELECT column1, column2 FROM my_table WHERE some_condition = 1
What I want to be able to do is bind the name my_table to an actual
tablename during runtime, i.e. when the query executes.
The equivalent effect of what I'd like can be represented as below:
SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition = 1.
Here get_my_table_name( ) is a function that evaluates and returns the table
name. Things don't work this way however.
Is there a way to accomplish this?
Any replies are greatly appreciated.
Thanks in advance,
--Abhi
Hi ,
No you can't do that that way. You must use dynamic SQL something like this
Declare @.SQL varchar(1000)
set @.SQL = 'SELECT column1, column2 FROM ' + 'my_table' + ' WHERE
some_condition = 1'
exec @.SQL
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Abhijith Das" <adas@.expeditevcs.com> wrote in message
news:u400jnbqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I'm trying to achieve the following using SQL and SQLServer2000 is the db
> I'm using.
> Here's a simple select statement
> SELECT column1, column2 FROM my_table WHERE some_condition = 1
> What I want to be able to do is bind the name my_table to an actual
> tablename during runtime, i.e. when the query executes.
> The equivalent effect of what I'd like can be represented as below:
> SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition =
> 1.
> Here get_my_table_name( ) is a function that evaluates and returns the
> table name. Things don't work this way however.
> Is there a way to accomplish this?
> Any replies are greatly appreciated.
> Thanks in advance,
> --Abhi
>
|||[posted and mailed, please reply in news]
Abhijith Das (adas@.expeditevcs.com) writes:
> I'm trying to achieve the following using SQL and SQLServer2000 is the db
> I'm using.
> Here's a simple select statement
> SELECT column1, column2 FROM my_table WHERE some_condition = 1
> What I want to be able to do is bind the name my_table to an actual
> tablename during runtime, i.e. when the query executes.
> The equivalent effect of what I'd like can be represented as below:
> SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition =
> 1.
> Here get_my_table_name( ) is a function that evaluates and returns the
> table name. Things don't work this way however. Is there a way to
> accomplish this?
Yes. But it is unlikely that it is the right thing to do. Since I don't
know your underlying problem, I cannot suggest a solution here and now.
But this article on my web site, both describes on how you can achieve
this - and why you most probably should not do it anyway.
http://www.sommarskog.se/dynamic_sql.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Hi all,
Somehow I lost the thread where I originally posted this query. Thanks
Erland and GregO for your answers. Very helpful indeed.
Although I think I can solve my problem with what you guys suggested, I'd
really like to know your opinion and maybe offer solutions
of achieving my goal. Let me put my problem forth in more detail:
My application has all it's logic in a COM component and all SQL queries are
issued from there.
The application itself was never designed with security and access-control
in mind
(which I'm cursing it for and have to incorporate now :-(( ). So, now I have
a
few thousand queries that I don't want to affect drastically.
I have a bunch of users in a [USER] table and a bunch of resources in a
[RESOURCE] table. What ultimately should happen is that
every user should be able to see resources only entitled to her based on
some security policy.
Here is how I think it can be done.
A new entity [SEC_GROUP] can be introduced where each user is part of one or
more security groups. For each security group,
I will create a view on the [RESOURCE] table: [admingrp_resource],
[generaluser_resource] and so on.
So, when a user issues a query like,
SELECT * FROM [RESOURCE]
I will actually substitute [RESOURCE] with a function like
get_resource_view(userid)
EXEC( 'SELECT * FROM ' + get_resource_view(userid))
The get_resource_view( ) function would get the appropriate resource view
for the user based on her security group.
The above would be rather easy if the user is part of only one security
group. If there are more, I might have to do
some unions. This would impact the queries quite a bit, but I can't think of
another way to implement this.
Once again, thanks for your responses, I appreciate your help and looking
forward to more suggestions.
Best regards,
--Abhi
Abhijith Das (adas@.expeditevcs.com) writes:
> I'm trying to achieve the following using SQL and SQLServer2000 is the db
> I'm using.
> Here's a simple select statement
> SELECT column1, column2 FROM my_table WHERE some_condition = 1
> What I want to be able to do is bind the name my_table to an actual
> tablename during runtime, i.e. when the query executes.
> The equivalent effect of what I'd like can be represented as below:
> SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition =
> 1.
> Here get_my_table_name( ) is a function that evaluates and returns the
> table name. Things don't work this way however. Is there a way to
> accomplish this?
Yes. But it is unlikely that it is the right thing to do. Since I don't
know your underlying problem, I cannot suggest a solution here and now.
But this article on my web site, both describes on how you can achieve
this - and why you most probably should not do it anyway.
http://www.sommarskog.se/dynamic_sql.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Abhijith Das (adas@.expeditevcs.com) writes:
> My application has all it's logic in a COM component and all SQL queries
> are issued from there. The application itself was never designed with
> security and access-control in mind (which I'm cursing it for and have
> to incorporate now :-(( ). So, now I have a few thousand queries that I
> don't want to affect drastically.
> I have a bunch of users in a [USER] table and a bunch of resources in a
> [RESOURCE] table. What ultimately should happen is that every user
> should be able to see resources only entitled to her based on some
> security policy. Here is how I think it can be done. A new entity
> [SEC_GROUP] can be introduced where each user is part of one or more
> security groups. For each security group, I will create a view on the
> [RESOURCE] table: [admingrp_resource], [generaluser_resource] and so
> on.
First of all, for this to be meaningful, you need to revoke access to
the tables from the users. Keep in mind that there are other means to
connecting to SQL Server, and a skilled user could for instance use
Query Analyzer to access the data.

> So, when a user issues a query like,
> SELECT * FROM [RESOURCE]
> I will actually substitute [RESOURCE] with a function like
> get_resource_view(userid)
> EXEC( 'SELECT * FROM ' + get_resource_view(userid))
But why do you want to have this function in SQL? Since you apparently
have all your logic client-side, why not stick to that? Depending on
the size of the data stored for these security groups, you could read
this data once, and keep it in memory. (May need some refresh mechanism
in case the security configuration is changed.)
From this follows that the user will need to have SELECT access to
the table what defines the security groups and the resources. (Unless
you use an application role.)

> The get_resource_view( ) function would get the appropriate resource
> view for the user based on her security group. The above would be rather
> easy if the user is part of only one security group. If there are more,
> I might have to do some unions. This would impact the queries quite a
> bit, but I can't think of another way to implement this.
A common approach to row-level security is to have views that includes
conditions like:
AND userid = SYSTEM_USER
Although one should be aware of that a skilled user with a query tool can
still be able to carve out glimpses of data he is not intended to see.
For a more complex security scheme, you could have table-valued
functions, but you would have to one for each base table.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

HELP! Concatinated Values

Hello everyone,

I would really appreciate if someone could help me out with this one.

I need to execute a select statement which returns more than one row but I need the values from all the rows returned in a single string.

For example

SELECT * FROM USERS would produce

ID NAME
1 Jason
2 Mark
3 Whatever

I need the returned value to be a string with 'Jason,Mark,Whatever' as a returned result.

Any thoughts anyone?

RegardsI would look towards using a cursor in a stored procedure to loop through each row and concatenating the values one by one.

I'm no big fan of serverside cursors, even if they have their time and place too, so depending on the environment and application demands I would look into putting such logic in the middle tier or even client side.

Cheers,
Robert|||Originally posted by Rawbat
I would look towards using a cursor in a stored procedure to loop through each row and concatenating the values one by one.

I'm no big fan of serverside cursors, even if they have their time and place too, so depending on the environment and application demands I would look into putting such logic in the middle tier or even client side.

Cheers,
Robert

declare @.name varchar(40),@.result varchar(500)
declare Cursor1 cursor
for
SELECT * FROM USERS
open Cursor1
fetch from Cursor1 into @.name
Set @.result=@.name
while(@.@.fetch_status=0)
begin
set @.result = ','+@.name
fetch from Cursor1 into @.name
end

HELP! - SQL Statement does not work!

select d.dname, count(s.staffid) scount
from tstaff s, tdept d
where s.deptid = d.deptid
group by d.dname
having scount > (select avg(count(s.staffid))
from tstaff s
group by s.staffid)
;

can anyone tell me why the above statement does not run. I am getting the following errors:

ERROR at line 5:
ORA-00904: invalid column name

I am trying to get the name of the department and the number of staff who have a higher than average number of staff assigned to that department... any suggestions!?scount is a column alias so you might want to put the actual expression into the HAVING clause

however, there's still a problem

the subquery in the HAVING clause is not scalar, i.e. it can return more than one value

rudy|||Originally posted by r937
scount is a column alias so you might want to put the actual expression into the HAVING clause

however, there's still a problem

the subquery in the HAVING clause is not scalar, i.e. it can return more than one value

rudy

Thanks! I will try that.|||Originally posted by r937
scount is a column alias so you might want to put the actual expression into the HAVING clause

however, there's still a problem

the subquery in the HAVING clause is not scalar, i.e. it can return more than one value

rudy

Hi,

I tried what you suggested but I am still getting problems. Is there an easier way of comparing the number of staff in each department to the overall average across all departments?!

C.|||the error probably wasn't the alias, then -- like i said, your subquery wasn't scalar :)
select d.dname, count(s.staffid) scount
from tstaff s, tdept d
where s.deptid = d.deptid
group by d.dname
having count(s.staffid) >
( select avg(deptcount)
from ( select d.deptid, count(*) as deptcount
from tstaff s, tdept d
where s.deptid = d.deptid
group by d.deptid ) as deptcounts
)
caution: untested|||Originally posted by r937
the error probably wasn't the alias, then -- like i said, your subquery wasn't scalar :)
select d.dname, count(s.staffid) scount
from tstaff s, tdept d
where s.deptid = d.deptid
group by d.dname
having count(s.staffid) >
( select avg(deptcount)
from ( select d.deptid, count(*) as deptcount
from tstaff s, tdept d
where s.deptid = d.deptid
group by d.deptid ) as deptcounts
)
caution: untested

cheers! I will give it a go - thanks a million.

HELP! - 8060 limit/select statement

BTW, I'm using SQL Server 2000
Thanks again...
"segis bata" <segisbata@.hotmail.com> wrote in message
news:%23wY31UQdIHA.4744@.TK2MSFTNGP06.phx.gbl...
> Hello all,
> I want to know if it's possible to make a select statement that brings
> only the records with a number of bytes of less than 8060
> so, imagine I have a table with 10 records, and two of those ten records
> have more than 8060 bytes, so, if I do this:
> select a, b, c, d from tableX
> where (len(a)+len(b)+len(c)+len(d) < 8060)
> it will only bring 8 records, not 10
> I tried this approach and it doesn't work, so, my question is, is there a
> way (similar to this) using a select statement to limit the results to
> those records with less than 8060 bytes, so I will never get the error?
> Thanks again for all your help!,
> SB-R
On Feb 22, 8:43Xam, "segis bata" <segisb...@.hotmail.com> wrote:
> BTW, I'm using SQL Server 2000
> Thanks again...
> "segis bata" <segisb...@.hotmail.com> wrote in message
> news:%23wY31UQdIHA.4744@.TK2MSFTNGP06.phx.gbl...
>
>
>
>
>
> - Show quoted text -
Dear Segis,
It is not possible to trim the record size using a where predicate. If
you are using table which has record length more than 8060 bytes then
it should be having varchar column. If so...on the varchar column use
substring function to trim the data above 8060 bytes. Hope this
suggestion helps.
Regards
Balaji
|||The LEN() function returns the number of characters, not the number of
bytes. DATALENGTH() returns the number of bytes. If you have any
NVARCHAR columns, which use two bytes per character, you need to use
DATALENGTH().
Also note that there is overhead to the row that is not captured with
the expression you have. Part of that is two bytes for each varying
length column.
Roy Harvey
Beacon Falls, CT
On Thu, 21 Feb 2008 22:43:49 -0500, "segis bata"
<segisbata@.hotmail.com> wrote:

>BTW, I'm using SQL Server 2000
>Thanks again...
>
>"segis bata" <segisbata@.hotmail.com> wrote in message
>news:%23wY31UQdIHA.4744@.TK2MSFTNGP06.phx.gbl...
>

Help writing SQL

I want to select records where a column is null, is not null or is equal to a specific number. How do I do this in one sql statement. My application is an asp.net web site with a business logic layer, a data access layer and sql server. Thank you in advance.select *
from tb
where isnull(col,'*')=isnull(@.search,'*')

help writing s.proc in sql2005

hi all.

in my sql2005 i have a function that returns a value. func(x) returns j

how can use it in a select clause inside a s.proce?

select bb, func(xx) as jj , from ....

?

You should be able to include it right in your SELECT statement, since it returns a scalar.

You will, however, need to qualify the function with the schema; SELECT dbo.func(xx) or SELECT myschema.func(xx) etc.

Help woth cursor!

Hi,
I have a stored procedure which cycles through a select statement and loads
the results into a cursor. It then sends an email off for each result. I
was wondering if it was possible to group all the results into one email?
My stored procedure is below for reference:-
OPEN surveillance_cursor
FETCH NEXT FROM surveillance_cursor
INTO @.REG_NO, @.URN, @.OFFICER, @.REVIEW_DATE, @.OFFICER_EMAIL
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
IF @.OFFICER_EMAIL IS NOT NULL
BEGIN
select @.recipient = LTRIM(RTRIM(@.OFFICER_EMAIL))
select @.sbj = 'List of Renewal Dates'
select @.msg = 'Reg No:- ' + @.REG_NO + ', ' + 'URN:- ' + @.URN + ', ' +
'Officer:- ' + @.OFFICER + ', ' + 'Review Date:- ' + @.REVIEW_DATE
exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
@.message=@.msg
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM surveillance_cursor
INTO @.REG_NO, @.URN, @.OFFICER, @.REVIEW_DATE, @.OFFICER_EMAIL
END
CLOSE surveillance_cursor
DEALLOCATE surveillance_cursor
GO
Any help on this would be greatly appreciated
Thanks
Damon> I have a stored procedure which cycles through a select statement and
> loads the results into a cursor. It then sends an email off for each
> result. I was wondering if it was possible to group all the results into
> one email?
You'll have to be more specific. Do you mean one e-mail for each unique
@.officer_email, or one e-mail total?|||Create another variable e.g @.allMSG , keep adding the data for every
cursor and then do the "exec sp_sendmail after the cursor has finished.
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"Damon" <nonsense@.nononsense.com> wrote in message
news:2F4Ef.71406$zt1.64049@.newsfe5-gui.ntli.net...
> Hi,
> I have a stored procedure which cycles through a select statement and
loads
> the results into a cursor. It then sends an email off for each result. I
> was wondering if it was possible to group all the results into one email?
> My stored procedure is below for reference:-
> OPEN surveillance_cursor
> FETCH NEXT FROM surveillance_cursor
> INTO @.REG_NO, @.URN, @.OFFICER, @.REVIEW_DATE, @.OFFICER_EMAIL
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
> WHILE @.@.FETCH_STATUS = 0
> IF @.OFFICER_EMAIL IS NOT NULL
> BEGIN
> select @.recipient = LTRIM(RTRIM(@.OFFICER_EMAIL))
> select @.sbj = 'List of Renewal Dates'
> select @.msg = 'Reg No:- ' + @.REG_NO + ', ' + 'URN:- ' + @.URN + ', ' +
> 'Officer:- ' + @.OFFICER + ', ' + 'Review Date:- ' + @.REVIEW_DATE
> exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
> @.message=@.msg
> -- This is executed as long as the previous fetch succeeds.
> FETCH NEXT FROM surveillance_cursor
> INTO @.REG_NO, @.URN, @.OFFICER, @.REVIEW_DATE, @.OFFICER_EMAIL
> END
> CLOSE surveillance_cursor
> DEALLOCATE surveillance_cursor
> GO
>
> Any help on this would be greatly appreciated
> Thanks
>
> Damon
>

Help with WHILE loop in a cursor

I have a cursor within a cursor which is like
Declare vendor_cursor cursor for
select distinct top 10 vendor_name from event_feed_view
Where vendor_id = @.vendor_id
Open vendor_cursor
Fetch Next from vendor_Cursor into @.vendor_name
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.vendor_feed = '<g:vendor>'+@.vendor_name+'</g:vendor>'
insert into temp_event_feed(xml_data) values (@.vendor_feed)
Fetch Next from vendor_Cursor into @.vendor_name
END
Close vendor_Cursor
Deallocate vendor_Cursor
The result I get here is printed in XML which is like
'<g:vendor>'+SHAWN M+'</g:vendor>'
'<g:vendor>'+MICHAEL L+'</g:vendor>'
'<g:vendor>'+DAWN K+'</g:vendor>'
'<g:vendor>'+LISA S+'</g:vendor>' and so on till 10
Since this is HTML i need my data to be in this format
'<g:vendor>'+SHAWN M+'</g:vendor>'
'<custom atribute: vendor1>'+MICHAEL L+<custom atribute: vendor1>
'<custom atribute: vendor2>'+DAWN K+<custom atribute: vendor2>
'<custom atribute: vendor3>'+LISA S+<custom atribute: vendor3>
till 10. There can be 10 or less or more vendors in the list
But I want only 10 in my HTML and the format should be like mentions
So I need to create a loop like do while count <=10
and create this html i would have to create a case if count = 1
then use this format:
<g:vendor>'+SHAWN M+'</g:vendor>'
if count is >1
then use the other format
'<custom atribute: vendor1>'+MICHAEL L+<custom atribute: vendor1>
and print 1 after vendor if count is 2, print 2 after vendor if count
is 3.
I hope this would be clear what I am looking for. Need to pout couple
of loops in there any suggesstion on this.Hi
Your TOP 10 clause should limit the number of rows returned to be 10
distinct vendors but you could try something like:
DECLARE @.cnt int
DECLARE vendor_cursor CURSOR FOR
SELECT DISTINCT TOP 10 vendor_name
FROM event_feed_view
WHERE vendor_id = @.vendor_id
OPEN vendor_cursor
FETCH NEXT FROM vendor_Cursor INTO @.vendor_name
SET @.cnt = 0
WHILE @.@.FETCH_STATUS = 0 AND @.cnt < 10
BEGIN
SET @.vendor_feed = CASE @.cnt WHEN 0 THEN
'<g:vendor>'+@.vendor_name+'</g:vendor>'
ELSE '<custom atribute: vendor' + CAST(@.cnt as varchar(2)) +
'>'+@.vendor_name+'</custom atribute: vendor' + CAST(@.cnt as varchar(2)) + '>'
END
INSERT INTO temp_event_feed(xml_data) VALUES (@.vendor_feed)
FETCH NEXT FROM vendor_Cursor INTO @.vendor_name
SET @.cnt = @.cnt + 1
END
CLOSE vendor_Cursor
DEALLOCATE vendor_Cursor
If you want to include '+' characters in the element value change it to:
'<g:vendor>+'+@.vendor_name+'+</g:vendor>'
John
"VJ" wrote:
> I have a cursor within a cursor which is like
> Declare vendor_cursor cursor for
> select distinct top 10 vendor_name from event_feed_view
> Where vendor_id = @.vendor_id
> Open vendor_cursor
> Fetch Next from vendor_Cursor into @.vendor_name
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.vendor_feed = '<g:vendor>'+@.vendor_name+'</g:vendor>'
> insert into temp_event_feed(xml_data) values (@.vendor_feed)
> Fetch Next from vendor_Cursor into @.vendor_name
> END
> Close vendor_Cursor
> Deallocate vendor_Cursor
>
> The result I get here is printed in XML which is like
>
> '<g:vendor>'+SHAWN M+'</g:vendor>'
> '<g:vendor>'+MICHAEL L+'</g:vendor>'
> '<g:vendor>'+DAWN K+'</g:vendor>'
> '<g:vendor>'+LISA S+'</g:vendor>' and so on till 10
> Since this is HTML i need my data to be in this format
>
> '<g:vendor>'+SHAWN M+'</g:vendor>'
> '<custom atribute: vendor1>'+MICHAEL L+<custom atribute: vendor1>
> '<custom atribute: vendor2>'+DAWN K+<custom atribute: vendor2>
> '<custom atribute: vendor3>'+LISA S+<custom atribute: vendor3>
> till 10. There can be 10 or less or more vendors in the list
> But I want only 10 in my HTML and the format should be like mentions
> So I need to create a loop like do while count <=10
> and create this html i would have to create a case if count = 1
> then use this format:
> <g:vendor>'+SHAWN M+'</g:vendor>'
> if count is >1
> then use the other format
> '<custom atribute: vendor1>'+MICHAEL L+<custom atribute: vendor1>
>
> and print 1 after vendor if count is 2, print 2 after vendor if count
> is 3.
> I hope this would be clear what I am looking for. Need to pout couple
> of loops in there any suggesstion on this.
>

Help with WHERE Clause in Stored Procedure

Hi,

I have an sp with the following WHERE clause

@.myqarep varchar(50)

SELECT tblCase.qarep FROM dbo.tblCase

WHERE dbo.tblCase.qarep = CASE @.myqarep WHEN '<All>' THEN
dbo.tblCase.qarep ELSE @.myqarep

@.myqarep is returned from a combo box (ms access)...the user either
picks a qarep from the combo box or they leave the default which is
'<All>'

they problem i'm having is that if the record's value for
dbo.tblCase.qarep is null...the record does not show up in the
results...but i need it to

any help is appreciated.

thanks
Paul... WHERE qarep = @.myqarep
OR @.myqarep = '<All>'

--
David Portas
SQL Server MVP
--|||thanks for the quick response...i'll give it a try!

David Portas wrote:
> ... WHERE qarep = @.myqarep
> OR @.myqarep = '<All>'
> --
> David Portas
> SQL Server MVP
> --

Help with Where

I help with my where statment, I need the Asset ID to return all valuse that are matching with the ESN Number or ID.

SELECT Asset.AssetId,
A.Description,
B.Barcode,
C.AssetDescription,
B.SKU,
J.EsnNumber,
G.WarehouseDescription,
I.TraceTime,
I.PreviousTraceTime,
I.HasMoved,
I.DistanceMiles,
I.Direction,
I.Landmark,
I.FemaLocation,
I.ReportTime,
I.ReplaceByDate,
I.CurrLocStreet,
I.CurrLocCity,
I.CurrLocState,
I.CurrLocZip,
I.CurrLocCounty,
I.CurrMileFromStratix,
I.PrevMileFromStratix
FROM AssetType As A
INNER JOIN Asset As B ON (A.AssetTypeId = B.AssetTypeId)
INNER JOIN AssetAttribute As C ON (A.AssetTypeId = C.AssetTypeId AND B.AssetAttributeId = C.AssetAttributeId)
INNER JOIN AssetVehicle As D ON (B.AssetId = D.AssetId)
INNER JOIN AssetCustomAttribute As E ON (B.AssetId = E.AssetId)
INNER JOIN AssetCustomAttributeDef As F ON (A.AssetTypeId = F.AssetTypeId AND E.AssetCustomAttributeDefId = F.AssetCustomAttributeDefId AND E.AssetTypeId = F.AssetTypeId)
INNER JOIN InventoryOrigin As G ON (B.WarehouseId = G.WarehouseId)
INNER JOIN EsnAsset As H ON (B.AssetId = H.AssetId)
INNER JOIN ESNTracking As I On (H.EsnID = I.EsnID)
INNER JOIN ESN As J ON (I.EsnId = J.EsnId)
WHERE B.AssetId = 'EsnNumber'

Hi,

I guess this is not enough information to help you, could you explain that in more detail ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Help with using Alias Fields in a expressions.

Using MSDE...(8.00.760 SP3)
I have the following query...I taking this from ms-access, but lets just
stick to using the EM here...
SELECT ProductName, SerialNumber, UnitPrice,
(SELECT SUM(UnitsAdd)
FROM inventory
WHERE ProductID = Products.ProductID) AS
TotalAddStock,
(SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductID = Products.ProductID) AS
TotalSoldStock
FROM dbo.Products
The problem is that I want to use the alias fields in a expression, and add
one more column to the above like:
(TotalAddStock - TotalSoldStock) as InStock.
In JET sql, I can simple use the above expression, and add it to the above
sql (ie: using those alias fields in expressions in the sql is legal).
However, In the EM, when I try to add the above expression...it says that
TotalAddStock is a not a valid field.
Is there some rule for using Alias fields?
Do I have to repeat the actual sql for those two sub-queries all over again
to make the expression of
( ("sub query repeated again") - ("sub query
repeated again") ) as InStock.
I have noticed that if I save the query (create a view),then I can certainly
write the following:
select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
(TotalAddStock - TotalSoldStock) as InStock
from myCoolView
However, this means I now have two sql views in place of what was one
before. It would be handy if I could throw in that one extra column.
Is creating two views here the recommend approach here? Any enlightenment on
using Alias fields in other column expressions is appreciated.
My best guess seems that you can't use a sub-query as a alias field in other
expressions...is this right?
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn
Albert,
Try nesting the statement this way:
select
ProductName,
SerialNumber,
UnitPrice,
TotalAddStock,
TotalSoldStock,
(TotalAddStock - TotalSoldStock) as InStock
from (
SELECT
ProductName,
SerialNumber,
UnitPrice,
(
SELECT SUM(UnitsAdd)
FROM inventory
WHERE ProductID = Products.ProductID
) AS TotalAddStock,
(
SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductID = Products.ProductID
) AS TotalSoldStock
FROM dbo.Products
) T
This accomplishes what you discovered worked with a view - here you
would be using a derived table, which is basically an ad-hoc view.
Steve Kass
Drew University
Albert D. Kallal wrote:

>Using MSDE...(8.00.760 SP3)
>I have the following query...I taking this from ms-access, but lets just
>stick to using the EM here...
>SELECT ProductName, SerialNumber, UnitPrice,
> (SELECT SUM(UnitsAdd)
> FROM inventory
> WHERE ProductID = Products.ProductID) AS
>TotalAddStock,
> (SELECT SUM(Quantity)
> FROM OrderDetails
> WHERE ProductID = Products.ProductID) AS
>TotalSoldStock
>FROM dbo.Products
>The problem is that I want to use the alias fields in a expression, and add
>one more column to the above like:
> (TotalAddStock - TotalSoldStock) as InStock.
>In JET sql, I can simple use the above expression, and add it to the above
>sql (ie: using those alias fields in expressions in the sql is legal).
>However, In the EM, when I try to add the above expression...it says that
>TotalAddStock is a not a valid field.
>Is there some rule for using Alias fields?
>Do I have to repeat the actual sql for those two sub-queries all over again
>to make the expression of
> ( ("sub query repeated again") - ("sub query
>repeated again") ) as InStock.
>I have noticed that if I save the query (create a view),then I can certainly
>write the following:
>select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
> (TotalAddStock - TotalSoldStock) as InStock
> from myCoolView
>However, this means I now have two sql views in place of what was one
>before. It would be handy if I could throw in that one extra column.
>Is creating two views here the recommend approach here? Any enlightenment on
>using Alias fields in other column expressions is appreciated.
>My best guess seems that you can't use a sub-query as a alias field in other
>expressions...is this right?
>
>
|||Both of these will, in all likelihood, force correlated subqueries--highly
inefficient. I would check the execution plans genereated to make sure.
Here's an alternative:
SELECT p.ProductName
,p.SerialNumber
,p.UnitPrice
,tas.TotalAddStock
,tss.TotalSoldStock
,InStock = (tas.TotalAddStock - tss.TotalSoldStock)
FROM dbo.Products AS p
INNER JOIN
(SELECT ProductID
,TotalAddStock = SUM(UnitsAdd)
FROM dbo.Inventory
GROUP BY ProductID
) AS tas
ON p.ProductID = tas.ProductID
(SELECT ProductID
,TotalSoldStock = SUM(Quantity)
FROM dbo.OrderDetails
GROUP BY ProductID
) AS tss
ON p.ProductID = tss.ProductID
Now, this too could be expensive in that the Inventory and/or OrderDetails
tables could be very large and creating the aggregates on all Products before
the JOIN restrictions could be CPU wasteful.
However, in this case, it looks like you already have an implicit RI
relationship between the Products, Inventory, and Order Details tables and,
with no restrictions on the final results, would have to calculate all those
aggregates anyway.
So, in this case, as long as the Optimizer would have choosen Lazy Spooling
for the first two examples, the solution provided here, a Set-Based example,
would be orders of magnitude faster.
Sincerely,
Anthony Thomas
"Steve Kass" wrote:

> Albert,
> Try nesting the statement this way:
> select
> ProductName,
> SerialNumber,
> UnitPrice,
> TotalAddStock,
> TotalSoldStock,
> (TotalAddStock - TotalSoldStock) as InStock
> from (
> SELECT
> ProductName,
> SerialNumber,
> UnitPrice,
> (
> SELECT SUM(UnitsAdd)
> FROM inventory
> WHERE ProductID = Products.ProductID
> ) AS TotalAddStock,
> (
> SELECT SUM(Quantity)
> FROM OrderDetails
> WHERE ProductID = Products.ProductID
> ) AS TotalSoldStock
> FROM dbo.Products
> ) T
> This accomplishes what you discovered worked with a view - here you
> would be using a derived table, which is basically an ad-hoc view.
> Steve Kass
> Drew University
> Albert D. Kallal wrote:
>
|||Interesting...but it seems that the query is optimized quite well. (I just
threw it into the Query analyzer).
It runs is less then one second for 125,000 invoice details and 2500
inventory records (stock additions), and 500 products.
Interesting, it also runs Steve's example in the same time..and thus the
query optimizer seems quite smart in this case. (that to me is rather
amazing...since I do have reasonable grasp of sql...and I can't believe
that sql server returns such good times for the kind of problems that don't
look at all that you can resolve those values together).
As for the joins,
I did have to put in a "left" join..since you were joining more then one
table here. So, I just changed the joins to:
LEFT OUTER JOIN
Anyway, I am surprised the engine does such a good job, considering you are
using a "group by" here..and that is joined to the "main" table. Often, jet
don't do such a good job in these cases. I should note that using the
sub-quires like I did does run very well in JET, and also sql server.
Anyway...thanks Anthony and Steve...
I am now temped to simply use a view here as per my original idea (I can
more easily "think" of my original syntax). However, I do "get" what you did
with the "group by"...and the fact that sql server seems to be more
forgiving for complex sql then is JET.
I certainly appreciate your answers, and in fact the two answers being so
different solutions also opens my mind up here...
Great stuff guys...and thanks!
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn
|||Glad I could help but you made a comment I'd like to follow up with, if I may.
You said, "I did have to put in a "left" join..since you were joining more
then one
table here. So, I just changed the joins to:
LEFT OUTER JOIN
"
The number of tables joined has nothing to do with the join types. They
were coded as INNER JOINS so that you'd only get matching records; that is,
records with ProductID's that existed in each of the joined tables. If you
use LEFT JOINs, you will get Products that do not have corresponding
Inventory or OrderDetail records, but then the values your are computing,
TotalAdd and TotalSold will be NULL for those record types.
The INNER JOINS will use the Primary Keys defined on the child and parent
tables when performing the join operation and will likely use a Cluster Index
Scan if you use the LEFT JOIN systax. Thus, it is more likely that the INNER
JOIN will give you better performance, especially if you want only the
records that will actually compute a value instead returning NULL results.
Also, I'm curious what the execution plan and run time were if you run the
snippet as I originally wrote it. I'm curious if the Optimizer was able to
resolve to the same execution plan. For this to be meaningful, you'll have
to use the multiple INNER JOINs because that was what the two original
queries where doing, in essence.
Thanks for helping me satisfy my curiosity.
Sincerely,
Anthony Thomas
"Albert D. Kallal" wrote:

> Interesting...but it seems that the query is optimized quite well. (I just
> threw it into the Query analyzer).
> It runs is less then one second for 125,000 invoice details and 2500
> inventory records (stock additions), and 500 products.
> Interesting, it also runs Steve's example in the same time..and thus the
> query optimizer seems quite smart in this case. (that to me is rather
> amazing...since I do have reasonable grasp of sql...and I can't believe
> that sql server returns such good times for the kind of problems that don't
> look at all that you can resolve those values together).
> As for the joins,
> I did have to put in a "left" join..since you were joining more then one
> table here. So, I just changed the joins to:
> LEFT OUTER JOIN
> Anyway, I am surprised the engine does such a good job, considering you are
> using a "group by" here..and that is joined to the "main" table. Often, jet
> don't do such a good job in these cases. I should note that using the
> sub-quires like I did does run very well in JET, and also sql server.
> Anyway...thanks Anthony and Steve...
> I am now temped to simply use a view here as per my original idea (I can
> more easily "think" of my original syntax). However, I do "get" what you did
> with the "group by"...and the fact that sql server seems to be more
> forgiving for complex sql then is JET.
> I certainly appreciate your answers, and in fact the two answers being so
> different solutions also opens my mind up here...
> Great stuff guys...and thanks!
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.attcanada.net/~kallal.msn
>
>
|||Ah,...yes...inner join does work!
(and...yes...some products might not have any invoice sold...so, likely I
will stick with left joins).
Anyway..here is the query plans for both:
http://www.attcanada.net/~kallal.msn/sqlplan/index.htm
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn
|||Glad I could help. Thanks for a peek at the EEP. You'll notice that the
Optimizer picked the same execution steps just changed the order of the joins.
It is concerning that that you are running Table Scans. This happens
because we are not limiting the results but aggregating all records. The
point is, you will have to touch every record. The fact you are doing Table
Scans indicates you have not defined a Clustered Index for any of these three
tables.
Every Table should have a Primary Key defined, for integrity and you are not
even in 1NF if you haven't defined a key, which means you do not have a
relational table. Bad mojo. Second, every Table should have a Clustered
Index defined. The reason being, this defines the actual data sort for
storage. Usually, the Primary Key and the Clustered Index are NOT the same,
at least with how many use them.
Sincerely,
Anthony Thomas
"Albert D. Kallal" wrote:

> Ah,...yes...inner join does work!
> (and...yes...some products might not have any invoice sold...so, likely I
> will stick with left joins).
> Anyway..here is the query plans for both:
> http://www.attcanada.net/~kallal.msn/sqlplan/index.htm
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.attcanada.net/~kallal.msn
>
>

Help with using Alias Fields in a expressions.

Using MSDE...(8.00.760 SP3)
I have the following query...I taking this from ms-access, but lets just
stick to using the EM here...
SELECT ProductName, SerialNumber, UnitPrice,
(SELECT SUM(UnitsAdd)
FROM inventory
WHERE ProductID = Products.ProductID) AS
TotalAddStock,
(SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductID = Products.ProductID) AS
TotalSoldStock
FROM dbo.Products
The problem is that I want to use the alias fields in a expression, and add
one more column to the above like:
(TotalAddStock - TotalSoldStock) as InStock.
In JET sql, I can simple use the above expression, and add it to the above
sql (ie: using those alias fields in expressions in the sql is legal).
However, In the EM, when I try to add the above expression...it says that
TotalAddStock is a not a valid field.
Is there some rule for using Alias fields?
Do I have to repeat the actual sql for those two sub-queries all over again
to make the expression of
( ("sub query repeated again") - ("sub query
repeated again") ) as InStock.
I have noticed that if I save the query (create a view),then I can certainly
write the following:
select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
(TotalAddStock - TotalSoldStock) as InStock
from myCoolView
However, this means I now have two sql views in place of what was one
before. It would be handy if I could throw in that one extra column.
Is creating two views here the recommend approach here? Any enlightenment on
using Alias fields in other column expressions is appreciated.
My best guess seems that you can't use a sub-query as a alias field in other
expressions...is this right?
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msnAlbert,
Try nesting the statement this way:
select
ProductName,
SerialNumber,
UnitPrice,
TotalAddStock,
TotalSoldStock,
(TotalAddStock - TotalSoldStock) as InStock
from (
SELECT
ProductName,
SerialNumber,
UnitPrice,
(
SELECT SUM(UnitsAdd)
FROM inventory
WHERE ProductID = Products.ProductID
) AS TotalAddStock,
(
SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductID = Products.ProductID
) AS TotalSoldStock
FROM dbo.Products
) T
This accomplishes what you discovered worked with a view - here you
would be using a derived table, which is basically an ad-hoc view.
Steve Kass
Drew University
Albert D. Kallal wrote:
>Using MSDE...(8.00.760 SP3)
>I have the following query...I taking this from ms-access, but lets just
>stick to using the EM here...
>SELECT ProductName, SerialNumber, UnitPrice,
> (SELECT SUM(UnitsAdd)
> FROM inventory
> WHERE ProductID = Products.ProductID) AS
>TotalAddStock,
> (SELECT SUM(Quantity)
> FROM OrderDetails
> WHERE ProductID = Products.ProductID) AS
>TotalSoldStock
>FROM dbo.Products
>The problem is that I want to use the alias fields in a expression, and add
>one more column to the above like:
> (TotalAddStock - TotalSoldStock) as InStock.
>In JET sql, I can simple use the above expression, and add it to the above
>sql (ie: using those alias fields in expressions in the sql is legal).
>However, In the EM, when I try to add the above expression...it says that
>TotalAddStock is a not a valid field.
>Is there some rule for using Alias fields?
>Do I have to repeat the actual sql for those two sub-queries all over again
>to make the expression of
> ( ("sub query repeated again") - ("sub query
>repeated again") ) as InStock.
>I have noticed that if I save the query (create a view),then I can certainly
>write the following:
>select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
> (TotalAddStock - TotalSoldStock) as InStock
> from myCoolView
>However, this means I now have two sql views in place of what was one
>before. It would be handy if I could throw in that one extra column.
>Is creating two views here the recommend approach here? Any enlightenment on
>using Alias fields in other column expressions is appreciated.
>My best guess seems that you can't use a sub-query as a alias field in other
>expressions...is this right?
>
>|||Both of these will, in all likelihood, force correlated subqueries--highly
inefficient. I would check the execution plans genereated to make sure.
Here's an alternative:
SELECT p.ProductName
,p.SerialNumber
,p.UnitPrice
,tas.TotalAddStock
,tss.TotalSoldStock
,InStock = (tas.TotalAddStock - tss.TotalSoldStock)
FROM dbo.Products AS p
INNER JOIN
(SELECT ProductID
,TotalAddStock = SUM(UnitsAdd)
FROM dbo.Inventory
GROUP BY ProductID
) AS tas
ON p.ProductID = tas.ProductID
(SELECT ProductID
,TotalSoldStock = SUM(Quantity)
FROM dbo.OrderDetails
GROUP BY ProductID
) AS tss
ON p.ProductID = tss.ProductID
Now, this too could be expensive in that the Inventory and/or OrderDetails
tables could be very large and creating the aggregates on all Products before
the JOIN restrictions could be CPU wasteful.
However, in this case, it looks like you already have an implicit RI
relationship between the Products, Inventory, and Order Details tables and,
with no restrictions on the final results, would have to calculate all those
aggregates anyway.
So, in this case, as long as the Optimizer would have choosen Lazy Spooling
for the first two examples, the solution provided here, a Set-Based example,
would be orders of magnitude faster.
Sincerely,
Anthony Thomas
"Steve Kass" wrote:
> Albert,
> Try nesting the statement this way:
> select
> ProductName,
> SerialNumber,
> UnitPrice,
> TotalAddStock,
> TotalSoldStock,
> (TotalAddStock - TotalSoldStock) as InStock
> from (
> SELECT
> ProductName,
> SerialNumber,
> UnitPrice,
> (
> SELECT SUM(UnitsAdd)
> FROM inventory
> WHERE ProductID = Products.ProductID
> ) AS TotalAddStock,
> (
> SELECT SUM(Quantity)
> FROM OrderDetails
> WHERE ProductID = Products.ProductID
> ) AS TotalSoldStock
> FROM dbo.Products
> ) T
> This accomplishes what you discovered worked with a view - here you
> would be using a derived table, which is basically an ad-hoc view.
> Steve Kass
> Drew University
> Albert D. Kallal wrote:
> >Using MSDE...(8.00.760 SP3)
> >
> >I have the following query...I taking this from ms-access, but lets just
> >stick to using the EM here...
> >
> >SELECT ProductName, SerialNumber, UnitPrice,
> > (SELECT SUM(UnitsAdd)
> > FROM inventory
> > WHERE ProductID = Products.ProductID) AS
> >TotalAddStock,
> > (SELECT SUM(Quantity)
> > FROM OrderDetails
> > WHERE ProductID = Products.ProductID) AS
> >TotalSoldStock
> >FROM dbo.Products
> >
> >The problem is that I want to use the alias fields in a expression, and add
> >one more column to the above like:
> >
> > (TotalAddStock - TotalSoldStock) as InStock.
> >
> >In JET sql, I can simple use the above expression, and add it to the above
> >sql (ie: using those alias fields in expressions in the sql is legal).
> >
> >However, In the EM, when I try to add the above expression...it says that
> >TotalAddStock is a not a valid field.
> >
> >Is there some rule for using Alias fields?
> >
> >Do I have to repeat the actual sql for those two sub-queries all over again
> >to make the expression of
> >
> > ( ("sub query repeated again") - ("sub query
> >repeated again") ) as InStock.
> >
> >I have noticed that if I save the query (create a view),then I can certainly
> >write the following:
> >
> >select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
> > (TotalAddStock - TotalSoldStock) as InStock
> > from myCoolView
> >
> >However, this means I now have two sql views in place of what was one
> >before. It would be handy if I could throw in that one extra column.
> >
> >Is creating two views here the recommend approach here? Any enlightenment on
> >using Alias fields in other column expressions is appreciated.
> >
> >My best guess seems that you can't use a sub-query as a alias field in other
> >expressions...is this right?
> >
> >
> >
>|||Interesting...but it seems that the query is optimized quite well. (I just
threw it into the Query analyzer).
It runs is less then one second for 125,000 invoice details and 2500
inventory records (stock additions), and 500 products.
Interesting, it also runs Steve's example in the same time..and thus the
query optimizer seems quite smart in this case. (that to me is rather
amazing...since I do have reasonable grasp of sql...and I can't believe
that sql server returns such good times for the kind of problems that don't
look at all that you can resolve those values together).
As for the joins,
I did have to put in a "left" join..since you were joining more then one
table here. So, I just changed the joins to:
LEFT OUTER JOIN
Anyway, I am surprised the engine does such a good job, considering you are
using a "group by" here..and that is joined to the "main" table. Often, jet
don't do such a good job in these cases. I should note that using the
sub-quires like I did does run very well in JET, and also sql server.
Anyway...thanks Anthony and Steve...
I am now temped to simply use a view here as per my original idea (I can
more easily "think" of my original syntax). However, I do "get" what you did
with the "group by"...and the fact that sql server seems to be more
forgiving for complex sql then is JET.
I certainly appreciate your answers, and in fact the two answers being so
different solutions also opens my mind up here...
Great stuff guys...and thanks!
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn|||Glad I could help but you made a comment I'd like to follow up with, if I may.
You said, "I did have to put in a "left" join..since you were joining more
then one
table here. So, I just changed the joins to:
LEFT OUTER JOIN
"
The number of tables joined has nothing to do with the join types. They
were coded as INNER JOINS so that you'd only get matching records; that is,
records with ProductID's that existed in each of the joined tables. If you
use LEFT JOINs, you will get Products that do not have corresponding
Inventory or OrderDetail records, but then the values your are computing,
TotalAdd and TotalSold will be NULL for those record types.
The INNER JOINS will use the Primary Keys defined on the child and parent
tables when performing the join operation and will likely use a Cluster Index
Scan if you use the LEFT JOIN systax. Thus, it is more likely that the INNER
JOIN will give you better performance, especially if you want only the
records that will actually compute a value instead returning NULL results.
Also, I'm curious what the execution plan and run time were if you run the
snippet as I originally wrote it. I'm curious if the Optimizer was able to
resolve to the same execution plan. For this to be meaningful, you'll have
to use the multiple INNER JOINs because that was what the two original
queries where doing, in essence.
Thanks for helping me satisfy my curiosity.
Sincerely,
Anthony Thomas
"Albert D. Kallal" wrote:
> Interesting...but it seems that the query is optimized quite well. (I just
> threw it into the Query analyzer).
> It runs is less then one second for 125,000 invoice details and 2500
> inventory records (stock additions), and 500 products.
> Interesting, it also runs Steve's example in the same time..and thus the
> query optimizer seems quite smart in this case. (that to me is rather
> amazing...since I do have reasonable grasp of sql...and I can't believe
> that sql server returns such good times for the kind of problems that don't
> look at all that you can resolve those values together).
> As for the joins,
> I did have to put in a "left" join..since you were joining more then one
> table here. So, I just changed the joins to:
> LEFT OUTER JOIN
> Anyway, I am surprised the engine does such a good job, considering you are
> using a "group by" here..and that is joined to the "main" table. Often, jet
> don't do such a good job in these cases. I should note that using the
> sub-quires like I did does run very well in JET, and also sql server.
> Anyway...thanks Anthony and Steve...
> I am now temped to simply use a view here as per my original idea (I can
> more easily "think" of my original syntax). However, I do "get" what you did
> with the "group by"...and the fact that sql server seems to be more
> forgiving for complex sql then is JET.
> I certainly appreciate your answers, and in fact the two answers being so
> different solutions also opens my mind up here...
> Great stuff guys...and thanks!
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.attcanada.net/~kallal.msn
>
>|||Ah,...yes...inner join does work!
(and...yes...some products might not have any invoice sold...so, likely I
will stick with left joins).
Anyway..here is the query plans for both:
http://www.attcanada.net/~kallal.msn/sqlplan/index.htm
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn|||Glad I could help. Thanks for a peek at the EEP. You'll notice that the
Optimizer picked the same execution steps just changed the order of the joins.
It is concerning that that you are running Table Scans. This happens
because we are not limiting the results but aggregating all records. The
point is, you will have to touch every record. The fact you are doing Table
Scans indicates you have not defined a Clustered Index for any of these three
tables.
Every Table should have a Primary Key defined, for integrity and you are not
even in 1NF if you haven't defined a key, which means you do not have a
relational table. Bad mojo. Second, every Table should have a Clustered
Index defined. The reason being, this defines the actual data sort for
storage. Usually, the Primary Key and the Clustered Index are NOT the same,
at least with how many use them.
Sincerely,
Anthony Thomas
"Albert D. Kallal" wrote:
> Ah,...yes...inner join does work!
> (and...yes...some products might not have any invoice sold...so, likely I
> will stick with left joins).
> Anyway..here is the query plans for both:
> http://www.attcanada.net/~kallal.msn/sqlplan/index.htm
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.attcanada.net/~kallal.msn
>
>

Help with using Alias Fields in a expressions.

Using MSDE...(8.00.760 SP3)
I have the following query...I taking this from ms-access, but lets just
stick to using the EM here...
SELECT ProductName, SerialNumber, UnitPrice,
(SELECT SUM(UnitsAdd)
FROM inventory
WHERE ProductID = Products.ProductID) AS
TotalAddStock,
(SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductID = Products.ProductID) AS
TotalSoldStock
FROM dbo.Products
The problem is that I want to use the alias fields in a expression, and add
one more column to the above like:
(TotalAddStock - TotalSoldStock) as InStock.
In JET sql, I can simple use the above expression, and add it to the above
sql (ie: using those alias fields in expressions in the sql is legal).
However, In the EM, when I try to add the above expression...it says that
TotalAddStock is a not a valid field.
Is there some rule for using Alias fields?
Do I have to repeat the actual sql for those two sub-queries all over again
to make the expression of
( ("sub query repeated again") - ("sub query
repeated again") ) as InStock.
I have noticed that if I save the query (create a view),then I can certainly
write the following:
select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
(TotalAddStock - TotalSoldStock) as InStock
from myCoolView
However, this means I now have two sql views in place of what was one
before. It would be handy if I could throw in that one extra column.
Is creating two views here the recommend approach here? Any enlightenment on
using Alias fields in other column expressions is appreciated.
My best guess seems that you can't use a sub-query as a alias field in other
expressions...is this right?
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msnAlbert,
Try nesting the statement this way:
select
ProductName,
SerialNumber,
UnitPrice,
TotalAddStock,
TotalSoldStock,
(TotalAddStock - TotalSoldStock) as InStock
from (
SELECT
ProductName,
SerialNumber,
UnitPrice,
(
SELECT SUM(UnitsAdd)
FROM inventory
WHERE ProductID = Products.ProductID
) AS TotalAddStock,
(
SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductID = Products.ProductID
) AS TotalSoldStock
FROM dbo.Products
) T
This accomplishes what you discovered worked with a view - here you
would be using a derived table, which is basically an ad-hoc view.
Steve Kass
Drew University
Albert D. Kallal wrote:

>Using MSDE...(8.00.760 SP3)
>I have the following query...I taking this from ms-access, but lets just
>stick to using the EM here...
>SELECT ProductName, SerialNumber, UnitPrice,
> (SELECT SUM(UnitsAdd)
> FROM inventory
> WHERE ProductID = Products.ProductID) AS
>TotalAddStock,
> (SELECT SUM(Quantity)
> FROM OrderDetails
> WHERE ProductID = Products.ProductID) AS
>TotalSoldStock
>FROM dbo.Products
>The problem is that I want to use the alias fields in a expression, and add
>one more column to the above like:
> (TotalAddStock - TotalSoldStock) as InStock.
>In JET sql, I can simple use the above expression, and add it to the above
>sql (ie: using those alias fields in expressions in the sql is legal).
>However, In the EM, when I try to add the above expression...it says that
>TotalAddStock is a not a valid field.
>Is there some rule for using Alias fields?
>Do I have to repeat the actual sql for those two sub-queries all over again
>to make the expression of
> ( ("sub query repeated again") - ("sub query
>repeated again") ) as InStock.
>I have noticed that if I save the query (create a view),then I can certainl
y
>write the following:
>select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
> (TotalAddStock - TotalSoldStock) as InStock
> from myCoolView
>However, this means I now have two sql views in place of what was one
>before. It would be handy if I could throw in that one extra column.
>Is creating two views here the recommend approach here? Any enlightenment o
n
>using Alias fields in other column expressions is appreciated.
>My best guess seems that you can't use a sub-query as a alias field in othe
r
>expressions...is this right?
>
>|||Both of these will, in all likelihood, force correlated subqueries--highly
inefficient. I would check the execution plans genereated to make sure.
Here's an alternative:
SELECT p.ProductName
,p.SerialNumber
,p.UnitPrice
,tas.TotalAddStock
,tss.TotalSoldStock
,InStock = (tas.TotalAddStock - tss.TotalSoldStock)
FROM dbo.Products AS p
INNER JOIN
(SELECT ProductID
,TotalAddStock = SUM(UnitsAdd)
FROM dbo.Inventory
GROUP BY ProductID
) AS tas
ON p.ProductID = tas.ProductID
(SELECT ProductID
,TotalSoldStock = SUM(Quantity)
FROM dbo.OrderDetails
GROUP BY ProductID
) AS tss
ON p.ProductID = tss.ProductID
Now, this too could be expensive in that the Inventory and/or OrderDetails
tables could be very large and creating the aggregates on all Products befor
e
the JOIN restrictions could be CPU wasteful.
However, in this case, it looks like you already have an implicit RI
relationship between the Products, Inventory, and Order Details tables and,
with no restrictions on the final results, would have to calculate all those
aggregates anyway.
So, in this case, as long as the Optimizer would have choosen Lazy Spooling
for the first two examples, the solution provided here, a Set-Based example,
would be orders of magnitude faster.
Sincerely,
Anthony Thomas
"Steve Kass" wrote:

> Albert,
> Try nesting the statement this way:
> select
> ProductName,
> SerialNumber,
> UnitPrice,
> TotalAddStock,
> TotalSoldStock,
> (TotalAddStock - TotalSoldStock) as InStock
> from (
> SELECT
> ProductName,
> SerialNumber,
> UnitPrice,
> (
> SELECT SUM(UnitsAdd)
> FROM inventory
> WHERE ProductID = Products.ProductID
> ) AS TotalAddStock,
> (
> SELECT SUM(Quantity)
> FROM OrderDetails
> WHERE ProductID = Products.ProductID
> ) AS TotalSoldStock
> FROM dbo.Products
> ) T
> This accomplishes what you discovered worked with a view - here you
> would be using a derived table, which is basically an ad-hoc view.
> Steve Kass
> Drew University
> Albert D. Kallal wrote:
>
>|||Interesting...but it seems that the query is optimized quite well. (I just
threw it into the Query analyzer).
It runs is less then one second for 125,000 invoice details and 2500
inventory records (stock additions), and 500 products.
Interesting, it also runs Steve's example in the same time..and thus the
query optimizer seems quite smart in this case. (that to me is rather
amazing...since I do have reasonable grasp of sql...and I can't believe
that sql server returns such good times for the kind of problems that don't
look at all that you can resolve those values together).
As for the joins,
I did have to put in a "left" join..since you were joining more then one
table here. So, I just changed the joins to:
LEFT OUTER JOIN
Anyway, I am surprised the engine does such a good job, considering you are
using a "group by" here..and that is joined to the "main" table. Often, jet
don't do such a good job in these cases. I should note that using the
sub-quires like I did does run very well in JET, and also sql server.
Anyway...thanks Anthony and Steve...
I am now temped to simply use a view here as per my original idea (I can
more easily "think" of my original syntax). However, I do "get" what you did
with the "group by"...and the fact that sql server seems to be more
forgiving for complex sql then is JET.
I certainly appreciate your answers, and in fact the two answers being so
different solutions also opens my mind up here...
Great stuff guys...and thanks!
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn|||Glad I could help but you made a comment I'd like to follow up with, if I ma
y.
You said, "I did have to put in a "left" join..since you were joining more
then one
table here. So, I just changed the joins to:
LEFT OUTER JOIN
"
The number of tables joined has nothing to do with the join types. They
were coded as INNER JOINS so that you'd only get matching records; that is,
records with ProductID's that existed in each of the joined tables. If you
use LEFT JOINs, you will get Products that do not have corresponding
Inventory or OrderDetail records, but then the values your are computing,
TotalAdd and TotalSold will be NULL for those record types.
The INNER JOINS will use the Primary Keys defined on the child and parent
tables when performing the join operation and will likely use a Cluster Inde
x
Scan if you use the LEFT JOIN systax. Thus, it is more likely that the INNE
R
JOIN will give you better performance, especially if you want only the
records that will actually compute a value instead returning NULL results.
Also, I'm curious what the execution plan and run time were if you run the
snippet as I originally wrote it. I'm curious if the Optimizer was able to
resolve to the same execution plan. For this to be meaningful, you'll have
to use the multiple INNER JOINs because that was what the two original
queries where doing, in essence.
Thanks for helping me satisfy my curiosity.
Sincerely,
Anthony Thomas
"Albert D. Kallal" wrote:

> Interesting...but it seems that the query is optimized quite well. (I just
> threw it into the Query analyzer).
> It runs is less then one second for 125,000 invoice details and 2500
> inventory records (stock additions), and 500 products.
> Interesting, it also runs Steve's example in the same time..and thus the
> query optimizer seems quite smart in this case. (that to me is rather
> amazing...since I do have reasonable grasp of sql...and I can't believe
> that sql server returns such good times for the kind of problems that don'
t
> look at all that you can resolve those values together).
> As for the joins,
> I did have to put in a "left" join..since you were joining more then one
> table here. So, I just changed the joins to:
> LEFT OUTER JOIN
> Anyway, I am surprised the engine does such a good job, considering you ar
e
> using a "group by" here..and that is joined to the "main" table. Often, je
t
> don't do such a good job in these cases. I should note that using the
> sub-quires like I did does run very well in JET, and also sql server.
> Anyway...thanks Anthony and Steve...
> I am now temped to simply use a view here as per my original idea (I can
> more easily "think" of my original syntax). However, I do "get" what you d
id
> with the "group by"...and the fact that sql server seems to be more
> forgiving for complex sql then is JET.
> I certainly appreciate your answers, and in fact the two answers being so
> different solutions also opens my mind up here...
> Great stuff guys...and thanks!
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.attcanada.net/~kallal.msn
>
>|||Ah,...yes...inner join does work!
(and...yes...some products might not have any invoice sold...so, likely I
will stick with left joins).
Anyway..here is the query plans for both:
http://www.attcanada.net/~kallal.msn/sqlplan/index.htm
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn|||Glad I could help. Thanks for a peek at the EEP. You'll notice that the
Optimizer picked the same execution steps just changed the order of the join
s.
It is concerning that that you are running Table Scans. This happens
because we are not limiting the results but aggregating all records. The
point is, you will have to touch every record. The fact you are doing Table
Scans indicates you have not defined a Clustered Index for any of these thre
e
tables.
Every Table should have a Primary Key defined, for integrity and you are not
even in 1NF if you haven't defined a key, which means you do not have a
relational table. Bad mojo. Second, every Table should have a Clustered
Index defined. The reason being, this defines the actual data sort for
storage. Usually, the Primary Key and the Clustered Index are NOT the same,
at least with how many use them.
Sincerely,
Anthony Thomas
"Albert D. Kallal" wrote:

> Ah,...yes...inner join does work!
> (and...yes...some products might not have any invoice sold...so, likely I
> will stick with left joins).
> Anyway..here is the query plans for both:
> http://www.attcanada.net/~kallal.msn/sqlplan/index.htm
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.attcanada.net/~kallal.msn
>
>