[es] - Having Count Distinct problem

steve585
Stevan Stanić
Peskovec

Član broj: 131815
Poruke: 165
*.xnet.hr.

Profil

^{08.01.2008. u 14:29}

Imam tekst fajl unutar kojeg bi trebao da izvrsim prebrojavanje faktura, gdje faktura sadrzi proizvode koje izabere korisnik.
Dakle korisnik unese npr. proizvod br. 1, proizvod br. 2 i proizvod br 3. Program treba da pokaze one fakture koje na sebi imaju i proizvod 1 i proizvod 2 i proizvod 3.

Podaci mi se nalaze u tekst fajl-u, te sam stoga napravio sljedeci kod:

Code:

Private Sub cmdIzvrsiUpit_Click()

Link01 = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="
Link02 = Chr(34)
Link3 = "DBQ=" & App.Path & ";DefaultDir=" & App.Path & ";Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;FILEDSN=" & App.Path & "\TXTODBC.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
TXTLink = Link1 & Link2 & Link3 & Link2

Dim TxtAdoConn As ADODB.Connection
Dim TxtAdoRS   As ADODB.Recordset
Dim TxtAdoFld  As ADODB.Field
Dim Server, SQLUpitiServeri As String

Set TxtAdoConn = New ADODB.Connection
TxtAdoConn.Open TXTLink

Dim Proizvodi
Dim Duzina As Integer
Duzina = Len(txtLista.Text)
Duzina = Duzina - 2
Proizvodi = Left(txtLista.Text, Duzina)

Dim CounUvjet As Integer
CounUvjet = Val(txtBrojProizvoda.Text)

SQLUpitServeri = "Select FAKTURA From TxtBaza.txt where ARTIKL in (" & Proizvodi & ") group by FAKTURA having count(DISTINCT(ARTIKL)) = " & CounUvjet
Poruka = MsgBox(SQLUpitServeri, vbOKOnly, "")

    'Kreiraj i otvori recordset
    Set TxtAdoRS = New ADODB.Recordset
    TxtAdoRS.Open SQLUpitServeri, TxtAdoConn

      With TxtAdoRS
      .MoveFirst
      Do While Not .EOF

        .MoveNext
      Loop
      End With
Exit Sub
End Sub

kada pokrenem izvrsavanje dobijem poruku koja se nalazi u prilogu. ODBC Text Driver nedefinirana funkcija 'DISTINCT'. Upit mora sadrzavati DISTINCT, jer jedino tako dobivam ispravne podatke.

Na koji nacin se moze srediti problem?

Prikačeni fajlovi

Error.gif - 6.8k

^{08.01.2008. u 14:29}

batalule

Član broj: 39086
Poruke: 104
194.106.178.*

Profil

Re: Having Count Distinct problem

^{08.01.2008. u 21:07}

Naravno da dobijas gresku jer DISTINCT nije funkcija.

"Select Distinct FAKTURA From TxtBaza.txt where ARTIKL in (" & Proizvodi & ") group by FAKTURA having count (ARTIKL)= " & CounUvjet & ";"

^{08.01.2008. u 21:07}

Aleksandar Ružičić
Developer, Haragei Creative Solutions
Beograd - Čačak

Moderator
Član broj: 26939
Poruke: 1778
*.dynamic.adsl.itsisp.net.

Jabber: krcko@haragei.com
Sajt: krcko.haragei.org

Profil

Re: Having Count Distinct problem

^{08.01.2008. u 21:16}

Code:

Link01 = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="
Link02 = Chr(34)
Link3 = "DBQ=" & App.Path & ";DefaultDir=" & App.Path & ";Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;FILEDSN=" & App.Path & "\TXTODBC.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
TXTLink = Link1 & Link2 & Link3 & Link2

zar LInk01 i Link02 ne bi trebalo da bude Link1 i Link2? (vidim da te dve promenljive koristis a Link01 i Link02 nigde...)

Čovekova dostignuća prevazilaze njegovu maštu.

^{08.01.2008. u 21:16}

steve585
Stevan Stanić
Peskovec

Član broj: 131815
Poruke: 165
62.159.241.*

Profil

Re: Having Count Distinct problem

^{09.01.2008. u 17:41}

Aleksandar Ružičić
Moguće da sam ovu 0 kod Link01 i Link02 greškom dodao prilikom kopiranja koda. Ako izbacim Distinct iz SQL upita radi bez problema.

batalule
Distinct mora stajati iza having (count), a ne na početku upita, u suprotnom rezultat koji dobijem nije ispravan.

^{09.01.2008. u 17:41}

steve585
Stevan Stanić
Peskovec

Član broj: 131815
Poruke: 165
*.xnet.hr.

Profil

Re: Having Count Distinct problem

^{13.01.2008. u 14:40}

Vezano za prethodni upit, opet isti problem sa DISTINCT. Evo koda:

Code:

Link1 = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="
Link2 = Chr(34)
Link3 = "DBQ=" & App.Path & ";DefaultDir=" & App.Path & ";Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;FILEDSN=" & App.Path & "\TXTODBC.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
TXTLink = Link1 & Link2 & Link3 & Link2

Dim TxtAdoConn As ADODB.Connection ' konekcija
Dim TxtAdoRS   As ADODB.Recordset  ' rekordset u kome ce biti podaci
Dim TxtAdoFld  As ADODB.Field
Dim Server, SQLUpitiServeri As String
Dim TxtAdoConn2 As ADODB.Connection ' konekcija
Dim TxtAdoRS2   As ADODB.Recordset

Dim DuzinaFakt As Long

Set TxtAdoConn = New ADODB.Connection
TxtAdoConn.Open TXTLink
SQLUpitServeri = "Select COUNT(DISTINCT(ARTIKL)) AS BROJF, FAKTURA From reportMag001.txt GROUP BY FAKTURA"
Set TxtAdoRS = New ADODB.Recordset
TxtAdoRS.Open SQLUpitServeri, TxtAdoConn
With TxtAdoRS
      .MoveFirst
      Do While Not .EOF
        A = !BROJF
        B = !FAKTURA
        txtRezultat.Text = txtRezultat.Text & A & " " & B & Chr(13) & Chr(10)
       .MoveNext
      Loop
End With

Zar ne postoji nacin da se izvrsi SQL upit

Code:

SQLUpitServeri = "Select COUNT(DISTINCT(ARTIKL)) AS BROJF, FAKTURA From reportMag001.txt GROUP BY FAKTURA"

Sta nije dobro programirano? Samo zelim prebrojati sve razlicite proizvode unutar faktura?

Prikačeni fajlovi

Err1.gif - 18.64k

Err2.gif - 47.38k

^{13.01.2008. u 14:40}

goranvuc
Goran Vucicevic
Novi Sad

Moderator
Član broj: 4934
Poruke: 1586
*.dialup.neobee.net.

Sajt: www.clickersoftware.com

Profil

Re: Having Count Distinct problem

^{13.01.2008. u 16:40}

Mrzi me da proveravam, ali možda da malo obrneš SQL, tj. da ga drugačije postaviš:

Code:

SELECT COUNT(Artikl) AS BrojF, Faktura
FROM (SELECT DISTINCT Artikl, Faktura FROM reportMag001.txt) AS Razliciti
GROUP BY Faktura

Suština je da prvo selektuješ sve različite, a onda iz tog skupa da vadiš Count() po fakturi.

vidi GoranVucicevic #28045

www.clickersoftware.com

^{13.01.2008. u 16:40}

steve585
Stevan Stanić
Peskovec

Član broj: 131815
Poruke: 165
*.xnet.hr.

Profil

Re: Having Count Distinct problem

^{13.01.2008. u 17:28}

Sada je javio novu gresku. Saljem je u prilogu.

Prikačeni fajlovi

err3.gif - 13.54k

^{13.01.2008. u 17:28}

steve585
Stevan Stanić
Peskovec

Član broj: 131815
Poruke: 165
*.xnet.hr.

Profil

Re: Having Count Distinct problem

^{13.01.2008. u 17:33}

Ne radim sa Access-om u pitanju su txt file-ovi

^{13.01.2008. u 17:33}

goranvuc
Goran Vucicevic
Novi Sad

Moderator
Član broj: 4934
Poruke: 1586
*.dialup.neobee.net.

Sajt: www.clickersoftware.com

Profil

Re: Having Count Distinct problem

^{13.01.2008. u 17:36}

Video sam - ali kasno, sorry

Probacu da simuliram kod mene, pa ti javim.

vidi GoranVucicevic #28045

www.clickersoftware.com

^{13.01.2008. u 17:36}

goranvuc
Goran Vucicevic
Novi Sad

Moderator
Član broj: 4934
Poruke: 1586
*.dialup.neobee.net.

Sajt: www.clickersoftware.com

Profil

Re: Having Count Distinct problem

^{13.01.2008. u 17:44}

Kao sto sam i mislio: kod mene radi.

vidi GoranVucicevic #28045

www.clickersoftware.com

^{13.01.2008. u 17:44}

steve585
Stevan Stanić
Peskovec

Član broj: 131815
Poruke: 165
*.xnet.hr.

Profil

Re: Having Count Distinct problem

^{13.01.2008. u 17:48}

U cemu je fazon?

^{13.01.2008. u 17:48}

goranvuc
Goran Vucicevic
Novi Sad

Moderator
Član broj: 4934
Poruke: 1586
*.dialup.neobee.net.

Sajt: www.clickersoftware.com

Profil

Re: Having Count Distinct problem

^{13.01.2008. u 17:53}

Ovo mi je ConnectionString za probni fajl koji sam kreirao da bih ti pomogao:
"Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DefaultDir=E:\Program Files\Common Files\ODBC\Data Sources;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=txt,csv,tab,asc;FIL=text;FILEDSN=E:\Program Files\Common Files\ODBC\Data Sources\Proba.txt.dsn;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
i koliko vidim nije nista specijalno drugaciji.

Stavi za konekciju da je CursorLocation = Client, a recordset otvori kao Static i ReadOnly - da proverimo da nije mozda to.

vidi GoranVucicevic #28045

www.clickersoftware.com

^{13.01.2008. u 17:53}

goranvuc
Goran Vucicevic
Novi Sad

Moderator
Član broj: 4934
Poruke: 1586
*.dialup.neobee.net.

Sajt: www.clickersoftware.com

Profil

Re: Having Count Distinct problem

^{13.01.2008. u 17:55}

Posto malo slabije stojim sa nemackim jezikom, ako mozes samo da das prevod te greske.

vidi GoranVucicevic #28045

www.clickersoftware.com

^{13.01.2008. u 17:55}

goranvuc
Goran Vucicevic
Novi Sad

Moderator
Član broj: 4934
Poruke: 1586
*.dialup.neobee.net.

Sajt: www.clickersoftware.com

Profil

Re: Having Count Distinct problem

^{13.01.2008. u 17:58}

Sad nesto gledam te tvoje screenshot-ove pa mi nesto nije jasno: ti kao da si ovo resenje sto sam ti dao testirao na necemu drugom, a ne na ekranu gde si prvobitno imao problem - hajde samo proveri da li na dobrom mestu testiras.

vidi GoranVucicevic #28045

www.clickersoftware.com

^{13.01.2008. u 17:58}

steve585
Stevan Stanić
Peskovec

Član broj: 131815
Poruke: 165
*.xnet.hr.

Profil

Re: Having Count Distinct problem

^{13.01.2008. u 18:12}

ni meni njemacki nije skroz jaka strana, ali otprilike znaci sljedece: 2 parametra su ocekivana, ali je premalo parametara poslano

Nadam se da si na ovo mislio kada si napisao da promjenim parametre kod konekcije i recordset-a

Code:

Set TxtAdoConn = New ADODB.Connection

TxtAdoConn.CursorLocation = adUseClient

TxtAdoConn.Open TXTLink
SQLUpitServeri = "SELECT COUNT(Artikl) AS BrojF, Faktura FROM (SELECT DISTINCT Artikl, Faktura FROM reportMag001.txt) AS Razliciti GROUP BY Faktura"
'" & FAKTURABROJ & A
Set TxtAdoRS = New ADODB.Recordset

TxtAdoRS.Open SQLUpitServeri, TxtAdoConn, adOpenStatic, adLockReadOnly

U pitanju je ista aplikacija u medjuvremenu sam dodao jo dugmadi.

^{13.01.2008. u 18:12}

goranvuc
Goran Vucicevic
Novi Sad

Moderator
Član broj: 4934
Poruke: 1586
*.dialup.neobee.net.

Sajt: www.clickersoftware.com

Profil

Re: Having Count Distinct problem

^{13.01.2008. u 18:15}

Ok je, potpuno se razumemo, to sam mislio.

Vidi, SQL koji sam ti dao je potpuno ispravan - 100% provereno i nije tu problem. Kazi mi da li ti radi najobicniji "SELECT Artikl, Faktura FROM reportMag001.txt" pa da onda sumnjam na nesto drugo.

vidi GoranVucicevic #28045

www.clickersoftware.com

^{13.01.2008. u 18:15}

goranvuc
Goran Vucicevic
Novi Sad

Moderator
Član broj: 4934
Poruke: 1586
*.dialup.neobee.net.

Sajt: www.clickersoftware.com

Profil

Re: Having Count Distinct problem

^{13.01.2008. u 18:17}

Cek, cek - sta ti je ovo posle SQL-a

Code:
'" & FAKTURABROJ & A

To ti pravi problem.

vidi GoranVucicevic #28045

www.clickersoftware.com

^{13.01.2008. u 18:17}

steve585
Stevan Stanić
Peskovec

Član broj: 131815
Poruke: 165
*.xnet.hr.

Profil

Re: Having Count Distinct problem

^{13.01.2008. u 18:20}

Obicni upiti mi rade.

Otisao sam do te mjere, da sam vodjen tvojim prvim postom napravio sljedece:

Code:

Link1 = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="
Link2 = Chr(34)
Link3 = "DBQ=" & App.Path & ";DefaultDir=" & App.Path & ";Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;FILEDSN=" & App.Path & "\TXTODBC.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
TXTLink = Link1 & Link2 & Link3 & Link2

Dim TxtAdoConn As ADODB.Connection ' konekcija
Dim TxtAdoRS   As ADODB.Recordset  ' rekordset u kome ce biti podaci
Dim TxtAdoFld  As ADODB.Field
Dim Server, SQLUpitiServeri As String
Dim TxtAdoConn2 As ADODB.Connection ' konekcija
Dim TxtAdoRS2   As ADODB.Recordset
Dim DuzinaFakt As Long

Kill (App.Path & "\reportMag011.txt")

Set TxtAdoConn = New ADODB.Connection
TxtAdoConn.Open TXTLink
SQLUpitServeri = "Select DISTINCT(ARTIKL) AS ARTIKLI, FAKTURA From reportMag001.txt order by FAKTURA"
'" & FAKTURABROJ & A
Set TxtAdoRS = New ADODB.Recordset
TxtAdoRS.Open SQLUpitServeri, TxtAdoConn
With TxtAdoRS
     .MoveFirst
      Do While Not .EOF
        A = !ARTIKLI
        B = !Faktura
        If txtRezultat.Text = "" Then
        txtRezultat.Text = "ARTIKLI; FAKTURA" & Chr(13) + Chr(10) & A & "; " & B & Chr(13) & Chr(10)
        ElseIf txtRezultat.Text <> "" Then
        txtRezultat.Text = txtRezultat.Text & A & "; " & B & Chr(13) & Chr(10)
        End If
       .MoveNext
      Loop
End With
With CommonDialog1
   .FileName = App.Path & "\reportMag011.txt"
     Open .FileName For Output As #1
     Print #1, txtRezultat.Text
   Close #1
End With
TxtAdoRS.Close
Set TxtAdoRS = Nothing
TxtAdoConn.Close
Set TxtAdoConn = Nothing
End Sub

Code:

Link1 = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="
Link2 = Chr(34)
Link3 = "DBQ=" & App.Path & ";DefaultDir=" & App.Path & ";Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;FILEDSN=" & App.Path & "\TXTODBC.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
TXTLink = Link1 & Link2 & Link3 & Link2

Dim TxtAdoConn As ADODB.Connection ' konekcija
Dim TxtAdoRS   As ADODB.Recordset  ' rekordset u kome ce biti podaci
Dim TxtAdoFld  As ADODB.Field
Dim Server, SQLUpitiServeri As String
Dim TxtAdoConn2 As ADODB.Connection ' konekcija
Dim TxtAdoRS2   As ADODB.Recordset
Dim DuzinaFakt As Long

Kill (App.Path & "\reportMag111.txt")

Set TxtAdoConn = New ADODB.Connection
TxtAdoConn.Open TXTLink
SQLUpitServeri = "Select COUNT(ARTIKLI) AS BRARTIKLI, FAKTURA From reportMag011.txt GROUP by FAKTURA"
'" & FAKTURABROJ & A
Set TxtAdoRS = New ADODB.Recordset
TxtAdoRS.Open SQLUpitServeri, TxtAdoConn
With TxtAdoRS
     .MoveFirst
      Do While Not .EOF
        A = !BRARTIKLI
        B = !Faktura
        If txtCount.Text = "" Then
        txtCount.Text = "BROJ; FAKTURA" & Chr(13) + Chr(10) & A & "; " & B & Chr(13) & Chr(10)
        ElseIf txtRezultat.Text <> "" Then
        txtCount.Text = txtCount.Text & A & "; " & B & Chr(13) & Chr(10)
        End If
       .MoveNext
      Loop
End With

With CommonDialog1
   .FileName = App.Path & "\reportMag111.txt"
     Open .FileName For Output As #1
     Print #1, txtRezultat.Text
   Close #1
End With

TxtAdoRS.Close
Set TxtAdoRS = Nothing
TxtAdoConn.Close
Set TxtAdoConn = Nothing
End Sub

Ove upite bez problema izvodi.

Mislim da se dobije isti rezultat, ali treba vise vremena i koda.

^{13.01.2008. u 18:20}