• About

sqlerrors

~ From the errors of others, a wise man corrects his own

sqlerrors

Category Archives: Uncategorized

What’s wrong with this query?

30 Thursday Jul 2015

Posted by Praveen D'sa in MS SQL Tips, Uncategorized

≈ Leave a comment

Tags

query, sub-query, table alias

Recently there was a question on one of the MS SQL forum, the query which user had posted was pretty simple but was behaving strangely.
Here I’m demonstrating you all the scenario which user had posted.

Create two tables and inserted few records in both the tables.

DECLARE @Header Table (EmpId Int , FirstName Varchar(50) , LastName Varchar(50))
DECLARE @Detail Table (id Int , Department Varchar(50))

INSERT INTO @Header Values(1 , 'ABC' , 'CBA'), (2 , 'BCD' , 'DCB'),(3 , 'CDE' , 'EDC')
INSERT INTO @Detail Values(7 , 'IT'), (8 , 'HR'),(9 , 'Admin')

Now execute below query, which pulls records from Header table for matching EmpId exists in Detail table.

SELECT * FROM @Header WHERE EmpId IN (SELECT EMPID From @Detail)

Following is the result set generated by above query.
Results

Is this query pulling out right data?

Apparently, yes it does, but wait a minute and carefully observe the sub-query.

SELECT EMPID From @Detail

If you carefully check the Detail table definition it doesn’t have any EMPID column defined, infact it has column name ID, but still entire query runs successfully and produces the result.

The EMPID in the sub-query does refer to the outer-query (Header) EMPID column and we are getting the result set.

Alter the above query introduce table alias and run it again.

SELECT * FROM @Header H WHERE H.EmpId IN (SELECT D.EMPID From @Detail D)

Now, the query fails with the error message.

Msg 207, Level 16, State 1, Line 7
Invalid column name ‘EMPID’.

Many a times we do commit this kind of coding errors in our queries without realising it, so it’s always recommended to use table alias’s in your queries to overcome this.

Hope this helps.!!!

Advertisement

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

I Want to be mentored by Paul Randal

14 Saturday Feb 2015

Posted by Praveen D'sa in Uncategorized

≈ Leave a comment

Tags

Paul Randal, SQLSkills

It’s been a long time since I blogged last time, due to hectic work schedule & other commitments, I could not dedicate any time to for writing blog. This post is also my fist post of 2015.
This blog post is in response to Paul’s (b/t) blog: “Want to be mentored by me? “, requesting Paul to consider my candidature to be groomed under his guidance.

About me

Myself is Praveen D’sa, I hold MCA (Master of Computer Application) degree from Mangalore University (same university from where Mr. Satya Nadella, CEO Microsoft attained his bachelor degree 😉 )
Post completion of MCA in 2005 I joined one of the leading media industry in India as a ASSP.Net developer, during that time SQL Server being administrated by a third party vendor in our company.
One fine day boss came upto me and asked me to take over DBA responsibilities from them and he gave me 3 months’ time to learn the skills, I replied, “Boss, I am your servant. Let it be done unto me according to your word”. Like many other DBA’s I too became DBA not by choice but by chance.
Very soon I got attracted towards by new responsibilities and gradually fell in love with SQL, I started to read and read and read, find SQL stuff online do R&D then I found the great site SQLSKILLS, inspired by his writings I took regular subscription to pluralsight.
I consider Paul as my guru, infact my first technical blog on this site is inspired by Pauls blog post (t).
Whenever Paul announces IE/IEPTO classes I madly wanted to attend one of these classes and wanted to be picked for SQLskills community mentoring program. Unfortunately due to my geographic barrier I never able to make to any of his IE/IEPTO classes and without which I cannot make it to SQLskills community mentoring program.
But this opportunity is a blessing in disguise; I desperately want to be mentored by Paul.

Why Paul?

I cannot see any other names but god of SQL to be my mentor, I have acquired lot of knowledge through my reading, research & online classes but right now it’s like a puzzle scattered into bits and pieces, I need mentor like Paul to put together these bits and pieces and make a beautiful picture of SQL. Paul is super dedicated person inspite of his busy schedule he manages to reply to your mails, I never anticipated a reply from Paul when I sent my first mail asking his advice, hats off to you Sir.

How do I benefit?

Grooming under Paul definitely help me to become a Professional DBA from accidental DBA, it also help to contribute to other SQL community members in a much better way (often responds to SQL related queries on MSDN, written few articles on technet wiki and own gold medal for this post)

Conclusion

I wanted to keep this post as short as possible but I am so overwhelmed by the thought of grooming under Paul I could not stop myself writing and pushing hard for my candidature. I apologize for keeping this long.
Desperately waiting for feb 16th to see my name in short listed candidates.
Wishing all best of luck.

Thanks

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

Thank you all for 1000+ views!

25 Friday Apr 2014

Posted by Praveen D'sa in Uncategorized

≈ Leave a comment

sqlerrors

First off, I’d like to say a big thank you for visiting my blog for more than 1000 times:

I’ve had hits from 65 countries around the world, top three being, India with 347, US with 310, UK 60.

I know that other bloggers have 1,000 views per day but for me, reaching 1,000 has made me happy and at the same time this milestone has motivated me to keep updating my blog.

Here are some blog stats.

top postscountry breakup

referrers

Thank you all very much…!.

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

Merry Christmas and Best Wishes for the New Year!!

24 Tuesday Dec 2013

Posted by Praveen D'sa in Uncategorized

≈ Leave a comment

sqlerrorsA big thank you for everyone who supported me and the many visitors
who have come along to read and comment on my Blog in 2013.
I hope you all return here again in 2014.
May you have a wonderful Christmas with your loved ones and enjoy all the blessing the season brings.
Merry Christmas and Best Wishes for the New Year!!

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

questionário

19 Tuesday Nov 2013

Posted by Praveen D'sa in Uncategorized

≈ Leave a comment

Share this:

  • More
  • Email
  • Print

Like this:

Like Loading...

Recent Posts

  • What’s wrong with this query? July 30, 2015

Categories

  • Learning (2)
  • MS SQL Tips (3)
  • Performance Tuning (1)
  • SQL Script (5)
  • SQL Server (14)
    • Index (1)
    • Log Shipping (1)
  • SQL Tips & Tricks (1)
  • Uncategorized (5)

Archives

  • July 2015 (2)
  • February 2015 (1)
  • October 2014 (2)
  • September 2014 (1)
  • May 2014 (1)
  • April 2014 (3)
  • March 2014 (2)
  • February 2014 (2)
  • January 2014 (1)
  • December 2013 (3)
  • November 2013 (9)

sqlerrors

sqlerrors

Disclaimer:

This is my personal blog. The opinions & suggestions expressed here are
my own. For accuracy and official references please refer to MSDN,Microsoft TechNet,
Books Online. I do not endorse any of the tools / applications / books / concepts
mentioned here on my blog. I have simply documented my personal experiences on this
blog.

Create a free website or blog at WordPress.com.

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Follow Following
    • sqlerrors
    • Already have a WordPress.com account? Log in now.
    • sqlerrors
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...
 

    %d bloggers like this: