Need some Oracle SQL help - usually do SQL Server

[SBendBuckeye]

Hello,

If someone could help me figure out the correct Oracle SQL syntax, I would be greatly appreciative. I have to query an Oracle database for Customer information. It will be read only and there are around 5600 total customers. This is the only Oracle piece in the whole project which is already behind schedule so I don't dare take much time.

What I need to do is populate a form combobox with a list of customers, some of which have multiple locations and client ids. Here is the simple case:

Select CustID, CustName From Customer

If a customer has multiple locations I need to append the city and state so the user can differentiate them.

Customer1
Customer2
Customer3 City1 State1
Customer3 City2 State1
Customer3 City3 State2
Customer4

Here is how I plan to do it:

CustomerName || ' - ' || City || ' - ' || State

Here are the problems I am having:

1. According to some documentation I read, SubQueries in Oracle are very slow and the MINUS statement should be used in its place. Here is what I would do in SQL Server and what I think I need to do in Oracle for the top half of a Union query (those with only 1 record):

Select CustID, CustName From Customer Where CustName Not In
(Select CustName From Customer Group By CustName Having Count(CustName) > 1)

Select CustID, CustName From Customer 
Minus
Select CustName From Customer Group By CustName Having Count(CustName) > 1)

For the bottom half (eg those with multiple records), I need to add the address table which requires a left join. Again, here are both examples so you can see what I am trying to do:

Select c.CustID, c.CustName & ' - ' & c.City 
From Customer c Left Join Addr a On c.CustID = a.CustID 
Where CustName In
(Select CustName From Customer Group By CustName Having Count(CustName) > 1)

Select c.CustID, c.CustName || ' - ' || c.City 
From Customer c, Addr a
Where c.CustID = a.CustID(+) 
Minus
Select c.CustID, c.CustName || ' - ' || c.City 
From Customer c, Addr a
Where c.CustID = a.CustID(+) 
Group By CustName Having Count(CustName) > 1)

Putting everything together, I think this is what I need:

Select CustID, CustName From Customer 

Minus

Select CustName From Customer Group By CustName Having Count(CustName) > 1)

Union

Select c.CustID, c.CustName || ' - ' || c.City 
From Customer c, Addr a
Where c.CustID = a.CustID(+) 

Minus

Select c.CustID, c.CustName || ' - ' || c.City 
From Customer c, Addr a
Where c.CustID = a.CustID(+) 
Group By CustName Having Count(CustName) > 1)

Now here's the kicker, there can be multiple address records for a single customer record. This means the bottom half of my query will return too many records.

What I need it to do is only return 1 so I can concatenate the city and state with the customer name. I think I was close up to this point and now I'm stuck. Thanks for any ideas and/or suggestions!


Have a great day!

j2consulting@yahoo.com

 

[nagornyi]

Instead of

From Customer c, Addr a

use

From Customer c, (select distinct CustID, City from Addr) a

 

[sem]

Can you explain in which table CITY colum resides? According to the code provided it's in CUSTOMER but according to the task description it's in ADDR. If the last is true then how about just

Select c.CustID, c.CustName || ' - ' || a.City 
From Customer c, Addr a
Where c.CustID = a.CustID(+)
[code]

???

BTW could you post the source of that sacral knowledge about slowness of Oracle subqueries?

Regards, Dima
[URL unfurl="true"]http://seminihin.narod.ru[/URL]

 

[SBendBuckeye]

sem,

Sorry, it should be a.city as you responded. I was rewriting my code into a shorter example because we use vary long table names and I was trying to get rid of some of the bulk to make it easier to read.

I'm not sure where I saw the link about SubQueries being slower. If that is not true, my job just got a whole lot easier since I know how to do it with SubQueries.

nagornyi,

I need to digest and test what you suggested. I have never seen anything like that usage before.

Thanks to both of you for responding!

Have a great day!

j2consulting@yahoo.com

 

[sem]

Did you try simple outer join that I've suggested above? If so why it doesn't suit you?

Regards, Dima

http://seminihin.narod.ru

 

[SBendBuckeye]

Hello sem,

I only want to concatenate the address information if the customer has more than one location. Multiple locations have different customer ids. Here is some sample data:

ID Name City
1000 Customer1
1001 Customer2
1002 Customer3 City1
1003 Customer3 City2
1004 Customer4
1005 Customer5
1006 Customer6 City2
1007 Customer6 City3
1008 Customer7
1009 Customer8

Thanks for your help!

Have a great day!

j2consulting@yahoo.com