Paul Stevenson
asked on
Create flat file from JOIN query
HI,
I am trying to get data from 3 tables to list on one line(flat file) for upload to a web site.
I am using JOINs to get from 2 related tables all linked with a field "itemCode".
The main table OITM is easy as it has all the information in one record but the other 2 require filtering from a couple of rows to get the information.
Currently the output is creating 16 rows because my WHERE clause is not isolating the right bits.
OITW has numerous rows relating to a single item but should be reduced to 2 which are being calculated and replaced by a Y or N (in stock yes or no).
SPP1 has numerous frows relating to a single item but I need just 2 rows (containing prices) for different lists which then also get multipled by 0.9 to give a reduced price.
I think it is the where clause error is either based around the SSP1 reulst as it should give me 4 fields but I get 16 rows or the OITW as it has 18 records and I want 2.!
Example OITM records:
Item Code WhsCode OnHand iscommited
K B04200241 02 0.000000 0.000000
K B04200241 03 0.000000 0.000000
K B04200241 04 41.000000 0.000000
K B04200241 05 0.000000 0.000000
K B04200241 10 0.000000 0.000000
K B04200241 20 0.000000 0.000000
K B04200241 30 1.000000 0.000000
K B04200241 40 0.000000 0.000000
K B04200241 50 0.000000 0.000000
K B04200241 51 3.000000 0.000000
K B04200241 52 0.000000 0.000000
K B04200241 53 0.000000 0.000000
K B04200241 54 0.000000 0.000000
K B04200241 55 0.000000 0.000000
K B04200241 56 0.000000 0.000000
K B04200241 57 0.000000 0.000000
K B04200241 58 0.000000 0.000000
K B04200241 70 0.000000 0.000000
Example SSP1 records:
ItemCode CardCode LineNum Price FromDate ToDate
K B04200241 *2 0 3.110000 18/09/2007 00:00:00 NULL
K B04200241 *3 0 2.360000 21/06/2007 00:00:00 16/09/2007 00:00:00
K B04200241 *3 1 2.420000 17/09/2007 00:00:00 NULL
K B04200241 *6 0 3.730000 12/09/2007 00:00:00 NULL
I can't change the tables as they are in SAP.
I am banging my head against a wall but getting nowhere. Please let me know if you need more info.
Thanks,
Paul
I am trying to get data from 3 tables to list on one line(flat file) for upload to a web site.
I am using JOINs to get from 2 related tables all linked with a field "itemCode".
The main table OITM is easy as it has all the information in one record but the other 2 require filtering from a couple of rows to get the information.
Currently the output is creating 16 rows because my WHERE clause is not isolating the right bits.
OITW has numerous rows relating to a single item but should be reduced to 2 which are being calculated and replaced by a Y or N (in stock yes or no).
SPP1 has numerous frows relating to a single item but I need just 2 rows (containing prices) for different lists which then also get multipled by 0.9 to give a reduced price.
I think it is the where clause error is either based around the SSP1 reulst as it should give me 4 fields but I get 16 rows or the OITW as it has 18 records and I want 2.!
Example OITM records:
Item Code WhsCode OnHand iscommited
K B04200241 02 0.000000 0.000000
K B04200241 03 0.000000 0.000000
K B04200241 04 41.000000 0.000000
K B04200241 05 0.000000 0.000000
K B04200241 10 0.000000 0.000000
K B04200241 20 0.000000 0.000000
K B04200241 30 1.000000 0.000000
K B04200241 40 0.000000 0.000000
K B04200241 50 0.000000 0.000000
K B04200241 51 3.000000 0.000000
K B04200241 52 0.000000 0.000000
K B04200241 53 0.000000 0.000000
K B04200241 54 0.000000 0.000000
K B04200241 55 0.000000 0.000000
K B04200241 56 0.000000 0.000000
K B04200241 57 0.000000 0.000000
K B04200241 58 0.000000 0.000000
K B04200241 70 0.000000 0.000000
Example SSP1 records:
ItemCode CardCode LineNum Price FromDate ToDate
K B04200241 *2 0 3.110000 18/09/2007 00:00:00 NULL
K B04200241 *3 0 2.360000 21/06/2007 00:00:00 16/09/2007 00:00:00
K B04200241 *3 1 2.420000 17/09/2007 00:00:00 NULL
K B04200241 *6 0 3.730000 12/09/2007 00:00:00 NULL
I can't change the tables as they are in SAP.
I am banging my head against a wall but getting nowhere. Please let me know if you need more info.
Thanks,
Paul
SELECT
OITM.ItemCode,
OITM.ItemName,
OITM.ItmsGrpCod,
OITM.U_ProductType,
OITM.FrgnName,
OITM.SuppCatNum,
OITM.SHeight1,
OITM.SHght1Unit,
OITM.SWidth1,
OITM.SWdth1Unit,
OITM.SLength1,
OITM.SLen1Unit,
OITM.SVolume,
OITM.SVolUnit,
OITM.SWeight1,
OITM.SWght1Unit,
OITM.VatGourpSa,
OITM.frozenFor,
((CASE
WHEN
((OITWa.onhand-OITWa.iscommited)>'0')
THEN
'Y'
ELSE
'N'
END))
AS AStock,
((CASE
WHEN
((OITWb.onhand-OITWb.iscommited)>'0')
THEN
'Y'
ELSE
'N'
END))
AS BStock,
SPP1R.Price AS AStockRetail,
SPP1R.Price * 0.9 AS BStockRetail,
SPP1T.Price AS AStockRetail,
SPP1T.Price * 0.9 AS BStockRetail
FROM
OITM
INNER JOIN OITW AS OITWA ON OITWA.ItemCode=OITM.ItemCode
INNER JOIN OITW AS OITWB ON OITWB.ItemCode=OITM.ItemCode
INNER JOIN SPP1 AS SPP1R ON SPP1R.ItemCode=OITM.ItemCode
INNER JOIN SPP1 AS SPP1T ON SPP1T.ItemCode=OITM.ItemCode
WHERE
OITWA.WhsCode='04'
AND
OITWB.WhsCode='30'
AND
(
(((SPP1R.CardCode)='*1')
AND ((SPP1R.FromDate)<GETDATE())
AND ((SPP1R.ToDate)>GETDATE())
OR (SPP1R.ToDate) IS NULL)
)
AND
(
(((SPP1T.CardCode)='*2')
AND ((SPP1T.FromDate)<GETDATE())
AND ((SPP1T.ToDate)>GETDATE())
OR (SPP1T.ToDate) IS NULL)
)
AND
OITM.ItemCode = 'K B04200241'
ASKER
mwvisa1 -
I need one row:
The information from OITM then on the same row fields Astock and Bstock which can be Y or N depending on the outcome of each CASE then finally 4 prices AstockRetail, Bstockretail, AstockTrade and BStockTrade:
OITM.ItemdCode, OITM.ItemName ....... OITM.Frozenfor, Astock, Bstock, AstockRetail,BstockRetail, AstockTrade, BstockTrade
Hope I am explaining my requirements well enough.
Cheers,
Paul
I need one row:
The information from OITM then on the same row fields Astock and Bstock which can be Y or N depending on the outcome of each CASE then finally 4 prices AstockRetail, Bstockretail, AstockTrade and BStockTrade:
OITM.ItemdCode, OITM.ItemName ....... OITM.Frozenfor, Astock, Bstock, AstockRetail,BstockRetail,
Hope I am explaining my requirements well enough.
Cheers,
Paul
Then you should be able to group on (based on example data above): Item Code.
Leave off the whscode column, and SUM on OnHand and iscommited.
Then you can do case when in an outer query unless I am misunderstanding. If that is the case, I will take a better look tonight.
Leave off the whscode column, and SUM on OnHand and iscommited.
Then you can do case when in an outer query unless I am misunderstanding. If that is the case, I will take a better look tonight.
ASKER
MWVisa1 -
Sorry I need the whscode and can't SUM OnHand etc as I have to know if there is stock in a particular warehouse (in this case 04 is our main warehouse and 30 is the damaged stock, all the others need to be omitted as they are vans and other departments).
Paul
Sorry I need the whscode and can't SUM OnHand etc as I have to know if there is stock in a particular warehouse (in this case 04 is our main warehouse and 30 is the damaged stock, all the others need to be omitted as they are vans and other departments).
Paul
ASKER
Just to help here is the output at the moment (without all of the OITM fields except itemcode)
Line 5 has the correct figures for prices:
ItemCode, Astock,BStock,AstockTrade, BStockTrad e,AstockRe tail,BStoc kRetail
K B04200241,Y,Y,3.73,3.357,3 .73,3.357
K B04200241,Y,Y,3.73,3.357,3 .11,2.799
K B04200241,Y,Y,3.73,3.357,2 .42,2.178
K B04200241,Y,Y,3.73,3.357,3 .73,3.357
K B04200241,Y,Y,3.11,2.799,3 .73,3.357
K B04200241,Y,Y,3.11,2.799,3 .11,2.799
K B04200241,Y,Y,3.11,2.799,2 .42,2.178
K B04200241,Y,Y,3.11,2.799,3 .73,3.357
K B04200241,Y,Y,2.42,2.178,3 .73,3.357
K B04200241,Y,Y,2.42,2.178,3 .11,2.799
K B04200241,Y,Y,2.42,2.178,2 .42,2.178
K B04200241,Y,Y,2.42,2.178,3 .73,3.357
K B04200241,Y,Y,3.73,3.357,3 .73,3.357
K B04200241,Y,Y,3.73,3.357,3 .11,2.799
K B04200241,Y,Y,3.73,3.357,2 .42,2.178
K B04200241,Y,Y,3.73,3.357,3 .73,3.357
Line 5 has the correct figures for prices:
ItemCode, Astock,BStock,AstockTrade,
K B04200241,Y,Y,3.73,3.357,3
K B04200241,Y,Y,3.73,3.357,3
K B04200241,Y,Y,3.73,3.357,2
K B04200241,Y,Y,3.73,3.357,3
K B04200241,Y,Y,3.11,2.799,3
K B04200241,Y,Y,3.11,2.799,3
K B04200241,Y,Y,3.11,2.799,2
K B04200241,Y,Y,3.11,2.799,3
K B04200241,Y,Y,2.42,2.178,3
K B04200241,Y,Y,2.42,2.178,3
K B04200241,Y,Y,2.42,2.178,2
K B04200241,Y,Y,2.42,2.178,3
K B04200241,Y,Y,3.73,3.357,3
K B04200241,Y,Y,3.73,3.357,3
K B04200241,Y,Y,3.73,3.357,2
K B04200241,Y,Y,3.73,3.357,3
I'm having difficulty understanding what you're after here.
Is the following a correct summary of what you want to show in your output:
ItemCode
Stock available in Warehouse 04? (Y/N)
Stock available in Warehouse 30? (Y/N)
Current CardCode *1 price
(Current CardCode *1 price) x 0.9
Current CardCode *2 price
(Current CardCode *2 price) x 0.9
Is the following a correct summary of what you want to show in your output:
ItemCode
Stock available in Warehouse 04? (Y/N)
Stock available in Warehouse 30? (Y/N)
Current CardCode *1 price
(Current CardCode *1 price) x 0.9
Current CardCode *2 price
(Current CardCode *2 price) x 0.9
ASKER
Calpurnia -
Yes that is what I want (along with the OITM fields) all on one line.
Cheers,
Paul
Yes that is what I want (along with the OITM fields) all on one line.
Cheers,
Paul
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks mwvisa1 it does exactly what I needed.
SPP1R.Price AS AStockRetail,
SPP1R.Price * 0.9 AS BStockRetail,
SPP1T.Price AS AStockRetail,
SPP1T.Price * 0.9 AS BStockRetail
That may cause you some issues.
Then for the duplication, I would do your grouping on the unique (repeating) Item information columns and then this
((CASE
WHEN
((OITWa.onhand-OITWa.iscom
THEN
'Y'
ELSE
'N'
END))
If I am understanding correctly you want to have one row where this is Y and one where this is N.