ORA-00960:Ambiguous column naming in select list

Ambiguous column error in oracle is a very familiar one as almost every oracle developer come across this error. But I recently got slapped by this error when I had duplicate column names in sub-query.
Let us go through a simple example on this,

The below query returns two columns and both with the same name TO_DATE,

SELECT SYSDATE TO_DAY, SYSTIMESTAMP TO_DAY
FROM   DUAL;


It works perfectly fine. But the moment when you put the same query as a sub-query of another like the one below oracle will show a RED card on you.

SELECT *
FROM  (SELECT SYSDATE TO_DAY, SYSTIMESTAMP TO_DAY
               FROM    DUAL);

I was taken aback by this error for a moment and then it struck my mind why I got this error. Its fine to have duplicate columns in the main query. But the moment it becomes a sub-query, it will be treated like a table (in-line view). We can't have duplicate columns in a table/view. So it make sense to get an error when your sub-query have duplicate column names.

IPL6: Who will reach Qualifiers?

IPL6 is a hit already with only one third of league games just got concluded. This season has already seen some fascinating contests, last ball finishes, hat-tricks, super overs and also some one-sided games. Overall this season has something special over the earlier versions due to the kind of balanced pitches used during this tournament so far. Unlike earlier versions, pitches being used in this season provides equal opportunities to both bowlers and batsmen and I am even tempted to say bowlers have  enjoyed more success than batsmen so far.

Having played at least 5 games apiece every team divulged their strengths and their weaknesses too are exposed. With handful results in hand predicting top 4 teams of this season now is fairly easier than doing this exercise before match one. At the same time its also difficult to precisely predict 4 out off 4 as this format is so unpredictable that a single player could beat a strong team on his day. So it will be fun comparing what you had predicted earlier with what had actually happened at the end of the tournament. That is exactly what I am trying to do.

The shocking surprise of this tournament is team Delhi Daredevils. Its inexplicable to figure out what is going wrong to this team. Perhaps they had lost both KP and Jessy before this season begins. But that does not explain this dismal performance as this team still have got number of match winners.They neither a weak team to loose 6 manes in a row nor have a strong batting line up to win 9 out off 10 games to push them to top 4. I hope they would have stop thinking (even stop dreaming) about getting qualified for knock out stages. Now their objective should be finding a decent position in bottom of the table at the end and if possible crushing some one's semifinal dream. Base on my observation chances for this team to make to the knock out stages is 5%

Next is the Kings XI Punjab(10% of chances). This is the only team which I am sure will not advance to the next stage. They do not have the right team to be consistent through out this tournament. I am sure they are going to slump sooner than later as the tournament progresses.

Sunrisers Hyderabad(20%): It is good to see them having  more than a decent debut season so far. Sunriser's strength is their bowling which could comfortably challenge any strong batting line up particularly in this season which offers something to every one. but their batting is too weak to match their substantial bowling line up. I give 20% to this team only for their bowling.

Pune Warriers (35%): YUVI,Uthapa,Bhuvneshwar Kumar, Rahul Sharma,Ashok Dinda,Manish Panday, Abhishek Nayar and Mithun Manhas. Interesting list is not it. This team consist of notable names to fill 7 domestic players slots and also names on overseas list also quite good. By and large they are strong team on papers but the results do not reflect the same. Unfortunately this team had suffered more losses (11 on the trot) which would play a huge role on their confidence and current form of some of the players and notably their captain's form are all worrying sign for this paper tigers.

Rajasthan Royals (50%): A team of thoughtfully picked overseas players, Handful of Indian international players and bits&pieces crickets who can chip in. This team can perform more than what is expected on them. Unlike RCB or MI this does not posses many big names on their squad but its a decent team who can beat any team on their day. Having lead by MR.Cricket who has been consistent in every IPL season Rajasthan Royals could surprise many by reaching next stage.



KKR(65%): Defending champions. Possess a rich group of overseas players. Their lower middle order is weaker and they largely dependent on Sunil Narine for wickets.
they may sweat a lot to earn the semifinal spot from Rajasthan and Pune.

Mumbai Indians(75%): Overall a very good team. Depth in their batting order is their biggest strength. The biggest concern is their not firing opening pair. undoubtedly they are nonpareil opening pair yet to open their account. You may expect them to catch up as the tournament progresses.  Except Dinesh Karthik all their batsmen have been fairly inconsistent. As this team possess everything a strong team should need you can see them sail through comfortably

RCB(90%): T20 is the game uncertainty and this team had proved this number of occasions. They posses a fearsome top order batsmen and to add more all three of them are in peak of their form. At the same time whenever their middle order is exposed they fail to impress and let their team down. Their neither strong nor weak bowling unit may let the team down on their bad day.

CSK(90%): Generally know as slow starters, but CSK have started this season strongly. Current form of most of the players is good and almost all of them have made notable contribution to boost their confidence. So you may expect them to grow stronger and stronger.

I love to come back on 19th May and LOL at me

You are invited to join my IPL Fantasy league...

Yes. IPL fantasy leagues is the best solution to your cricketing itch as thay claim. Its indeed an arousing challenge to any craziest cricket follower. And the good part is that you can team up and have league with you. have not you enrolled for fantasy league yet? Do not think you are late. Certainly you have not lost much of the contest as we have gone past just 25% of the league stages. If you start now, you will be given 80 substitutions to make + 1 free uncapped player sub for each game. So If you have a right strategy worked out and have a thirst to have fun then join my fantasy league "Starts of IPL (league code:24479)".

Wish you happy gaming.

FAQs of Fantasy league:: https://fantasy.iplt20.com/ifl/default/faq

Are we ready with new Shewag-Gambhir combination?

Are we ready with new Shewag-Gambhir combination?

I have doubt it is still early days to compare our brand new opening pair of Dhawan-Vijay with Shewag-Gambhir. It may sound injustice to many. But this pair looks very assured and has more reasons to compare with their long term predecessors.

Today, it could be the pitch or an average bowling for Aussies or anything…but Dhawan deserves huge credit for such a fantabulous innings. This innings have just made us to think result is still possible in this match against India’s favour else it would have been a dull draw. Shewag did this before many times. Now a new hope, we have got a new guy who can play a game changing innings.

At the same moment I feel sorry for Aussies who are preparing for two back-to-back Ashes series during second half of this year. Aussies need to address so many gaps with not much cricket left before Ashes. Being a strong Aussie supporter when it comes to Ashes, I wish something special happens to Australia something similar to resurgence of Watson two years back. I would be glad if it is their long term spinner.

Lockers...

Generally you don't inspired with every puzzle that you solve. Every body would have seen handful of puzzles that made them elevated fore few moment. All of them will introduces something new to us. It is not the feeling of solving the puzzle makes a puzzle solver excited instead the wisdom it brought in indeed make it more thrilling experience. Here is the one such puzzle...



There are 1000 lockers in a high school with 1000 students. The problem begins with the first student opening all 1000 lockers; next the second student closes lockers 2,4,6,8,10 and so on to locker 1000; the third student changes the state (opens lockers closed, closes lockers open) on lockers 3,6,9,12,15 and so on; the fourth student changes the state of lockers 4,8,12,16 and so on. This goes on until every student has had a turn. How many lockers will be open at the end?



Locker 1 will remain open for sure
Locker 2 will be closed (opened by 1 and closed by 2)
Locker 3 will also be closed
Locker 4 will be open (opened by 1, closed by 2 and opened by 4)
Locker 5 - closed (1-5)
Locker 6 - closed ( 1-2-3-6)
Locker 7 - closed (1-7)
Locker 8 - closed (1-2-4-8)
Locker 9 - open (1-3-9)

One can easily come to a below conclusion by looking at the above iterations,

 numbers have even number of factors will be closed at the end and numbers having odd number of factors will remain open.


 Now, the question is to get the list of numbers having odd number of factors. The below points are obvious when it comes to factors of a number

1. every number has at least two factors (1 and the number itself)
2. every factor can be paired with another factor in such a way that multiplying both the factor will be the number itself
    For instance, number 6 has four factors, 1,2,3 and 6
              here 1 can be paired with 6, 2 can be paired with 3.

3. Total number of factors will be even if the paired factors are not same.

In other words the number of factors are not even if any of the pairs have the same numbers ( for instance 2,2 for 4).

if a factor pair contains the same number then the number must be a perfect square :).
Also a number can not have two such factor pairs with identical number (which is obvious).

Hence number of lockers those remain open will be perfect squares (perfect is not it :) ).
So total number of lockers remain open is 31 (31^2 =961, 32^2= 1024 ).

A simple For..Loop Puzzle

What is the output of below pl/sql block?

declare

        var1 number(2) :=1;
        var2 number(2) :=5;
begin
      for i in var1..var2 loop
           dbms_output.put_line( 'i,var2: ' || i || ','||var2);
           if i= 3 then
                var2 :=7;
           end if;
      end loop;
end;

The output of the above pl/sql block is,

i,var2: 1,5
i,var2: 2,5
i,var2: 3,5
i,var2: 4,7
i,var2: 5,7

Surprised?? One can argue that the loop should have been continued until i reaches the new value of var2, which is 7. The below extract from oracle documentation will silent the argument,

Numeric FOR_LOOP loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP.

The range is evaluated when the FOR loop is first entered and is never re-evaluated. The loop body is executed once for each integer in the range defined by lower_bound..upper_bound. After each iteration, the loop index is incremented.

I lost my Boarding pass

I used to hear people saying that the big challenge in problem solving lies in understanding the problem.The solution just follows that... This puzzle is a classical example.

Question:

On a sold out flight, 100 people line up to board the plane. The first passenger in the line has lost his boarding pass, but was allowed in, regardless. He takes a random seat. Each subsequent passenger takes his or her assigned seat if available, or a random unoccupied seat, otherwise. What is the probability that the last passenger to board the plane finds his seat unoccupied?

Solution:

One can make this very complex by starting from Passenger 1 to 100 and working out all possibilities.But this question is as simple as below,

What is the probability for a passenger to get a specific seat among 100 seats? It is a simple probability question is not it?

answer is : 99!/100! = 1/100 = 1%

n(A) = other 99 seats can be occupied by other passengers = 99! ways.

n(s) =  number of ways 100 passengers can occupy 100 seats =100!