Invalid Expression error

[#1]

any ideas why im getting an error? syntax looks ok to me... thanks

Expected:
NOCONCATENATE LOAD
order_no as po_no,
min(expected_date) as min_expected_date,
if(max_date_received > expected_date, inv_order_no) as lateOrders
RESIDENT opdetm
GROUP BY order_no;

[kaushiknsolanki · Accepted solution]

Hi,

   You missed one more field in group by condition.

   Try this.

Expected:

NOCONCATENATE LOAD

order_no as po_no,

min(expected_date) as min_expected_date,

if(max_date_received > expected_date, inv_order_no) as lateOrders

RESIDENT opdetm

GROUP BY order_no,if(max_date_received > expected_date, inv_order_no);

Regards,

Kaushik Solanki

[pat_agen]

hi,

you are using a group by command so any fields not referred to in the group by area will have to have an aggregation function.

min(expected_date) is ok but the following line wont work as neither max_date_recieved nor expected_date have an aggregation function.

your query is basically bringing back one line per order_no but it would appear that you would want one line per lateOrder is that right?