Toad World® Forums

SQL question


#1

Please help me with the following.

Example

SELECT DISTINCT fund_code, org_code
FROM whatever_table;

How to change select to get like this (suppress repeated fund_codes).

Thank you,

Bella

fund_code org_code

01 100
200
300

02 150
250

03 610
620


#2

Bella,

Please help me with the following.

Example
SELECT DISTINCT fund_code, org_code
FROM whatever_table;

How to change select to get like this (suppress repeated fund_codes).

Thank you,
Bella
fund_code org_code

01 100
200
300

02 150
250

03 610
620

Before the SELECT add this line:

BREAK ON fund_code skip 1

Then, execute as script (F5) in Toad.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#3

Google sqlplus specifically the break command.

chris


#4

Bella,

If you don’t want to use the SQL*Plus Break command you can do this with
the Analytic function LED.

CREATE TABLE whatever_table
( fund_code VARCHAR2(2),
org_code VARCHAR2(10));

INSERT INTO whatever_table VALUES (‘01’,‘100’);
INSERT INTO whatever_table VALUES (‘01’,‘200’);
INSERT INTO whatever_table VALUES (‘01’,‘300’);
INSERT INTO whatever_table VALUES (‘02’,‘150’);
INSERT INTO whatever_table VALUES (‘02’,‘250’);
INSERT INTO whatever_table VALUES (‘03’,‘610’);
INSERT INTO whatever_table VALUES (‘03’,‘620’);

COMMIT;

SELECT CASE
WHEN LAG(fund_code,1,’-1’)
OVER (ORDER BY fund_code,org_code) != fund_code
THEN
fund_code
ELSE
NULL
END AS fund_code,
org_code
FROM whatever_table;

Ed
[TeamT]

On 7/27/2010 11:15 AM, Bella Bechutsky wrote:

Please help me with the following.

Example
SELECT DISTINCT fund_code, org_code
FROM whatever_table;

How to change select to get like this (suppress repeated fund_codes).
Thank you,
Bella

fund_code org_code

01 100
200
300

02 150
250

03 610
620


#5

Chris,

Unfortunately, I can not use break because I need to use this SQL later in PL/SQL.
Thanks,
bella


#6

But if you are using it in PL/SQL then you are presumably outputting the results of a cursor via program control and thus just blank out values as and when needed.