We help IT Professionals succeed at work.

Why can't I concatenate with a comma?

DalTXColtsFan
on
Why is that this SQL works:

select UPC || ',' || UPC from Products

but this doesn't:

select UPC || ',' from Products

I'm trying to select a set of UPCs with commas after them so I can paste them into the WHERE clause of another querying utility.
Comment
Watch Question

Most Valuable Expert 2011
Top Expert 2012

Commented:
what doesn't work about it?  

raises error? wrong results? never returns? keyboard catches fire?

Author

Commented:
Good question.

It returns the UPCs without the commas.

But the first SQL statement returns the UPCs with the comma between them.
Most Valuable Expert 2011
Top Expert 2012
Commented:
how are you running these queries?

if you have upc "123"

the first query should return

"123,123"

the second should return

"123,"

if you're not seeing those results,  either you have hit a significant "wrong results" bug in oracle, or whatever query/reporting tool you are using is messing with your results.

Author

Commented:
SQL Navigator was giving me those incorrect results.  SQL*Plus gave me the correct results.
Most Valuable Expert 2011
Top Expert 2012

Commented:
To eliminate whatever your tool you are using

try sqlplus.

I created my own products table and queried it with your selects, results were as I expected

what do you get with your table?
if you have many rows, add

WHERE rownum < 10

to your selects

SQL> create table products as
  2  select level upc from dual connect by level < 25;

Table created.

SQL> select UPC || ',' || UPC from Products;

UPC||','||UPC
----------------------------------------------------------------

1,1
2,2
3,3
4,4
5,5
6,6
7,7
8,8
9,9
10,10
11,11

UPC||','||UPC
----------------------------------------------------------------

12,12
13,13
14,14
15,15
16,16
17,17
18,18
19,19
20,20
21,21
22,22

UPC||','||UPC
----------------------------------------------------------------

23,23
24,24

24 rows selected.

SQL> select UPC || ',' from Products;

UPC||','
-----------------------------------------
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,

UPC||','
-----------------------------------------
12,
13,
14,
15,
16,
17,
18,
19,
20,
21,
22,

UPC||','
-----------------------------------------
23,
24,

24 rows selected.

SQL>

Open in new window

Most Valuable Expert 2011
Top Expert 2012

Commented:
ah, you already had it.  ok, glad you found it