Q

Working with InterDev and DreamWeaver

This Content Component encountered an error
I am designing a Web application using InterDev and DreamWeaver that accesses an Oracle Table. I have an ASP page that runs a query of selected filters. The answers are then posted to a table on a results ASP page. I want to have report information that totals certain results of the query. for instance- Fields are INDEXPTS(numeric),MIR5P(varchar), STATUS(varchar), & TOTALCREDIT(numeric), ISSUETO(varchar).

If I query ISSUETO I may have several rows returned with various data from the other fields. It might look like this (under the column titles):

 
ISSUETO, MIR5P, INDEXPTS, STATUS, TOTALCREDIT
ABC CO, yes,  100,  closed,  234.90;
ABC CO, yes,  10,  closed,   45.00;
ABC CO,  ,   ,     open,  67.00;
ABC CO, no,   ,    open,  0.00 

In a separate table I want to know, specific to each query:
Number of rows returned (issueto), how many "yes" values were returned(mir5p), sum of index points(indexpts), how many "open" values were returned(status), and sum of charges(totalcredit) I got the sum of charges to work by: if not isnull(rs("totalcredit")) then total cost=totalcost + cdbl(rs("totalcredit")) and then the asp in the report table has <%=formatcurrency(totalcost)%> to display the results.

You may have to use the GROUP by function in your SQL statement. I set up a table called SHELLYS_TABLE (sorry I spelled your name wrong) and inserted data in the table as follows.

If you want an output that will be something like the following for the company ABC CO

SELECT issueto, MIR5P, status, SUM(indexpts), SUM(TOTALCREDIT) FROM SHELLYS_TABLE WHERE ISSUETO='ABC CO' GROUP BY ISSUETO, MIR5P, STATUS order by DECODE(MIR5P, NULL, '999999', MIR5P) DESC , STATUS DESC / If you want the counts of certain columns, say MIR5P and status then, you can use count function on these columns. For the second example I ran the query for the entire table, the query and its output is provided below:

SELECT issueto, MIR5P, status, count(MIR5P) CNT_M, count(status) CNT_S, SUM(indexpts) SUM_IDP, SUM(TOTALCREDIT), SUM_TC FROM SHELLYS_TABLE GROUP BY ISSUETO, MIR5P, STATUS order by ISSUETO, DECODE(MIR5P, NULL, '999999', MIR5P) DESC , STATUS DESC /

SELECT issueto, MIR5P, status, count(MIR5P), count(status), SUM(indexpts), SUM(TOTALCREDIT) FROM SHELLYS_TABLE WHERE ISSUETO='123 CO' GROUP BY ISSUETO, MIR5P, STATUS order by DECODE(MIR5P, NULL, '999999', MIR5P) DESC , STATUS DESC /

This was first published in February 2005

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close