Execute multiple queries of oracle with loops in C#

Question

I want to execute multiple select queries along with for and if loops.

Tables: A: Orderno, ContractNo

B: ContractNo, ItemNo, Price, TotalAmount

C: OrderNo, ITemNo, Quantity

One contract can have multiple Orders(Contract and Orders have one-to-many relationship). I have only ItemNo and ContractNo as input.

Below is my pseudocode for the same.

    Find the total amount for the item no from table B. 
    List the orders for the same contract.
    Foreach(orderno in orders)
        List items in orderno
        if(itemno matches 1)
        get the quantity (which is equal to totalamount/price)
    End
    Print quantity

I am new to Oracle, I guess this can be done with a stored procedure but I am not sure whether it can fulfill the requirement of pseudocode. Can anyone advice or give me an idea of what can be used to execute this block of code?

Answer 1

I want to get total item quantity value for each order no (rows) within a contract. I wonder how that can be done in a single query

SELECT o.contractid, SUM(i.quantity)
FROM 
  orders o
  JOIN orderitems i ON i.orderid = o.orderid
WHERE
  i.itemno = 1
GROUP BY 
  o.contractid

It should go without saying but.. as you haven't posted exactly what tables and column names you have, I've had to guess at some things

It appears you have Orders that asa assigned to Contracts. I thus assumed that an Order has a ContractId.

Orders have Items which seem to have a Quantity - item is thus like a line detail on an order. For example an Order for a server has a line Item for Memory and a Quantity of 4 if there are 4 sticks of ram in the server

You only want items of ID 1. If A contract has 3 orders, and each order has 4 servers, and each server has 8 sticks of ram (which is ID 1) then the query there gets you contract C1, and a summed quantity of 96 - 3 orders * 4 servers * 8 sticks = 96. If there were 2 contracts, c1 (we mentioned already) and c2 and if c2 was 5 orders of 1 server with 2 sticks of ram then you'd have 2 rows in your results, a 96 and a 10