Nov 30, 2007
I have a query in SSIS which works fine.
SELECT SUBSTR(DOB_CHAR, 7, 2) AS EXP3, SUBSTR(DOB_CHAR, 5, 2) AS EXPR1, SUBSTR(DOB_CHAR, 1, 4) AS EXPR2
FROM WAITLIST
results
20
12
1948
07
09
1982
29
07
1960
However, when I try to concatinate, it falls over
SELECT SUBSTR(DOB_CHAR, 7, 2) + '/' + SUBSTR(DOB_CHAR, 5, 2) + '/' + SUBSTR(DOB_CHAR, 1, 4) AS EXPR2
FROM WAITLIST
The syntax works ok on SQL Server query, but not in SSIS.
(Reading from Oracle, number 19481220, an is varchar2)
Select will work with pipes (as in oracle) but it must be of the form | | '/' | |
that is
SUBSTR(DOB_CHAR, 7, 2) | | '/' | | SUBSTR(DOB_CHAR, 5, 2) | | '/' | | SUBSTR(DOB_CHAR, 1, 4) AS EXP2
I would like to know if this is correct, or is it because I am reading from Oracle?
I do get an error message in query builder, but the query then runs as required.
Error in list of function arguments: '|' not recognized.
Error in list of function arguments: ',' not recognized.
Error in list of function arguments: ')' not recognized.
Unable to parse query text.
Any views?
View 4 Replies
View Related
Mar 22, 2007
I have a need to query some data and string all my results by id. I am fairly close to the results but stuck on the final piece. Any help would be greatly appreciated.
Here's the scenario: My data looks as follows:
UserID
Results
1095
,,,,,,,
1095
,,,,,8,,
1095
,,,,,,,
1095
,,,,,8,,
1247
,2,3,,,6,,,
1247
,2,3,,,6,,,
1247
,2,3,,,6,,,
1247
,2,3,,,6,,,
4069
,,,,,,,
4069
,,,,,,,
4069
,,,,,,,
4069
,,,,,,,
4070
,,6,,,,,
4070
,,6,,,,,
4070
,,6,,,,,
4070
,,6,,,,,
I want to query it and end up with the results all strung together under each UserID as follows:
1095
,,,,,,,,,,,,8,,,,,,,,,,,,,,8,,
1247
,2,3,,,6,,,,2,3,,,6,,,,2,3,,,6,,,,2,3,,,6,,,
4069
,,,,,,,,,,,,,,,,,,,,,,,,,,,,
4070
,,6,,,,,,,6,,,,,,,6,,,,,,,6,,,,,
I know if I use the following code I can string all the results together
select *, Cast((SELECT Results + ',' FROM #temp1 FOR XML PATH('')) as varchar(max) ) as Results from #temp1
But I can't figure out how to break it down by individual UserID. Any help is greatly appreciated. Thanks in advance
View 11 Replies
View Related
Feb 25, 2007
Hi Guys
i write a thread before asking for help with reading an uploaded csv file,
i have my code, it reads the csv file and currently displays it in a datagrid but what i actually want is to take the read information and import it into my sql express db.
Heres the code<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
If (IsPostBack) Then
Grid1.Visible = True
Else
Grid1.Visible = False
End If
End Sub
Sub UploadButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
'Save the uploaded file to an "Uploads" directory
' that already exists in the file system of the
' currently executing ASP.NET application.
' Creating an "Uploads" directory isolates uploaded
' files in a separate directory. This helps prevent
' users from overwriting existing application files by
' uploading files with names like "Web.config".
Dim saveDir As String = "Data"
' Get the physical file system path for the currently
' executing application.
Dim appPath As String = Request.PhysicalApplicationPath
' Before attempting to save the file, verify
' that the FileUpload control contains a file.
If (FileUpload1.HasFile) Then
Dim savePath As String = appPath + saveDir + FileUpload1.FileName
' Call the SaveAs method to save the
' uploaded file to the specified path.
' This example does not perform all
' the necessary error checking.
' If a file with the same name
' already exists in the specified path,
' the uploaded file overwrites it.
FileUpload1.SaveAs(savePath)
' Notify the user that the file was uploaded successfully.
UploadStatusLabel.Text = "Your file was uploaded successfully."
Else
' Notify the user that a file was not uploaded.
UploadStatusLabel.Text = "You did not specify a file to upload."
End If
End Sub
Sub DisplayButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:inetpubwwwrootMerlinLocalPostOfficeAppData;Extended Properties=""text;HDR=NO;FMT=Delimited"""
Dim objConn As New OleDbConnection(sConnectionString)
objConn.Open()
Dim objCmdSelect As New OleDbCommand("SELECT * FROM csv.txt", objConn)
Dim objAdapter1 As New OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect
Dim objDataset1 As New DataSet()
objAdapter1.Fill(objDataset1, "csv.txt")
Grid1.DataSource = objDataset1.Tables(0).DefaultView
Grid1.DataBind()
objConn.Close()
End Sub
</script>
My csv file does not have headers so the default value is F1, F2, F3 F4, F5,F6, F7, my database has the following columns, ID,AddressLine1,AddressLine2,AddressLine3,AddressLine4,AddressLine5,AddressLine6,Postcode
I need to know how to import the information direct into the db rather than displaying it on the page, ive tried but im really new to this and cant get it to work. I cant use DTS or bulk insert as the server this will go on doesnt have sql on it, the db is an MDF file so is transportable with the app.
Thanks for your help
View 3 Replies
View Related
