Using SQL Expression

#1

First of all, thanks for this amazing tool.

I wonder if somebody has used the SQL Expressions.
In my model I have two tables one for:

PurchaseOrder_Header
-ph_po_nbr
-ph_vendor_nbr
-ph_date
-ph_amount_total

PurchaseOrder_Detail
-pd_po_nbr
-pd_vendor_nbr
-pd_sku_nbr
-pd_qty
-pd_subtotal

So, it can be multiple records for Detail for every Header. The amount total in Header will be the sum(Subtotal) in Detail for every po_nbr.

So I’m trying to use in SQL Expression:
In table PurchaseOrder_Header, field ph_amount_total
Select sum(pd_subtotal) FROM PurchaseOrder_Detail where pd_po_nbr=:ph_po_nbr

I even try to make a Simple Select as:
Select ph_vendor_nbr FROM PurchaseOrder_Detail where pd_po_nbr=:ph_po_nbr
And all the possible variants to this without succeeding.
If somebody could guide me, it would be appreciated.

Thanks in advance,
Jesus

#2

As possible solution for your first query (Select sum(pd_subtotal) FROM PurchaseOrder_Detail where pd_po_nbr=:ph_po_nbr), I’d probably use something like:

select 
  pd_vendor_nbr,
  sum(pd_qty) as "sum of subtotals"
from PurchaseOrder_Detail
group by pd_vendor_nbr
order by pd_vendor_nbr

Your second query runs into difficulties, because you are SELECTing a column that does not exist (in the PurchaseOrder_Detail table).

If you want to write a SQL statement that calculates the sum of subtotals of the “Details” table, and stores it in the ph_amount_totals column of the “Header” table, you could use:

update PurchaseOrder_Header
set ph_amount_total = ( 
    select 
      sum(pd_qty)
    from PurchaseOrder_Detail
    where ph_vendor_nbr = pd_vendor_nbr
    group by pd_vendor_nbr 
   )

– Tested on an Oracle 12c server.

You could probably get away with NOT having the ph_amount_total column (in the “Header” table) and the pd_subtotal column (in the “Details”) table, as the values stored in these columns are “calculated” ie they can be generated on the fly.

Also, I’m not quite sure why you have used the operator := in the WHERE clause of your queries (did you want to use bind variables?).