Re: Informix SQL help needed
Sam, being too lazy to create a database to test this, I haven't, but
you might try something like the following. See my modifications to
your program below, labelled "## PR" ...
database northern end
define
variable inputd1 date
variable inputd2 date
end
input
prompt for inputd1 using
"Enter Starting DATE for Range > "
prompt for inputd2 using
"Enter Ending DATE for Range > "
end
output
left margin 0
top margin 2
bottom margin 1
page length 60
report to "tmph9"
end
select cusid, sum(selpr) sum_selpr ## PR
from inventory ## PR
where dsold >= $inputd1 ## PR
and dsold <= $inputd2 ## PR
and qques = "I" ## PR
into temp t1 with no log ; ## PR
select
selpr, cusid, dsold, qques, clr
, sum_selpr ## PR
from
inventory ## PR
, t1
where
dsold >= $inputd1
and dsold <= $inputd2
and qques = "I"
and sum_selpr.cusid = inventory.cusid ## PR
order by
sum_selpr, ## PR
cusid
end
format
page header
print
ascii 027, ascii 038, ascii 107, ascii 050, ascii 083,
column 45,
TODAY, 14 spaces, "SOLD REPORT (selling price - count)"
print
column 26, "FROM : ", inputd1, " TO : ", inputd2
skip 1 line
print
"************************************************************",
"************************************************************"
print
4 spaces, "CusID", 10 spaces, "E - #",
12 spaces, "R - #", 9 spaces, "S - #",
10 spaces, "Others - #", 10 spaces, "Totals - #"
print
"************************************************************",
"************************************************************"
skip 1 line
after group of cusid
print
4 spaces, cusid, column 17,
group total of (selpr) where clr = "E" using "#,###,###", " ",
group count where clr = "E" using "####", column 37,
group total of (selpr) where clr = "R" using "#,###,###", " ",
group count where clr = "R" using "####", column 57,
group total of (selpr) where clr = "S" using "#,###,###", " ",
group count where clr = "S" using "####", column 77,
group total of (selpr) where clr <> "E" and clr <> "R"
and clr <> "S" using "#,###,###", " ",
group count where clr <> "E" and clr <> "R"
and clr <> "S" using "####", column 97,
group total of (selpr) using "#,###,###", " ", **** This is
what I am trying to order the report by ****
group count using "####"
skip 1 line
page trailer
print 62 spaces, pageno using "Page <<<"
skip 4 lines
end
... give it a bash and see if it works?
- Paul