Dataphor SQL RAC (Relational Application Companion)


A site of hope for those looking for a true relational database system

Monday, October 01, 2007

Sql - Do you know what the Sql CTE is?

From Sql Server 2005 Bol for WITH CTE (common_table_expression):
'Specifies a temporary named result set, known as a common table expression (CTE).
 This is derived from a simple query and defined within the execution scope of
 a single SELECT, INSERT, UPDATE,
 or DELETE statement.'

Now this works fine (using the NorthWind database in Sql Server 2005):

WITH Emps (Emp, Cnt) AS
(
    SELECT employeeid,count(*) AS Cnt
    FROM orders
    GROUP BY employeeid
)
SELECT Max(Cnt)
FROM Emps

But try to define a CTE independently. You can't.
So this CTE craps out:

WITH Emps (Emp, Cnt) AS
(
    SELECT employeeid,count(*) AS Cnt
    FROM orders
    GROUP BY employeeid
)

Try to use the CTE in a batch with a 2nd SELECT. You can't 'reuse' the CTE.
So this batch craps out with an error thrown on the use of the CTE
on the 2nd SELECT.

WITH Emps (Emp, Cnt) AS
(
    SELECT employeeid,count(*) AS Cnt
    FROM orders
    GROUP BY employeeid
)
SELECT Max(Cnt)
FROM Emps

SELECT Min(Cnt)
FROM Emps       -- Invalid object name 'Emps'.

Now what if a CTE in a batch could be defined independently and
be used with multiple SELECT statements. What would the batch look
like. Well it could look something like this using the D4 language of Dataphor
(see http://beyondsql.blogspot.com/2007/09/dataphor-all-tables-are-typed-variables.html
 for some more background):

//Define a CTE independently. And to define a CTE independently it must
//be stored in a variable. We are using a pass-thru query from D4 to Sql Server
//in t-sql to the NorthWind database.
var EMPS.CTE:=
              SQLQuery("SELECT employeeid,count(*) AS Cnt
                          FROM orders
                            GROUP BY employeeid");
//Define a row using the CTE.
var LRow:=row{Min(Cnt from EMPS.CTE) MinCnt,Max(Cnt from EMPS.CTE) MaxCnt};//A row.  
//SELECT the row.
select LRow; 
/*
MinCnt MaxCnt
------ ------
42     156
*/
//Use the CTE in a 2nd SELECT, SELECT a table
select EMPS.CTE return 2 by {Cnt desc};
/*
employeeid Cnt
---------- ---
4          156
3          127
*/

What is really going on here. Now Sql has managed to immunize itself
against computer science forever. But if we were to apply a compute
science term to the sql CTE what term what we call it. We would call
it a value. Because a value cannot exist independently (by itself :))
and cannot be reused. To reuse a value we have to declare every time
we want to use it. With a variable we don't have to worry about
any of these value limitations. We can simply reuse the variable
as much as we want. The 'var' in 'var EMPS.CTE:=..' means define
a variable that contains a value and the value is the CTE SELECT
statement. So now you know the sql term 'result set' is really just
a value :) And now you know the benefit of working with variables
over values.

Of course the situation with the sql CTE is not the only case where
you have to repeat the damn thing to use it.

This works fine in sql.

SELECT orderid,customerid,employeeid
FROM orders
WHERE customerid in ('COMMI','TORTU','HUNGO')

But can you store the list of ('COMMI','TORTU','HUNGO') in a variable? No.
You have to repeat it every time you want to use it. It's because there is
no variable available to assign the list to. Of course in D4 we can
assign the list to a variable and use the variable just like the CTE.

var LList:=list(String){'COMMI','TORTU','HUNGO'};
select Orders
where CustomerID in LList
{OrderID,CustomerID,EmployeeID};

Doesn't this make more sense than having to use xml in sql to shred the
string or other crazy sql techniques to store the list items in a table? 

32 comments:

Ross said...

"But can you store the list of ('COMMI','TORTU','HUNGO') in a variable? No."

This is a lie.

DECLARE @foo TABLE ( customerid varchar(30))
INSERT @foo VALUES ('COMMI')
INSERT @foo VALUES ('TORTU')
INSERT @foo VALUES ('HUNGO')

SELECT orderid,customerid,employeeid
FROM orders
WHERE customerid in (SELECT * FROM @foo)

SELECT orderid,customerid,employeeid
FROM orders
WHERE customerid NOT in (SELECT * FROM @foo)

qishaya said...

one day i went shopping outside,and in an ed hardy store,I found some kinds of ed hardy i love most they are Your website is really good Thank you for the information ed hardy ed hardy ed hardy clothing ed hardy clothing ed hardy shoes ed hardy shoes don ed hardy don ed hardy ed hardy clothes ed hardy clothes ed hardy bags ed hardy bags ed hardy swimwear ed hardy swimwear ed hardy jeans ed hardy jeans ed hardy mens ed hardy mens Thank you for the information

www.estadisticasweb.biz said...

It can't really have effect, I think this way.

Anonymous said...

Actually when someone doesn't understand after that its up to other visitors that they will help, so here it happens.

Also visit my blog Saltwater aquarium

Anonymous said...

I don't even know how I ended up here, but I thought this post was good. I don't know who you are but definitely
you are going to a famous blogger if you aren't already ;) Cheers!

My page Orlanso Sushi Restaurant

Anonymous said...

I have been surfing online more than three hours today, yet I never found any interesting article like yours.
It's pretty worth enough for me. Personally, if all webmasters and bloggers made good content as you did, the internet will be a lot more useful than ever before.

Feel free to surf to my web-site ... florastor capsule

Anonymous said...

It's a pity you don't have a donate button! I'd most certainly donate to this superb blog! I guess for now i'll settle for book-marking and adding your
RSS feed to my Google account. I look forward to brand new updates
and will share this blog with my Facebook group. Talk soon!


Also visit my site; orlando restaurants that deliver

Anonymous said...

I was suggested this blog by my cousin. I'm not sure whether this post is written by him as nobody else know such detailed about my difficulty. You are amazing! Thanks!

Feel free to surf to my weblog :: cheap used golf balls in bulk

Anonymous said...

Hey there, You have done an incredible job. I'll certainly digg it and personally suggest to my friends. I'm
confident they will be benefited from this web site.


My website ... athletic trainer certification texas

Anonymous said...

An outstanding share! I have just forwarded this
onto a coworker who was doing a little homework on this.
And he in fact ordered me dinner simply because I stumbled upon it for him.
.. lol. So allow me to reword this.... Thank YOU for the meal!
! But yeah, thanx for spending the time to discuss this topic here on your web site.


My blog :: lower back pain relief tips

Anonymous said...

Right here is the perfect site for anyone who really wants
to understand this topic. You know a whole lot its almost hard to argue with you (not that I really would want to…HaHa).
You definitely put a brand new spin on a topic that has been written about for
many years. Wonderful stuff, just wonderful!

My web blog :: www.backfence.com

Anonymous said...

Attractive section of content. I just stumbled upon your
web site and in accession capital to assert that I get actually
enjoyed account your blog posts. Any way I'll be subscribing to your augment and even I achievement you access consistently rapidly.

Here is my web page; average pay chiropractic assistant

Anonymous said...

Write more, thats all I have to say. Literally, it seems as
though you relied on the video to make your point.
You clearly know what youre talking about, why throw away your intelligence on just posting videos to your site when you could be giving us something informative to read?



Here is my blog post: lung pain flu

Anonymous said...

Supеrb webѕіte уou
have hегe but I was ωondering if уou knew of any disсussion boarԁs thаt covеr the ѕamе toρiсs discusseԁ in thіѕ article?

ӏ'd really like to be a part of online community where I can get opinions from other experienced individuals that share the same interest. If you have any suggestions, please let me know. Many thanks!

Visit my homepage raspberry ketone uk

Anonymous said...

I'm impressed, I have to admit. Seldom do I encounter a blog that's
both educative and interesting, and let me tell you,
you have hit the nail on the head. The issue is something
which too few men and women are speaking intelligently about.
Now i'm very happy that I found this in my hunt for something relating to this.

my website :: Foursquare.com

Anonymous said...

Currently it seems like Drupal is the preferred blogging platform available right now.
(from what I've read) Is that what you're using on your blog?



Take a look at my blog; sciatica symptoms and bladder

Anonymous said...

Good information. Lucky me I came across your site by chance (stumbleupon).
I've bookmarked it for later!

my webpage - golf cart batteries 12 volt

Anonymous said...

Hey I am so happy I found your blog page, I really found you by accident, while I was browsing on Aol for something else, Regardless I am here now and would just like to say cheers for a fantastic post and a all round thrilling
blog (I also love the theme/design), I don’t have time to look over it all at the
minute but I have book-marked it and also added in your RSS feeds, so when I have time I will be back to read much more, Please do keep
up the fantastic jo.

Here is my web page - Las Vegas Golf Lessons

Anonymous said...

certainly like your web site however you have to take a look
at the spelling on several of your posts. Many of
them are rife with spelling problems and I to find it very bothersome to tell the
truth however I'll surely come again again.

My blog post - pain between shoulder blades while sleeping

Anonymous said...

This is a topic that is near to my heart..
. Best wishes! Exactly where are your contact details though?


Here is my homepage: Orlando Sushi

Anonymous said...

What's up, after reading this awesome post i am too happy to share my familiarity here with friends.

Take a look at my webpage personal injury attorney

Anonymous said...

Fine way of explaining, and nice piece of writing to get data concerning my presentation
subject matter, which i am going to present in institution of higher education.


Also visit my homepage St Cloud Florist

Anonymous said...

Hi! My spouse and I frequently publish guest articles or blog posts
for other website owners to help gain exposure to our work, as well as provide good content to website
owners. It really is a win win situation! If you happen to be interested feel
free to email me at: lindseysmart@live.com so we may discuss further.
Thank you!

Also visit my blog post: fiance visa lawyers

Anonymous said...

Hi there Dear, are you truly visiting this web site daily,
if so afterward you will without doubt obtain fastidious know-how.


My blog; las vegas golf schools

Anonymous said...

Thank you a lot for sharing this with all of us you really recognise what you are talking approximately!

Bookmarked. Kindly additionally discuss with my website =).
We may have a link change arrangement among us

My blog post: kitchen remodeling pictures white cabinets

Anonymous said...

Hi! Тhis is my first visit to youг blοg!
We arе a collectiоn of volunteers and starting a neω project
in a community in the ѕamе niche.

Youг blοg provided us useful infoгmatіοn to work on.
Υou haνe donе а outѕtandіng job!


Μy ωeb site :: home based business opportunities

Anonymous said...

When someone wгiteѕ an paragraph he/she maintains thе thought of a user in
his/heг brain that how a user can bе аωare of it.
Therefore that's why this piece of writing is great. Thanks!

Stop by my web site; personal health insurance

Anonymous said...

Hey there! I know this is kinda off topic nevertheless I'd figured I'd ask.
Would you be interested in trading links or maybe guest authoring a blog
article or vice-versa? My website goes over a lot of the same topics as yours and I think
we could greatly benefit from each other. If you are interested feel free to shoot me an email.
I look forward to hearing from you! Wonderful blog by the way!


My website :: appliance repair []

Anonymous said...

Excellent, what a website it is! This website gives valuable information to
us, keep it up.

Look at my web-site: http://sunrisesinthewest.org

Anonymous said...

Just wіѕh tо ѕay уour aгticlе iѕ as astounding.
Тhe clearneѕs in your put uр is just ехcellent and
і cаn thіnk you're knowledgeable in this subject. Well with your permission let me to grab your feed to stay updated with imminent post. Thanks one million and please carry on the enjoyable work.

My web blog - sportsbet []

Anonymous said...

Hello therе, I belіеve yоuг
site might bе haѵing internеt brοωsеr compatibility іssues.
Whenеver I loοk at your ωebsitе
in Safari, it looks fine howeνer when oρening in
IE, it has some overlaрpіng іsѕueѕ.

ӏ merelу wаnted to ρrovіdе уou ωіth а
quісk headѕ up! Besіdeѕ that, fantaѕtіc site!



Alsο visit my wеbpаge: raspberry ketones

Anonymous said...

electronic cigarette starter kit, best electronic cigarettes, buy electronic cigarette, smokeless cigarettes, ecig, electronic cigarette brands

About Me

My photo
Phoenix, Arizona, United States