November 21, 2008 at 12:50 pm
Anyone have suggestions on how to get this WHERE statement to work?
WHERE
CASE
WHEN dbo.Firms.ChildSpecsAsSubreports = 1 THEN
(dbo.Components.ParentField IS NULL)
AND (Projects.ProjectID = @ProjectID)
WHEN dbo.Firms.ChildSpecsAsSubreports = 0 THEN
(Projects.ProjectID = @ProjectID)
END
November 21, 2008 at 12:58 pm
Got it:
WHERE
(Projects.ProjectID = @ProjectID)
AND ((dbo.Firms.ChildSpecsAsSubreports = 1) AND (dbo.Components.ParentField IS NULL) OR(dbo.Firms.ChildSpecsAsSubreports = 0))
November 21, 2008 at 3:58 pm
Just a suggestion, as I believe I read somewhere that 3 part naming of columns (schema.tablename.columnname) is being depreciated, you may want to look at dropping the dbo. from your code in select lists, where clauses, etc.
November 22, 2008 at 10:22 am
Lynn,
Where did you read this? Everything I've ever read suggests 3 part naming due to different schemas.
November 22, 2008 at 10:27 am
I haven't read that, but I think you should be 2 part naming your tables, aliasing the tables, and then doing alias.column.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 22, 2008 at 10:41 am
Actually, I take that back, I've actually been told that it's best to use FULLY qualified names (which I would alias immediately of course). IE.
ServerName.DatabaseName.Schema.Table.Column.
November 22, 2008 at 10:58 am
Garadin (11/22/2008)
Lynn,Where did you read this? Everything I've ever read suggests 3 part naming due to different schemas.
Here it is:
Transact-SQL
Three-part and four-part column references in SELECT list
Two-part names is the standard-compliant behavior.
More than two-part column name
You can find it here:
November 22, 2008 at 10:59 am
Lynn Pettis (11/22/2008)
Garadin (11/22/2008)
Lynn,Where did you read this? Everything I've ever read suggests 3 part naming due to different schemas.
Here it is:
Transact-SQL
Three-part and four-part column references in SELECT list
Two-part names is the standard-compliant behavior.
More than two-part column name
You can find it here:
http://msdn.microsoft.com/en-us/library/ms143729.aspx
I realize it says in select statements, but if people are using them in where clauses it is most likely they are doing it in the select clause as well.
November 22, 2008 at 11:29 am
Good info Lynn, thanks.
Just to clarify though, 3/4/5 part naming in the FROM clause isn't being touched, this just seems to imply that they want you to alias the tables for reference in other clauses. And who wants to retype all that crap anyways!
November 22, 2008 at 12:50 pm
Garadin (11/22/2008)
Good info Lynn, thanks.Just to clarify though, 3/4/5 part naming in the FROM clause isn't being touched, this just seems to imply that they want you to alias the tables for reference in other clauses. And who wants to retype all that crap anyways!
True, but I was checking what I wrote. I did say in select lists, where clauses, etc. Perhaps I should have explicitly stated that they would still be used in the FROM clause (Never seen 5 part naming used, got an example?).
November 22, 2008 at 3:01 pm
MyServer.AdventureWorks.dbo.Products.ProductID
Server.Database.Schema.Table.Column
You use it in linked server queries.
November 22, 2008 at 3:57 pm
Garadin (11/22/2008)
MyServer.AdventureWorks.dbo.Products.ProductID
Server.Database.Schema.Table.Column
You use it in linked server queries.
I have used linked servers, but I have never done that in queries.
I would use the four part name (server.database.schema.table) in the FROM clause and alias the table and then use the two part name alias.column in the SELECT, or ON, or WHERE clauses (or any other clause I might use in the query). Even in multi-table queries, I'd alias the table in the FROM clause and use two part naming (again, alias.column).
Maybe I'm just lazy, but that is just too much typing.
November 23, 2008 at 12:38 am
If I've gotta join it to anything, it's getting aliased. Which means, you're correct, I've probably never used a 5 parter either, didn't really think about it =) I use 4 in the from and then use the alias everywhere else.
November 23, 2008 at 8:19 am
Man, I'm with Jack... use 2 or 3 part naming in the FROM clause, add aliases, and then use the aliases everywhere else. Done properly, it makes the code much easier to read. Of course, some decent formatting will also do that.
I can just see it now... when the deprecation occurs (you know what I mean), I can see all the ERP programs and all the people in a drop dead panic adding the full table name as the alias... :sick:
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2008 at 10:01 am
Jeff Moden (11/23/2008)
Man, I'm with Jack... use 2 or 3 part naming in the FROM clause, add aliases, and then use the aliases everywhere else. Done properly, it makes the code much easier to read. Of course, some decent formatting will also do that.I can just see it now... when the deprecation occurs (you know what I mean), I can see all the ERP programs and all the people in a drop dead panic adding the full table name as the alias... :sick:
I'm still not sure about the aliases thing, I know I differ from the crowd, but I'd rather the alias to the full table name BE the table name. splitting hairs perhaps, most folks don't consider that an alias, but
SELECT claim.claimid, provider.fullname
FROM MyServer.MyDB.dbo.claim claim JOIN MyServer.MyDB.dbo.provider provider ON claim.provid = provider.provid
is much easier for me to interpret than
SELECT c.claimid, p.fullname
FROM MyServer.MyDB.dbo.claim c
JOIN MyServer.MyDB.dbo.provider p
ON c.provid = p.provid
particularly when the code is (much) more complex. Of course, nobody ever follows that much common sense, it's always things like:
SELECT cheezburger.claimid,
sally.fullname,
jeffIsAGiantGoober.somefield
FROM claim cheezburger
JOIN provider sally ON sally.provid = cheezburger.provid
JOIN sometable jeffIsAGiantGoober ON jeffIsAGiantGoober.somefield = cheezburger.somefield
... and I consequently spend more time interpreting their stupid aliases rather than getting productive work done.
There, I feel better now. :hehe:
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply