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,
-
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 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.
-
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'.
-
SQL Server Error :
Server: Msg 8120, Level 16, State 1, Line 1
Column 'LG_086_01_STLINE.STOCKREF' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
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 , STOCKREF
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 OUTREMCOST.STOCKREF = STOK.LOGICALREF
WHERE (SIPFIS.FICHENO = '00076166') AND (LG_086_01_GNTOTST.INVENNO = 0)
If the GROUP BY function if disabled, two rows of full turns in 1083. Whereas only six lines are in order of receipt. VBA code that was converted into a version of SQL code, obtained from the EXCEL solution is as follows.
Private Sub CommandButton1_Click()
'Bir butonumuz var o butona tiklayinca sirasi ile asagidaki islemler gerçeklesecek
Dim Baglanti As Object, KayitSeti As Object
'Baglanti ve KayitSeti nesnelerini tanimliyoruz
Set Baglanti = CreateObject("adodb.connection")
'Baglanti nesnesinin türünü belirliyoruz
Set KayitSeti = CreateObject("adodb.recordset")
'KayitSeti nesnesinin türünü belirliyoruz
strFirma = Format(Sheets("SETUP").Range("B5"), "000")
'Ilgili EXCEL çalisma kitabinda SETUP adinda bir sayfa var,Bu sayfadaki B5 hücresine LOGO Firma numarasini yazacagiz
strServer = Sheets("SETUP").Range("B1").Value
'Ilgili EXCEL çalisma kitabinda SETUP adinda bir sayfa var,Bu sayfadaki B1 hücresine LOGO nun SERVER IP sini yazacagiz
strDatabase = Sheets("SETUP").Range("B4").Value
'Ilgili EXCEL çalisma kitabinda SETUP adinda bir sayfa var,Bu sayfadaki B4 hücresine LOGO nun DATABASE adini yazacagiz
strKullanici = Sheets("SETUP").Range("B2").Value
'Ilgili EXCEL çalisma kitabinda SETUP adinda bir sayfa var,Bu sayfadaki B2 hücresine SQL in USER adini yazacagiz
strParola = Sheets("SETUP").Range("B3").Value
'Ilgili EXCEL çalisma kitabinda SETUP adinda bir sayfa var,Bu sayfadaki B3 hücresine SQL USER inin sifresini yazacagiz
'tarih1 = Format(Range("I2"), "yyyy-mm-dd")
'Ilgili EXCEL çalisma kitabinda verilerin listelenecegi bir sayfa var,Bu sayfadaki I2 hücresine TARIH yazacagiz , formati GG.AA.YY seklinde olacak
' S degiskeni ve degiskenin alacagi degeri getiren T-SQL kodunu VBA formatinda yaziyoruz
S = "SELECT SIPFIS.DATE_ AS Tarih, SIPFIS.TIME_ AS Saat, SIPFIS.FICHENO AS Siparis_No, LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_CLCARD.CODE, LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_CLCARD.DEFINITION_, "
S = S & "STOK.PRODUCERCODE AS Barkodu, STOK.CODE AS [Stok Kodu], STOK.NAME AS [Stok Adi], SIPSATIR.AMOUNT AS Miktar,LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_GNTOTST.ONHAND AS [Eldeki Miktar], "
S = S & "(SELECT PRICE From LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_PRCLIST Where CARDREF=STOK.LOGICALREF AND LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_PRCLIST.PTYPE = 2) As Liste_Fiyati,"
S = S & "SIPSATIR.PRICE AS [Satis.Br.Fiyat],SIPSATIR.VATMATRAH / SIPSATIR.AMOUNT AS [NET SATIS BR.Fiyat],"
S = S & "(SELECT TOP 1 OUTREMCOST FROM LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_STLINE "
S = S & "WHERE STOCKREF = STOK.LOGICALREF AND (TRCODE = 51 OR (TRCODE = 8 OR (TRCODE=1))) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0 "
S = S & "ORDER BY DATE_ DESC) AS BrMaliyet, SIPSATIR.TOTAL AS [Brüt.Tutar], SIPSATIR.VATMATRAH AS [Net Tutar], "
S = S & "SIPSATIR.AMOUNT * (SELECT TOP 1 OUTREMCOST FROM LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_STLINE WHERE STOCKREF = STOK.LOGICALREF AND (TRCODE = 51 OR "
S = S & "(TRCODE = 8 OR (TRCODE=1))) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0 ORDER BY DATE_ DESC) AS [Toplam Maliyet], "
S = S & "SIPSATIR.VATMATRAH - SIPSATIR.AMOUNT * (SELECT TOP 1 OUTREMCOST FROM LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_STLINE WHERE STOCKREF = STOK.LOGICALREF AND "
S = S & "(TRCODE = 51 OR (TRCODE = 8 OR (TRCODE=1))) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0 ORDER BY DATE_ DESC) AS NETKAR, "
S = S & "(CASE WHEN CONVERT(FLOAT,SIPSATIR.VATMATRAH)*100>0 THEN CONVERT(FLOAT,(SIPSATIR.VATMATRAH - SIPSATIR.AMOUNT * (SELECT TOP 1 OUTREMCOST FROM LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_STLINE WHERE STOCKREF = STOK.LOGICALREF AND "
S = S & "(TRCODE = 51 OR (TRCODE = 8 OR (TRCODE=1))) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0 ORDER BY DATE_ DESC)))/CONVERT(FLOAT,SIPSATIR.VATMATRAH)*100 ELSE 0 END) AS KARORANI "
S = S & "FROM LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_ITEMS STOK INNER JOIN LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_ORFLINE "
S = S & "SIPSATIR ON STOK.LOGICALREF = SIPSATIR.STOCKREF RIGHT OUTER JOIN LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_ORFICHE SIPFIS ON SIPSATIR.ORDFICHEREF = SIPFIS.LOGICALREF INNER JOIN "
S = S & "LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_CLCARD ON SIPFIS.CLIENTREF = LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_CLCARD.LOGICALREF LEFT OUTER JOIN "
S = S & "LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_GNTOTST ON SIPSATIR.STOCKREF = LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_GNTOTST.STOCKREF "
S = S & "WHERE (SIPFIS.FICHENO = '" & Format(Sheets("Analiz-2").Range("D3"), "00000000") & "') AND (LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_GNTOTST.INVENNO = 0) "
Baglanti.Open "Provider=SQLOLEDB; Data Source=" & strServer & "; Initial Catalog=" & strDatabase & "; User ID=" & strKullanici & "; Password=" & strParola & ";"
'If Baglanti.State = 1 Then MsgBox "Veritabani ile baglanti kuruldu..."
'On Local Error Resume Next
KayitSeti.Open S, Baglanti, 1, 1
'If Err Then MsgBox "Hata Olustu"
'If KayitSeti.State = 1 Then MsgBox "Kayitsetine SQL sorgusu ile veriler aktarildi"
Range("A9:IV65536").ClearContents
Range("A9:IV65536").ClearFormats
Cells(9, 1).CopyFromRecordset KayitSeti
'Baglanti.Open "Provider=SQLOLEDB; Data Source=" & strServer & "; Initial Catalog=" & strDatabase & "; User ID=" & strKullanici & "; Password=" & strParola & ";"
'Baglanti Nesnesini açiyoruz.
' KayitSeti.Open S, Baglanti, 1, 1
'Kayit Seti Nesnesini açiyoruz.
' Range("A9:IV65536").ClearContents
'Tarih kistasina göre butona her tiklandiginda veriler yeniden listelenecegi için , daha önceki çok verinin üzerine az veri yazilmasi gerekebilir...listelenen verileri sildiriyoruz..tekrar yazdiriyoruz.
' Cells(9, 1).CopyFromRecordset KayitSeti
'KayitSeti nesnesinin SQL sorgusundan çektigi bilgilerin , verilerin listelenecegi sayfada 9.cu satir 1.ci sütundan itibaren listelenebilmesi için
KayitSeti.Close
Baglanti.Close
Set KayitSeti = Nothing
Set Baglanti = Nothing
strFirma = vbNullString
strServer = vbNullString
strDatabase = vbNullString
strParola = vbNullString
strKullanici = vbNullString
S = vbNullString
'Worksheet_Change(ByVal Target As Range)
[F9:S65536].Select
'*****
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'*****
[F9].CurrentRegion.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
Range("D1:S7").Select
Range("F1").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("F8:S8").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
Range("F1:G2").Select
Columns("K:M").Select
Selection.NumberFormat = "#,##0.00000_ ;[Red]-#,##0.00000 "
Columns("O:O").Select
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
Columns("N:Q").Select
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
Range("F1:G2").Select
End Sub
Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
End Sub
Private Sub CommandButton1_GotFocus()
End Sub
Private Sub CommandButton1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
End Sub
Private Sub CommandButton1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
End Sub
Private Sub CommandButton2_Click()
Range("D3").Value = Format(Range("D3").Value + 1, "00000000")
End Sub
Private Sub CommandButton3_Click()
Range("D3").Value = Format(Range("D3").Value - 1, "00000000")
End Sub
Private Sub CommandButton4_Click()
Dim say As Byte, bul As Range
say = Range("J108").End(3).Row
Set kontrol = Range("J9:J" & say).Find("Elde Stok Yok", , , 1)
If Not kontrol Is Nothing Then
MsgBox "Daha Önce Analiz Yapilmis", 64, "Www.ExcelVBA.Net"
Exit Sub
End If
If WorksheetFunction.CountIf(Range("J9:J" & say), 0) = 0 Then MsgBox "Miktar hatasi yok,Depodaki tüm miktarlar yeterli": Exit Sub
For Each bul In Range("J9:J" & say)
If CStr(bul.Value) = CStr(0) Then
bul.Value = "Elde Stok Yok"
bul.Font.ColorIndex = 3
bul.Font.Bold = True
bul.Offset(0, -1).Font.ColorIndex = 2
bul.Offset(0, -1).Font.Bold = True
bul.Offset(0, -2).Font.ColorIndex = 2
bul.Offset(0, -2).Font.Bold = True
bul.Offset(0, -3).Font.ColorIndex = 2
bul.Offset(0, -3).Font.Bold = True
bul.Offset(0, -4).Font.ColorIndex = 2
bul.Offset(0, -4).Font.Bold = True
bul.Offset(0, 1).Font.ColorIndex = 2
bul.Offset(0, 2).Font.ColorIndex = 2
bul.Offset(0, 2).Font.Bold = True
bul.Offset(0, 3).Font.ColorIndex = 2
bul.Offset(0, 3).Font.Bold = True
bul.Offset(0, 4).Font.ColorIndex = 2
bul.Offset(0, 4).Font.Bold = True
bul.Offset(0, 4).Value = 0
bul.Offset(0, 5).Font.ColorIndex = 2
bul.Offset(0, 5).Font.Bold = True
bul.Offset(0, 5).Value = 0
bul.Offset(0, 6).Font.ColorIndex = 2
bul.Offset(0, 6).Font.Bold = True
bul.Offset(0, 6).Value = 0
bul.Offset(0, 7).Font.ColorIndex = 2
bul.Offset(0, 7).Font.Bold = True
bul.Offset(0, 7).Value = 0
bul.Offset(0, 8).Font.ColorIndex = 2
bul.Offset(0, 8).Font.Bold = True
bul.Offset(0, 8).Value = 0
bul.Offset(0, 9).Font.ColorIndex = 2
bul.Offset(0, 9).Font.Bold = True
bul.Offset(0, 9).Value = 0
Range(bul.Offset(0, -9).Address(False, False) & ":" & bul.Offset(0, 9).Address(False, False)).Interior.ColorIndex = 1
'Else
'MsgBox "MIKTAR HATASI YOK !"
End If
Next bul
End Sub
Private Sub CommandButton5_Click()
Dim say As Byte, bul As Range
say = Range("L108").End(3).Row
Set kontrol = Range("L9:L" & say).Find("Fiyat YOK", , , 1)
If Not kontrol Is Nothing Then
MsgBox "Daha Önce Analiz Yapilmis", 64, "Www.ExcelVBA.Net"
Exit Sub
End If
If WorksheetFunction.CountIf(Range("L9:L" & say), 0) = 0 Then MsgBox "Satis fiyati girilmemis stok yok": Exit Sub
For Each bul In Range("L9:L" & say)
If CStr(bul.Value) = CStr(0) Then
bul.Value = "Fiyat YOK"
bul.Font.ColorIndex = 5
bul.Font.Bold = True
bul.Offset(0, -1).Font.ColorIndex = 6
bul.Offset(0, -1).Font.Bold = True
bul.Offset(0, -2).Font.ColorIndex = 2
bul.Offset(0, -2).Font.Bold = True
bul.Offset(0, -3).Font.ColorIndex = 2
bul.Offset(0, -3).Font.Bold = True
bul.Offset(0, -4).Font.ColorIndex = 2
bul.Offset(0, -4).Font.Bold = True
bul.Offset(0, -5).Font.ColorIndex = 2
bul.Offset(0, -5).Font.Bold = True
bul.Offset(0, 1).Font.ColorIndex = 2
bul.Offset(0, 1).Font.Bold = True
bul.Offset(0, 1).Value = 0
bul.Offset(0, 2).Font.ColorIndex = 2
bul.Offset(0, 2).Font.Bold = True
bul.Offset(0, 2).Value = 0
bul.Offset(0, 3).Font.ColorIndex = 2
bul.Offset(0, 3).Font.Bold = True
bul.Offset(0, 3).Value = 0
bul.Offset(0, 4).Font.ColorIndex = 2
bul.Offset(0, 4).Font.Bold = True
bul.Offset(0, 4).Value = 0
bul.Offset(0, 5).Font.ColorIndex = 4
bul.Offset(0, 5).Font.Bold = True
bul.Offset(0, 5).Value = 0
bul.Offset(0, 6).Font.ColorIndex = 2
bul.Offset(0, 6).Font.Bold = True
bul.Offset(0, 6).Value = 0
bul.Offset(0, 7).Font.ColorIndex = 2
bul.Offset(0, 7).Font.Bold = True
bul.Offset(0, 7).Value = 0
Range(bul.Offset(0, -9).Address(False, False) & ":" & bul.Offset(0, 7).Address(False, False)).Interior.ColorIndex = 3
End If
Next bul
End Sub
Private Sub CommandButton6_Click()
'Worksheet_Change(ByVal Target As Range)
[F9:S65536].Select
'*****
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'*****
[F9].CurrentRegion.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 4
End With
Range("D1:S7").Select
Range("F1").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("F8:S8").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = 4
End With
Range("F1:G2").Select
Columns("K:M").Select
Selection.NumberFormat = "#,##0.00000_ ;[Red]-#,##0.00000 "
Columns("O:O").Select
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
Columns("N:Q").Select
Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
Range("F1:G2").Select
End Sub
Private Sub ToggleButton1_Click()
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = [d3].Address Then
[H4] = "Siparis Numarasi Sorgulaniyor"
If Not Len(Target.Value) = 8 Then
MsgBox "Hatali Siparis Numarasi girdiniz !"
[H4] = "Hatali Uzunluk"
Exit Sub
End If
Dim rekortseti As Object, baglan As Object
Set baglan = CreateObject("adodb.connection")
Set rekortseti = CreateObject("adodb.recordset")
strFirma = Sheets("SETUP").Range("B5").Value
strServer = Sheets("SETUP").Range("B1").Value
strDatabase = Sheets("SETUP").Range("B4").Value
strKullanici = Sheets("SETUP").Range("B2").Value
strParola = Sheets("SETUP").Range("B3").Value
q = "SELECT FICHENO from LG_" & Format(Sheets("SETUP").Range("B5"), "000") & "_01_ORFICHE" ''' Bu SQL Sorgusunu sirketinize uyarlayin
baglan.Open "Provider=SQLOLEDB; Data Source=" & strServer & "; Initial Catalog=" & strDatabase & "; User ID=" & strKullanici & "; Password=" & strParola & ";"
'baglan.Open "Provider=SQLOLEDB; Data Source=" & strServer & "; Initial Catalog=" & strDatabase & "; User ID=" & strKullanici & "; Password=" & strParola & ";"
Set rekortseti = New ADODB.Recordset
rekortseti.Open q, baglan, adOpenForwardOnly, adLockReadOnly, adCmdText
If rekortseti.EOF Then
MsgBox "Hatali Siparis Numarasi girdiniz !"
[H4] = "Kayitli Siparis No. Yok !"
Else
[H4] = "Analiz yapabilirsiniz..."
End If
rekortseti.Close
baglan.Close
Set rekortseti = Nothing
Set baglan = Nothing
strFirma = vbNullString
strServer = vbNullString
strDatabase = vbNullString
strParola = vbNullString
strKullanici = vbNullString
q = vbNullString
End If
End Sub
Sub ELDEKIMIKTAR()
Dim i As Integer
On Error Resume Next
For i = 1 To 100
If Cells(i, 9).Value = 0 Then
Cells(i, 9).Value = "ELDE STOK YOK"
End If
Next i
End Sub
Sub ForNext_03()
For sayac = 1 To 1500
Cells(sayac, 1).Value = sayac
Next sayac
End Sub
Private Sub ELDEKI_MIKTAR(ByVal Target As Range)
'Biçimlendirme krtiterinin belirlendigi alan. burda A sütununa yazilan degerler sözkonusu
If Intersect(Target, [J:J]) Is Nothing Then Exit Sub
On Error GoTo Son
'Renklendirme yapacaginiz araligi belirleryin. Burda A ile N sütunlari arasi
adr = "A" & Target.Row & ":N" & Target.Row
' Select Case LCase(.Value) küçük harfe duyarli yapabiliriz.
' Select Case UCase(.Value) büyük harfe duyarli yapabiliriz.
Select Case Target
' Interior.ColorIndex yerine .Font.ColorIndex kullanilark biçimlendirmeyi fonta göre yapmak mümkün.
Case Null: Range(adr).Interior.ColorIndex = 20
Case "SENET": Range(adr).Interior.ColorIndex = 19
Case "POS": Range(adr).Interior.ColorIndex = 40
Case "PROTESTO MASRAFI": Range(adr).Interior.ColorIndex = 15
Case "KREDI": Range(adr).Interior.ColorIndex = 35
'Bos satir renklendirmesi
Case "": Range(adr).Interior.ColorIndex = 46
End Select
Son:
End Sub
Sub Bicim()
Dim say As Byte, bul As Range
say = Range("J108").End(3).Row
For Each bul In Range("J9:J" & say)
If CStr(bul.Value) = CStr(0) Then
bul.Value = "Elde Stok Yok"
bul.Font.ColorIndex = 2
bul.Font.Bold = True
bul.Offset(0, -1).Font.ColorIndex = 2
bul.Offset(0, -1).Font.Bold = True
Range(bul.Offset(0, -9).Address(False, False) & ":" & bul.Offset(0, 7).Address(False, False)).Interior.ColorIndex = 1
End If
Next bul
End Sub
-
Turned a result of the latest changes you've made. But failed, not the correct result. 6 lines of data would return the query results. But just 64 lines of data returned. In the far left or far right of the LEFT JOIN of the table, we can decide what should be the do not know how.
SQL Messages
(64 row(s) affected)
-
Here is something that would really help; provide the DDL (CREATE TABLE statement(s)) for the table9s) involved, sample data (as a series of INSERT INTO tablename statements) for the table(s) involved, expected results based on the sample data (note, this is really important to check our code).
For all of this you can cut it down the tables to just those columns needed for the query and (data selected, joins betwee tables, indexes). The sample data should be just that, sample data. It should represent the problem you are trying to solve, and since you have outer joins in your query you would want to be sure this is represented. The expected resluts is the most important, it shows us what the results of the query should return to be correct.
Doing this will help us help you, and in return you will get tested code.
-
I dare say. Field OUTREMCOST somehow got removed from the group by function. Consider these criteria to your query, know what to redesign?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply