ERPNext Foundation ERPNext Cloud User Manual Blog Discuss Frappé* Donate

Query must be select

bug

#1

i have created a query report and add if else conditions in it. when i execute my query it gives error of
"ValidationError: Query must be a SELECT "

SET @contestType := (SELECT contest_type FROM tabProperty Sales Campaign WHERE campaign_name = %(campaign_name)s);
IF(@contestType = “By Loan Approval Count”) THEN
SELECT
sales_agency_name as “Agencies:Link/Property Sales Campaign:300”,
sales_campaign as “Property Sales Campaign:Link/Property Sales:200”,
COUNT(tabProperty Sales.docstatus) as “Total Count of Loan Approved:Float:200”

FROM
tabProperty Sales
INNER JOIN
tabProperty Sales Loan ON tabProperty Sales.docstatus = tabProperty Sales Loan.docstatus
INNER JOIN
tabProperty Sales Campaign ON tabProperty Sales Campaign.campaign_name = tabProperty Sales.sales_campaign
INNER JOIN
tabSales Campaign Agencies ON tabSales Campaign Agencies.agency_name = tabProperty Sales.sales_agency_name
&&
tabSales Campaign Agencies.parent = tabProperty Sales.sales_campaign

WHERE
tabProperty Sales.docstatus = 1
&&
tabProperty Sales.is_cash_buyer = 1 OR (tabProperty Sales Loan.status = “Approved” AND tabProperty Sales Loan.selected_by_customer = 1)

ELSE IF(@contestType = “Commission Collected”) THEN
SELECT
sales_agency_name as “Agencies:Link/Property Sales Campaign:300”,
sales_campaign as “Property Sales Campaign:Link/Property Sales:200”,
SUM(tabSales Agency Commission.commission_value) as “Total Commission Collected:Float:200”

FROM
tabSales Agency Commission
INNER JOIN
tabProperty Sales Campaign ON tabProperty Sales Campaign.docstatus = tabSales Agency Commission.docstatus
INNER JOIN
tabProperty Sales ON tabProperty Sales Campaign.campaign_name = tabProperty Sales.sales_campaign
INNER JOIN
tabSales Campaign Agencies ON tabSales Campaign Agencies.agency_name = tabProperty Sales.sales_agency_name
&&
tabSales Campaign Agencies.parent = tabProperty Sales.sales_campaign

WHERE
tabSales Agency Commission.eligibility = 1
&&
tabSales Agency Commission.payment_date < tabProperty Sales Campaign.contest_end_date

ELSE
SELECT
sales_agency_name as “Agencies:Link/Property Sales Campaign:300”,
sales_campaign as “Property Sales Campaign:Link/Property Sales:200”,
SUM(sales_price_after_discount) as “Total Sales Value:Float:200”

FROM
tabProperty Sales
INNER JOIN
tabProperty Sales Campaign ON tabProperty Sales Campaign.campaign_name = tabProperty Sales.sales_campaign
INNER JOIN
tabSales Campaign Agencies ON tabSales Campaign Agencies.agency_name = tabProperty Sales.sales_agency_name
&&
tabSales Campaign Agencies.parent = tabProperty Sales.sales_campaign

WHERE
tabProperty Sales Campaign.enable_contest = 1
&&
tabProperty Sales.docstatus = 1
&&
tabSales Campaign Agencies.agency_name = tabProperty Sales.sales_agency_name
&&
tabProperty Sales.booking_date < tabProperty Sales Campaign.contest_end_date
GROUP BY sales_agency_name
ORDER BY sales_price_after_discount DESC
END IF


#2

Please paste your query here.


#3

SET @contestType := (SELECT contest_type FROM tabProperty Sales Campaign WHERE campaign_name = %(campaign_name)s);
IF(@contestType = “By Loan Approval Count”) THEN
SELECT
sales_agency_name as “Agencies:Link/Property Sales Campaign:300”,
sales_campaign as “Property Sales Campaign:Link/Property Sales:200”,
COUNT(tabProperty Sales.docstatus) as “Total Count of Loan Approved:Float:200”

FROM
tabProperty Sales
INNER JOIN
tabProperty Sales Loan ON tabProperty Sales.docstatus = tabProperty Sales Loan.docstatus
INNER JOIN
tabProperty Sales Campaign ON tabProperty Sales Campaign.campaign_name = tabProperty Sales.sales_campaign
INNER JOIN
tabSales Campaign Agencies ON tabSales Campaign Agencies.agency_name = tabProperty Sales.sales_agency_name
&&
tabSales Campaign Agencies.parent = tabProperty Sales.sales_campaign

WHERE
tabProperty Sales.docstatus = 1
&&
tabProperty Sales.is_cash_buyer = 1 OR (tabProperty Sales Loan.status = “Approved” AND tabProperty Sales Loan.selected_by_customer = 1)

ELSE IF(@contestType = “Commission Collected”) THEN
SELECT
sales_agency_name as “Agencies:Link/Property Sales Campaign:300”,
sales_campaign as “Property Sales Campaign:Link/Property Sales:200”,
SUM(tabSales Agency Commission.commission_value) as “Total Commission Collected:Float:200”

FROM
tabSales Agency Commission
INNER JOIN
tabProperty Sales Campaign ON tabProperty Sales Campaign.docstatus = tabSales Agency Commission.docstatus
INNER JOIN
tabProperty Sales ON tabProperty Sales Campaign.campaign_name = tabProperty Sales.sales_campaign
INNER JOIN
tabSales Campaign Agencies ON tabSales Campaign Agencies.agency_name = tabProperty Sales.sales_agency_name
&&
tabSales Campaign Agencies.parent = tabProperty Sales.sales_campaign

WHERE
tabSales Agency Commission.eligibility = 1
&&
tabSales Agency Commission.payment_date < tabProperty Sales Campaign.contest_end_date

ELSE
SELECT
sales_agency_name as “Agencies:Link/Property Sales Campaign:300”,
sales_campaign as “Property Sales Campaign:Link/Property Sales:200”,
SUM(sales_price_after_discount) as “Total Sales Value:Float:200”

FROM
tabProperty Sales
INNER JOIN
tabProperty Sales Campaign ON tabProperty Sales Campaign.campaign_name = tabProperty Sales.sales_campaign
INNER JOIN
tabSales Campaign Agencies ON tabSales Campaign Agencies.agency_name = tabProperty Sales.sales_agency_name
&&
tabSales Campaign Agencies.parent = tabProperty Sales.sales_campaign

WHERE
tabProperty Sales Campaign.enable_contest = 1
&&
tabProperty Sales.docstatus = 1
&&
tabSales Campaign Agencies.agency_name = tabProperty Sales.sales_agency_name
&&
tabProperty Sales.booking_date < tabProperty Sales Campaign.contest_end_date
GROUP BY sales_agency_name
ORDER BY sales_price_after_discount DESC
END IF