Must declare the scalar variable @Po ...

We may get this sql exception error when dealing with sql query. I also faced this and after a big analysis , found two causes.

1. We did DB migration - So your case check any migration (version changes) happened.

2. Second issue - Syntax issue like space , name mismatch, declaring is wrong.

SubQuery for Beginners

Sub query  - Embedding a SQL statement within another SQL statement. It can be used with sql comparison operators like =,<.>,<=,>= etc.,

Additionally, we can write with LIKE IN, NOT IN, ANY and IN operators. Now you may have question when we have to use LIKE IN etc., and comparison operators while writing query.

Things to remember:

1. Sub query may return one row or more than one row.

2. If it will return more than one row then you should use IN operator.

3.It must be enclosed with parenthesis.

4. You can not use ORDER BY in sub query. Instead of that, you can use GROUP BY.

Sub Query Format:

SELECT "column_name1"
FROM "table_name1"
WHERE "column_name2" [Comparison Operator] or [LIKE IN, ANY etc.,]
(SELECT "column_name3"
FROM "table_name2"
WHERE "condition");

Highlighted in blue color is inner query and highlighted in red color is called outer query. Let see the example one by one, so that we able to understand more clearly.

Example I: Sub Query with IN operator

ID     NAME  AGE PLACE     SALARY  

  1     Rajesh     35    Chennai       2000.00
  2     Velava     25    Mumbai       1500.00
  3     kajol        23    Hyderabad   2000.00
  4     Mukesh   24    Madurai       10000.00

Sub Query:

SELECT * 
FROM CUSTOMERS 
WHERE ID IN (SELECT ID 
             FROM CUSTOMERS 
             WHERE SALARY > 1800) ;

Output:

 ID   NAME      AGE  PLACE      SALARY  

  1     Rajesh        35     Chennai          2000.00 
  3     kajol           23     Hyderabad      2000.00 
  4     Mukesh      24     Madurai        10000.00 

Note: Above query uses IN operator, due to inner query will return more than one rows.

Sub Query with Comparison Operator

        student                                     marks

Student Id         Name                                 Student Id                 Marks      
     1                Kalpana                                       1                               91
     2                Peter                                             2                               89
     3                Rajini                                           3                               97
     4                Ajith                                             4                               96 
     5                Vijay                                             5                               95

Ok, Now the question is , How to identify all students who get high marks than one of the student who`s id is 5. Here, you don't know marks of student id 95.

So we can split this question as two,
 1. What s the mark of student Id 95 ? (95Marks)
 2. Who are all getting high marks than studetn Id 95 marks? (>95 Marks)

For 1st question, query is below,

Select * from marks
where student_id = '5'

Output:

Student Id     Marks
       5                  95

Now second question, query is below,

Select a1.student_id,a1.name,m1.marks 
from student s1, marks m1
where s1.student_id = m1.student_id
AND m1.marks > 95

Output:

studentId     name       marks
3                   Rajini         97
4                   Ajith           96

Now combine these two query, 

SELECT a.studentid, a.name, b.marks  
FROM student a, marks b  
WHERE a.studentid = b.studentid AND b.marks >  
(SELECT marks  
FROM marks  

WHERE studentid =  '95');  

Output:

studentId     name       marks
3                   Rajini         97
4                   Ajith           96

How LEFT JOIN and RIGHT JOIN works

As usual, we are keeping the same two table Employee and location to explain left and right joins.

Employee Table

empId	EmpName
101	Rajesh
102	Vinodh
103	Kumar
104	Mukesh

Location Table

empId	Location
101	Chennai
102	Mumbai
103	Kolkatta
105	Hyderabad

What is LEFT Join ?

The LEFT JOIN keyword returns all rows from the left table (What is left table ? ), with whatever matching rows in the right table (What is right table ?).

Suppose if there is no match in the right side, result is NULL.

We can write query first for this left join, after that I will explain. Left join query will be,

select * from employee left join location 
on employee.empID = location.empID;

Output : 

Employee.empID	Employee.empName	Location.empID	Location.empLocation
101	Rajesh	101	Chennai
102	Vinodh	102	Mumbai
103	Kumar	103	Kolkatta
104	Mukesh	Null	Null

From the result, we able to understand , left join query taking the rows from left table completely. If there is no match in the right tale it will return null like above.

Ven Diagram 

Left Join Ven diagram

What is Right Join ?

The RIGHT JOIN returns all rows from the right table with the matching rows in the left table. If there is no match in the left table it will return NULL. 

Ven diagram of Right Join

Right Join Ven diagram

 

Right Join Query

select * from employee right join location
on employee.empID = location.empID; 

Below figure represents some clear idea about left and right join.  

Table pointing identification of right and left join

Understanding Self Join Briefly with Example

Self join is easiest to understand. We can see how its working with one good example clearly. Consider the below table we have called Employee which have two columns name and location.

Employee Table

Name	Location
Millar	Chennai
Rajesh	Delhi
Stalin	Chennai
Kumar	Mumbai

Now you have to write the query for the below question.

1. How you find out which employees are coming from same location as name have Millar.

After seeing the question you will write the query immediately like below.

select name
from Employee
Where location in (select location from employee where name = "Millar") 

Fine. It will work perfectly. But using sub query is not a good practice. So in this place SELF JOIN can play its role perfectly. 

What is SELF JOIN ? 

             Self join is nothing but, when a table is joined itself called self join. So it will take two copies to join the table itself. To work with self join we must use aliases which will allow you to write a query with good efficient way. The reason here, why we are using aliases means, we should differentiate two copies of that table. Right ?  Yes. 

Any join(left, right, outer etc.,) will work with condition only. So self join also having condition to perform self join operation. Now throw the sub query from your mind and bring your mind into Self Join. Already we have discussed , two copies of table it have. Just call those copies as e1, e2.

 Table  -    e1

Name	Location
Millar	Chennai
Rajesh	Delhi
Stalin	Chennai
Kumar	Mumbai

 Table  -    e2

Name	Location
Millar	Chennai
Rajesh	Delhi
Stalin	Chennai
Kumar	Mumbai

As we discussed earlier, two tables was created like e1 and e2. After writing query Self join query is like below. 

SELECT e1.Name
FROM employee e1, employee e2
WHERE e1.location = e2.location
AND e2.Name="Millar";

Lets analyze the query , the condition WHERE e1.location = e2.location will give results (Rows) of location. Again we must add another condition which should be e2.Name="Millar". Based on the name we should filter , so that we added this condition. But you may get one doubt here, why we want to add only e2.Name ?.   Since, we need to return only the rows name as Millar. If you will add condition for both side of e1 and e2, it will return both side of table rows.

So you will find below finally,

e1.Name	e1.Location	e2.Name	e2.Location
Millar	Chennai	Millar	Chennai
Stalin	Chennai	Stalin	Chennai

Anonymous block complete

Some times you will get this error, when you tried to run stored procedure.

There is a two way you can solve this.

1. You must add and run SET SERVEROUTPUT ON before your procedure code. All latest version need this.

2.Click -->View-->Dbms Output and then click  + symbol, while you clicking on this + symbol, it will ask DB connect may be. Select DB environment.Now Run , you can see output in Dbms output window.