Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Avoiding IF/ELSE Expand / Collapse
Author
Message
Posted Friday, November 16, 2012 11:12 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 644, Visits: 3,702
Thanks all. I went with dwain.c's "normal" catch all query form.
Post #1385797
Posted Friday, November 16, 2012 1:02 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:05 PM
Points: 825, Visits: 2,495
dwain.c (11/15/2012)


It occurred to me on lookback that this is going to fail if #Companies has more than one column or that column has a data type that is incompatible with CompanyID from #CompanyGroups.

Oops ... that's what I get for rushing things probably should have done the following, which was what I had in my head at the time
;with companyNotNull as (
SELECT * FROM #Companies WHERE CompanyID = @CompanyID
),
companyNull as (
Select CompanyID FROM #CompanyGroups WHERE CompanyGroupID = @CompanyGroupID
)
SELECT *
FROM companyNotNull
WHERE @companyID is not null
UNION ALL
SELECT *
FROM #Companies c
WHERE exists (SELECT 1 FROM companyNull n WHERE n.CompanyID = c.CompanyID)

Post #1385838
Posted Sunday, November 18, 2012 5:35 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:42 PM
Points: 3,596, Visits: 5,112
Post deleted. Coffee has not yet arrived.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1386115
Posted Monday, November 19, 2012 11:12 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
>> believe the DDL and my commented out notes and questions should explain what I am hoping to achieve <<

Tables have to have a key; this is a pile of loose punch cards and not anything like a RDBMS. Companies are identified by a DUNS (we even get radio ads for DUNS here in Austin!); Rows and records are totally different concepts. You also did not use the row constructor syntax.

Group membership is an attribute of a company, so it should be in the Companies table. I will make a guess that the list of groups is short and constant; sure wish we had specs and valid DDL. The name of this design flaw is “attribute splitting”; it refers to putting one entity into two or more tables that have to be re-assembled over and over and over to get the entity that should have been modeled in the first palce.

Here is how an SQL programmer would have written that DDL:

CREATE TABLE Companies
(company_duns CHAR(9) NOT NULL PRIMARY KEY,
company_grp CHAR(1) --- nullable is a bad design, too
CHECK (company_grp IN ('A','B', ..)),
.. );

Instead of a NULL-able group, it is better to have a code for an “unassigned” group, but let's go with what you did.

INSERT INTO Companies
VALUES ('A', '000000001'),
('A', '000000002'),
(NULL, '000000003');

Most of the work in SQL is done in the DDL. But when your DDL is wrong, life stinks.

--Scenario 1
--@company_duns is populated, company_grp is NULL

SELECT company_duns, company_grp
FROM Companies
WHERE company_duns = @in_company_duns

--Scenario 2
--@company_duns is NULL, company_grp is populated

We make this impossible with DDL; declarative constraints are one way that tables are not like decks of punch cards. This idea of a group of companies without members is absurd, so we forbid it in the DDL.
>> The question: My select statement is complicated and I'd prefer not to duplicate it in each part of the IF-THEN-ELSE statements? <<

False question; SQL is declarative, so we view IF-THEN-ELSE statements like cannibalism . You do not do it unless you absolutely, positively have to do it. Your mindset is completely wrong and a forum is not a good place to get the basic education you need. I like MANGA GUIDE TO DATABASE as a first book for an absolute beginner. You will get the concepts and some exposure to simple SQL.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1386512
Posted Monday, November 19, 2012 11:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
CELKO (11/19/2012)

... Companies are identified by a DUNS (we even get radio ads for DUNS here in Austin!);
...


That is real delight! How cool it is! There is only one small problem here...
Are you sure that most of people on this planet have any idea about where Austin is?
I honestly believe, if everyone would knew that there is a radio ads for DUNS in your place, all of people would try to get to Austin to hear it straight away (may be youtube link at least)!
I can also believe that you have checked every single company in Austin and made sure they have their DUNS done. Including every independent pizza or burger shop...
Sorry to upset you, but unfortunately the rest of the world is not quite in Austin yet, therefore there are still some companies around (and people) who never heard about such a beautiful thing as DUNS, which allows to uniquely identify company on our small planet. Actually, I've worked for quite few large companies who do deal with a lot of corporate clients of any size, and I never, really NEVER! have seen any database where DUNS is used as PK. Actually, before you have mentioned it couple month ago, I have never even heard this word at all. But, of course, it is poor me, living on the outskirts of the world in the middle-of-no-where called London-village...
Oh lucky you...
I will now sleep and dream about real paradise on the earth called Austin, where even radio informs happy citizens about DUNS...


By the way, for many of us rows and records are the same... I'm so sorry...

And to OP, just stick with INT CompanyId's PKs, it's a common sense to use it... until you are from Austin where even radio...




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1386525
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse