DB2 V8.2 on AIX using C language applications. I have these two
queries I would like to put together:
"SELECT SUM(products.loc1_size) FROM products WHERE (location1 =3D
'NFUSA1') AND (main_loc IN (SELECT main_loc FROM status WHERE
backordered =3D 'N')) AND (products.main_loc IN (SELECT main_loc FROM
main_store WHERE location =3D 'NFUSA1' AND location IN (SELECT item from
item_storage where brand =3D main_store.item_brand))) AND
((USER_DEF_FUNCTION1(products.loc1_item_vers, products.loc1_vers,
products.loc2_item_vers, products.loc2_vers )) =3D 1) FOR READ ONLY"
"SELECT SUM(products.loc2_size) FROM products WHERE (location2 =3D
'NFUSA1') AND (main_loc IN (SELECT main_loc FROM status WHERE
backordered =3D 'N')) AND (products.main_loc IN (SELECT main_loc FROM
main_store WHERE location =3D 'NFUSA1' AND location IN (SELECT item from
item_storage where brand =3D main_store.item_brand))) AND
((USER_DEF_FUNCTION1(products.loc1_item_vers, products.loc1_vers,
products.loc2_item_vers, products.loc2_vers )) =3D 1) FOR READ ONLY"
The main issue with putting them together is summing two different
fields depending on if the matching LOCATION is LOCATION1 or LOCATION2
(or it could be both or neither). So if it is only LOCATION1 then I
only want loc1_size to be included in the SUM and if it is only
LOCATION2 then I only want to included loc2_size in the SUM. But if
it is both then I want both to be SUMed...This has to be done with
thousands of possible records. After I built the two individual
queries I can add the two SUMs together but I was thinking it would be
nice to have it all done in one step instead of three steps.
I looked at various ways to do this such as modifying the existing
queries, cases, creating a user-defined function, etc but cant quite
get the logic to work correctly all together. Any suggestions? Here
is what the tables might look like if that will help:
MAIN_STORE
Main_loc Ind Location Item_brand
MHUB1 1 NFUSA1 2
MHUB1 2 NFUSA2 2
MHUB1 3 NFUSA3 2
MHUB1 4 NFUSA3 2
MHUB2 1 NFUSA1 2
MHUB2 3 NFUSA1 2
=2E....
PRODUCTS
Main_locs Type Location1 Location2 Location3 Location4 Loc1_item_vers
Loc1_vers Loc2_item_vers Loc2_vers Loc1 size Loc2 size Loc3 size Loc4
size
MHUB1 13 NFUSA1 NFUSA3 100 100 100 100 2 4 0 0
MHUB1 14 NFUSA2 NFUSA4 100 100 100 100 3 4 0 0
MHUB2 13 NFUSA1 NFUSA1 100 200 100 100 5 5 0 0
MHUB2 14 *NONE *NONE 0 0 0 0
ITEM_STORAGE
item brand
NFUSA1 2
NFUSA2 2
NFUSA3 2
NFUSA4 2
STATUS
Main _loc Backordered
MHUB1 =91N=92
MHUB2 =91N=92
If you know of a way to put this together to work efficently let me
know...A simple "you can use ...... to do this" would be good enough
and I can try to research how to do it from there.
Thanks in advance for the help!
|
