We are migrating our PRODUCTION database from MSSQL to Oracle 11G. Part of this process is to recreate some existing stored procedures from TSQL to Oracle SQL. The following discussion is part of that conversion and will be used in a SP.
Being new to Oracle, I have a question on best practice in a specific situation.
Essentially, I have a table that summarizes data from a sales transaction table. The basic layout of the table (F5542HIS) is as follows (* indicates index):
*NSAN8 (sold_to) number
*NSSHAN (ship_to) number
*NSMCU (sold_from) varchar(4)
*NSITM (item) number
*NSCTRY (Century) number
*NSYR (year) number
*NSMNTH (month) number
*NSDCTO (Order Type) varchar(2)
*NSLNTY (Order Line Sales Type) varchar(1)
NSAEXP (Total Extended Price) number
NSSOQS (Total Units Shipped) number
NSECST (Total Extended Cost) number
On my first pass of the script, I populate the table with the primary index values (indicated by the * above, this script is NOT shown).
On the second pass, I need to do an update to this table to populate the summation values from my transaction tables. The transaction tables are the F4211 (open orders) and F42119 (order history), so a union is required.
So, I need to do an update based on the following select statement which returns the proper data:
select sum(cast(sdaexp as numeric)) sumaexp,
sum(cast(sduorg as numeric)) sumuorg,
sum(cast(sdecst as numeric)) sumecst
from PRODDTA.F4211 a inner join proddta.f5542his b
on(a.sdan8 = b.nsan8 and
a.sdshan = b.nsshan and
a.sditm = b.nsitm and
a.sdlnty = b.nslnty and
a.sddcto = b.nsdcto and
a.sdlnty = b.nslnty and
ltrim(rtrim(a.sdmcu)) = ltrim(rtrim(b.nsmcu)) )
where sdtrdj >= 108001 and
sdtrdj <= 108031 and
group by sdaexp, sduorg, sdecst
UNION
select sum(cast(sdaexp as numeric)) sumaexp,
sum(cast(sduorg as numeric)) sumuorg,
sum(cast(sdecst as numeric)) sumecst
from PRODDTA.F4211 a inner join proddta.f5542his b
on(a.sdan8 = b.nsan8 and
a.sdshan = b.nsshan and
a.sditm = b.nsitm and
a.sddcto = b.nsdcto and
a.sdlnty = b.nslnty and
ltrim(rtrim(a.sdmcu)) = ltrim(rtrim(b.nsmcu)) )
where sdivd >= 108001 and
sdivd <= 108031 and
group by sdaexp, sduorg, sdecst
Notice that the SDTRDJ (transaction date) and also the SDIVD (Invoice date) are using the values >= 108001 and <= 108031. These are JULIAN DAYS (not julian dates). It is a strange method of retaining dates, but it works. 108001 = Jan 1, 2008, and 108031 = Jan 31, 2008.
Also, the F5542HIS table will have a WHERE clause on the update for this example where the NSCTRY (Century) = 20, NSYR (Year) = 8, and NSMNTH (Month) = 1. So essentially, the structure could be inthe form:
UPDATE f5542HIS
SET nsaexp=sumaexp, nsuorg-sumuorg, nsecst=sumecst from<union>
where nsctry=20 and nsyr=8 and nsmnth=1
I have been looking at the SQL `Update` and also the SQL `MERGE` functions. Can someone help me define the best practice for formatting this statement into either an `UPDATE` or `MERGE` that will work here?