Konu: Cross, Pivot sorgu örneği. Nasıl yapabilirim.
Öncelikle Herkese Merhaba ,
Aşağıda xModel ve xDetay Cursor leri var. Bir modeldin en fazla kaç taş boyu varsa, o boylara göre dinamik bir Pivot SQL oluşturmak istiyorum. Oluşan Cross table daki max kolon sayısı, En çok kullanılan modelin kontürüne eşi. MAX(COUNT(Modelno)).
Eğer, oluşan Pivot sorguda, modele ait tüm taş boyları bulunmuyorsa, ( 0 ) olmalı.
Tas1 Tas2 Tas3 .... TasN
Model1 1 2 2 .... x
Model2 1 0 0 .... 0
Model3 4 7 14 .... 0
Model4 4 2 0 ..... 0
Üstad ve ustaların yardımlarını bekliyorum ....
CreateCursor_xModel()
Procedure CreateCursor_xModel
LOCAL lcData, lcTemp
lcTemp = SYS(2015)+'.tmp'
TEXT TO m.lcData noshow
"Z-42R",14
"E-698M-1",13
"E-540N-1",12
"Z-43R",11
"Z-46R",11
"D-642B",10
"E-146R",10
"E-299M",10
"E-475M",10
"E-499R-2",10
"E-546M",10
"E-563M-1",10
"MJ-0066N",10
"E-377R",9
"E-541E",9
"MO011M",9
"MO011R",9
"MO100E",9
"MO114M",9
"MO114R",9
"C-988R",8
"D-111E",8
"D-897N",8
"E-140M",8
"E-363R",8
"E-431R",8
"E-453E",8
"E-563E-1",8
"G.E5964",8
"MO100M",8
ENDTEXT
STRTOFILE(m.lcData,m.lcTemp)
create CURSOR xModel ;
( ;
MODELNO C(14,0) NOT NULL ;
,TASKONTUR N(10,0) NOT NULL ;
)
APPEND FROM (m.lcTemp) TYPE delimited
ERASE (m.lcTemp)
endproc
*/*/*/ ikinci table
CreateCursor_xDetay()
Procedure CreateCursor_xDetay
LOCAL lcData, lcTemp
lcTemp = SYS(2015)+'.tmp'
TEXT TO m.lcData noshow
"D-642B",1,50,0,00,11,"",0,00,"",0,,0000000,""
"D-642B",1,30,0,00,26,"",0,00,"",0,,0000000,""
"D-642B",1,45,0,00,60,"",0,00,"",0,,0000000,""
"D-642B",1,25,0,00,35,"",0,00,"",0,,0000000,""
"D-642B",1,35,0,00,11,"",0,00,"",0,,0000000,""
"D-642B",1,10,0,00,1,"",0,00,"",0,,0000000,""
"D-642B",1,40,0,00,45,"",0,00,"",0,,0000000,""
"D-642B",1,45,0,00,42,"RUBİ",0,00,"",0,,0000000,""
"D-642B",1,40,0,00,65,"RUBİ",0,00,"",0,,0000000,""
"D-642B",1,20,0,00,30,"RUBİ",0,00,"",0,,0000000,""
"E-146R",2,10,0,00,3,"",0,00,"",0,,0000000,""
"E-146R",2,00,0,00,8,"",0,00,"",0,,0000000,""
"E-146R",1,90,0,00,8,"",0,00,"",0,,0000000,""
"E-146R",1,05,0,00,6,"",0,00,"",0,,0000000,""
"E-146R",1,20,0,00,4,"",0,00,"",0,,0000000,""
"E-146R",1,35,0,00,6,"",0,00,"",0,,0000000,""
"E-146R",1,40,0,00,6,"",0,00,"",0,,0000000,""
"E-146R",1,50,0,00,4,"",0,00,"",0,,0000000,""
"E-146R",1,60,0,00,26,"",0,00,"",0,,0000000,""
"E-146R",1,70,0,00,25,"",0,00,"",0,,0000000,""
"E-299M",2,20,0,00,4,"",0,00,"",0,,0000000,""
"E-299M",2,10,0,00,4,"",0,00,"",0,,0000000,""
"E-299M",2,00,0,00,2,"",0,00,"",0,,0000000,""
"E-299M",1,90,0,00,6,"",0,00,"",0,,0000000,""
"E-299M",1,80,0,00,2,"",0,00,"",0,,0000000,""
"E-299M",1,70,0,00,2,"",0,00,"",0,,0000000,""
"E-299M",1,55,0,00,2,"",0,00,"",0,,0000000,""
"E-299M",1,50,0,00,6,"",0,00,"",0,,0000000,""
"E-299M",1,45,0,00,63,"",0,00,"",0,,0000000,""
"E-299M",3,00,0,00,2,"",0,00,"",0,,0000000,""
"E-475M",2,50,0,00,20,"",0,00,"",0,,0000000,""
"E-475M",2,40,0,00,6,"",0,00,"",0,,0000000,""
"E-475M",2,30,0,00,5,"",0,00,"",0,,0000000,""
"E-475M",2,25,0,00,8,"",0,00,"",0,,0000000,""
"E-475M",2,20,0,00,5,"",0,00,"",0,,0000000,""
"E-475M",2,10,0,00,4,"",0,00,"",0,,0000000,""
"E-475M",2,00,0,00,7,"",0,00,"",0,,0000000,""
"E-475M",1,90,0,00,4,"",0,00,"",0,,0000000,""
"E-475M",1,80,0,00,4,"",0,00,"",0,,0000000,""
"E-475M",1,75,0,00,2,"",0,00,"",0,,0000000,""
"E-499R-2",1,80,0,00,4,"",0,00,"",0,,0000000,""
"E-499R-2",1,70,0,00,2,"",0,00,"",0,,0000000,""
"E-499R-2",1,60,0,00,2,"",0,00,"",0,,0000000,""
"E-499R-2",1,55,0,00,20,"",0,00,"",0,,0000000,""
"E-499R-2",1,50,0,00,2,"",0,00,"",0,,0000000,""
"E-499R-2",1,45,0,00,2,"",0,00,"",0,,0000000,""
"E-499R-2",1,35,0,00,6,"",0,00,"",0,,0000000,""
"E-499R-2",1,40,0,00,14,"",0,00,"",0,,0000000,""
"E-499R-2",1,10,0,00,5,"",0,00,"",0,,0000000,""
"E-499R-2",6,90,8,00,1,"orta",0,00,"",0,,0000000,""
"E-540N-1",1,25,0,00,12,"",0,00,"",0,,0000000,""
"E-540N-1",1,05,0,00,106,"",0,00,"",0,,0000000,""
"E-540N-1",2,25,0,00,1,"",0,00,"",0,,0000000,""
"E-540N-1",2,10,0,00,2,"",0,00,"",0,,0000000,""
"E-540N-1",1,90,0,00,4,"",0,00,"",0,,0000000,""
"E-540N-1",4,00,6,00,1,"OVAL",0,00,"",0,,0000000,""
"E-540N-1",1,25,0,00,12,"",0,00,"",0,,0000000,""
"E-540N-1",1,05,0,00,106,"",0,00,"",0,,0000000,""
"E-540N-1",2,25,0,00,1,"",0,00,"",0,,0000000,""
"E-540N-1",2,10,0,00,2,"",0,00,"",0,,0000000,""
"E-540N-1",1,90,0,00,4,"",0,00,"",0,,0000000,""
"E-540N-1",4,00,6,00,1,"",0,00,"",0,,0000000,""
"E-698M-1",1,45,0,00,1,"",0,00,"",0,,0000000,""
"E-698M-1",1,50,0,00,1,"",0,00,"",0,,0000000,""
"E-698M-1",1,55,0,00,1,"",0,00,"",0,,0000000,""
"E-698M-1",1,60,0,00,1,"",0,00,"",0,,0000000,""
"E-698M-1",1,70,0,00,1,"",0,00,"",0,,0000000,""
"E-698M-1",1,80,0,00,1,"",0,00,"",0,,0000000,""
"E-698M-1",1,90,0,00,4,"",0,00,"",0,,0000000,""
"E-698M-1",2,10,0,00,1,"",0,00,"",0,,0000000,""
"E-698M-1",2,20,0,00,1,"",0,00,"",0,,0000000,""
"E-698M-1",2,25,0,00,1,"",0,00,"",0,,0000000,""
"E-698M-1",2,30,0,00,1,"",0,00,"",0,,0000000,""
"E-698M-1",2,40,0,00,1,"",0,00,"",0,,0000000,""
"E-698M-1",2,50,0,00,1,"",0,00,"",0,,0000000,""
"Z-42R",1,00,0,00,2,"",0,00,"",0,,0000000,""
"Z-42R",1,05,0,00,4,"",0,00,"",0,,0000000,""
"Z-42R",1,10,0,00,2,"",0,00,"",0,,0000000,""
"Z-42R",1,20,0,00,2,"",0,00,"",0,,0000000,""
"Z-42R",1,25,0,00,2,"",0,00,"",0,,0000000,""
"Z-42R",1,30,0,00,2,"",0,00,"",0,,0000000,""
"Z-42R",1,35,0,00,2,"",0,00,"",0,,0000000,""
"Z-42R",1,40,0,00,2,"",0,00,"",0,,0000000,""
"Z-42R",1,45,0,00,2,"",0,00,"",0,,0000000,""
"Z-42R",1,50,0,00,4,"",0,00,"",0,,0000000,""
"Z-42R",1,55,0,00,32,"",0,00,"",0,,0000000,""
"Z-42R",3,00,0,00,1,"",0,00,"",0,,0000000,""
"Z-42R",2,70,0,00,2,"",0,00,"",0,,0000000,""
"Z-42R",2,10,0,00,2,"",0,00,"",0,,0000000,""
"Z-43R",1,70,0,00,9,"",0,00,"",0,,0000000,""
"Z-43R",1,60,0,00,6,"",0,00,"",0,,0000000,""
"Z-43R",1,55,0,00,4,"",0,00,"",0,,0000000,""
"Z-43R",1,50,0,00,3,"",0,00,"",0,,0000000,""
"Z-43R",1,40,0,00,3,"",0,00,"",0,,0000000,""
"Z-43R",1,35,0,00,3,"",0,00,"",0,,0000000,""
"Z-43R",1,25,0,00,9,"",0,00,"",0,,0000000,""
"Z-43R",1,20,0,00,6,"",0,00,"",0,,0000000,""
"Z-43R",1,10,0,00,6,"",0,00,"",0,,0000000,""
"Z-43R",1,05,0,00,6,"",0,00,"",0,,0000000,""
"Z-43R",2,25,0,00,6,"",0,00,"",0,,0000000,""
"Z-46R",2,10,0,00,12,"",0,00,"",0,,0000000,""
"Z-46R",2,30,0,00,12,"",0,00,"",0,,0000000,""
"Z-46R",1,05,0,00,6,"",0,00,"",0,,0000000,""
"Z-46R",1,10,0,00,4,"",0,00,"",0,,0000000,""
"Z-46R",1,20,0,00,2,"",0,00,"",0,,0000000,""
"Z-46R",1,30,0,00,2,"",0,00,"",0,,0000000,""
"Z-46R",1,35,0,00,6,"",0,00,"",0,,0000000,""
"Z-46R",1,40,0,00,2,"",0,00,"",0,,0000000,""
"Z-46R",1,45,0,00,2,"",0,00,"",0,,0000000,""
"Z-46R",1,55,0,00,4,"",0,00,"",0,,0000000,""
"Z-46R",1,70,0,00,10,"",0,00,"",0,,0000000,""
ENDTEXT
STRTOFILE(m.lcData,m.lcTemp)
create CURSOR xDetay ;
( ;
MODELNO C(14,0) NOT NULL ;
,MM1 N(5,2) NOT NULL ;
,MM2 N(5,2) NOT NULL ;
,TASADET N(4,0) NOT NULL ;
,KESIM C(15,0) NOT NULL ;
,CT N(6,2) NOT NULL ;
,NOT C(25,0) NOT NULL ;
,FYTGIRIS N(8,0) NOT NULL ;
,FYTCIKIS N(8,7) NOT NULL ;
,TASKOD C(14,0) NOT NULL ;
)
APPEND FROM (m.lcTemp) TYPE delimited
ERASE (m.lcTemp)
endproc
-------------------------------------------------------------------------------------------------------------
Hayat bir bisiklete binmek gibidir. Pedalı çevirmeye devam ettiğiniz sürece düşmezsiniz. Claude Peppeer
Kusuru söylenmeyen adam, ayıbını hüner sanır. Türk Atasözü