Editing a Command done in Crystal XI 1

[NewB2007]

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!

 

[Madawc]

Does the SQL Command do a Create for a temporary file? That would explain it.

You could also try asking in the SQL forum.

An inefficient but safe alternative is to re-add the table in Crystal, linking to the SQL.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[fprzystas]

Whenever a "Union" is used, you must have the same number of selects in each union. Example: Select a, b, c from alphabet
union
select d,e
from alphabet

This would return an error because the number of selects in the first part do not match the second part.

 

[lbass]

Try:

SELECT
'B' TX_TYPE, TX_DUE, 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, TX_DUE, 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, NULL, 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 3, 4, 9

Note the field (or Null) must be present in each union, and in the same position in the select statement. Since I inserted the field in second position, I changed the Order by clause to reflect the new ordinal positions.

-LB

 

[NewB2007]

Like a charm!!!!!!! Thanks!!!!!