Hi Anand,
The error which is coming is same and is as below:
[10:28:40.778] Traceback (innermost last):
File "../lib/webnotes/handler.py", line 159, in handle
execute_cmd(cmd)
File "../lib/webnotes/handler.py", line 192, in execute_cmd
ret = call(method, webnotes.form_dict)
File “…/lib/webnotes/handler.py”, line 211, in call
return fn(**newargs)
File “…/lib/webnotes/widgets/query_report.py”, line 44, in run
result = [list(t) for t in webnotes.conn.sql(query)]
File “…/lib/webnotes/db.py”, line 111, in sql
raise e
ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select t.*,\n (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Cu’ at line 45”)
The error is coming right before we start the sub-query
The code being used is this:
select
tabItem
.name as “Item Code:Link/Item:150”,
tabItem
.description as “Description::300”,
ifnull(tabItem
.re_order_level,0) as “ROL:Currency:50”,
ifnull(tabBin
.reserved_qty,0) as “SO:Currency:50”,
ifnull(tabBin
.ordered_qty,0) as “PO:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“DEL20A” then tabBin
.actual_qty end),0) as “1D:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“BGH655” then tabBin
.actual_qty end),0) as “2B:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“RG-BGH655” then tabBin
.actual_qty end),0) as “BRG:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“HT-BGH655” then tabBin
.actual_qty end),0) as “BHT:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“FG-BGH655” then tabBin
.actual_qty end),0) as “BFG:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“SLIT-DEL20A” then tabBin
.actual_qty end),0) as “DSL:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“RG-DEL20A” then tabBin
.actual_qty end),0) as “DRG:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“FG-DEL20A” then tabBin
.actual_qty end),0) as “DFG:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“TEST-DEL20A” then tabBin
.actual_qty end),0) as “DTS:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“REJ-DEL20A” then tabBin
.actual_qty end),0) as “DRJ:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“RM-DEL20A” then tabBin
.actual_qty end),0) as “DRM:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“RM-BGH655” then tabBin
.actual_qty end),0) as “BRM:Currency:50”
from
tabItem
left join tabBin
on
tabItem
.name = tabBin
.item_code
where
tabBin
.item_code <>“”
and tabBin
.item_code NOT REGEXP ‘^CN’
and tabBin
.item_code NOT REGEXP ‘^J’
and tabBin
.item_code = tabItem
.name
group by tabItem
.name
order by tabItem
.name asc
(select t.*,
(‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’) as tot1,
(case when ‘1D:Currency:50’ + ‘2B:Currency:50’ < SO:Currency:50’ then “1 CORD” when (‘1D:Currency:50’ + ‘2B:Currency:50’ + ‘BRG:Currency:50’ + ‘BHT:Currency:50’ < ‘SO:Currency:50’ + ‘ROL:Currency:50’ then “2 CSTK” end)
from (
select
tabItem
.name as “Item Code:Link/Item:150”,
tabItem
.description as “Description::300”,
ifnull(tabItem
.re_order_level,0) as “ROL:Currency:50”,
ifnull(tabBin
.reserved_qty,0) as “SO:Currency:50”,
ifnull(tabBin
.ordered_qty,0) as “PO:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“DEL20A” then tabBin
.actual_qty end),0) as “1D:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“BGH655” then tabBin
.actual_qty end),0) as “2B:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“RG-BGH655” then tabBin
.actual_qty end),0) as “BRG:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“HT-BGH655” then tabBin
.actual_qty end),0) as “BHT:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“FG-BGH655” then tabBin
.actual_qty end),0) as “BFG:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“SLIT-DEL20A” then tabBin
.actual_qty end),0) as “DSL:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“RG-DEL20A” then tabBin
.actual_qty end),0) as “DRG:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“FG-DEL20A” then tabBin
.actual_qty end),0) as “DFG:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“TEST-DEL20A” then tabBin
.actual_qty end),0) as “DTS:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“REJ-DEL20A” then tabBin
.actual_qty end),0) as “DRJ:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“RM-DEL20A” then tabBin
.actual_qty end),0) as “DRM:Currency:50”,
ifnull(min(case when tabBin
.warehouse=“RM-BGH655” then tabBin
.actual_qty end),0) as “BRM:Currency:50”
from
tabItem
left join tabBin
on
tabItem
.name = tabBin
.item_code
where
tabBin
.item_code <>“”
and tabBin
.item_code NOT REGEXP ‘^CN’
and tabBin
.item_code NOT REGEXP ‘^J’
and tabBin
.item_code = tabItem
.name
group by tabItem
.name
order by tabItem
.name asc
) t
On Saturday, January 12, 2013 10:12:19 AM UTC+5:30, Anand Doshi wrote:
Hi Aditya,
When using labels to add (+) or compare (<) in your outer query, put them in accented quotes instead of double quotes.
Eg.
`1D:Currency:50` + `2B:Currency:50` + `BRG:Currency:50` + `BHT:Currency:50`
) as tot1
Give this a try and if no/different error comes, then atleast part of the problem is solved.
Thanks,
Anand.
On Jan 12, 2013 9:01 AM, "Aditya Duggal" <
ad...@gmail.com> wrote:
I have got some help from Stack Overflow, but I am unable to implement since I am getting a syntax error, would appreciate if some one could guide me with the correct syntax to use in the query report.
To get a column of the row total and a logical column, I need to add the following lines, my question on stack overflow is this.
select t.*,
(“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50”
) as tot1,
(case when “1D:Currency:50” + “2B:Currency:50” < “SO:Currency:50”
then ‘1 CORD’
when (“1D:Currency:50” + “2B:Currency:50” + “BRG:Currency:50” + “BHT:Currency:50” < “SO:Currency:50” + “ROL:Currency:50”
then “2 CSTK”
end)
from (<your query>) t
Can someone shed some light on how to use this in correct syntax for my code.
On Friday, January 11, 2013 8:27:44 PM UTC+5:30, Aditya Duggal wrote:
Hi,
Finally I have been able to get what I wanted in the report now just a few todo’s are left. First the code is on the gist. And the comments on the code are:
Version 3: of the code is giving the results as per my satisfaction, now the TODOs are:
1. Get a ROW Total based on selected columns.
2. Get the COLUMN Total as well for various WAREHOUSES.
3. Get a New Column Based on defined RULES (basically this column would tell if we need to manufacture more of an item or not)
- This is the most important TODO to make this report generic, we need to make the code dynamic so that the code can be used in any system without much ado.
My suggestion is that if we could then we should REPLACE the NEW STOCK LEVEL REPORT by this report since it gives a good idea of the stock items in a SNAPSHOT view.
On Friday, January 11, 2013 3:44:18 PM UTC+5:30, Aditya Duggal wrote:
I have created a gist for the above report, i am working on it if anyone can come up with any solution then do let me know:
https://gist.github.com/c7b6539f088c454f189a
On Friday, January 11, 2013 2:09:04 PM UTC+5:30, Aditya Duggal wrote:
Hi,
I am planning to make a report, which we have been using on excel for a long time by exporting the BIN table report and then running a macro to show a pivot table report, I guess I have sent this excel sheet to you sometime back as well
Now what is this report, this report is:
- Based on “BIN” table
- BIN table is joined with the ITEM table, so that we could fetch fields like Re-Order Level (X) and Description, since the two fields are not in BIN table.
- This report basically would convert the the rows of BIN table into columns, therefore in this report we would have ONE ROW per item with columns showing actual quantities in Warehouses (D). So if we have 2 warehouses then D1 and D2 would show the actual quantities in the 2 warehouses.
- Now there would be a column for Reserved Quantities (A), which would show the TOTAL Reserved Quantity across all Warehouses.
- Similarly the columns for Ordered (B) and Planned quantities (C) would show all the sum of all the warehouses.
- Projected Quantities would be = D1 + D2 + B + C -A
- Another column we could add which would be like a Calculation column, which could suggest the person what to do
- Like if : D1 + D2 < A (when items on order are more than the items on Stock), then the column would show "Give Production"
- If D1+D2>A but D1+D2 < A+X (this means that the warehouses have sufficient quantities for order, but after the order is delivered the quantities would go below the re-order level and hence the column should show "Give production order for STOCK"
- Similarly we could add a number of rules via the IF command and hence we would have a READY reckoner for the Business Intelligence.
This is the code I am planning to write and you people could tell me if this is the right direction (would not break the system), and once the report is ready I would post the report to you.
Please help me with the below code, the problem being faced is this
- I want to show each warehouse in a column, rather than in a row which would be a perfect thing for me and I guess for others as well.
- The problem is that the number of WAREHOUSE should be dynamic in the code so if we have 10 warehouses then it would show the quantities in 10 columns.
- But I guess that there is no FUNCTION of PIVOT TABLE in mysql, but on my google search found out some work arounds which obviously I was not able to understand.
So I would request the developers to kindly look into this and preferably make a report in QUERY so that we could add a column of our own logic if the need be
select
`tabBin`.item_code as "IC:Link/Item:150",
`tabItem`.description as "Description::250",
`tabItem`.re_order_level as "ROL::40",
`tabBin`.warehouse as "WH::80",
`tabBin`.reserved_qty as "SO::40",
`tabBin`.ordered_qty as "PO::40",
`tabBin`.planned_qty as "PLAN::40",
`tabBin`.actual_qty as "QTY::40"
from
`tabItem`
`tabItem`.name=`tabBin`.item_code
)
where
`tabBin`.actual_qty <>""
order by `tabBin`.item_code asc
–
You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.
To post to this group, send email to erpnext-dev…@googlegroups.com.
To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/F7SYdXLPIFIJ.
For more options, visit https://groups.google.com/groups/opt_out.
–
You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.
To post to this group, send email to erpnext-dev…@googlegroups.com.
To unsubscribe from this group, send email to erpnext-developer-forum+unsubscr…@googlegroups.com.