djmulroney
New Member
- Joined
- Apr 11, 2010
- Messages
- 1
I'm trying to create an open items listing, this is still early but having a problem.
I am using Excel 2007, using Microsoft Query via an DSN. I am trying to add a Case Statement to the Select Sql, but its giving me an errror:
[Informix][Informix OBDC Driver][Informix]A syntax error has occurred.
The query (code below) works fine with the following excluded:
CASE ar_type.at_oper WHEN '+' THEN ar_item.ar_amount * 1 WHEN '-' THEN ar_item.ar_amount * -1 End as CorrectAmt
Ultimately I want to sum all the items by ar_item.ar_invoice_nbr and exclude any items that (after rounded to 2 decimal places) exclude any items that total zero. Not sure how to do this yet, so if you do know, Yes Please!!! I'd like to put this into VBA code to create this query, was going to start off with logic first.
I greatly appreciate any help that you have to give, many thanks in advance.
SELECT ar_item.ar_invoice_nbr, ar_item.ar_customer, ar_item.ar_seq_nbr, ar_item.ar_tran_type, ar_item.ar_tran_dt, ar_item.ar_amount, ar_item.ar_due_dt, ar_item.ar_disc_date, ar_item.ar_disc_amt, ar_item.ar_shift_link, ar_type.at_oper, ar_type.at_desc, ar_type.at_gl_db, ar_type.at_gl_cr, ar_type.at_prft_ctr, CASE ar_type.at_oper WHEN '+' THEN ar_item.ar_amount * 1 WHEN '-' THEN ar_item.ar_amount * -1 End as CorrectAmt
FROM ssfactor.ar_item ar_item, ssfactor.ar_type ar_type
WHERE ar_item.ar_tran_type = ar_type.at_type_code AND ((ar_type.at_prft_ctr>=500) AND (ar_item.ar_customer='71180005'))
GROUP BY ar_item.ar_invoice_nbr, ar_item.ar_customer, ar_item.ar_seq_nbr, ar_item.ar_tran_type, ar_item.ar_tran_dt, ar_item.ar_amount, ar_item.ar_due_dt, ar_item.ar_disc_date, ar_item.ar_disc_amt, ar_item.ar_shift_link, ar_type.at_oper, ar_type.at_desc, ar_type.at_gl_db, ar_type.at_gl_cr, ar_type.at_prft_ctr
I am using Excel 2007, using Microsoft Query via an DSN. I am trying to add a Case Statement to the Select Sql, but its giving me an errror:
[Informix][Informix OBDC Driver][Informix]A syntax error has occurred.
The query (code below) works fine with the following excluded:
CASE ar_type.at_oper WHEN '+' THEN ar_item.ar_amount * 1 WHEN '-' THEN ar_item.ar_amount * -1 End as CorrectAmt
Ultimately I want to sum all the items by ar_item.ar_invoice_nbr and exclude any items that (after rounded to 2 decimal places) exclude any items that total zero. Not sure how to do this yet, so if you do know, Yes Please!!! I'd like to put this into VBA code to create this query, was going to start off with logic first.
I greatly appreciate any help that you have to give, many thanks in advance.
SELECT ar_item.ar_invoice_nbr, ar_item.ar_customer, ar_item.ar_seq_nbr, ar_item.ar_tran_type, ar_item.ar_tran_dt, ar_item.ar_amount, ar_item.ar_due_dt, ar_item.ar_disc_date, ar_item.ar_disc_amt, ar_item.ar_shift_link, ar_type.at_oper, ar_type.at_desc, ar_type.at_gl_db, ar_type.at_gl_cr, ar_type.at_prft_ctr, CASE ar_type.at_oper WHEN '+' THEN ar_item.ar_amount * 1 WHEN '-' THEN ar_item.ar_amount * -1 End as CorrectAmt
FROM ssfactor.ar_item ar_item, ssfactor.ar_type ar_type
WHERE ar_item.ar_tran_type = ar_type.at_type_code AND ((ar_type.at_prft_ctr>=500) AND (ar_item.ar_customer='71180005'))
GROUP BY ar_item.ar_invoice_nbr, ar_item.ar_customer, ar_item.ar_seq_nbr, ar_item.ar_tran_type, ar_item.ar_tran_dt, ar_item.ar_amount, ar_item.ar_due_dt, ar_item.ar_disc_date, ar_item.ar_disc_amt, ar_item.ar_shift_link, ar_type.at_oper, ar_type.at_desc, ar_type.at_gl_db, ar_type.at_gl_cr, ar_type.at_prft_ctr