Having Count Distinct problem

Having Count Distinct problem 08.01.2008. u 14:29 · pre 228 meseci #1

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?

Potpis

steve585

Error.gif6.8k

Re: Having Count Distinct problem 08.01.2008. u 21:07 · pre 228 meseci #2

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 & ";"

Re: Having Count Distinct problem 08.01.2008. u 21:16 · pre 228 meseci #3

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...)

Potpis

rafforum.rs - Forum Računarskog Fakulteta

Re: Having Count Distinct problem 09.01.2008. u 17:41 · pre 228 meseci #4

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.

Potpis

steve585

Re: Having Count Distinct problem 13.01.2008. u 14:40 · pre 228 meseci #5

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?

Potpis

steve585

Err1.gif18.64k

Err2.gif47.38k

Re: Having Count Distinct problem 13.01.2008. u 16:40 · pre 228 meseci #6

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.

Re: Having Count Distinct problem 13.01.2008. u 17:28 · pre 228 meseci #7

Sada je javio novu gresku. Saljem je u prilogu.

Potpis

steve585

err3.gif13.54k

Re: Having Count Distinct problem 13.01.2008. u 17:33 · pre 228 meseci #8

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

Potpis

steve585

Re: Having Count Distinct problem 13.01.2008. u 17:36 · pre 228 meseci #9

Video sam - ali kasno, sorry

Probacu da simuliram kod mene, pa ti javim.

Re: Having Count Distinct problem 13.01.2008. u 17:44 · pre 228 meseci #10

Kao sto sam i mislio: kod mene radi.

Re: Having Count Distinct problem 13.01.2008. u 17:48 · pre 228 meseci #11

U cemu je fazon?

Potpis

steve585

Re: Having Count Distinct problem 13.01.2008. u 17:53 · pre 228 meseci #12

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.

Re: Having Count Distinct problem 13.01.2008. u 17:55 · pre 228 meseci #13

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

Re: Having Count Distinct problem 13.01.2008. u 17:58 · pre 228 meseci #14

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.

Re: Having Count Distinct problem 13.01.2008. u 18:12 · pre 228 meseci #15

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.

Potpis

steve585

Re: Having Count Distinct problem 13.01.2008. u 18:15 · pre 228 meseci #16

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.

Re: Having Count Distinct problem 13.01.2008. u 18:17 · pre 228 meseci #17

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

Code:
'" & FAKTURABROJ & A

To ti pravi problem.

Re: Having Count Distinct problem 13.01.2008. u 18:20 · pre 228 meseci #18

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.

Potpis

steve585

Re: Having Count Distinct problem 13.01.2008. u 18:23 · pre 228 meseci #19

Citat:

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

Code:
'" & FAKTURABROJ & A

To ti pravi problem.

Ovo je komentar izbrisao sam i to i dalje ista greska

Potpis

steve585

Re: Having Count Distinct problem 13.01.2008. u 18:30 · pre 228 meseci #20

Samo te jedno molim, posto ne verujem u zone sumraka: Izvrsi ovaj SQL kod sebe bez ikakvih modifikacija sa tvoje strane (Evo ti jos jednom):

Code:

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

pa javi.

Srodne teme