ShockUK
asked on
Amend a union query, to include Having/Where statement
Hello, I have the following code which I have attached and in the 2nd table called "ledger_current_irb_balanc
I want to change the syntax so it doesn't pull across records with the world
*Internal* in cust_type_code field.
Can some change the code for this to work?
Thanks
I want to change the syntax so it doesn't pull across records with the world
*Internal* in cust_type_code field.
Can some change the code for this to work?
Thanks
SELECT
ccs.figures AS rr,
sum(remain_to_roll) AS remain,
sum(month_end_debt)-sum(remain_to_roll) AS sixty_plus,
sum(month_end_debt) AS month_end,
sum(ninety_plus) AS over_ninety
FROM
ledger_current_icoms_balances l_bal
INNER JOIN
cht_collections_stuff ccs
ON l_bal.c_c_c = ccs.index_field
GROUP BY
ccs.figures
UNION ALL SELECT
ccs.figures as rr,
sum(remain_to_roll) AS remain,
sum(0-remain_to_roll+month_end_debt) AS sixty_plus,
sum(month_end_debt) AS month_end,
sum(ninety+onetwenty) AS over_ninety,
FROM
ledger_current_irb_balances l_bal
INNER JOIN
cht_collections_stuff ccs
ON
l_bal.c_c_c = ccs.index_field
GROUP BY
ccs.figures
ASKER
I created the changes as below, but when I run the syntax I get the following error:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing,
or the punctuation is incorrect
The SELECT statement includes a reserved word or an argument name that is misspelled or missing,
or the punctuation is incorrect
SELECT
ccs.figures AS rr,
sum(remain_to_roll) AS remain,
sum(month_end_debt)-sum(remain_to_roll) AS sixty_plus,
sum(month_end_debt) AS month_end,
sum(ninety_plus) AS over_ninety
FROM
ledger_current_icoms_balances l_bal
INNER JOIN
cht_collections_stuff ccs
ON l_bal.c_c_c = ccs.index_field
GROUP BY
ccs.figures
UNION ALL SELECT
ccs.figures as rr,
sum(remain_to_roll) AS remain,
sum(0-remain_to_roll+month_end_debt) AS sixty_plus,
sum(month_end_debt) AS month_end,
sum(ninety+onetwenty) AS over_ninety,
FROM
ledger_current_irb_balances l_bal
INNER JOIN
cht_collections_stuff ccs
ON
l_bal.c_c_c = ccs.index_field
Where l_bal.cust_type_code= chr(42) & "Internal" & chr(42)
GROUP BY
ccs.figures;
You need to be sure that there is a space between _field and Where (lines 37 & 38)
ASKER
100% sure there is a space there, also a carriage return
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
cht_collections_stuff ccs
ON
l_bal.c_c_c = ccs.index_field
Where 1_bal.cust_type_code= chr(42) & "Internal" & chr(42)
(I am assuming you mean "*Internal*" literally and you are not using * as a wildcard.)