Breaks for SQL

From: Mechanical Mann <eric_at_REMOVE_SPAMspeco.com>
Date: Tue, 30 Oct 2001 10:46:50 -0600
Message-ID: <9rmljo$qtk$1_at_bob.news.rcn.net>

I have 2 tables. An item table and a bill of materials. I'm trying to report component items for assemblies. I've got the select statement to return the data I want but I'm having trouble with column breaks.

The problem arises when I join the item table a second time to get the component's description.

This code works:

COLUMN t$item HEADING 'ASSY NO'
COLUMN t$sitm HEADING 'COMPONENT'
COLUMN t$dsca HEADING 'DESCRIPTION'

BREAK ON t$item SKIP 2 ON t$dsca

SELECT fn.t$item, fn.t$dsca, bm.t$sitm

   FROM baan.ttiitm001100 fn, baan.ttibom010100 bm, baan.ttiitm001100 rw WHERE fn.t$item = bm.t$mitm

     AND bm.t$sitm = rw.t$item
    AND fn.t$dsca like '%PAWL ASS%'
ORDER BY fn.t$item, fn.t$dsca, bm.t$sitm /

Producing this output:

ASSY NO          DESCRIPTION                                   COMPONENT
---------------- ------------------------------ ----------------
106935           LONG PAWL ASSEMBLY (4 PARTS)   102316

108008

108245

108574

108803 SHORT PAWL ASSEMBLY (4 PARTS) 108008 108245

    108481

108574

If I change my Select statement to read

SELECT fn.t$item, fn.t$dsca, bm.t$sitm, rw.t$dsca

I get this output:

ASSY NO          DESCRIPTION                              COMPONENT

DESCRIPTION

---------------- ------------------------------ ---------------- -----------
-------------------
106935           LONG PAWL ASSEMBLY (4 PARTS)   102316           LONG PAWL
                       LONG PAWL ASSEMBLY (4 PARTS)   108008           PAWL
PIN F664
                       LONG PAWL ASSEMBLY (4 PARTS)   108245           PAWL
SPRING F71
                       LONG PAWL ASSEMBLY (4 PARTS)   108574           PAWL

KNOB F70 108803 SHORT PAWL ASSEMBLY (4 PARTS) 108008 PAWL PIN F664

                      SHORT PAWL ASSEMBLY (4 PARTS)  108245           PAWL
SPRING F71
                      SHORT PAWL ASSEMBLY (4 PARTS)  108481           SHORT
PAWL F338
                      SHORT PAWL ASSEMBLY (4 PARTS)  108574           PAWL

KNOB F70 I want to have the assembly description appear only once. I've tried messing with the column aliases but have had no luck.

Any ideas?

Thank you one and all

Eric Received on Tue Oct 30 2001 - 17:46:50 CET