Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Amend a union query, to include Having/Where statement

Posted on 2008-10-24
5
232 Views
Last Modified: 2012-05-05
Hello, I have the following code which I have attached and in the 2nd table called "ledger_current_irb_balances" there is a field called "cust_type_code"
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

Open in new window

0
Comment
Question by:ShockUK
  • 3
  • 2
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22794624
INNER JOIN
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.)
0
 

Author Comment

by:ShockUK
ID: 22795361
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
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;

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 22795869
You need to be sure that there is a space between _field and Where (lines 37 & 38)
0
 

Author Comment

by:ShockUK
ID: 22811392
100% sure there is a space there, also a carriage return
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 22812053
How are you running this?
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access 2016 - query 23 72
T-SQL Query to include null values 3 46
How to update the value of duplicated records (except latest one) 2 24
Stored Proc - Rewrite 42 55
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question