Thursday, March 22, 2012
apt select
can any one clearly distinguish between the following 2 select stmts.
which one should I Choose for better performance.
1) select * from tab1,tab2,tab3,tab4
where tab1.Id1=tab2.Id
and tab1.Id2=tab3.Id
and tab1.Id3=tab4.Id
2)select * from tab1 join tab2
on tab1.Id1=tab2.Id
join tab3
on tab1.Id2=tab3.Id
join tab4
on tab1.Id3=tab4.Idwell..............
this is an old one.and i will give the "check it out for yourself" answer first.
....depending on record size and count, (and other factors). the only true test is to check statistics [TIME and IO] against each executed (or estimated) running of the queries..
now for my opinion.
there is no difference between the two.
I prefer the ansi joins sheerly from an portability aspect. everyone who is anyone is using them (even oracle has switched to ansi joins as per 9i).
just me however.
A GIS for "join style" returns a consensus on this matter "no real difference but your code will be portable".
Approximate row count?
large table without having to do a SELECT COUNT(*)?EXEC sp_spaceused 'table_name'
SELECT MAX(rowcnt) FROM sysindexes WHERE id = OBJECT_ID('table_name')
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
> Is there any way to use the statistics to get an approximate row count on
> a
> large table without having to do a SELECT COUNT(*)?|||Those will be more accurate, of course, if you first issue DBCC
UPDATEUSAGE(0) ...
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
> Is there any way to use the statistics to get an approximate row count on
> a
> large table without having to do a SELECT COUNT(*)?|||Try:
select
rowcnt
from
dbo.sysindexes
where
id = object_id ('MyTable')
and
indid in (0, 1)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
Is there any way to use the statistics to get an approximate row count on a
large table without having to do a SELECT COUNT(*)?|||Hi,
DBCC UPDATEUSAGE command will lock the table.
Use the below command to get the approximate table count:-
sp_spaceused <table_name>
Inacse if you have more descrepency then use the command UPDATEUSAGE which
scans the data pages in the table and makes any
necessary corrections to the sysindexes table regarding the storage space
used by each table
sp_spaceused <table_name>,@.updateusage = 'TRUE'
The above command will locks, so execute when the table access is less,
Thanks
Hari
SQL Server MVP
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
> Is there any way to use the statistics to get an approximate row count on
> a
> large table without having to do a SELECT COUNT(*)?|||Thanks for the quick response. Is there any way to do something similar, but
find out how many rows fit a particular value for an indexed (or nonindexed)
column?
"Aaron Bertrand [SQL Server MVP]" wrote:
> EXEC sp_spaceused 'table_name'
> SELECT MAX(rowcnt) FROM sysindexes WHERE id = OBJECT_ID('table_name')
>
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:384F4C0A-B30E-4074-86E9-1D48DF7EBE8B@.microsoft.com...
>
>|||> Thanks for the quick response. Is there any way to do something similar,
> but
> find out how many rows fit a particular value for an indexed (or
> nonindexed)
> column?
No, values are stored in the table, so you need to query the table.
What is the actual problem? Does SELECT COUNT(*) take too long? How many
rows are there, is there a clustered index, and what kind of hardware is it?
I am currently looking at a table on a system here, with 70,000,000 rows,
and SELECT COUNT(*) comes back in a few seconds. Granted, I use the NOLOCK
hint for ad hoc stuff like this, and it is on enterprise-class hardware
(quad zeon, 16gb ram, emc clariion). But design is definitely going to have
a big impact here.
A|||> The above command will locks, so execute when the table access is less,
Yes Hari, I didn't mean to imply that anyone should run DBCC UPDATEUSAGE
before every query.sql
Monday, March 19, 2012
Applying criteria at the start of a series of views
... from view4; View 4 is select ... from view3 etc.
That all works fine, but what this does is include all of the data from
whatever tables are involved even though I need a subset. My typical
scenario is that the majority of the data are in the table(s) involved in the
initial view(s)
What I would like to do is pass the necessary parameters to the view(s)
involved so as to limit the amount of data that gets passed along. I'm still
learning the right way to do things in SQL, so I appreciate any insights that
anyone can provide.
TIA,
John"DevalilaJohn" <DevalilaJohn@.discussions.microsoft.com> wrote in message
news:098BB47D-453F-4B0A-A2E2-B843B10F30AA@.microsoft.com...
>I have a series of views that provide a final result set e.g view 5 is
>select
> ... from view4; View 4 is select ... from view3 etc.
> That all works fine, but what this does is include all of the data from
> whatever tables are involved even though I need a subset. My typical
> scenario is that the majority of the data are in the table(s) involved in
> the
> initial view(s)
> What I would like to do is pass the necessary parameters to the view(s)
> involved so as to limit the amount of data that gets passed along. I'm
> still
> learning the right way to do things in SQL, so I appreciate any insights
> that
> anyone can provide.
>
SQL Server will pass where-clause predicates down through views to the base
tables if it can. If the column in a view is the result of an expression,
however, this won't work.
David
Applying criteria at the start of a series of views
... from view4; View 4 is select ... from view3 etc.
That all works fine, but what this does is include all of the data from
whatever tables are involved even though I need a subset. My typical
scenario is that the majority of the data are in the table(s) involved in the
initial view(s)
What I would like to do is pass the necessary parameters to the view(s)
involved so as to limit the amount of data that gets passed along. I'm still
learning the right way to do things in SQL, so I appreciate any insights that
anyone can provide.
TIA,
John
"DevalilaJohn" <DevalilaJohn@.discussions.microsoft.com> wrote in message
news:098BB47D-453F-4B0A-A2E2-B843B10F30AA@.microsoft.com...
>I have a series of views that provide a final result set e.g view 5 is
>select
> ... from view4; View 4 is select ... from view3 etc.
> That all works fine, but what this does is include all of the data from
> whatever tables are involved even though I need a subset. My typical
> scenario is that the majority of the data are in the table(s) involved in
> the
> initial view(s)
> What I would like to do is pass the necessary parameters to the view(s)
> involved so as to limit the amount of data that gets passed along. I'm
> still
> learning the right way to do things in SQL, so I appreciate any insights
> that
> anyone can provide.
>
SQL Server will pass where-clause predicates down through views to the base
tables if it can. If the column in a view is the result of an expression,
however, this won't work.
David
Applying criteria at the start of a series of views
t
... from view4; View 4 is select ... from view3 etc.
That all works fine, but what this does is include all of the data from
whatever tables are involved even though I need a subset. My typical
scenario is that the majority of the data are in the table(s) involved in th
e
initial view(s)
What I would like to do is pass the necessary parameters to the view(s)
involved so as to limit the amount of data that gets passed along. I'm stil
l
learning the right way to do things in SQL, so I appreciate any insights tha
t
anyone can provide.
TIA,
John"DevalilaJohn" <DevalilaJohn@.discussions.microsoft.com> wrote in message
news:098BB47D-453F-4B0A-A2E2-B843B10F30AA@.microsoft.com...
>I have a series of views that provide a final result set e.g view 5 is
>select
> ... from view4; View 4 is select ... from view3 etc.
> That all works fine, but what this does is include all of the data from
> whatever tables are involved even though I need a subset. My typical
> scenario is that the majority of the data are in the table(s) involved in
> the
> initial view(s)
> What I would like to do is pass the necessary parameters to the view(s)
> involved so as to limit the amount of data that gets passed along. I'm
> still
> learning the right way to do things in SQL, so I appreciate any insights
> that
> anyone can provide.
>
SQL Server will pass where-clause predicates down through views to the base
tables if it can. If the column in a view is the result of an expression,
however, this won't work.
David
Sunday, March 11, 2012
Applying CASE in Stored Procedures
I'm creating a stored procedures, and pass in few parameters, which is @.keys, @.fields, @.types and @.likes, whereby I will select from certain table based on the @.types, where the @.fields equal to @.keys, I also wanted to implement the LIKE to the statement as well, but I found out some problem, so ends up my stored procedure looks like this:
ALTER PROCEDURE searchBooks @.keys varchar(50), @.fields varchar(50)
AS
SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END)= @.keys
This statement run well, but I would like to specify the table to select with parameter instead of specified it, but I found it won't works, not even let me save the stored procedure.
Similar to the statements above, I would like another version of it where I using LIKE at the WHERE clause using CASE, I did something like this:
SELECT *FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END) LIKE @.keys
But it said that there was syntax error near the @.keys, do not how to fix it, I tried. Thanks in advanced.
I'm using SQL Server 2000, connecting with Visual Studio.NET 2003
Moving to the Transact-SQL forum, which is best suited to this type of questions.|||What is the value of @.keys?
If you hard-code the values do you get results?
|||I pass in value to the @.key, I want the key to be the column name in the table, and compare it with the value, my statement is just a simple Select statement, but I want to write in the better way that allow programmer to specify which column to check, so I do not need to write so many statement to select different table.|||Using dynamic column names is not possible, you would probably have to use dynamic SQL to compose the string first and then execute it using either EXEC or sp_executesql.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Well, the CASE in the WHERE does works
SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END)= @.keys
but if I try to use the LIKE instead of '=' , it did alert me said got syntax error near @.keys, I couldn't get it fix.. adding CASE in the FROM was not possible though, thanks for the link, I will do some reading there.
|||This works fine in 2005, but consider not doing this. The plan for this query will always be horrendous and will have to touch/lock every row in the books table. I would suggest you look at the following paper:
http://www.sommarskog.se/dyn-search.html
It has a wealth of possibilities for handing this type of situation. There are a few ways to do this, but no matter what I wouldn't just give the user the chance to search for one or the other. I would personally use full dynamic SQL to do something like this, especially in SQL Server 2005 where you can get around the security issues of dynamic SQL. Then the plan can be adjusted on calls that don't use the same filters.
All of this is nicely covered in that article.
|||Hi,
In my sql server 2000 (Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) ) the LIKE statement works fine..
Pls check all the columns (Author, Publisher, Title, ISBN) datatype, if it is different convert all the columns to varchar/nvarchar,
SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Convert(NVarchar,Author)
WHEN 'Publisher' THEN Convert(NVarchar,Publisher)
WHEN 'Title' THEN Convert(NVarchar,Title)
WHEN 'ISBN' THEN Convert(NVarchar,ISBN)
END) LIKE @.keys
apply, ok, save dont function ?
anywhere, then select APPLY or OK or SAVE nothing happens !
what the heck is going on ?
scottis there something in setup i have missed ?
its hosted on 2003 server|||basically the links to any aspx page seem to work but any scripts (i guess)
like NEW FOLDER, DATA SOUCRE, UPLOAD etc dont work
Apply top & Group By
I want to show top selling items for each store. Sample SQL Query is "SELECT TOP n ItemNumber FROM Inventory Group By Store" n - is the dynamic value. I am accepting this n parameter from the user. If the user is giving 5 for n then, I want to show top 5 selling items for each store. It will be greatfull, If anybodies giving me an idea of how to do this?.
Thanks
I have done it with the row_number function.
apply diff between two similar tables
select * from Table1
where {a,b,c} not in
(select a,b,c from Table2).
Without using concatenation on a,b,c.
(need it to apply 'diff' between two similar tables).
??:mad:
:confused:select * from Table1
where not exists
(select 1 from Table2
where a = Table1.a
and b = Table1.b
and c = Table1.c )
rudy
http://rudy.ca/
Wednesday, March 7, 2012
Application role help
I would like to generate report for each of my application role. Mean each
role has select /add/ update/delete / ... permissions with theri object
name. Like we see the permissions in properties tab of role.
Is their any stored procedure available internally or if we can write some
script ?
Thanks,
Manoj
See sp_helprotect in SQL Server Books Online.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Manoj" <Manoj@.discussions.microsoft.com> wrote in message
news:4712B2BA-9958-4583-83E0-5D92F5D38E2B@.microsoft.com...
> Hi
> I would like to generate report for each of my application role. Mean
each
> role has select /add/ update/delete / ... permissions with theri object
> name. Like we see the permissions in properties tab of role.
> Is their any stored procedure available internally or if we can write some
> script ?
> Thanks,
> Manoj
Application role help
I would like to generate report for each of my application role. Mean each
role has select /add/ update/delete / ... permissions with theri object
name. Like we see the permissions in properties tab of role.
Is their any stored procedure available internally or if we can write some
script ?
Thanks,
ManojSee sp_helprotect in SQL Server Books Online.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Manoj" <Manoj@.discussions.microsoft.com> wrote in message
news:4712B2BA-9958-4583-83E0-5D92F5D38E2B@.microsoft.com...
> Hi
> I would like to generate report for each of my application role. Mean
each
> role has select /add/ update/delete / ... permissions with theri object
> name. Like we see the permissions in properties tab of role.
> Is their any stored procedure available internally or if we can write some
> script ?
> Thanks,
> Manoj
Application Role & 2nd Database
Most of the select statements I use reference tables in a separate database.
After reading the BOL I find that this only works through the GUEST account.
Drat!
Any suggestions to fix this? I'm considering setting up views in the local
DB, but this will involve changing the code and creating the views and I
still don't know if it will work.
--
Jeffrey R. Price
Database Manager
Computing & Communication Services
Max M. Fisher college of Business
The Ohio State University
320F Mason Hall
250 W. Woodruff Avenue
Columbus, OH 43210-1309Creating referencing views will work fine. If you don't want to grant
permissions on the underlying tables to guest or public, you can use an
unbroken cross-database ownership chain so that the app role needs only
permissions on views in the application role database.
To maintain an unbroken chain, the owners of the objects involved need to
map to the same login. If your objects are owned by 'dbo', this
necessitates that the owners of both databases be the same. Also, the
cross-database chaining option (introduced in SQL 2000 SP3) needs to be
enabled for those databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Price" <price.9@.osu.edu> wrote in message
news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> I'm trying to use application roles for the 1st time and have a problem.
> Most of the select statements I use reference tables in a separate
database.
> After reading the BOL I find that this only works through the GUEST
account.
> Drat!
> Any suggestions to fix this? I'm considering setting up views in the
local
> DB, but this will involve changing the code and creating the views and I
> still don't know if it will work.
> --
> Jeffrey R. Price
> Database Manager
> Computing & Communication Services
> Max M. Fisher college of Business
> The Ohio State University
> 320F Mason Hall
> 250 W. Woodruff Avenue
> Columbus, OH 43210-1309
>|||Thanks, I can run it fine now from Query Analyzer, but not my app... more
work to do there.
I had to set the DB owners as the same account and a few other tasks. The
following MSDN article helped:
http://msdn.microsoft.com/library/d.../>
up_1cj5.asp
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ecqxs2D8DHA.2056@.TK2MSFTNGP10.phx.gbl...
> Creating referencing views will work fine. If you don't want to grant
> permissions on the underlying tables to guest or public, you can use an
> unbroken cross-database ownership chain so that the app role needs only
> permissions on views in the application role database.
> To maintain an unbroken chain, the owners of the objects involved need to
> map to the same login. If your objects are owned by 'dbo', this
> necessitates that the owners of both databases be the same. Also, the
> cross-database chaining option (introduced in SQL 2000 SP3) needs to be
> enabled for those databases.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeff Price" <price.9@.osu.edu> wrote in message
> news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> database.
> account.
> local
>|||Correction......
I did not get the Application role to work across Databases. I continue to
receive the error "Server user 'price_9' is not a valid user in database
'FCoB_Contacts'."
I've tried both
EXEC sp_configure 'Cross DB Ownership Chaining', '1';RECONFIGURE
and
EXEC sp_configure 'Cross DB Ownership Chaining', '0';RECONFIGURE with
EXEC sp_dboption 'FCoB_Contacts', 'db chaining', 'TRUE'
Both DBs are owned by the same account.
Drat!
"Jeff Price" <price.9@.osu.edu> wrote in message
news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> I'm trying to use application roles for the 1st time and have a problem.
> Most of the select statements I use reference tables in a separate
database.
> After reading the BOL I find that this only works through the GUEST
account.
> Drat!
> Any suggestions to fix this? I'm considering setting up views in the
local
> DB, but this will involve changing the code and creating the views and I
> still don't know if it will work.
> --
> Jeffrey R. Price
> Database Manager
> Computing & Communication Services
> Max M. Fisher college of Business
> The Ohio State University
> 320F Mason Hall
> 250 W. Woodruff Avenue
> Columbus, OH 43210-1309
>|||Since an application role is only known in a single database, you need to
enable the 'guest' user in the other database so you have a security context
in the other database. No guest user permissions need to be granted. For
example:
Use MyOtherDatabase
EXEC sp_adduser 'guest'
Also, you don't need to enable cross-database chaining at the server level.
Your can specify it at the database level for only the databases that need
this option turned on:
EXEC sp_dboption 'MyDatabase, 'db chaining', 'TRUE'
EXEC sp_dboption 'MyOtherDatabase, 'db chaining', 'TRUE'
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Price" <price.9@.osu.edu> wrote in message
news:epWnGnO8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> Correction......
> I did not get the Application role to work across Databases. I continue
to
> receive the error "Server user 'price_9' is not a valid user in database
> 'FCoB_Contacts'."
> I've tried both
> EXEC sp_configure 'Cross DB Ownership Chaining', '1';RECONFIGURE
> and
> EXEC sp_configure 'Cross DB Ownership Chaining', '0';RECONFIGURE with
> EXEC sp_dboption 'FCoB_Contacts', 'db chaining', 'TRUE'
> Both DBs are owned by the same account.
> Drat!
> "Jeff Price" <price.9@.osu.edu> wrote in message
> news:Ohj0lGD8DHA.360@.TK2MSFTNGP12.phx.gbl...
> database.
> account.
> local
>|||Thanks! That did the trick.
Do we need to be concerned with the existence of the "Guest" account?
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e4vgGmQ8DHA.4060@.tk2msftngp13.phx.gbl...
> Since an application role is only known in a single database, you need to
> enable the 'guest' user in the other database so you have a security
context
> in the other database. No guest user permissions need to be granted. For
> example:
> Use MyOtherDatabase
> EXEC sp_adduser 'guest'
> Also, you don't need to enable cross-database chaining at the server
level.
> Your can specify it at the database level for only the databases that need
> this option turned on:
> EXEC sp_dboption 'MyDatabase, 'db chaining', 'TRUE'
> EXEC sp_dboption 'MyOtherDatabase, 'db chaining', 'TRUE'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jeff Price" <price.9@.osu.edu> wrote in message
> news:epWnGnO8DHA.2168@.TK2MSFTNGP12.phx.gbl...
> to
problem.
I
>|||> Do we need to be concerned with the existence of the "Guest" account?
As long as you haven't granted additional permissions to guest or public,
guest user access is limited to default public role permissions. This
includes the ability to view meta data, (e.g. table aned column names) but
no access to user objects and data. Users will still need an account to
connect to SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jeff Price" <price.9@.osu.edu> wrote in message
news:OkTtG8j8DHA.2404@.TK2MSFTNGP12.phx.gbl...
> Thanks! That did the trick.
> Do we need to be concerned with the existence of the "Guest" account?
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:e4vgGmQ8DHA.4060@.tk2msftngp13.phx.gbl...
to
> context
For
> level.
need
continue
database
with
> problem.
the
and
> I
>
Sunday, February 19, 2012
Append without UNION
UNION
select * from GEt_lu_Lookup_UNION -- rows 423
I want to get the full 1,817,571 rows without using a Union Statement, can
anyone help?On Tue, 8 Mar 2005 02:35:04 -0800, marcmc wrote:
>select * from GEt_lu_Lookup_ST1 -- rows 1,817,148
>UNION
>select * from GEt_lu_Lookup_UNION -- rows 423
>I want to get the full 1,817,571 rows without using a Union Statement, can
>anyone help?
Hi marcmc,
My first reaction can only be: why' Is there any particular reason why
you want to avoid UNION?
In case you're worried about performance: UNION will attempt to remove
duplicates. If you're sure there are no duplicates (or if you don't want
them removed), use UNION ALL. This should be lots faster, as the step to
remove duplicates is skipped.
In case you have another reason for not wanting to use UNION: here's one
possible way to get the same results without using UNION or UNION ALL:
-- Same as UNION
SELECT COALESCE(a.Column1, b.Column1) AS Column1,
COALESCE(a.Column2, b.Column2) AS Column2,
...
COALESCE(a.ColumnN, b.ColumnN) AS ColumnN
FROM GEt_lu_Lookup_ST1 AS a
FULL OUTER JOIN GEt_lu_Lookup_UNION AS b
ON a.Column1 = b.Column1
AND a.Column2 = b.Column2
AND ....
AND a.ColumnN = b.ColumnN
-- Same as UNION ALL
SELECT COALESCE(a.Column1, b.Column1) AS Column1,
COALESCE(a.Column2, b.Column2) AS Column2,
...
COALESCE(a.ColumnN, b.ColumnN) AS ColumnN
FROM GEt_lu_Lookup_ST1 AS a
FULL OUTER JOIN GEt_lu_Lookup_UNION AS b
ON 1 = 2
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thx Hugo. Beacause I am investigating the use of an indexed view. The new
code gets what I want but then i get...
CREATE UNIQUE CLUSTERED INDEX [GEv_lu_Lookup] ON
[dbo].[GEv_lu_Lookup]([Ge_lookup_id], [Ge_source_id],
[Ge_lookup_code], [Ge_lookup_parent]) ON [PRIMARY]
GO
Server: Msg 1936, Level 16, State 1, Line 1
Cannot index the view 'DbName.dbo.GEv_lu_Lookup'. It contains one or more
disallowed constructs.
Any ideas...I've read the rules I just can't find why the workaround won't
work.|||Maybe its the full outer join in the design. The GEt_lu_Lookup_ST1 and
GEt_lu_Lookup_UNION are dummy temp tables.
CREATE VIEW GEv_lu_Lookup WITH SCHEMABINDING AS
SELECT COALESCE(a.Ge_lookup_id, b.Ge_lookup_id) AS Ge_lookup_id,
COALESCE(a.Ge_source_id, b.Ge_source_id) AS Ge_source_id,
COALESCE(a.Ge_lookup_code, b.Ge_lookup_code) AS Ge_lookup_code,
COALESCE(a.Ge_lookup_desc, b.Ge_lookup_desc) AS Ge_lookup_desc,
COALESCE(a.Ge_lookup_parent, b.Ge_lookup_parent) AS Ge_lookup_parent
FROM dbo.GEt_lu_Lookup_ST1 AS a
FULL OUTER JOIN dbo.GEt_lu_Lookup_UNION AS b
ON a.Ge_lookup_id = b.Ge_lookup_id
AND a.Ge_lookup_code = b.Ge_lookup_code
-- 1817512 rows in Time: 1:29|||On Tue, 8 Mar 2005 04:11:02 -0800, marcmc wrote:
>Thx Hugo. Beacause I am investigating the use of an indexed view. The new
>code gets what I want but then i get...
> CREATE UNIQUE CLUSTERED INDEX [GEv_lu_Lookup] ON
>[dbo].[GEv_lu_Lookup]([Ge_lookup_id], [Ge_source_id],
>[Ge_lookup_code], [Ge_lookup_parent]) ON [PRIMARY]
>GO
>Server: Msg 1936, Level 16, State 1, Line 1
>Cannot index the view 'DbName.dbo.GEv_lu_Lookup'. It contains one or more
>disallowed constructs.
>Any ideas...I've read the rules I just can't find why the workaround won't
>work.
Hi marcmc,
Can you post the CREATE VIEW statement used to create the view
GEv_lu_lookup?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 8 Mar 2005 04:23:02 -0800, marcmc wrote:
>Maybe its the full outer join in the design.
Hi marcmc,
Yes, that must be the reason. According to Books Online, outer joins are
not permitted in an indexed view.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||any other ideas how I can get all the rows and index my view without the use
of UNION/OUTER JOIN or any other rule breakers?
"Hugo Kornelis" wrote:
> On Tue, 8 Mar 2005 04:23:02 -0800, marcmc wrote:
>
> Hi marcmc,
> Yes, that must be the reason. According to Books Online, outer joins are
> not permitted in an indexed view.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Tue, 8 Mar 2005 06:15:05 -0800, marcmc wrote:
>any other ideas how I can get all the rows and index my view without the us
e
>of UNION/OUTER JOIN or any other rule breakers?
Hi marcmc,
Not without knowing more about your actual business problem than I know
now. Please post the following:
1. Table structure of both tables, posted as CREATE TABLE statement. If
there are many columns, you may omit those that play no role of
importance. Do include all constraints and properties; especially
PRIMARY KEY constraint and UNIQUE constraints are important in this
case. (www.aspfaq.com/5006)
2. Some sample data to illustrate your situation. No need to post all
1.8 million rows, of course - just enoguh to give me a feeling for the
structure of your data. (http://vyaskn.tripod.com/code.htm#inserts)
3. Expected output from the posted sample data. Especially handling of
duplicates should be visible in the expected output.
4. A short but concise description of the actual business problem you're
trying to solve. Don't assume I know your business - I probably don't.
5. Also: what you are trying to accomplish with this indexed view. An
indexed view can never be a goal in itself; it can only be part of a way
to achieve some other goal. If you can elaborate on that goal, I can
help you find other, maybe even better ways to achieve the same goal.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Append string to field value in select list
SELECT Code + '-20' FROM tb....
I want to the above to return 2000-20 for example.
How can I do this?
Mike BJust like you did if Code is a character-based datatype. If not, - then CAST(Code as varchar(10)) + '-20'
Thursday, February 9, 2012
Anyone using Excel 2007 as client ?
Overall pretty cool, but apparently you can't select a single level of a dimension in the pivot table.
For instance, I have level Year, Quarter, Month, Week, Day.
Having to look at last week's sales per day, I'd be selecting a specific week and I'd have the Day level only as row labels.
This worked in Excel 2003, but I can't figure out how to leave out the Year, Q, M and Week levels.
In the PivotTable Field List, you have a check box next to the Time dimension, but nothing next to each level in the dimension. So you have to have all levels of the dimension in the rows of the pivot.
This means a lot of useless drillthrough for the users as they go down from Year to Quarter to Month to Week to Days.
Am I missing something ?
In Excel 2007 you can display the Day level only by 'hiding' the other levels. If you drill down to the lowest level day, right click on any item | Show/Hide Fields | Uncheck the Year level - the year level is hidden. Repeat for the other levels you want to hide.
Hope this helps!
|||Thanks Zaheera it does help, that's what I needed.
I think this is a feature that needs improvement, since I need 17 clicks before I have drilled down to Days, and then removed the levels one by one.
|||
To accomplish this in fewer clicks - you can right click on an item in the highest level | Expand/Collapse | Expand to <lowest level>. In one-click you've drilled down to the lowest level. Now you'll need to hide each level one by one as described above (which can be a pain - I agree). Or you can do this in one-step in VBA (Alt + F11 to open the editor, Cntrl + G to go to the immediate window): Activecell.PivotField.CubeField.HiddenLevels = 3 and the top 3 levels will be hidden at once.