Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Create flat file from JOIN query

Posted on 2008-10-02
9
Medium Priority
?
487 Views
Last Modified: 2012-05-05
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
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'

Open in new window

0
Comment
Question by:PaulBS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22628407
You have these columns named the same:
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.iscommited)>'0')
            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.
0
 

Author Comment

by:PaulBS
ID: 22629112
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
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22629147
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.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:PaulBS
ID: 22629285
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
0
 

Author Comment

by:PaulBS
ID: 22629376
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,BStockTrade,AstockRetail,BStockRetail
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
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 22629710
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
0
 

Author Comment

by:PaulBS
ID: 22631450
Calpurnia -

Yes that is what I want (along with the OITM fields) all on one line.

Cheers,

Paul
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22632483
Try something like this:
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,
         IsNull((SELECT CASE
		WHEN (OITW.onhand - OITW.iscommited) > 0
		THEN 'Y'
		ELSE 'N'
	END FROM OITW 
         WHERE OITW.ItemCode=OITM.ItemCode AND OITW.WhsCode='04'),'N')
	AS AStock,
	IsNull((SELECT CASE
		WHEN (OITW.onhand - OITW.iscommited) > 0
		THEN 'Y'
		ELSE 'N'
	END FROM OITW 
         WHERE OITW.ItemCode=OITM.ItemCode AND OITW.WhsCode='30'),'N')
	AS BStock,
	IsNull((SELECT Price FROM SPP1
         WHERE SPP1.CardCode='*1'
	AND SPP1.FromDate < GETDATE()
         AND Coalesce(SPP1.ToDate, GETDATE()+1) > GETDATE()
	AND SPP1.ItemCode = OITM.ItemCode), 0.00)
         AS AStockTrade,
	IsNull((SELECT Price FROM SPP1
         WHERE SPP1.CardCode='*1'
	AND SPP1.FromDate < GETDATE()
         AND Coalesce(SPP1.ToDate, GETDATE()+1) > GETDATE()
	AND SPP1.ItemCode = OITM.ItemCode), 0.00) * 0.9
         AS BStockTrade,
	IsNull((SELECT Price FROM SPP1
         WHERE SPP1.CardCode='*2'
	AND SPP1.FromDate < GETDATE()
         AND Coalesce(SPP1.ToDate, GETDATE()+1) > GETDATE()
	AND SPP1.ItemCode = OITM.ItemCode), 0.00)
         AS AStockRetail,
	IsNull((SELECT Price FROM SPP1
         WHERE SPP1.CardCode='*2'
	AND SPP1.FromDate < GETDATE()
         AND Coalesce(SPP1.ToDate, GETDATE()+1) > GETDATE()
	AND SPP1.ItemCode = OITM.ItemCode), 0.00) * 0.9 
         AS BStockRetail
FROM OITM
--WHERE OITM.ItemCode = 'K B04200241'
GROUP BY 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

Open in new window

0
 

Author Closing Comment

by:PaulBS
ID: 31502528
Thanks mwvisa1 it does exactly what I needed.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question