Różny typ danych w koumnie, SQL

0

Cześć,

Mam problem z porównaniem zawartości kolumn w momencie jeśli w danej kolumnie znajdują się różne typy danych, np: "aaa" oraz "111"
Poniższy fragment kodu wybiera wszystko co znajduje sie w BAZA2 a czego nie ma w BAZA1, jednak jeśli w którejkolwiek z kolumn pojawią się różne typy danych, zwraca błąd.

"select * from BAZA2.csv baza_2 where not exists " & _
"(select * from BAZA1.csv baza_1 where " & _
"baza_1.kolumna_1 = baza_2.kolumna_1 and " & _
"baza_1.kolumna_2 = baza_2.kolumna_2 and " & _
"baza_1.kolumna_3 = baza_2.kolumna_3)"

Czy ktoś ma pomysł w jaki sposób można rozwiązać ten problem?

2

Kolumna ma swój typ danych, porównując wartości w kolumnach o różnych typach dokonujesz niejawnej konwersji typów. Nie piszesz na jakim silniku pracujesz, ale to zagadnienie podchodzi pod data type precedence, dla mssql jest taki: https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017

Czyli jeżeli np.: baza_1.kolumna_1 to int, a baza_2.kolumna_1 to varchar to wg. kolejności silnik będzie się starał dokonać konwersji baza_2.kolumna_1 do typu int. to zadziała dopóki będzie się to dało zrobić.

rozwiązanie to dokonać konwersji typu samemu, np. tak:

convert(varchar(100),baza_1.kolumna_1)  = baza_2.kolumna_1
0

Nie jestem pewny jaki to silnik. Mój kod to VBScript w którym jest wpleciona kwerenda w SQL, wygląda to tak:

```	Set fso = CreateObject("Scripting.FileSystemObject")
```	Set shell = CreateObject("WScript.Shell")
```	If Instr(WScript.FullName, "\System32\") And fso.FolderExists("C:\Windows\SysWow64") Then
```		shell.Run "C:\Windows\SysWow64\wScript.exe  """ & wscript.scriptFullName & """"
```		Wscript.quit
```	End If
```'
```	Dim polacz	
```	Const adOpenStakwerenda = 3
```	Const adLockOptimiskwerenda = 3
```	Const adCmdText = &H0001
```	Dim fullPath
```	fullPath = fso.GetParentFolderName(fso.GetFile(Wscript.ScriptFullName))
```'	
```	Set objExcel = CreateObject("Excel.Application")
```	objExcel.Visible = False
```	objExcel.DisplayAlerts = False
```'
```	Set polacz = CreateObject("ADODB.Connection")
```	polacz.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & fullPath & ";" & "Extended Properties=""text;HDR=YES;FMT=Delimited"""	
```'			
```	Set rekord = CreateObject("ADODB.Recordset")
```'	
```'<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
```'<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
```kwerenda = "select * from BAZA2.csv baza_2 where not exists " & _
```	"(select * from BAZA1.csv baza_1 where " & _
```	"baza_1.kolumna_1 = baza_2.kolumna_1 and " & _
```	"baza_1.kolumna_2 = baza_2.kolumna_2 and " & _
```	"baza_1.kolumna_3 = baza_2.kolumna_3)"
```'<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
```'<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
```'
```	rekord.Open kwerenda, polacz, adOpenStakwerenda, adLockOptimiskwerenda, adCmdText 
```'
```	Set OutputFile = fso.CreateTextFile(fullPath & "\wynik.js")
```'
```	i=0	
```'
```	Do Until rekord.eof
```		OutputFile.WriteLine("kwerenda[" & i & "]={" & _
```					chr(34) & "znaleziony rekord" & chr(34) & ":" & _
```					chr(34) & rekord.fields.item("kolumna_1") & chr(34) & "," & _
```					chr(34) & rekord.fields.item("kolumna_2") & chr(34) & "," & _
```					chr(34) & rekord.fields.item("kolumna_3") & chr(34) & _
```		"};")
```'		
```		i=i+1
```		rekord.movenext	
```	Loop
```'
```	rekord.Close
```	OutputFile.Close
```'	
```	objExcel.Quit
0

Używasz JET-a Accessowego do pobrania danych z 2 plików CSV: baza1.csv i baza2.csv i generujesz plik wynik.js, pomijam fakt, że nie mam pojęcia po co jest w nim tworzona instancja Excela...

Sam kwerenda powinna wyglądać mniej więcej tak:

kwerenda = "select * from BAZA2.csv baza_2 where not exists " & _
"(select * from BAZA1.csv baza_1 where " & _
"cstr(baza_1.kolumna_1) = cstr(baza_2.kolumna_1) and " & _
 "cstr(baza_1.kolumna_2) = cstr(baza_2.kolumna_2) and " & _
 "cstr(baza_1.kolumna_3) = cstr(baza_2.kolumna_3))"
0

Instancja Excela jest tworzona na potrzeby pozostałego fragmentu kodu który skasowałem przed wklejeniem do tego posta (żeby nie wprowadzać niepotrzebnych elementów) - a zapomniałem skasować tworzenie obiektu :)

Dziękuję Ci bardzo za pomoc

0

Ale obawiam się, że to rozwiązanie nie do końca pomogło :(
Wskazuje błąd "Invalid use of Null"
w linii
rekord.Open kwerenda, polacz, adOpenStakwerenda, adLockOptimiskwerenda, adCmdText

0
kwerenda = "select * from BAZA2.csv baza_2 where not exists " & _
"(select * from BAZA1.csv baza_1 where " & _
"cstr(nz(baza_1.kolumna_1,"""")) = cstr(nz(baza_2.kolumna_1,"""")) and " & _
 "cstr(nz(baza_1.kolumna_2,"""")) = cstr(nz(baza_2.kolumna_2,"""")) and " & _
 "cstr(nz(baza_1.kolumna_3,"""")) = cstr(nz(baza_2.kolumna_3,"""")))"
0

A za co odpowiada funkcja "nz"? Jest jak jak sie okazuje niezdefiniowana.

0

Zamienia null na co chcesz, w przypadku co podał @Panczo na pusty string.

https://support.office.com/en-us/article/nz-function-8ef85549-cc9c-438b-860a-7fd9f4c69b6c

1

Faktycznie nz zadziała jak wywolasz w access, w przypadku ado trzeba użyć iif:

kwerenda = "select * from BAZA2.csv baza_2 where not exists " & _
"(select * from BAZA1.csv baza_1 where " & _
"iif(isnull(baza_1.kolumna_1),"""",cstr(baza_1.kolumna_1)) = iif(isnull(baza_2.kolumna_1),"""",cstr(baza_1.kolumna_1)) and " & _
 "iif(isnull(baza_1.kolumna_2),"""",cstr(baza_1.kolumna_2)) = iif(isnull(baza_2.kolumna_2),"""",cstr(baza_1.kolumna_3)) and " & _
 "iif(isnull(baza_1.kolumna_3),"""",cstr(baza_1.kolumna_3)) = iif(isnull(baza_2.kolumna_3),"""",cstr(baza_1.kolumna_3)))"
0

Moje docelowe bazy które będę porównywał mają po ok 600-700 tyś rekordów, każdy składający się z 10 kolumn. W kolumnach będzie od 2 do max 300 znaków...

0

No to przy takiej ilości zajedziesz Access-a i będziesz czekal wieki na wynik

0

Tu chyba nie tylko chodzi o te różnice, sam nie wiem. Kod wykonuje się lub nie w zależności od tego co się pojawia w tabelach. Dla przykładu:

```BAZA1.csv                            BAZA2.csv
``` baza_1 | baza_2 | baza_3            baza_1 | baza_2 | baza_3
``` 1      | c      | 1                 1      | 1      | 1
``` 1      | 1      | c                 1      | 1      | 1
``` c      | 1      | 1                 1      | 1      | 1

Wykonuje się prawidłowo, ale już jeśli w BAZA1.csv w jakimkolwiek wierszu dam c | c | c, to jest error

Jeśli natomiast jest odwrotnie, czyli
```BAZA1.csv                            BAZA2.csv
``` baza_1 | baza_2 | baza_3            baza_1 | baza_2 | baza_3
``` 1      | 1      | 1                 1      | c      | 1
``` 1      | 1      | 1                 1      | 1      | c
``` 1      | 1      | 1                 c      | 1      | 1 

To kod się również wykonuje ale tam gdzie jest "c" wstawia pusty string "".
Również w przypadku c | c | c zwraca błąd wyświetljąc: <u>Data type mismatch in criteria expression</u> i wksazując na wiersz ```  rekord.Open kwerenda, polacz, adOpenStakwerenda, adLockOptimiskwerenda, adCmdText

A co myślicie o schema.ini? Czytałem, że to rozwiązuje wiele problemów
0

Mam wrażenie, ze błądzisz po omacku nie rozumiejąc zupełnie co robisz...

Ta linijka w kodzie

polacz.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & fullPath & ";" & "Extended Properties=""text;HDR=YES;FMT=Delimited""" 

"Informuje" że czytasz pliki csv które w pierwszym wierszu zawierają nazwy kolumn, w twoim przykładze baza_1 | baza_2 | baza_3 , w kodzie maja w nazwie kolumna
dodatkowo podział na kolumny jest zdefiniowany w rejestrze: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\ Engines\Text

schema.ini pewnie rozwiąże sporo problemów, choćby przez zdefiniowanie typu kolumny, ale nadal trzeba wiedzieć co się robi

0

Ciągle modyfikuje kod i mże stąd ten rozjazd. W tym momencie wygląda tak:

Dim fso,shell
	Set fso = CreateObject("Scripting.FileSystemObject")
	Set shell = CreateObject("WScript.Shell")
	If Instr(WScript.FullName, "\System32\") And fso.FolderExists("C:\Windows\SysWow64") Then
		shell.Run "C:\Windows\SysWow64\wScript.exe  """ & wscript.scriptFullName & """"
		Wscript.quit
	End If

	Dim polacz	
	Const adOpenStakwerenda = 3
	Const adLockOptimiskwerenda = 3
	Const adCmdText = &H0001
	Dim fullPath
	fullPath = fso.GetParentFolderName(fso.GetFile(Wscript.ScriptFullName))
	
	Set objExcel = CreateObject("Excel.Application")
	objExcel.Visible = False
	objExcel.DisplayAlerts = False

	Set polacz = CreateObject("ADODB.Connection")
	polacz.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & fullPath & ";" & "Extended Properties=""text;HDR=YES;FMT=Delimited"""	
			
	Set rekord = CreateObject("ADODB.Recordset")
	
'<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
'<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
kwerenda = "select * from TABELA2.csv tabela_2 where not exists " & _
"(select * from TABELA1.csv tabela_1 where " & _
"iif(isnull(tabela_1.kolumna_1),"""",cstr(tabela_1.kolumna_1)) = iif(isnull(tabela_2.kolumna_1),"""",cstr(tabela_1.kolumna_1)) and " & _
 "iif(isnull(tabela_1.kolumna_2),"""",cstr(tabela_1.kolumna_2)) = iif(isnull(tabela_2.kolumna_2),"""",cstr(tabela_1.kolumna_3)) and " & _
 "iif(isnull(tabela_1.kolumna_3),"""",cstr(tabela_1.kolumna_3)) = iif(isnull(tabela_2.kolumna_3),"""",cstr(tabela_1.kolumna_3)))"
'<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>
'<><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><><>

	rekord.Open kwerenda, polacz, adOpenStakwerenda, adLockOptimiskwerenda, adCmdText 

	Set OutputFile = fso.CreateTextFile(fullPath & "\wynik.js")

	i=0	

	Do Until rekord.eof
		OutputFile.WriteLine("kwerenda[" & i & "]={" & _
					chr(34) & "znaleziony rekord" & chr(34) & ":" & _
					chr(34) & rekord.fields.item("kolumna_1") & chr(34) & "," & _
					chr(34) & rekord.fields.item("kolumna_2") & chr(34) & "," & _
					chr(34) & rekord.fields.item("kolumna_3") & chr(34) & _
		"};")
		
		i=i+1
		rekord.movenext	
	Loop

	rekord.Close
	OutputFile.Close
	
	objExcel.Quit

Do tego dodałem plik schema.ini do tego samego folderu, o zawartości:

[TABLE1.csv]
ColNameHeader = true
CharacterSet = ANSI
Format = CSVDelimited
Col1=kolumna_1 Text
Col2=kolumna_2 Text
Col3=kolumna_3 Text

[TABLE2.csv]
ColNameHeader = true
CharacterSet = ANSI
Format = CSVDelimited
Col1=kolumna_1 Text
Col2=kolumna_2 Text
Col3=kolumna_3 Text

Jednak po jego dodaniu, kod niczego nie wyciąga.

Moja wiedza z VBScript jest taka sobie a z SQL bardzo mała, jednak bardzo potrzebuję to ogarnąć. Będę wdzięczny za sugestie.

0

Ja nie wiem jak ci pomóc bo za każdym razem podajesz inne dane, raz plik nazywa się baza1 w schema table1, w przykładzie dajesz | jako rozdzielnik w schema masz ,, trudno sensownie się do tego odnieść

0

schema.ini wyglada oczywiscie tak jak poniżej a separatorami są przecinki. Przepraszam ze marnuje Twój / Wasz czas glupimi literówkami.

[TABELA1.csv]
ColNameHeader = true
CharacterSet = ANSI
Format = CSVDelimited
Col1=kolumna_1 Text
Col2=kolumna_2 Text
Col3=kolumna_3 Text

[TABELA2.csv]
ColNameHeader = true
CharacterSet = ANSI
Format = CSVDelimited
Col1=kolumna_1 Text
Col2=kolumna_2 Text
Col3=kolumna_3 Text

Na chwilę obecną ta kwerenda działa, o ile nie ma pustych rekordów. Przy pustych wyrzuca błąd.

	kwerenda = "select * from TABELA2.csv tabela_2 where not exists " & _ 
				"(select * from TABELA1.csv tabela_1 where " & _ 
				"cstr(tabela_1.kolumna_1) = cstr(tabela_2.kolumna_1) and " & _ 
				"cstr(tabela_1.kolumna_2) = cstr(tabela_2.kolumna_2) and " & _ 
				"cstr(tabela_1.kolumna_3) = cstr(tabela_2.kolumna_3))" 

natomiast ta zwraca dane jedynie w momencie kiedy któryś rekord w wierszu jest pusty:

kwerenda = "select * from TABELA2.csv tabela_2 where not exists " & _
			"(select * from TABELA1.csv tabela_1 where " & _
			"iif(isnull(tabela_1.kolumna_1),"""",cstr(tabela_1.kolumna_1)) = iif(isnull(tabela_2.kolumna_1),"""",cstr(tabela_1.kolumna_1)) and " & _
			"iif(isnull(tabela_1.kolumna_2),"""",cstr(tabela_1.kolumna_2)) = iif(isnull(tabela_2.kolumna_2),"""",cstr(tabela_1.kolumna_3)) and " & _
			"iif(isnull(tabela_1.kolumna_3),"""",cstr(tabela_1.kolumna_3)) = iif(isnull(tabela_2.kolumna_3),"""",cstr(tabela_1.kolumna_3)))"
0

Proponuje, dać w załączniku próbke danych z obu plików csv, dołączyć schema.ini i napisać jakiego rezultatu się oczekuje, bo tak się nie dogadamy.

0

W załączniku znajdują się wszystkie pliki.

Jako rezultat potrzebuję otrzymać takie rekordy z pliku TABELA2.csv które nie występują w pliku TABELA1.csv. Z pliku TABELA1.csv nic nie powinno być wypisane, jedynie z TABELA2.csv

0

Nie wiem czy to może mieć znaczenie, ale zamieniłem miejscami argumenty w każdym IIF w taki sposób

iif(isnull(tabela_1.kolumna_1),cstr(tabela_1.kolumna_1),"""")

jednak wynik pozostał bez zmian

0

Naprawdę "Szalony" z Ciebie ogórek ;) ... generalnie w większości funkcji kolejność argumentów ma znaczenie ... byś sprawdził u wujka co IIF w ogóle znaczy to w życiu byś tego nie zrobił ... a może by s zrobił ale było by to całkiem bez sensu :|

0

Haha, taki nick mi nadało, widać nie bez powodu :) Czytałem oczywiście o IIF, i patrząc na jego składnię i na kod który przedstawił Panczo, to nie widzę powodu żeby działał tak jak działa, był więc to test w stylu "A może akurat" :)

0

hehe w sumie ja wiem, że "czary z mleka" czasem działają choć nie powinny no ale bez ... no wiadomo czego ... to nie miało szans zadziałać.

Kod Pancza "ISNULL" sprawdza czy wartość kolumny jest null (bo z taką wartością nie da się nic zrobić) -- jesli tak to zamienia na pusty string """" (bo taką wartość można już porównywać), a jeśli wartość nie jest null to wstawia faktyczną wartość z kolumny.
Także żadnego sensu nie ma sprawdzać czy kolumna ma wartość null i jeśli TAK to zostawić ten NULL a każda inna wartość zamienić na """" :)

0

Zgodzę się z Tobą w 100%, jednak na podstawie danych z bazy przy jakich został puszczony skrypt, mianowicie:

TABELA1.csv                            TABELA2.csv
kolumna_1,kolumna_2,kolumna_3          kolumna_1,kolumna_2,kolumna_3
1        ,m        ,m                  1        ,1        ,c
m        ,1        ,m                           ,         ,
         ,         ,                   f        ,i        ,/

powinien zwrócić z TABELA2.csv

1        ,1        ,c
f        ,i        ,/

bo te wiersze nie występują w TABELA1.csv.

Zwraca natomiast

        ,         ,
0

hmm to tak jakby Ci wcieło to "NOT EXIST" ;) na pewno tam dalej jest ten fragment ?

0

OMG przecież Ty tu masz literówki!!!

kwerenda = "select * from TABELA2.csv tabela_2 where not exists " & _
"(select * from TABELA1.csv tabela_1 where " & _
"iif(isnull(tabela_1.kolumna_1),"""",cstr(tabela_1.kolumna_1)) = iif(isnull(tabela_2.kolumna_1),"""",cstr(tabela_1.kolumna_1)) and " & _
"iif(isnull(tabela_1.kolumna_2),"""",cstr(tabela_1.kolumna_2)) = iif(isnull(tabela_2.kolumna_2),"""",cstr(tabela_1.kolumna_3)) and " & _
"iif(isnull(tabela_1.kolumna_3),"""",cstr(tabela_1.kolumna_3)) = iif(isnull(tabela_2.kolumna_3),"""",cstr(tabela_1.kolumna_3)))"

Popatrz na kod dokładnie masz błedy w nazwie tabeli i w kolumnie...

0

Mam dzisiaj dobry dzień, to Ci to napisze, ale to ostani raz dalej już tylko naprowadzam, dla plików które podałeś jest jeden problem którego nie bierzesz pod uwagę, w przypadku łączenia wierszy masz w każdym z nich pustą linię, najprościej to przekopiować te CSV do mdb, dodac id i posiłkować się left joinem, jednak dla ilości które opisujesz, Access nie wydoli, albo przynajmniej będzie działał baaardzo długo.

Skrypt

' Uruchom w trybie 32 bit
	Dim fso,shell,mdbFile
	dim con
	Set fso = CreateObject("Scripting.FileSystemObject")
	Set shell = CreateObject("WScript.Shell")
	If Instr(WScript.FullName, "\System32\") And fso.FolderExists("C:\Windows\SysWow64") Then
		shell.Run "C:\Windows\SysWow64\wScript.exe  """ & wscript.scriptFullName & """"
		Wscript.quit
	End If

	'Zmienne do skryptu
	fullPath = fso.GetParentFolderName(fso.GetFile(Wscript.ScriptFullName))
	mdbFile = fullPath & "\wynik.mdb"
	
	GenerujPlikJS
	
Sub GenerujPlikJS()		
	'Przygotuj dane do porównania
	'Stwórz mdb
	
	CreateNewMDB mdbFile, 5	
	
	'połącz się z plikiem
	Set con = CreateObject("ADODB.Connection")
	con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & mdbFile
	
	'skopiuj csv do mdb
	KopiujDaneZCSV  "tabela1.csv", "t1"
	KopiujDaneZCSV  "tabela2.csv", "t2"
	
	'Stworz plik JS
	
	genJS
	'Posprzątaj
	con.close
	set con = Nothing
	fso.DeleteFile mdbFile
End Sub	
Sub genJS()
	SQL = "SELECT t2.* " & _
				" FROM t2 left join t1 on iif(isnull(t1.kolumna_1),"""",t1.kolumna_1) = iif(isnull(t2.kolumna_1),"""",t2.kolumna_1) and " & _
				" iif(isnull(t1.kolumna_2),"""",t1.kolumna_2) = iif(isnull(t2.kolumna_2),"""",t2.kolumna_3) and " & _
				" iif(isnull(t1.kolumna_3),"""",t1.kolumna_3) = iif(isnull(t2.kolumna_3),"""",t2.kolumna_3)" & _
				" where t1.id is null"

	set rst = con.Execute(SQL)

	Set OutputFile = fso.CreateTextFile(fullPath & "\wynik.js")

	i=0	

	Do Until rst.eof
		OutputFile.WriteLine("kwerenda[" & i & "]={" & _
					chr(34) & "znaleziony rekord" & chr(34) & ":" & _
					chr(34) & rst.fields.item("kolumna_1") & chr(34) & "," & _
					chr(34) & rst.fields.item("kolumna_2") & chr(34) & "," & _
					chr(34) & rst.fields.item("kolumna_3") & chr(34) & _
		"};")
		
		i=i+1
		rst.movenext	
	Loop

	rst.Close
	OutputFile.Close
End Sub
Sub KopiujDaneZCSV(FileName, destTable)
	Dim SQL
	With con
		'wgraj dane do tabeli
		SQL="SELECT * into " & destTable & " FROM [Text; DATABASE=" & fullPath & "\].[" &  filename & "]"
		.Execute SQL
		'dodaj id
		SQL = "ALTER TABLE " & destTable & " ADD COLUMN ID COUNTER"
		.Execute SQL
	End With
end sub
Sub CreateNewMDB(FileName, Format)
  Dim Catalog
  Set Catalog = CreateObject("ADOX.Catalog")
  Catalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Jet OLEDB:Engine Type=" & Format & _
    ";Data Source=" & FileName
End Sub	

	

dla danych które dałeś wynik to:

kwerenda[0]={"znaleziony rekord":"1","1","c"};
kwerenda[1]={"znaleziony rekord":"f","1","/"};

0

Zamiast żmudnego Do Until rst.eof .. Loop lepiej zrobić rst.GetString(2, , ";")

0

Bardzo Ci dziekuje za cala pomoc. Kod który przedstawiles wyglada super i po wyniku widac ze robi to co trzeba. Niestety nie mam mozliwosci skorzystania z Accessa co uniemożliwi mi wykorzystanie go w tym momencie.

0

Ten kod nie wymaga accessa...

1 użytkowników online, w tym zalogowanych: 0, gości: 1