Re: Debit / Credit SQL Output - and a new puzzle
In article <5ril4l$oa@cssun.mathcs.emory.edu>,
Sujit Pal <spal@scotch.den.csci.csc.com> wrote:
>
>Try as I might I could not do this with a single query. Maybe it can be
>done?
Someone posted a single-query solution a last week, it has expired at my
site but I think it went something like this:-
create temp table t1 ( ACCTNUM integer,
VALUE money(8,2),
TRANTYPE char(6) ) with no log ;
insert into t1 values ( 12000, 50.00 , "Credit" ) ;
insert into t1 values ( 12000, 50.00 , "Credit" ) ;
insert into t1 values ( 13000, 100.00 , "Credit" ) ;
insert into t1 values ( 13000, 100.00 , "Credit" ) ;
insert into t1 values ( 13000, 10.00 , "Debit" ) ;
select unique t1Z.ACCTNUM, ( select sum(VALUE)
from t1 t1A
where t1A.ACCTNUM = t1Z.ACCTNUM
and t1A.TRANTYPE = 'Credit'
),
( select sum(VALUE)
from t1 t1B
where t1B.ACCTNUM = t1Z.ACCTNUM
and t1b.TRANTYPE = 'Debit'
)
from t1 t1Z;
which seems to work. Initially I didn't think it could be done without
temp tables, but I now stand corrected.
OK, here's my 4GL reporting puzzle: how do I produce a report ordered
like this:
ALABAMA LOUISIANA OHIO
ALASKA MAINE OKLAHOMA
ARIZONA MARYLAND OREGON
ARKANSAS MASSACHUSETTS PENNSYLVANIA
CALIFORNIA MICHIGAN RHODE ISLAND
COLORADO MINNESOTA SOUTH CAROLINA
CONNECTICUT MISSISSIPPI SOUTH DAKOTA
DELAWARE MISSOURI TENNESSEE
FLORIDA MONTANA TEXAS
GEORGIA NEBRASKA UTAH
HAWAII NEVADA VERMONT
IDAHO NEW HAMPSHIRE VIRGINIA
ILLINOIS NEW JERSEY WASHINGTON
INDIANA NEW MEXICO WEST VIRGINIA
IOWA NEW YORK WISCONSIN
KANSAS N CAROLINA WYOMING
KENTUCKY NORTH DAKOTA
One way would be load up an array, but that would set a limit on the number
of elements we would handle (since the array has to be declared as of being
of some finite size). Is there a better way to do it?
- Paul