Đào Duy Thức
Thành viên mới
- Tham gia
- 16/12/16
- Bài viết
- 6
- Được thích
- 1
Xin chào mọi người,
Mình hiện đang gặp một vấn đề khó khăn mong mọi người giúp đỡ. Mình đang chuyển đổi từ cách xử lý dữ liệu truyền thống sang cách mới, và đang bế tắc vì gặp lỗi khi tạo lại hàm NTILE của Oracle sang script SQL chạy trên Excel, mặc dù mình đã thành công khi viết lại hàm NTILE chạy được trên Oracle PL/SQL Developer.
Đây là cách truyền thống:
Với cách mới, script SQL sẽ được chỉnh lại một chút vì một số hàm và câu lệnh trong Oracle không tồn tại trong SQL trên Excel (ví dụ: NTILE, REMAINDER, TRUNC, CASE). NTILE thì mình viết lại; REMAINDER thay bằng MOD; TRUNC thay bằng INT; CASE thay bằng IIF.
Dưới đây là script SQL của:
Trong đoạn code SQL trên, cách truyền thống là đoạn -- ket qua NTILE mong muon.
Và cách mới là đoạn -- Ham NTILE viet lai chay tren Excel SQL.
Với cách mới, cần nhớ phải có file testntile.csv như mô tả ở phần INSERT ALL
Cuối cùng, đây là các sheet, sub và function trong VBA.
Với sheet mình dùng code name, không phải sheet name
Mặc dù mình đã cho ra kết quả giống hệt hàm NTILE với script SQL viết lại cho trường hợp chạy trên Oracle PL/SQL Developer, nhưng khi mình chạy script SQL viết lại cho trường hợp chạy trên Excel thì luôn bị báo lỗi. Mình đã thử rút gọn query SQL lại chỉ còn một đoạn SELECT IIf( ) đơn giản nhất như dưới đây nhưng vẫn gặp lỗi. Mọi người chạy thử lại sẽ thấy.
Mình đã tốn rất nhiều thời gian cho việc này nhưng vẫn không có kết quả. Vì vậy mình rất biết ơn nếu mọi người có thể giúp mình được.
Mình hiện đang gặp một vấn đề khó khăn mong mọi người giúp đỡ. Mình đang chuyển đổi từ cách xử lý dữ liệu truyền thống sang cách mới, và đang bế tắc vì gặp lỗi khi tạo lại hàm NTILE của Oracle sang script SQL chạy trên Excel, mặc dù mình đã thành công khi viết lại hàm NTILE chạy được trên Oracle PL/SQL Developer.
Đây là cách truyền thống:
- Script SQL được lưu trên sheet Excel.
- Mình viết macro kết nối với cơ sở dữ liệu Oracle, và open recordset với script SQL nói trên, rồi copy recordset đó vào một sheet khác.
- Script SQL được lưu trên sheet Excel.
- Một bảng trong cơ sở dữ liệu Oracle được lưu thành file csv trên ổ cứng.
- Mình viết macro kết nối với file csv (nhưng không mở nó lên) bằng Microsoft.Jet.OLEDB.4.0, và open recordset với script SQL nói trên, rồi copy recordset đó vào một sheet khác.
Với cách mới, script SQL sẽ được chỉnh lại một chút vì một số hàm và câu lệnh trong Oracle không tồn tại trong SQL trên Excel (ví dụ: NTILE, REMAINDER, TRUNC, CASE). NTILE thì mình viết lại; REMAINDER thay bằng MOD; TRUNC thay bằng INT; CASE thay bằng IIF.
Dưới đây là script SQL của:
- Tạo bảng trong Oracle
- Kết quả NTILE mong muốn
- Hàm NTILE được viết lại chạy trên Oracle (bước trung gian)
- Hàm NTILE được viết lại chạy trên Excel SQL
SQL:
CREATE TABLE testntile
(
appid NUMBER(2),
sample VARCHAR2(50),
score NUMBER(3)
);
INSERT ALL
INTO testntile (appid, sample, score) VALUES (5, 'A', 4)
INTO testntile (appid, sample, score) VALUES (2, 'B', 6)
INTO testntile (appid, sample, score) VALUES (3, 'C', 8)
INTO testntile (appid, sample, score) VALUES (1, 'C', 1)
INTO testntile (appid, sample, score) VALUES (4, 'B', 2)
INTO testntile (appid, sample, score) VALUES (8, 'C', 3)
INTO testntile (appid, sample, score) VALUES (6, 'C', 8)
INTO testntile (appid, sample, score) VALUES (9, 'B', 9)
INTO testntile (appid, sample, score) VALUES (7, 'C', 7)
INTO testntile (appid, sample, score) VALUES (10, 'B', 5)
INTO testntile (appid, sample, score) VALUES (14, 'A', 2)
INTO testntile (appid, sample, score) VALUES (12, 'C', 7)
INTO testntile (appid, sample, score) VALUES (13, 'C', 7)
INTO testntile (appid, sample, score) VALUES (11, 'A', 9)
INTO testntile (appid, sample, score) VALUES (15, 'C', 3)
INTO testntile (appid, sample, score) VALUES (16, 'C', 12)
INTO testntile (appid, sample, score) VALUES (18, 'C', 12)
INTO testntile (appid, sample, score) VALUES (17, 'C', 16)
INTO testntile (appid, sample, score) VALUES (19, 'C', 12)
INTO testntile (appid, sample, score) VALUES (20, 'D', 14)
INTO testntile (appid, sample, score) VALUES (21, 'D', 11)
SELECT * FROM dual;
COMMIT;
-- ket qua NTILE mong muon
SELECT
NTILE(3) OVER (PARTITION BY sample ORDER BY score) RangeList,
s.*
FROM testntile s;
-- Ham NTILE viet lai chay tren Oracle
-- (day la buoc trung gian de viet tiep chay tren Excel SQL)
SELECT
CASE WHEN REMAINDER(
(SELECT count(t2.appid)
FROM testntile t2
WHERE t2.sample = t1.sample)
,3) <> 0
THEN CASE WHEN ((SELECT count(t2.appid)
FROM testntile t2
WHERE t2.sample = t1.sample) / 3) < 1
THEN (SELECT COUNT(*) +1
FROM testntile t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
)
ELSE 1 + TRUNC((SELECT COUNT(*)
FROM testntile t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
) / ((SELECT count(t2.appid)
FROM testntile t2
WHERE t2.sample = t1.sample) / 3))
END
ELSE CASE WHEN ((SELECT count(t2.appid)
FROM testntile t2
WHERE t2.sample = t1.sample) / 3) = 1
THEN (SELECT COUNT(*) +1
FROM testntile t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
)
ELSE 1 + TRUNC((SELECT COUNT(*)
FROM testntile t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
) / ((SELECT count(t2.appid)
FROM testntile t2
WHERE t2.sample = t1.sample) / 3))
END
END RangeList,
t1.appid,
t1.sample,
t1.score
FROM
testntile t1
ORDER BY
t1.sample,
t1.score,
t1.appid
-- Ham NTILE viet lai chay tren Excel SQL
SELECT
IIf(MOD(
(SELECT count(t2.appid)
FROM &Selected_Table t2
WHERE t2.sample = t1.sample)
,3) <> 0
, IIf( ((SELECT count(t2.appid)
FROM &Selected_Table t2
WHERE t2.sample = t1.sample) / 3) < 1
, (SELECT COUNT(*) +1
FROM &Selected_Table t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
)
, 1 + INT((SELECT COUNT(*)
FROM &Selected_Table t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
) / ((SELECT count(t2.appid)
FROM &Selected_Table t2
WHERE t2.sample = t1.sample) / 3))
)
, IIf( ((SELECT count(t2.appid)
FROM &Selected_Table t2
WHERE t2.sample = t1.sample) / 3) = 1
, (SELECT COUNT(*) +1
FROM &Selected_Table t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
)
, 1 + INT((SELECT COUNT(*)
FROM &Selected_Table t2
WHERE t2.sample = t1.sample
AND (t2.score < t1.score OR (t2.score = t1.score AND t2.appid < t1.appid))
) / ((SELECT count(t2.appid)
FROM &Selected_Table t2
WHERE t2.sample = t1.sample) / 3))
)
) RangeList,
t1.appid,
t1.sample,
t1.score
FROM
&Selected_Table t1
ORDER BY
t1.sample,
t1.score,
t1.appidTrong đoạn code SQL trên, cách truyền thống là đoạn -- ket qua NTILE mong muon.
Và cách mới là đoạn -- Ham NTILE viet lai chay tren Excel SQL.
Với cách mới, cần nhớ phải có file testntile.csv như mô tả ở phần INSERT ALL
Cuối cùng, đây là các sheet, sub và function trong VBA.
Với sheet mình dùng code name, không phải sheet name
- shSQLList : chứa script SQL (cách mới) ở ô A1
- shResult : chứa dữ liệu recordset trả về sau khi chạy script SQL
Mã:
Option Explicit
Public strPath As String
Public strTable As String
Public Contn As New ADODB.Connection
Sub test()
Dim SQL As String
Dim rRange As Range
'let the user choose files needed for making a report
strPath = BrowseFileExplorer(, , ThisWorkbook.Path)
If strPath = vbNullString Then 'user cancelled
MsgBox "No file selected."
Else 'user picked a file
strPath = RemoveTrailingSlash(strPath)
strTable = Right(strPath, Len(strPath) - InStrRev(strPath, "\"))
'if file name contains underscore, ask the user to remove underscore
If InStr(1, strTable, "_", vbTextCompare) Then
MsgBox "SQL in Excel cannot run with underscore(s) in file name." & vbCrLf & _
"Please remove all underscores from the csv file, then run the macro again.", _
vbCritical + vbOKOnly, "Error"
Exit Sub
End If
End If
Application.ScreenUpdating = False
SQL = ""
Set rRange = Nothing
SQL = shSQLList.Range("A1").Value
SQL = Replace(SQL, "&Selected_Table", "[" & strTable & "]")
'table name needs enclosing in [ ] if it has space or special characters
'to run SQL in Excel, table name cannot contain underscore
Set rRange = shResult.Range("A1")
Call ImportCSVfile(SQL, strPath, rRange.Cells(1, 1))
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Function BrowseFileExplorer(Optional DialogTitle As String = "Select a file", _
Optional ViewType As Office.MsoFileDialogView = MsoFileDialogView.msoFileDialogViewSmallIcons, _
Optional InitialDirectory As String) As String
'Other option:
'ViewType As Office.MsoFileDialogView = msoFileDialogViewList
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.Title = DialogTitle
.InitialView = ViewType
.ButtonName = "&Select"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "CSV", "*.csv"
If Dir(InitialDirectory, vbDirectory) <> vbNullString Then
If Right(InitialDirectory, 1) <> "\" Then
InitialDirectory = InitialDirectory & "\"
End If
.InitialFileName = InitialDirectory
Else
.InitialFileName = CurDir
End If
If .Show = True Then
' user picked a file
BrowseFileExplorer = .SelectedItems(1)
Else
' user cancelled
BrowseFileExplorer = vbNullString
End If
End With
End Function
Public Function TrailingSlash(strFolder As String) As String
If Len(strFolder) > 0 Then
If Right(strFolder, 1) = "\" Then
TrailingSlash = strFolder
Else
TrailingSlash = strFolder & "\"
End If
End If
End Function
Public Function RemoveTrailingSlash(strFile As String) As String
If Len(strFile) > 0 Then
If Right(strFile, 1) = "\" Then
RemoveTrailingSlash = Left(strFile, Len(strFile) - 1)
Else
RemoveTrailingSlash = strFile
End If
End If
End Function
Sub ImportCSVfile(SQL As String, sPath As String, Destination As Range)
'---------------------------------------------------------------------------------------
' NOTE: Requires reference to ADO library:
' 1. Open the Visual Basic Editor (Alt + Fll)
' 2. Choose Tools | References
' 3. Select the checkbox for Microsoft ActiveX Data Object 2.5 Library (or higher)
'---------------------------------------------------------------------------------------
Dim RcdSet As ADODB.Recordset
Dim fldRS As ADODB.Field
Dim i As Integer
Set Contn = New ADODB.Connection
'for before Excel 2007 to open .xls file
' Contn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"";"
'for Excel 2007 or later to open .xls file
' Contn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & sPath & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"";"
'for Excel 2007 or later to open .xlsx file
' Contn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & sPath & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
'for all Excel versions to open .csv file
'get the folder path with trailing slash
sPath = Left(sPath, InStrRev(sPath, "\", , vbTextCompare))
Contn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & ";" & _
"Extended Properties=""Text;HDR=YES;FMT=Delimited"";"
'HDR=Yes indicates that the first row contains column names, not data
Set RcdSet = New ADODB.Recordset
'If you are using an SQL statement with an ADO recordset,
'make sure that the final parameter for the Open method is adCmdText (not adCmdTable)
RcdSet.Open SQL, Contn, CursorTypeEnum.adOpenForwardOnly, _
LockTypeEnum.adLockReadOnly, CommandTypeEnum.adCmdText
Destination.CopyFromRecordset RcdSet
RcdSet.Close
Set RcdSet = Nothing
Contn.Close
Set Contn = Nothing
End SubMặc dù mình đã cho ra kết quả giống hệt hàm NTILE với script SQL viết lại cho trường hợp chạy trên Oracle PL/SQL Developer, nhưng khi mình chạy script SQL viết lại cho trường hợp chạy trên Excel thì luôn bị báo lỗi. Mình đã thử rút gọn query SQL lại chỉ còn một đoạn SELECT IIf( ) đơn giản nhất như dưới đây nhưng vẫn gặp lỗi. Mọi người chạy thử lại sẽ thấy.
SQL:
SELECT
IIf( ((SELECT count(t2.appid)
FROM &Selected_Table t2
WHERE t2.sample = t1.sample) / 3) < 1
, 1, 0
) RangeList
t1.appid,
t1.sample,
t1.score
FROM
&Selected_Table t1Mình đã tốn rất nhiều thời gian cho việc này nhưng vẫn không có kết quả. Vì vậy mình rất biết ơn nếu mọi người có thể giúp mình được.
