Re: A SQL question.

From: John Lillywhite <jlillywhite_at_iap.org.uk>
Date: Mon, 21 Sep 1998 14:46:01 +0100
Message-ID: <6u5l9q$3ug$1@taliesin.netcom.net.uk>

dont know if you can do it in one SQL statement, but heres a PL/SQL block which does the job
rgds,john

Zhang Yiquan wrote in message <36026ab1.3273792_at_news.singnet.com.sg>...
>Hi all.
>
>I would appreciate any help available on this problem.
>
>I have to print some data as a report using an SQL statement.
>It's a line-item and control break kind of report.
>For example I have these...
>
>Type CodeNo Price
>A2 0912 14.00
>A2 0913 16.00
>A2 0914 15.00
>A4 1150 25.00
>A2 0916 18.00
>A4 1152 29.00
>A4 1153 29.00
>
>How would I make a query so that I can get the results returned as
>sorted by Type and then CodeNo with a running serial count for each
>Type? For example....
>
>
>Report Title
>
>Count Type CodeNo Price
>1 A2 0912 14.00
>2 A2 0913 16.00
>3 A2 0914 15.00
>4 A2 0916 18.00
>1 A4 1150 25.00
>2 A4 1152 29.00
>3 A4 1153 29.00
>
>
>The thing here is HOW to produce that Count column that would reset at
>every change in Type? I've tried grouping and ordering and even
>rownum, but just cannot get it to reset to 1 at every change in Type.
>
>Thanks in advance!
>
>
>

begin 666 jtest.sql

M+RH@(&QO860@=&%B;&4_at_86YD(&5X=')A8W0@#0HJ+PT*<V5T('-E<G9E<F]U
M='!U="!O;@T*<W!O;VP@:G1E<W0N;&]G#0ID<F]P('1A8FQE(&IT86(R.PT*
M8W)E871E('1A8FQE(&IT86(R#0H@(" @*" @(" @("!T>7!E(" @(" @("!V
M87)C:&%R,B_at_R*0T*(" @(" @(" L(" @8V]D96YB<B @(" @=F%R8VAA<C(H

M-"D-"B @(" @(" @+" @('!R:6-E(" @(" @(&YU;6)E<B_at_V+#(I#0H@(" @ M*3L-"FEN<V5R="!I;G1O(&IT86(R('9A;'5E<R H)T$R)RPG,#DQ,B<L,30N M,# I.PT*:6YS97)T(&EN=&\@:G1A8C(@=F%L=65S("@G03(G+"<P.3$S)RPQ

M-BXP,"D[#0II;G-E<G0@:6YT;R!J=&%B,B!V86QU97,@*"=!-"<L)S Y,30G
M+#$U+C P*3L-"FEN<V5R="!I;G1O(&IT86(R('9A;'5E<R H)T$T)RPG,#(Q
M,B<L,C4N,C4I.PT*:6YS97)T(&EN=&\@:G1A8C(@=F%L=65S("@G03(G+"<Q

M,34S)RPR.2XU-2D[#0HO*B!03"]344P_at_0FQO8VL@=&\@<&5R9F]R;2!Q=65R

M>2 J+R @(" -"F1E8VQA<F4-"B @("!C;G0@;G5M8F5R*#4L,"D@.CTQ.PT*
M(" @($-54E-/4B!J8W5R<R!)4PT*(" @(" @("!314Q%0U0@='EP92QC;V1E
M;F)R+'!R:6-E(" @(" @(" @(" @(" @(" @#0H@(" @(" @(" @("!&4D]-
M(&IT86(R($]21$52($)9('1Y<&4[#0H@(" @<G(@("!J=&%B,B523U=465!%

M.PT*(" @(&QA<W1T>7!E(&IT86(R+G1Y<&4E5%E012 Z/2 G("<[#0IB96=I M;@T*(" @($]014X@:F-U<G,[#0H@(" @1$)-4U]/5510550N4%547TQ)3D4H M)SX_at_0T]53E0@(%194$4@($-/1$5.0E(@(" @("!04DE#12<I.PT*(" @($1" M35-?3U544%54+E!55%],24Y%*"<^("TM+2TM(" M+2TM(" M+2TM+2TM(" @ M(" @+2TM+2TG*3L-"@T*3$]/4 T*(" @($9%5$-((&IC=7)S($E.5$\@<G(N M='EP92QR<BYC;V1E;F)R+')R+G!R:6-E.PT*(" @($58250_at_5TA%3B!J8W5R M<R5.3U1&3U5.1#L-"B @("!)1B!L87-T='EP92 A/2!R<BYT>7!E(%1(14X@

M8VYT.CT@,3L@( T*(" @(" @(" @(" @(" @(" @(" @(" @(" @(" @("!L
M87-T='EP93H]<G(N='EP93L-"B @(" @(" @14Q312 @("!C;G0@.CT_at_8VYT
M("L@,3L-"B @("!%3D0_at_248[#0H@(" @1$)-4U]/5510550N4%547TQ)3D4H
M(" @)SXG?'QT;U]C:&%R*&-N="PG.3DY.3DG*0T*(" @(" @(" @(" @(" @
M(" @(" @(" @('Q\)R @)WQ\;'!A9"AR<BYT>7!E+#0L)R G*0T*(" @(" @

M(" @(" @(" @(" @(" @(" @('Q\)R @)WQ\;'!A9"AR<BYC;V1E;F)R+#<L

M)R G*0T*(" @(" @(" @(" @(" @(" @(" @(" @('Q\)R @)WQ\=&]?8VAA
M<BAR<BYP<FEC92PG.3DY.3DN.3DG*0T*(" @(" @(" @(" @(" @(" @(" @
M(" @("D[#0I%3D0_at_3$]/4#L-"F5N9#L-"B\@(" @(" @(" @(" @(" @(" @
M(" @(" @(" @(" @(" @(" @(" @(" @(" @(" @(" @(" @(" -"G-H;W<@

097)R;W(-"G-P;V]L(&]F9@``
`
end Received on Mon Sep 21 1998 - 08:46:01 CDT