Concatenate (in Oracle)

[eddy54]

Hi All,

I'm trying to concatenate one field with another and it's not working.

Here's an example of my statement.

SELECT customer.job_name || customer.cust_cd, balances.bal_name, sum (cust_ar.balance)
FROM customer, balances, cust_ar

I'm using the Pipe symbol (||)
ans as you can see I have a Sum in my select statement, so I need a group by clause. When I add it, I'm not able to get the results of my query.

I tried different things and It dosen't work.

Do you have any idea what could be the appropriate syntax for the group by clause.

Thanks
Ed

 

[PHV]

SELECT customer.job_name || customer.cust_cd, balances.bal_name, sum (cust_ar.balance)
FROM customer, balances, cust_ar
WHERE ....
GROUP BY customer.job_name, customer.cust_cd, balances.bal_name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[eddy54]

I tried and it dosen't work.

And I made a mistake it's not against an Oracle DB but
Informix. I don't if there is big difference between those 2 compare to Oracle Vs SQL since i Oracle we use the || and in the SQL it's more + ('+ .......

thanks
Ed

 

[PHV]

I tried and it dosen't work
Any chance you could explain that ? Anay error message ? Unexpected behaviour ? ...
Anyway, if you really use Informix you may try this:
SELECT customer.job_name || customer.cust_cd, balances.bal_name, SUM(cust_ar.balance)
FROM customer, balances, cust_ar
WHERE ....
GROUP BY 1,2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[eddy54]

The error I got was Syntax error.

I know what I did wrong
as an example here's my SQL

SELECT customer.job_name || customer.cust_cd, balances.bal_name, sum (cust_ar.balance)
FROM customer, balances, cust_ar

then my group by would have to be
Group By customer.job_name, customer.cust_cd, balances.bal_name
The customer.cust_cd has to be added in the group by clause
since I added I'm able to get the right data.

The only issue now that I have is that one of my tool does not recongzine the query and does not return any values.
but I'll figure this one out.

Thanks for the help, I really appreciate

Eddy