SQL Output

[dbadmin]

Hi,

I have a customer table which I am joining with an address table and another customer code table. There may be many codes and addreses for a CUSTOMER. I need to run a sql statement which will give me output like this.

CUSTOMER_ID addr1 code

10000 100 Main street, My city... 20
200 My Street, That City.... 30
283 That Street, City1 ..... 40

20000 103 Any Street, City2 .... 912
102 My Road, City3 ..... 210


How could I do this? I can achieve this only with sqlplus formatting?

dbadmin.

 

[sommererdbeere]

hi,

assuming that all 3 tables, customer, address, and customer code, has customerID as identical. i will have sth like:

select
c.customerID,
a.addressID,
cc.customerCode
from customer c, address a, customer_code cc
where
c.customerID = a.customerID and
c.customerID = cc.customerID

you might wanna use outerjoin(+) if you want IF you want everything from the table of customer like:

select
c.customerID,
a.addressID,
cc.customerCode
from customer c, address a, customer_code cc
where
c.customerID(+) = a.customerID and
c.customerID = cc.customerID


hope this help. if not, i believe you might need to provide more info like table names and what common field all of these 3 tables have. the more common field the better. this can eliminate duplicates.

m

 

[dbadmin]

Hi,

Thank you for the reply. But your query will give me the output like this

CUSTOMER_ID addr1 code

10000 100 Main street, My city... 20
10000 200 My Street, That City.... 30
10000 283 That Street, City1 ..... 40

20000 103 Any Street, City2 .... 912
20000 102 My Road, City3 ..... 210

In my output I don't want the CUSTOMER_ID to be repeated.

Thanks

dbadmin

 

[nadeu26]

HI

you can type before you run your select :break on customer_id.

 

[dbadmin]

Hi,

It means I can achieve this only with sqlplus formatting. Is it correct? If another application needs its formatted in the way I described, they have to do it in their code, right?

dbadmin

 

[sommererdbeere]

i'm not sure about break on that nadeau26 suggested, but i used decode for repeated value. sometimes it's useful *IF* not many fields is selected or don't have much decode. Otherwise, the sql statement will run slow.

select
a_addID,
cc_custCode,
decode(col1,1,c_custID,'') custID
from(
select
c.customerID c_custID,
a.addressID a_addID,
cc.customerCode cc_custCode,
row_number() over (PARTITION BY a.addressID, cc.customerCode, c.customerID order by a.addressID, cc.customerCode, c.customerID) as col1
from customer c, address a, customer_code cc
where
c.customerID(+) = a.customerID and
c.customerID = cc.customerID

this will eliminate duplicates for customerID.

if you use Crystal report as your outputting report, you can choose "Suppress if duplicates" option.

if you a any better idea besides decode, pleaase posted it as well. i'm trying to eliminate duplicates when 5 tables are join together, but decode takes too much resources.

hope this help u

m

 

[SantaMufasa]

Here are a few thoughts on the above replies to this thread:

DBAdmin: The "group-suppression" feature you need, as Nadeu26 mentions, is available in SQL*Plus. If your target application cannot accept SQL*Plus output, then, as you suggest, the other target application can "do it in their code", if such code is available. If such code is not readily available, and if the other application can run user-defined functions as easily as it can run built-in Oracle functions, then you can write a user-defined function in PL/SQL to achieve the group-suppression behaviour which you seek. (If you want to try that idea, but do not know how to proceed, post a follow-up request here.)

Sommererdbeere: In your first reply, you say, "assuming that all 3 tables...have customerID as identical." Actually, if all three tables have "customerID as identical", then the output would produce extraneous, otherwise duplicate rows for the different codes that have the same customerID.

Also, on your first response, I believe the outer join is backwards. The way it was written ("where c.customerID(+) = a.customerID"), means "Display rows when a CustomerID in the Address table has no matching CustomerID in the Customer table." I believe the opposite condition is the likely scenario, right?

Mufasa
(aka Dave of Sandy, Utah, USA @ 09:26 (06May04) UTC (aka "GMT" and "Zulu"), 02:26 (06May04) Mountain Time)