SQL Query - Is this possible?
John Dinning
adds an extra column.
The value in the extra column is the result of other query, but the query
called depends on the value of a field in the original table.
It may look something like this (although this doesn't work obviously):
SELECT * FROM Table1
IF Table1.Field1 = True
SELECT COUNT (*) FROM Table2 AS ExtraColumn
ELSE
SELECT COUNT (*) FROM Table3 AS ExtraColumn
The result would have all the fields from Table1, plus an extra column
called ExtraColumn
ExtraColumn would be calculated differently for each record depending on the
value of Table1.Field1
Is this possible, and if so could someone provide an example?
Any help welcome.
John.
Tom Moreau
select
OrderID
, OrderDate
, case
when OrderID % 2 = 0 then (select count (*) from Shippers)
when OrderID % 2 = 1 then (select count (*) from Suppliers)
end as ExtraColumn
from
Orders
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"John Dinning" <jo...@NOSPAMbarcodelogic.com.au> wrote in message
news:uZVZAgOO...@TK2MSFTNGP10.phx.gbl...
--CELKO--
There are no BOOLEANs in SQL. Let's get back to the basics of an RDBMS.
Rows are not records; fields are not columns; tables are not files;
there is no sequential access or ordering in an RDBMS, so "first",
"next" and "last" are totally meaningless. DUH!!!
In about a year, when you can post something with the right words
(even if you do not know the concepts), you can look up the CASE
expression. Please do not confuse it with a CASE statement that does
not exist in SQL.
>> ExtraColumn would be calculated differently for each record [sic] depending on the value of Table1.Field1 <<
Gee, I wish I had DDL so I could not have to guess about everything.
Try this while you are writing specs that can be meaningful.
SELECT foo_key,
COUNT(CASE WHEN stupid_flag = 'T'
THEN 1 ELSE NULL END) AS t_tally,
COUNT(CASE WHEN stupid_flag = 'F'
THEN 1 ELSE NULL END) AS f_tally
FROM Foobar
GROUP BY foo_key;
Sample Data? Clear specs? CODE!!!!
John Dinning
Thank you very much.
John.
"Tom Moreau" <t...@dont.spam.me.cips.ca> wrote in message
news:%23oBcBkO...@TK2MSFTNGP10.phx.gbl...
John Dinning
My 'pseudo code' was just to help others easily understand my question.
Tom understood my question and simply and succinctly answered it with
exactly the information I needed, for which I am very grateful.
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:1140748461.7...@e56g2000cwe.googlegroups.com...
Mike Hodgson
The sqlserver newsgroups should come with a warning when you connect to the NNTP server:
"Beware! There's a poster lurking around named Joe Celko who is notorious for biting the heads off other posters who mention the words record, field, file or sequential in relation to SQL within their post. He frequently mutters about 'back in the 60s when I was programming Fortran on punch cards...', calls anyone who can walk without a frame or walking stick a 'kid' and anyone who doesn't have at least 10 years SQL experience a 'newbie'. His catch-cry is 'rows are not records, fields are not columns, tables are not files'. He rarely offers solid practical SQL advice, as he's a relational theory nut, and valiantly attempts to include the phrase 'violates 1NF' or the names of 'Dr Codd' or 'Chris Date' in every post. His usual extremely helpful advice is 'go buy a beginners book or 10 on relational theory and come back when you understand something about RDBMS' and he often mentions having to go fail some of his students. Post at your own risk."
--
mike
hodgson
http://sqlnerd.blogspot.com
Raymond D'Anjou
news:5BD45199-A70D-4A7A...@microsoft.com...
>A bit long, but what a great t-shirt that would be! :)
>
>
> ML
>
> ---
> http://milambda.blogspot.com/
But don't make the mistake of putting a photo of Celko on the t-shirt.
Nobody would buy it, unless you actually like to scare little kids.
http://www.weekendwithexperts.com/photos/JoeReads.JPG
SQL
Celko and LaVey,separated at birth?
http://www.geocities.com/whkinfo1/Anton_lavey.jpg (Anton LaVey, the
founder of the Church of Satan)
;-)
Alexander Kuznetsov
Joe,
in your opinion is it an advantage or a disadvantage?
Anith Sen
boolean logic in a language like SQL that supports NULLs.
--
Anith
Alexander Kuznetsov
To my best knowledge, 2-valued boolean logic is implemented by SQL as a
subset of its 3-valued logic. As long as all the arguments are not
null, 2-valued boolean logic rules aply, do they not?
Anith Sen
>> As long as all the arguments are notnull, 2-valued boolean logic rules
>> aply, do they not?
Not really. SQL inherently has 3VL with logical values TRUE, FALSE &
UNKNOWN. Having NULL in 3VL would require a 4th "state" and as such
invalidate the known 3VL tautologies and would result in invalid truth table
values. Check out some of the old discussions in the google archives of this
newsgroup, I think the actual 3VL truth tables were posted here several
times before.
--
Anith
--CELKO--
system, but only search condition rules, which are defined by look ups.
If we had a full logical system, we would have inference rules defined
with implication. Dave McGovern makes this point in some of his
articles. The bad news is that there are two different ways to define
a 3VL implication within the SQL framework.
--CELKO--
LOL!! Hey, I could sell it on my website! Next to the "But what about
all the Good Things Hitler Did?" t-shirts.
--CELKO--
is NULL-able. NULLs have a well-defined behavior. This moves us to a
4VL which is logical inconsistent.
In a practical sense, I have always found it is better not to think in
Boolean / BIT / punch card/ assembly language flag models. Sit down
for a day and research for an industry standard code, if that fails,
then carefully design an encoding scheme. Get a copy of SQL PROGRAMING
STYLE and read the chapters on scales, measurements and the design of
encodings. I want validation, verification and room for expansion.