Facing Problem in sql Query in if function

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

//Below is my code for my solution

SQL

if (SELECT Student_ID,Fee_Type, SUM(amount) AS paid FROM FeePaid_Details WHERE (Student_ID = 'PS20130001') AND (Fee_Type = 'Admission Fee') AND (Month_Details = 'jan') GROUP BY Student_ID, Fee_Type) and (SELECT Student_ID,Fee_Type, SUM(amount) AS paid FROM FeePaid_Details WHERE (Student_ID = 'PS20130001') AND (Fee_Type = 'Admission Fee') AND (Month_Details = 'feb') GROUP BY Student_ID, Fee_Type)
begin
select q.Student_ID,q.Fee_Type,q.amount,q.amount-p.paid as Current_month_due,q.amount-g.paid as previous_month_due,(q.amount-g.paid)+(q.amount-p.paid) as total_amount from Student_Fee_Quotations q join(select d.Student_ID,d.Fee_Type,sum(d.amount) as paid from FeePaid_Details d where d.Student_ID='PS20130001' and d.Fee_Type='Admission Fee' and d.Month_Details='jan' group by d.Student_ID,d.Fee_Type )as g on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee' join (select d.Student_ID,d.Fee_Type,sum(d.amount) as paid from FeePaid_Details d where d.Student_ID='PS20130001' and d.Fee_Type='Admission Fee' and d.Month_Details='feb' group by d.Student_ID,d.Fee_Type ) as p on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee'
end
else if (SELECT Student_ID,Fee_Type, SUM(amount) AS paid FROM FeePaid_Details WHERE (Student_ID = 'PS20130001') AND (Fee_Type = 'Admission Fee') AND (Month_Details = 'jan') GROUP BY Student_ID, Fee_Type) or (SELECT Student_ID,Fee_Type, SUM(amount) AS paid FROM FeePaid_Details WHERE (Student_ID = 'PS20130001') AND (Fee_Type = 'Admission Fee') AND (Month_Details = 'feb') GROUP BY Student_ID, Fee_Type)
begin
if (SELECT Student_ID,Fee_Type, SUM(amount) AS paid FROM FeePaid_Details WHERE (Student_ID = 'PS20130001') AND (Fee_Type = 'Admission Fee') AND (Month_Details = 'jan') GROUP BY Student_ID, Fee_Type)
begin
select q.Student_ID,q.Fee_Type,q.amount,q.amount as Current_month_due,q.amount-g.paid as previous_month_due,(q.amount-g.paid)+(q.amount) as total_amount from Student_Fee_Quotations q join(select d.Student_ID,d.Fee_Type,sum(d.amount) as paid from FeePaid_Details d where d.Student_ID='PS20130001' and d.Fee_Type='Admission Fee' and d.Month_Details='jan' group by d.Student_ID,d.Fee_Type )as g on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee'
end
else(SELECT Student_ID,Fee_Type, SUM(amount) AS paid FROM FeePaid_Details WHERE (Student_ID = 'PS20130001') AND (Fee_Type = 'Admission Fee') AND (Month_Details = 'feb') GROUP BY Student_ID, Fee_Type)
begin
select q.Student_ID,q.Fee_Type,q.amount,q.amount-p.paid as Current_month_due,0 as previous_month_due,(q.amount-p.paid) as total_amount from Student_Fee_Quotations q join(select d.Student_ID,d.Fee_Type,sum(d.amount) as paid from FeePaid_Details d where d.Student_ID='PS20130001' and d.Fee_Type='Admission Fee' and d.Month_Details='feb' group by d.Student_ID,d.Fee_Type ) as p on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee'
end
end
else
begin
select Student_ID,Fee_Type,amount,amount as Current_month_due,0 as previous_month_due,amount as total_amount from Student_Fee_Quotations where  Student_ID='PS20130001' and Fee_Type='Admission Fee'
end

//after executing this these are the errors i am facing

Msg 4145, Level 15, State 1, Line 1
An expression of non-boolean type specified in a context where a condition is expected, near 'and'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'else'.
Msg 4145, Level 15, State 1, Line 5
An expression of non-boolean type specified in a context where a condition is expected, near 'or'.
Msg 4145, Level 15, State 1, Line 8
An expression of non-boolean type specified in a context where a condition is expected, near 'begin'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'else'.
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'else'.

Posted 7-Mar-13 17:53pm

ntitish

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Accepted Answer · 2013-03-07T17:57:00

Solution 1

Hi
Where you are comparing values... ? and your Query also return More than one columns

SQL

if (SELECT Student_ID,Fee_Type, SUM(amount) AS paid 
    FROM FeePaid_Details WHERE (Student_ID = 'PS20130001') AND (Fee_Type = 'Admission Fee') AND (Month_Details = 'jan') 
    GROUP BY Student_ID, Fee_Type) =? 
and (SELECT Student_ID,Fee_Type, SUM(amount) AS paid 
     FROM FeePaid_Details 
     WHERE (Student_ID = 'PS20130001') AND (Fee_Type = 'Admission Fee') AND (Month_Details = 'feb') 
     GROUP BY Student_ID, Fee_Type) =?

Your Query it self wrong....

Check the links...
IF...ELSE (Transact-SQL)[^]
SQL IF...ELSE Statement[^]

Regards,
GVPrabu

Posted 7-Mar-13 17:57pm

gvprabu

Updated 7-Mar-13 18:00pm

v3

Comments

ntitish 8-Mar-13 0:05am

sir cant we use two select statements in if condition foe example like below
if (select * from student_details where student_id='1001') and (select * from student_details where student_name='pavan')
begin

end
else
begin

end

ntitish 8-Mar-13 0:09am

actually my problem is
(select * from student_details where student_id='1001') having zero records
(select * from student_details where student_name='pavan') having Zero records means
it should not go inside of the if condition

gvprabu 8-Mar-13 0:14am

Check the IF EXISTS .... why u need to take all column for checking

ntitish 8-Mar-13 0:25am

//for example the below code will work ha sir

if exists(select * from student_details where student_id='1001') and (select * from student_details where student_name='pavan')
begin
select * from student_details where student_id='1001' and student_Fee_Type='annual'
end
else if exists(select * from student_details where student_id='1001') or (select * from student_details where student_name='pavan')
begin
select * from student_details where student_id='1001' and student_Fee_Type='month'
end
else
begin
select* from student_details
end

//the above syntax is correct ha sir...

gvprabu 8-Mar-13 0:48am

Try this...

if exists(select 1 from student_details where student_id='1001' AND student_name='pavan')
begin
select Column_Name from student_details where student_id='1001' and student_Fee_Type='annual'
end
else if exists(select 1 from student_details where student_id='1002' AND student_name='suresh')
begin
select Column_Name from student_details where student_id='1001' and student_Fee_Type='month'
end
else
begin
select Column_Name from student_details
end

ntitish 8-Mar-13 1:11am

thanks sir i am getting the solution thanks for spending your valuable time on this error

gvprabu 8-Mar-13 1:27am

Hi Nitish,
Read some basic TSQL Programming... then it will help you... welcome

Davidduraisamy · Accepted Answer · 2013-03-07T18:06:00

if exists((SELECT Student_ID,Fee_Type, SUM(amount) AS paid FROM FeePaid_Details WHERE (Student_ID = 'PS20130001') AND (Fee_Type = 'Admission Fee') AND (Month_Details = 'jan') GROUP BY Student_ID, Fee_Type) and (SELECT Student_ID,Fee_Type, SUM(amount) AS paid FROM FeePaid_Details WHERE (Student_ID = 'PS20130001') AND (Fee_Type = 'Admission Fee') AND (Month_Details = 'feb') GROUP BY Student_ID, Fee_Type))
begin
select q.Student_ID,q.Fee_Type,q.amount,q.amount-p.paid as Current_month_due,q.amount-g.paid as previous_month_due,(q.amount-g.paid)+(q.amount-p.paid) as total_amount from Student_Fee_Quotations q join(select d.Student_ID,d.Fee_Type,sum(d.amount) as paid from FeePaid_Details d where d.Student_ID='PS20130001' and d.Fee_Type='Admission Fee' and d.Month_Details='jan' group by d.Student_ID,d.Fee_Type )as g on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee' join (select d.Student_ID,d.Fee_Type,sum(d.amount) as paid from FeePaid_Details d where d.Student_ID='PS20130001' and d.Fee_Type='Admission Fee' and d.Month_Details='feb' group by d.Student_ID,d.Fee_Type ) as p on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee'
end
else if exists((SELECT Student_ID,Fee_Type, SUM(amount) AS paid FROM FeePaid_Details WHERE (Student_ID = 'PS20130001') AND (Fee_Type = 'Admission Fee') AND (Month_Details = 'jan') GROUP BY Student_ID, Fee_Type) or (SELECT Student_ID,Fee_Type, SUM(amount) AS paid FROM FeePaid_Details WHERE (Student_ID = 'PS20130001') AND (Fee_Type = 'Admission Fee') AND (Month_Details = 'feb') GROUP BY Student_ID, Fee_Type))
begin
if exists(SELECT Student_ID,Fee_Type, SUM(amount) AS paid FROM FeePaid_Details WHERE (Student_ID = 'PS20130001') AND (Fee_Type = 'Admission Fee') AND (Month_Details = 'jan') GROUP BY Student_ID, Fee_Type)
begin
select q.Student_ID,q.Fee_Type,q.amount,q.amount as Current_month_due,q.amount-g.paid as previous_month_due,(q.amount-g.paid)+(q.amount) as total_amount from Student_Fee_Quotations q join(select d.Student_ID,d.Fee_Type,sum(d.amount) as paid from FeePaid_Details d where d.Student_ID='PS20130001' and d.Fee_Type='Admission Fee' and d.Month_Details='jan' group by d.Student_ID,d.Fee_Type )as g on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee'
end
else if exists(SELECT Student_ID,Fee_Type, SUM(amount) AS paid FROM FeePaid_Details WHERE (Student_ID = 'PS20130001') AND (Fee_Type = 'Admission Fee') AND (Month_Details = 'feb') GROUP BY Student_ID, Fee_Type)
begin
select q.Student_ID,q.Fee_Type,q.amount,q.amount-p.paid as Current_month_due,0 as previous_month_due,(q.amount-p.paid) as total_amount from Student_Fee_Quotations q join(select d.Student_ID,d.Fee_Type,sum(d.amount) as paid from FeePaid_Details d where d.Student_ID='PS20130001' and d.Fee_Type='Admission Fee' and d.Month_Details='feb' group by d.Student_ID,d.Fee_Type ) as p on q.Student_ID='PS20130001' and q.Fee_Type='Admission Fee'
end
end
else
begin
select Student_ID,Fee_Type,amount,amount as Current_month_due,0 as previous_month_due,amount as total_amount from Student_Fee_Quotations where Student_ID='PS20130001' and Fee_Type='Admission Fee'
end