Hi Folks,
I am attempting to build a new query subject using SQL (Informix DB) in FM.
I have 7 similar queries and am looking to build query subject for each of them and a UNION ALL for the final report query subject.
SQL type is pass-through (I understand the risks of this), but Native gives the same result. The following error:
RQP-DEF-0191 Column names must be unique. Column name '(constant)' is used more than once.
There is no column called 'constant' and no duplicate columns from what I can see.
I have access to the db catalog so can build it from scratch, but it's an ad-hoc request and not often required, so I am looking for a quicker method.
What is the issue here?
Here is one of the queries:
select stocks.dept_no,
stocks.sub_dept_no,
country.country_name,
br_ord_hdr.branch_no,
branch.branch_name,
0, stocks.stock_no,
descr,
color_size,
"Unproc",
sum(br_ord_line.qty_ordered*stocks.pack_size),
stocks.pack_size,
sum(br_ord_line.qty_ordered*stocks.pack_size*stocks.cost_price),
alloc_hdr.order_no
FROM br_ord_hdr,
stocks,
br_ord_line,
branch,
country,
outer (alloc_hdr, alloc_line)
WHERE br_ord_hdr.branch_no = branch.branch_no
AND branch.country = country.country
AND country.country != "A"
AND br_ord_hdr.br_ord_no = br_ord_line.br_ord_no
AND stocks.stock_no = br_ord_line.stock_no
AND bo_type in (1) AND stocks.dept_no = 55
AND (stocks.sub_dept_no = 29 OR br_ord_line.stock_no in (select stock_no from stocks_query))
AND alloc_line.br_ord_no = br_ord_hdr.br_ord_no
AND alloc_hdr.flow_refer_no = alloc_line.flow_refer_no
AND alloc_hdr.stock_no = stocks.stock_no
AND alloc_line.branch_no = br_ord_hdr.branch_no
GROUP BY 1,2,3,4,5,6,7,8,9,10,12,14
It might be the "Unproc" column, but also, you should alias the two sum columns.
edit - also alias the "Unproc" column.
The aliasing worked for that error...cheers.
I am getting some strange data, but this could be down to the pass-through.
Building from scratch to be 100%