Here is the command that I am trying to edit. I have never done a report with a command and I am not sure on exactly how to edit it. But here is the code:
SELECT
'B' TX_TYPE, VENDOR_ID, BILL_NUMBER, TX_DATE, ROUND(TX_AMT, 2) TX_AMT,
CURRENCY_CODE, TX_POSTED, AP_BILL_ID ID,
(
SELECT
U.NAME
FROM
UNIT U
WHERE
U.UNIT_SYMBOL = AP_BILL.CURRENCY_CODE AND
U.UNIT_TYPE = 'Y'
) CURRENCY_NAME,
(
SELECT
CASE
WHEN V.BILLING_CODE IS NULL THEN V.NAME || ' - ' || V.VENDOR_ID
ELSE V.VENDOR_ID || ' (CLIENT: ' || V.BILLING_CODE || ')'
END
FROM
VENDOR V
WHERE
V.VENDOR_ID = AP_BILL.VENDOR_ID
) VENDOR_NAME
FROM
AP_BILL
WHERE
TX_POSTED = 'True' AND
TX_TYPE <> 'Credit'
UNION
SELECT
'B' TX_TYPE, VENDOR_ID, BILL_NUMBER, TX_DATE, (ROUND(TX_AMT, 2) * -1) TX_AMT,
CURRENCY_CODE, TX_POSTED, AP_BILL_ID ID,
(
SELECT
U.NAME
FROM
UNIT U
WHERE
U.UNIT_SYMBOL = AP_BILL.CURRENCY_CODE AND
U.UNIT_TYPE = 'Y'
) CURRENCY_NAME,
(
SELECT
CASE
WHEN V.BILLING_CODE IS NULL THEN V.NAME || ' - ' || V.VENDOR_ID
ELSE V.VENDOR_ID || ' (CLIENT: ' || V.BILLING_CODE || ')'
END
FROM
VENDOR V
WHERE
V.VENDOR_ID = AP_BILL.VENDOR_ID
) VENDOR_NAME
FROM
AP_BILL
WHERE
TX_POSTED = 'True' AND
TX_TYPE = 'Credit'
UNION
SELECT
'C' TX_TYPE, PAY_TO VENDOR_ID, BILL_NUMBER, TX_DATE,
((ROUND(CB.TX_AMT, 2) + ROUND(CB.DISCOUNT_AMT, 2)) * -1) TX_AMT,
GL.GL_CURRENCY CURRENCY_CODE, CM.TX_POSTED, CM.CHECK_ID ID,
(
SELECT
U.NAME
FROM
UNIT U
WHERE
U.UNIT_SYMBOL = GL.GL_CURRENCY AND
U.UNIT_TYPE = 'Y'
) CURRENCY_NAME,
(
SELECT
CASE
WHEN V.BILLING_CODE IS NULL THEN V.NAME || ' - ' || V.VENDOR_ID
ELSE V.VENDOR_ID || ' (CLIENT: ' || V.BILLING_CODE || ')'
END
FROM
VENDOR V
WHERE
V.VENDOR_ID = CM.PAY_TO
) VENDOR_NAME
FROM
CHECK_MSTR CM, CHECK_MSTR_BILL CB, GLACCOUNT GL
WHERE
CM.CHECK_ID = CB.CHECK_ID AND
CB.GL_ACCT_ID = GL.GL_ACCT_ID AND
CM.TX_POSTED = 'True' AND
CM.PAY_TYPE = 'V'
ORDER BY 2, 3, 8
************************
I need to add another field to pull the column is called TX_DUE and it comes from the table AP_BILL. I try and add the column to what I feel are the appropriate places and I get something in the affect of the columns do no match with a SQLState of 42826. Again I have no clue on how to do this, but any help would be greatly appreciated! Thanks!
SELECT
'B' TX_TYPE, VENDOR_ID, BILL_NUMBER, TX_DATE, ROUND(TX_AMT, 2) TX_AMT,
CURRENCY_CODE, TX_POSTED, AP_BILL_ID ID,
(
SELECT
U.NAME
FROM
UNIT U
WHERE
U.UNIT_SYMBOL = AP_BILL.CURRENCY_CODE AND
U.UNIT_TYPE = 'Y'
) CURRENCY_NAME,
(
SELECT
CASE
WHEN V.BILLING_CODE IS NULL THEN V.NAME || ' - ' || V.VENDOR_ID
ELSE V.VENDOR_ID || ' (CLIENT: ' || V.BILLING_CODE || ')'
END
FROM
VENDOR V
WHERE
V.VENDOR_ID = AP_BILL.VENDOR_ID
) VENDOR_NAME
FROM
AP_BILL
WHERE
TX_POSTED = 'True' AND
TX_TYPE <> 'Credit'
UNION
SELECT
'B' TX_TYPE, VENDOR_ID, BILL_NUMBER, TX_DATE, (ROUND(TX_AMT, 2) * -1) TX_AMT,
CURRENCY_CODE, TX_POSTED, AP_BILL_ID ID,
(
SELECT
U.NAME
FROM
UNIT U
WHERE
U.UNIT_SYMBOL = AP_BILL.CURRENCY_CODE AND
U.UNIT_TYPE = 'Y'
) CURRENCY_NAME,
(
SELECT
CASE
WHEN V.BILLING_CODE IS NULL THEN V.NAME || ' - ' || V.VENDOR_ID
ELSE V.VENDOR_ID || ' (CLIENT: ' || V.BILLING_CODE || ')'
END
FROM
VENDOR V
WHERE
V.VENDOR_ID = AP_BILL.VENDOR_ID
) VENDOR_NAME
FROM
AP_BILL
WHERE
TX_POSTED = 'True' AND
TX_TYPE = 'Credit'
UNION
SELECT
'C' TX_TYPE, PAY_TO VENDOR_ID, BILL_NUMBER, TX_DATE,
((ROUND(CB.TX_AMT, 2) + ROUND(CB.DISCOUNT_AMT, 2)) * -1) TX_AMT,
GL.GL_CURRENCY CURRENCY_CODE, CM.TX_POSTED, CM.CHECK_ID ID,
(
SELECT
U.NAME
FROM
UNIT U
WHERE
U.UNIT_SYMBOL = GL.GL_CURRENCY AND
U.UNIT_TYPE = 'Y'
) CURRENCY_NAME,
(
SELECT
CASE
WHEN V.BILLING_CODE IS NULL THEN V.NAME || ' - ' || V.VENDOR_ID
ELSE V.VENDOR_ID || ' (CLIENT: ' || V.BILLING_CODE || ')'
END
FROM
VENDOR V
WHERE
V.VENDOR_ID = CM.PAY_TO
) VENDOR_NAME
FROM
CHECK_MSTR CM, CHECK_MSTR_BILL CB, GLACCOUNT GL
WHERE
CM.CHECK_ID = CB.CHECK_ID AND
CB.GL_ACCT_ID = GL.GL_ACCT_ID AND
CM.TX_POSTED = 'True' AND
CM.PAY_TYPE = 'V'
ORDER BY 2, 3, 8
************************
I need to add another field to pull the column is called TX_DUE and it comes from the table AP_BILL. I try and add the column to what I feel are the appropriate places and I get something in the affect of the columns do no match with a SQLState of 42826. Again I have no clue on how to do this, but any help would be greatly appreciated! Thanks!