Fault that joins between multiple tables MS SQL Server 2000
-
Hi, I'm a problem I had .5 relationship between different tables. I need to add more to this relationship a table. Thus, the relationship between the table I will have six. In the first table, and sixth in the table in question is not data. The name of the sixth LG_086_PRICE table. I was in the first table, the sixth in the table do not want to come to the data value is NULL. I'm very weak in joins. Thanks to what might help. Ersoy Aydın System Specialist LBS.
Code with the following six statements, incorrect number brings the data. In this case there are errors in the code join. But I do not know the solution.
SELECT SIPFIS.DATE_ AS Tarih, SIPFIS.TIME_ AS Saat, SIPFIS.FICHENO AS Sipariş_No, STOK.PRODUCERCODE AS Barkodu, STOK.CODE AS [Stok Kodu],
STOK.NAME AS [Stok Adı], SIPSATIR.AMOUNT AS Miktar, LG_086_01_GNTOTST.ONHAND AS [Eldeki Miktar], SIPSATIR.PRICE AS [SATIŞ BR.Fiyat],
LG_086_PRCLIST.PRICE AS [Liste Fiyatı], SIPSATIR.VATMATRAH / SIPSATIR.AMOUNT AS [NET SATIŞ BR.Fiyat], SIPSATIR.TOTAL AS [Br?t.Tutar],
SIPSATIR.VATMATRAH AS [Net Tutar],
(SELECT TOP 1 OUTREMCOST
FROM LG_086_01_STLINE
WHERE STOCKREF = STOK.LOGICALREF AND (TRCODE = 51 OR
TRCODE = 8 OR
TRCODE = 1) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0
ORDER BY DATE_ DESC) AS BrMaliyet, SIPSATIR.AMOUNT *
(SELECT TOP 1 OUTREMCOST
FROM LG_086_01_STLINE
WHERE STOCKREF = STOK.LOGICALREF AND (TRCODE = 51 OR
TRCODE = 8 OR
TRCODE = 1) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0
ORDER BY DATE_ DESC) AS [Toplam Maliyet], SIPSATIR.VATMATRAH - SIPSATIR.AMOUNT *
(SELECT TOP 1 OUTREMCOST
FROM LG_086_01_STLINE
WHERE STOCKREF = STOK.LOGICALREF AND (TRCODE = 51 OR
TRCODE = 8 OR
TRCODE = 1) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0
ORDER BY DATE_ DESC) AS NETKAR
FROM
LG_086_ITEMS STOK INNER JOIN
LG_086_01_ORFLINE SIPSATIR ON STOK.LOGICALREF = SIPSATIR.STOCKREF INNER JOIN
LG_086_PRCLIST ON STOK.LOGICALREF = LG_086_PRCLIST.CARDREF RIGHT OUTER JOIN
LG_086_01_ORFICHE SIPFIS ON SIPSATIR.ORDFICHEREF = SIPFIS.LOGICALREF LEFT OUTER JOIN
LG_086_01_GNTOTST ON SIPSATIR.STOCKREF = LG_086_01_GNTOTST.STOCKREF
WHERE (SIPFIS.FICHENO = '00076166') AND (LG_086_01_GNTOTST.INVENNO = 0) AND (LG_086_PRCLIST.PTYPE = 2)
-
This looks overly complex to me, but I don;t have sufficient information to explain why or how to design it more efficiently.
I would suggest that you break this query up into two or more separate queries just to get a better handle on what is happening and perhaps how to better optimize it. You can combine everything back into a single query later if you so desire.
The probability of survival is inversely proportional to the angle of arrival.
-
First of all, thank you for your interest.
These complex queries, an order of profitability analysis. A diagram will now add a picture.
Then the table names, field names and will send a diagram describing the relationship.
Table field names in some funny, some are in English acronym, some of them are Turkish shortened formed. We'll send you a chart, will eliminate all the complexity.
Thank you.
-
6 store card in order to have the chips.Table name LG_086_ITEMS
Price List price information in the table has only four stock cards.Table name LG_086_PRICE
I write my SQL code returns the following result.
NamePrice
ITEM1Price1
ITEM2Price2
ITEM3Price3
ITEM6Price6
However, I want to return must be based on the results as follows.
NamePrice
ITEM1Price1
ITEM2Price2
ITEM3Price3
ITEM4NULL
ITEM5NULL
ITEM6Price6
-
For pricing information has not been defined, the order to plug the unit price of 2 stock cards have not arrived. While my analysis of ordering, the stock price information, not just a list of all the stocks.
-
Thanks for providing additional info. I do not have time at the moment to spend more than a minute or two to look at it but I will get to it later if possible.
I can tell you right now that when you have right outer join followed by left outer join you need to understand what is happening or your results may not be what you are expecting and NULLs can change the dynamic. I would restructure the query to avoid that and/or break it into separate queries so you can evaluate the intermediate results.
The probability of survival is inversely proportional to the angle of arrival.
-
Thanks anyway, I'm waiting for your response available to you. Good work.
-
JOIN books issue from beginning to end, I had to work. But it was worth. Correct code is as follows.
SELECT
SIPFIS.DATE_ AS Tarih,
SIPFIS.TIME_ AS Saat,
SIPFIS.FICHENO AS Siparis_No,
STOK.PRODUCERCODE AS Barkodu,
STOK.CODE AS [Stok Kodu],
STOK.NAME AS [Stok Adi],
SIPSATIR.AMOUNT AS Miktar,
LG_086_01_GNTOTST.ONHAND AS [Eldeki Miktar],
SIPSATIR.PRICE AS [SATIS BR.Fiyat],
SIPSATIR.VATMATRAH / SIPSATIR.AMOUNT AS [NET SATIS BR.Fiyat],
SIPSATIR.TOTAL AS [Brüt.Tutar],
SIPSATIR.VATMATRAH AS [Net Tutar],
(SELECT TOP 1 OUTREMCOST
FROM LG_086_01_STLINE
WHERE STOCKREF = STOK.LOGICALREF AND (TRCODE = 51 OR
TRCODE = 8 OR
TRCODE = 1) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0
ORDER BY DATE_ DESC) AS BrMaliyet,
SIPSATIR.AMOUNT * (SELECT TOP 1 OUTREMCOST
FROM LG_086_01_STLINE
WHERE STOCKREF = STOK.LOGICALREF AND (TRCODE = 51 OR
TRCODE = 8 OR
TRCODE = 1) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0
ORDER BY DATE_ DESC) AS [Toplam Maliyet],
SIPSATIR.VATMATRAH - SIPSATIR.AMOUNT *
(SELECT TOP 1 OUTREMCOST
FROM LG_086_01_STLINE
WHERE STOCKREF = STOK.LOGICALREF AND (TRCODE = 51 OR
TRCODE = 8 OR
TRCODE = 1) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0
ORDER BY DATE_ DESC) AS NETKAR,
(SELECT Amount From LG_086_PRCLIST Where CARDREF=STOK.LOGICALREF AND LG_086_PRCLIST.PTYPE = 2) As Fiyat
FROM LG_086_ITEMS STOK INNER JOIN
LG_086_01_ORFLINE SIPSATIR ON STOK.LOGICALREF = SIPSATIR.STOCKREF INNER JOIN
LG_086_01_GNTOTST ON SIPSATIR.STOCKREF = LG_086_01_GNTOTST.STOCKREF RIGHT OUTER JOIN
LG_086_01_ORFICHE SIPFIS ON SIPSATIR.ORDFICHEREF = SIPFIS.LOGICALREF
WHERE (SIPFIS.FICHENO = '00076166') AND (LG_086_01_GNTOTST.INVENNO = 0)
Thanks,
-
This was removed by the editor as SPAM
-
Hi , Thank you
but
When I run the query, I get the following error.
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'STOK' does not match with a table name or alias name used in the query.
-
This was removed by the editor as SPAM
-
This time I received the following error.
Server: Msg 1033, Level 15, State 1, Line 31
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
You're saying, I tried to apply. Unfortunately, I got the following error.
SELECTSIPFIS.DATE_ AS Tarih,
SIPFIS.TIME_ AS Saat,
SIPFIS.FICHENO AS Siparis_No,
STOK.PRODUCERCODE AS Barkodu,
STOK.CODE AS [Stok Kodu],
STOK.NAME AS [Stok Adi],
SIPSATIR.AMOUNT AS Miktar,
LG_086_01_GNTOTST.ONHAND AS [Eldeki Miktar],
SIPSATIR.PRICE AS [SATIS BR.Fiyat],
SIPSATIR.VATMATRAH / SIPSATIR.AMOUNT AS [NET SATIS BR.Fiyat],
SIPSATIR.TOTAL AS [Brüt.Tutar],
SIPSATIR.VATMATRAH AS [Net Tutar],
OUTREMCOST AS BrMaliyet,
SIPSATIR.AMOUNT * OUTREMCOST AS [Toplam Maliyet],
SIPSATIR.VATMATRAH - SIPSATIR.AMOUNT * OUTREMCOST AS NETKAR,
LG_086_PRCLIST.PRICE As Fiyat
FROM LG_086_01_ORFICHE SIPFIS
LEFT JOIN LG_086_01_ORFLINE SIPSATIR ON SIPSATIR.ORDFICHEREF = SIPFIS.LOGICALREF
LEFT JOIN LG_086_ITEMS STOK ON STOK.LOGICALREF = SIPSATIR.STOCKREF
LEFT JOIN LG_086_01_GNTOTST ON SIPSATIR.STOCKREF = LG_086_01_GNTOTST.STOCKREF
LEFT JOIN LG_086_PRCLIST ON CARDREF=STOK.LOGICALREF AND LG_086_PRCLIST.PTYPE = 2
LEFT JOIN (SELECT OUTREMCOST
FROM LG_086_01_STLINE
WHERE(TRCODE IN (51,8,1))
AND OUTREMCOST <> 0
AND LPRODSTAT = 0
AND LINETYPE = 0
GROUP BY OUTREMCOST )
OUTREMCOST ON STOCKREF = STOK.LOGICALREF
WHERE (SIPFIS.FICHENO = '00076166') AND (LG_086_01_GNTOTST.INVENNO = 0)
sql eRROR mESAGE :
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'STOCKREF'.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply