?
Solved

Sybase group by

Posted on 2007-11-20
6
Medium Priority
?
838 Views
Last Modified: 2012-06-21
I have to join two tables on a column name
1) table has

Name      Group      Account      
John      1      1      
John      1      2      
Harry      1      3      
Smith      2      4      

table 2)
Name      Address
John      NJ
John      NY
Harry      CA
Smith      MO

and group similar groups together. If I use group by it doesn't provide right results. Please advise.
0
Comment
Question by:saibsk
[X]
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
  • 3
  • 2
6 Comments
 
LVL 39

Expert Comment

by:Adam314
ID: 20323519
What results do you want?
0
 

Author Comment

by:saibsk
ID: 20323542

Name      Group      Account      Address
John      1      1      NJ
John      1      2      NY
Harry      1      3      CA
Smith      2      4      MO
0
 

Author Comment

by:saibsk
ID: 20323552
I want to write to a file in this format. I want to group all the accounts of same group number together when I write to the file. I cannot use group by when I write the query. It is providing confusing results. I am using another table to join for generating the above result.

#Group 1 Accounts
John, 1, 1, NJ, 2, NY
Harry, 1, 3, CA

#Group 2 accounts
Smith, 2, 4, MO      

I could write it to the file but I want to write similar groups together something like above.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 39

Expert Comment

by:Adam314
ID: 20323598
How do you know John with Account 1 is the NJ address an not the NY address?

To get the results ordered by group, use
    ORDER BY Group
0
 
LVL 19

Accepted Solution

by:
grant300 earned 2000 total points
ID: 20323729
The GROUP BY is doing exactly what it is supposed to do.  The problem is that a GROUP BY, while necessary, is not adequate by itself.  You are doing more than that; you are also doing a pivot to stack multiple records with the same NAME in the same row.

The bad news is that this not supported directly in SQL.  No way in a single query.

The good news is that, within certain constraints, there are tricks you can do to get something like the result you want.

Given that you are trying to do this from Perl, I would recommend you get the raw query results you need from the database and do the funky formatting in Perl.  Break processing and stacking string values is probably easier for you to do there than it would be for us to introduce a bunch of complex SQL and stored procedure constructs.

The SQL gets pretty simple; no group by required...

SELECT A.NAME, A.GROUP, A.ACCOUNT, B.ADDRESS
   FROM <TABLE A> A
    JOIN <TABLE B> B
       ON B.NAME = A.NAME
 ORDER BY A.GROUP, A.NAME, A.ACCOUNT

Then in Perl, you are going to take these results and do your report writing.
 - Keep track of the last Group and Name values
 - Every time the Group changes, spit out a Group Header
 - Every record that comes in with the same Name, add the Account and Address fields to the output record string
 - Every time the Name changes, spit out the output record string, clear it, and drop in the new Name, Group, Account, and Address field.
 - When you run our of records, spit out the output record string

That ought to do it for you with no psychedelic SQL and no stored procedures.

Regards,
Bill
0
 

Author Closing Comment

by:saibsk
ID: 31410232
Thanks. Works great.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Many time we need to work with multiple files all together. If its windows system then we can use some GUI based editor to accomplish our task. But what if you are on putty or have only CLI(Command Line Interface) as an option to  edit your files. I…
A year or so back I was asked to have a play with MongoDB; within half an hour I had downloaded (http://www.mongodb.org/downloads),  installed and started the daemon, and had a console window open. After an hour or two of playing at the command …
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Six Sigma Control Plans

718 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