|
All,
I have been reading and trying diffrent ways to get this to work but the gist is I am trying to create a temp table that is slecting cloumns from multiple tables
I cant see how a join would work and I have tried pass the columns from table2 as a varible..no luck
The goal is to create one big table I can export off to a file
I am using SQL 2008 Ent.
Here is what I have:
BEGIN
If NOT Exists(Select * from tempdb..sysobjects Where id = object_id('tempdb.dbo.#temp'))
SELECT firstname,lastname, INTO #TEMP FROM Table1
Select email, username into #temp from Table2
END
Regards,
Hulicat
|
|
|
|
|
Do you get any error message? Can you explain better your problem?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Thanks I think I got it...I was trying to get one result set from multiple tables
I needed to use a cross join....
Regards
Regards,
Hulicat
|
|
|
|
|
;with cte as(select t1.*, t2.* from table1 t1 cross join table2 t2)
select * from cte
OR
;with cte as(select t1.*, t2.* from table1 t1 ,table2 t2)
select * from cte
Niladri Biswas
|
|
|
|
|
Ok, if anyone knows anything about the subject please help.
Here's the setup: I have written a dll using vs 2005 that reads data from an excel file. It works fine when I reference the dll in my windows app...but when I create the assembly/function combo in SQL I get an OleDB Unspecified error. The weird thing is that it was working on Monday, then when I came back in on Tuesday the server gave me weird errors and now the conn.Open throws an exception I cannot figure it out: (Thanks in advance for any help!)
Here is the error when I try to call the Function in SQL
Declare @a int
set @a = (Select dbo.ExcelTester())
A .NET Framework error occurred during execution of user-defined routine or aggregate "ExcelTester":
System.Data.OleDb.OleDbException: Unspecified error
System.Data.OleDb.OleDbException:
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at ChargeCardCom.ChargeCardCom.ExcelTester()
Here are the errors that happened on the server Tuesday:
AppDomain 122 (KWCDB_WBU.dbo[runtime].121) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations.
and
AppDomain 130 (KWCDB_WBU.dbo[runtime].129) is marked for unload due to memory pressure.
and
AppDomain KWCDB_WBU.dbo[ddl].131 was unloaded by escalation policy to ensure the consistency of your application. Out of memory happened while accessing a critical resource.
|
|
|
|
|
|
Thank you for your reply Abhishek...I will post some code shortly (I have to log in to work as I'm at home now). Basically the ExcelTester (at this point) just creates an OleDB Connection to an excel file and attempts to open it. This is not my first CLR function integration project and I had the program working almost perfectly by Monday afternoon. Then I came in on Tuesday and started cleaning up some comments, re built the project and once again attempted to create the assemblies. It was at this time I began receiving the errors listed in my post and basically I spent all week trying to make it work again. It would probably be much easier to figure out if OleDB was not returning Unspecified Error when I catch the exception. The whole thing is weird and I'm sure it is something simple that I am just overlooking.
Basically, the function just returns a string "Success" if it works and "Failure" if it doesn't (or an Integer 0 if it works and -1 if not..I've tried many variations). It is breaking on the conn.Open and I feel pretty certain the connection string is okay. If I remove the .Open call the function works perfectly and I'm able to create the assemblies and function and call it with no problem.
|
|
|
|
|
Abhishek,
Here is the class I am using (along with my AssemblyInfo file). The only section at this point I am worrying about is the ExcelTester...I had the chgCardRead working by calling it in SQL on Monday, but again, something has gone wrong and I'm at a loss. Also, I am creating the assemblies as Unsafe already. Like I said before, it worked just fine and then just stopped and I have no idea why. Thank you for your assistance.
Option Strict Off
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Reflection
Imports System.Data
Imports System.IO
Imports System.Runtime.InteropServices
Imports System
Imports Microsoft.Win32
Imports System.Data.OleDb.OleDbPermission
<ComClass(ChargeCardCom.ClassId, ChargeCardCom.InterfaceId, ChargeCardCom.EventsId)> _
Public Class ChargeCardCom
#Region "COM GUIDs"
' These GUIDs provide the COM identity for this class
' and its COM interfaces. If you change them, existing
' clients will no longer be able to access the class.
Public Const ClassId As String = "2DF8ED1A-55D0-4EC1-AF8E-1611FF6C631E"
Public Const InterfaceId As String = "14CA7221-FBFD-4D6C-92B3-E60EAD6B48D7"
Public Const EventsId As String = "F994EEB1-4001-444C-BF01-09EA090B7CA6"
'Public Const ClassId As String = "d05374f7-f2e0-4c82-badc-2f5f93c6f1b1"
'Public Const InterfaceId As String = "b4d276c1-6548-4d70-9c05-e9e1e034c55a"
'Public Const EventsId As String = "0d3e3508-c31e-45df-b41a-b4bd0997fcb4"
#End Region
' A creatable COM class must have a Public Sub New()
' with no parameters, otherwise, the class will not be
' registered in the COM registry and cannot be created
' via CreateObject.
Public Sub New()
MyBase.New()
End Sub
Public Shared Function ExcelTester() As String
Log("in tester")
Dim xlconn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\10098886-1509.xls;Extended Properties=""Excel 8.0;HDR=No;IMEX=1""")
Dim xlda As New OleDbDataAdapter("select * from [Small Batch$]", xlconn)
Dim xldt As New DataTable
Log("about to open")
xlda.Fill(xldt)
Log("opened")
xlda.Dispose()
xlconn.Close()
End Function
'<Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillCardReadRows")> _
'Public Shared Function GetChargeData_InitMethod(ByVal Solution As String, ByVal Batch As String, _
' ByVal Charge As String, ByVal sqlinstance As String, ByVal sqldatabase As String) As IEnumerable
' 'Public Shared Function InitMethod(ByVal logname As String) As IEnumerable
' 'Dim err3() As String = {"-1~InInitMethod~0~0~0"}
' 'Return New ArrayList(err3)
' Try
' 'Return New EventLog(logname, Environment.MachineName).Entries
' WriteToLogFile("In GetChargeData_InitMethod ")
' 'Dim err3() As String = {"-1~1WroteToFile~0~0~0"}
' 'Return New ArrayList(err3)
' 'If sqlinstance.Length = 0 Then
' ' Dim err1() As String = {"-1~SQLServerNotFound~0~0~0"}
' ' Return New ArrayList(err1)
' ' Exit Function
' 'End If
' 'If sqldatabase.Length = 0 Then
' ' Dim err2() As String = {"-1~DataBaseNotFound~0~0~0"}
' ' Return New ArrayList(err2)
' ' Exit Function
' 'End If
' 'strSQLServer = sqlinstance 'regKeySubFolder.GetValue("data source").ToString()
' 'strSQLServerDatabase = sqldatabase 'regKeySubFolder.GetValue("initial catalog").ToString()
' 'Dim err3() As String = {"-1~2SetDBInfo~0~0~0"}
' 'Return New ArrayList(err3)
' 'calling this here so that any future errors wille be logged
' 'modErrorLog.SetConnectionParameters(sqldatabase, sqlinstance)
' Dim arr1() As String = chgCardRead(Solution, Batch, Charge, sqlinstance, sqldatabase)
' WriteToLogFile("After chgCardRead call from GetChargeData_InitMethod, there are " & CStr(arr1.Length) & " arrays lines returned")
' 'WriteToLogFile("arr1 in InitMethod " & arr1(0).ToString)
' Return New ArrayList(arr1)
' Catch ex As Exception
' WriteToLogFile("In InitMethod Error Handler ")
' 'modErrorLog.SetConnectionParameters(sqldatabase, sqlinstance)
' modErrorLog.LogErrorToSQL(ex.Message, "GetChargeData_InitMethod")
' Dim arr1() As String = {"-1~ExcInInitMethod~0~0~0"}
' Return New ArrayList(arr1)
' End Try
'End Function
''Public Function chgCardRead(ByVal Solution As String, ByVal Batch As String, ByVal Charge As String) As DataSet
'Public Shared Function chgCardRead(ByVal Solution As String, ByVal Batch As String, ByVal Charge As String, _
' ByVal sqlinstance As String, ByVal sqldatabase As String) As String()
' 'Need to assign so there is no null reference
' 'Dim ResultsArray() As String = {"-1~" & "strSearchMaterial" & "~" & "strSearchBatch" & "~~0~0~0"}
' Dim ResultsArray() As String = {"-1~ChargeNotFound~0~0~0"}
' 'Dim Charge1 As Integer = Convert.ToInt32(Charge)
' 'Dim Charge9 As Integer = (Convert.ToInt32(Charge) + 9).ToString()
' 'Dim intChgRow As Integer = 0
' 'Dim intChgCol As Integer = 0
' 'Dim myDataset As New DataSet()
' Dim fileCheck As String = ""
' 'Dim ChargeNumbers(9) As String
' 'Dim retVal As Double = Charge Mod 10
' 'If retVal <> 0 Then
' ' Dim SCharge As String = Charge.Substring(0, Charge.Length - 1)
' ' Charge = SCharge & "0"
' 'End If
' 'For i As Integer = Convert.ToInt32(Charge.Substring(Charge.Length - 1, 1)) To 9
' ' ChargeNumbers(i) = (Convert.ToInt32(Charge) + i).ToString()
' 'Next
' Try
' If sqlinstance.Length = 0 Then
' Dim err1() As String = {"-1~SQLServerNotFound~0~0~0"}
' 'Return New ArrayList(err1)
' Return err1
' 'Exit Function
' End If
' If sqldatabase.Length = 0 Then
' Dim err2() As String = {"-1~DataBaseNotFound~0~0~0"}
' Return err2
' 'Exit Function
' End If
' strSQLServer = sqlinstance 'regKeySubFolder.GetValue("data source").ToString()
' strSQLServerDatabase = sqldatabase 'regKeySubFolder.GetValue("initial catalog").ToString()
' If Not InitializeApplicationSettings() Then
' 'insert interface error
' ResultsArray(0) = "-1~ErrSettingSettings~0~0~0"
' Return ResultsArray
' End If
' strNVPPath = strNVPPath & Solution & "-" & Batch & ".xls"
' fileCheck = Dir(strNVPPath)
' If fileCheck = "" Then
' 'myDataset = Nothing
' 'Return myDataset
' 'insert interface error
' ResultsArray(0) = "-1~FileDNE~0~0~0"
' Return ResultsArray
' 'Exit Function
' End If
' 'strNVPPath = strNVPPath & Solution & "-" & Batch & ".xls"
' Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source=" & strNVPPath & ";" & _
' "Extended Properties=""Excel 8.0;HDR=Yes; IMEX=1"""
' 'Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
' ' strNVPPath & ";Extended Properties=""Excel 8.0;HDR=No;"""
' Dim conn1 As New System.Data.OleDb.OleDbConnection(strConn)
' conn1.Open()
' Dim cmd1 As New System.Data.OleDb.OleDbCommand("SELECT * FROM [" & _
' strNVPSpreadSheet & "$] WHERE " & strChargeColumn & " BETWEEN '" & _
' Charge & "' AND '" & CStr(CInt(Charge) + 9) & "'", conn1)
' Dim rdr As OleDbDataReader = cmd1.ExecuteReader
' If Not rdr.HasRows Then
' 'return error with mt data array
' Return ResultsArray
' End If
' Dim intRowCounter As Integer = 0
' Dim strReturnValues
' Do While rdr.Read()
' strReturnValues = ""
' For i As Integer = 0 To rdr.FieldCount - 1
' If i = strMaterialIDCol Or i = strRMNameCol Or i = strQtyCol Or i = strUOMCol Or i = strToleranceCol Then
' Console.Write(rdr.Item(i) & "~")
' strReturnValues &= rdr.Item(i) & "~"
' End If
' Next
' Console.WriteLine(vbCrLf)
' ReDim Preserve ResultsArray(intRowCounter)
' ResultsArray(intRowCounter) = strReturnValues
' intRowCounter += 1
' Loop
' 'If intRowCounter = 0 Then
' ' 'return error
' 'End If
' rdr.Close()
' conn1.Close()
' 'Dim a() As String = ResultsArray.Split("~")
' Return ResultsArray
' Catch ex As Exception
' LogErrorToSQL(ex.Message, "chgCardRead")
' Dim ErrorArray() As String = {"-1~ErrorInChgCardRead~0~0~0"}
' Return ErrorArray
' End Try
'End Function
'Public Shared Sub FillCardReadRows(ByVal obj As Object, <Out()> ByRef MaterialID As SqlString, <Out()> ByRef RMName As SqlString, <Out()> ByRef Quantity As SqlString, <Out()> ByRef UOM As SqlString, <Out()> ByRef Tolerance As SqlString)
' 'Public Shared Sub FillRow(ByVal obj As Object, <Out()> ByRef timeWritten As SqlDateTime, <Out()> ByRef message As SqlChars, <Out()> ByRef category As SqlChars, <Out()> ByRef instanceId As Long)
' Try
' WriteToLogFile("In FillCardReadRows")
' Dim array As String = CType(obj, String)
' WriteToLogFile(array.ToString)
' Dim a() As String = array.Split("~")
' MaterialID = New SqlString(a(0))
' RMName = New SqlString(a(1))
' Quantity = New SqlString(a(2))
' UOM = New SqlString(a(3))
' Tolerance = New SqlString(a(4))
' 'qualityinspect = New SqlString(a(5))
' 'blocked = New SqlString(a(6))
' 'Dim eventLogEnTry As EventLogEntry = CType(obj, EventLogEntry)
' 'timeWritten = New SqlDateTime(eventLogEnTry.TimeWritten)
' 'message = New SqlChars(eventLogEnTry.Message)
' 'category = New SqlChars(eventLogEnTry.Category)
' 'instanceId = eventLogEnTry.InstanceId
' Catch ex As Exception
' modErrorLog.LogErrorToSQL(ex.Message, "FillCardReadRows")
' WriteToLogFile("In FillCardReadRows Error Handler ")
' Dim array As String = CType(obj, String)
' Dim a() As String = array.Split("~")
' MaterialID = New SqlString(a(0))
' RMName = "ErrFillCardReadRows"
' Quantity = New SqlString(a(2))
' UOM = New SqlString(a(3))
' Tolerance = New SqlString(a(4))
' End Try
'End Sub
Public Shared Sub Log(ByVal msg As String)
Dim SW As StreamWriter = File.AppendText("C:\jeffcccom.txt")
SW.WriteLine(msg & " : " & Now())
SW.Flush()
SW.Close()
End Sub
End Class
Imports System
Imports System.Reflection
Imports System.Runtime.InteropServices
' General Information about an assembly is controlled through the following
' set of attributes. Change these attribute values to modify the information
' associated with an assembly.
' Review the values of the assembly attributes
<Assembly: AssemblyTitle("ChargeCardCom")>
<Assembly: AssemblyDescription("")>
<Assembly: AssemblyCompany("")>
<Assembly: AssemblyProduct("ChargeCardCom")>
<Assembly: AssemblyCopyright("")>
<Assembly: AssemblyTrademark("")>
<Assembly: ComVisible(True)>
'The following GUID is for the ID of the typelib if this project is exposed to COM
<Assembly: Guid("8068fffd-907a-44ec-b51c-a9dc0e7b3ecf")>
'<Assembly: Guid("1306f111-0f59-4a60-a826-78034ab8651d")>
' Version information for an assembly consists of the following four values:
'
' Major Version
' Minor Version
' Build Number
' Revision
'
' You can specify all the values or you can default the Build and Revision Numbers
' by using the '*' as shown below:
' <Assembly: AssemblyVersion("1.0.*")>
<Assembly: AssemblyVersion("1.0.0.0")>
<Assembly: AssemblyFileVersion("1.0.0.0")>
|
|
|
|
|
I have been guided to put my question to this forum.
I am using vb express and MsAccess as database.
I am trying to write a query for the calculation of Stock.
My tables are as under:
PId PDate ItemId Description Price Quantity Amount
1 28/8/2009 1 Coca Cola Normal 1,00 10 10,00
2 28/8/2009 2 Coca Cola Zero 1,00 5 5,00
3 29/8/2009 1 Coca Cola Normal 1,00 5 5,00
4 29/8/2009 2 Coca Cola Zero 1,00 10 10,00
Data in Sales Table is as under:
SId SDate ItemId Description Price Quantity Amount
1 30/8/2009 1 Coca Cola Normal 2,70 2 5,40
2 30/8/2009 2 Coca Cola Zero 2,70 3 7,10
3 31/8/2009 1 Coca Cola Normal 2,70 1 2,70
4 31/8/2009 2 Coca Cola Zero 2,70 2 2,70
I tried something like this to have balance of stock at any date or interval between two dates
Dim cmdText As String = "SELECT pt.ItemId, pt.Description, SUM(pt.Quantity)AS QuantityPurchased, SUM(st.Quantity) AS QuantitySold, (SUM(pt.Quantity) - SUM(st.Quantity)) AS Balance FROM PurchaseTable pt INNER JOIN SalesTable st ON pt.ItemId=st.ItemId WHERE pt.PDate Between @START and @END GROUP BY pt.ItemId, pt.Description"
If con.State = ConnectionState.Closed Then con.Open()
Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(cmdText, con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@START", OleDb.OleDbType.Date).Value = TextBox1.Text
cmd.Parameters.AddWithValue("@END", OleDb.OleDbType.Date).Value = TextBox2.Text
Dim dr As OleDb.OleDbDataReader
If con.State = ConnectionState.Closed Then con.Open()
dr = cmd.ExecuteReader
If Not dr.HasRows Then
MessageBox.Show("No Records Found for Date: " & TextBox1.Text)
Else
MessageBox.Show("Record found for Date: " & TextBox1.Text)
ListView1.Items.Clear()
ListView1.ForeColor = Color.DarkRed
ListView1.GridLines = True
While dr.Read
Dim ls As New ListViewItem(dr.Item("ItemId").ToString())
ls.SubItems.Add(dr.Item("Description").ToString())
ls.SubItems.Add(dr.Item("QuantityPurchased").ToStr ing())
ls.SubItems.Add(dr.Item("QuantitySold").ToString() )
ls.SubItems.Add(dr.Item("Balance").ToString())
ListView1.Items.Add(ls)
End While
End If
But i could not get the desired results. Please advise what i am doing wrong with this.
Thanks
|
|
|
|
|
The first thing that I notice from your query is that you are using an inner join. This will only return information for items that have an entry in both the Purchase table and the Sales table. If you have any items that are only in one table (e.g a Purchase but no Sale for the same item), they will not show up in your results.
The second thing I notice is that you are selecting on the basis of the date in the Purchase table, but you are ignoring the date in the Sales table. So, you will pick up a subset of the Purchase data but all of the Sales data, which seems a bit wrong to me.
When you say that you are not getting the desired results, what exactly is going wrong? That would help us to narrow down the problem.
|
|
|
|
|
The problem i am having is as under:
Data in PurchaseTable
PId PDate ItemId Description Price Quantity Amount
1 28/8/2009 1 Coca Cola Normal 1,00 10 10,00
2 28/8/2009 2 Coca Cola Zero 1,00 5 5,00
3 29/8/2009 1 Coca Cola Normal 1,00 5 5,00
4 29/8/2009 2 Coca Cola Zero 1,00 10 10,00
Data in Sales Table is as under:
SId SDate ItemId Description Price Quantity Amount
1 30/8/2009 1 Coca Cola Normal 2,70 2 5,40
2 30/8/2009 2 Coca Cola Zero 2,70 3 7,10
3 31/8/2009 1 Coca Cola Normal 2,70 1 2,70
4 31/8/2009 2 Coca Cola Zero 2,70 2 2,70
The result of query with date range of 28-08-2009 to 29-08-2009 and also with the date range of 28-08-2009 to 31-08-2009 is displayed as under:
Coca Cola Normal 30 6 24
Coca Cola Zero 30 10 20
whereas actually for date range of 28-08-2009 to 29-08-2009 the result should be as under:
Coca Cola Normal 15 0 15
Coca Cola Zero 15 0 15
And with the date range of 28-08-2009 to 31-08-2009 the result should be as under:
Coca Cola Normal 15 3 12
Coca Cola Zero 15 5 10
Pleae advise what i am doing wrong with the query.
Thanks again for your guidance.
|
|
|
|
|
I have already told you what the problem is. Re-read my previous post. You are not including the date on the Sales table in your query.
|
|
|
|
|
I saw that this class (easy_odbc) have serious memory leak. I try to insert one record every second, it makes the increase of memory without limit(every insert deals memory about 500kb). It seems that no frees statament memory, how can i resolve this problem?
At moment i close and open continuously the database, but in my opinion this isn't best solution.
thank you
Marco
|
|
|
|
|
I seem to have left my magic all seeing eyes at home, so I can't scan your code for the problem -or to put it another way, if you want help you need to let us see whats happening.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I'm apologize...
Easy_ODBC class : EasyODBC[^]
this is my code(m_pDbM is valid pointer to open database):
m_pDbM.Close();
m_pDbM.Open("nChart","","");
CString query;
query.Format("INSERT INTO %s (%s... (continue)");
m_pDbM.Execute(query);
return true;
|
|
|
|
|
Ok, I didn't realise it was based on a CP article. Your best bet is to leave a message for the author (at the end of the article) as they should have an idea about the problem.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I just want to double check if this script is on the right track.
select t.name, count(c.column_id) as columns
from sys.tables as t
inner join sys.columns as c on t.object_id = c.object_id
where schema_id = (select schema_id from sys.schemas where sys.schemas.name='EMR') -- Alter Schema name
group by t.name
order by [name] asc
Basically I want to check for altered tables between two different databases, this ought to pull out all the tables in a schema and the number of columns for each table. Obviously, any changes on the table that don't affect the number of columns, I will not be alerted of.
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
I prefer to use the information schema view - 'cause I can.
SELECT Table_Name, COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'STG'
GROUP BY Table_Name
|
|
|
|
|
See, this is why I love this site 
I like your script better and will steal it!
Now , to figure out how to realize that there are changes!
If the post was helpful, please vote, eh!
Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ
Now and forever, defiant to the end.
What is Multiple Sclerosis[ ^]?
|
|
|
|
|
hi guys
i want to upate sub string of one of my column
update tale1 set substring(orderno,6,2) = 'tt'
but above query is not working .
Tauseef A Khan
MCP Dotnet framework 2.0.
|
|
|
|
|
Do you get error message?
Explain more your question.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
1) Are you using transactions ? Make sure you have a "Commit" after your update statement.
2) Do you realize that in your example there is no "where" clause ? Every row in the table would be affected.
I believe the root of your problem is that you can't have a function on the left side of the assignment operator in the update statement.
The following code seems to work for me ...
select first_name from fsuser where user_code = 'DMUJICA'
begin transaction
update fsuser set first_name = substring(first_name,1,1) + 'XXX' +
substring(first_name,5,len(first_name)-4) where user_code = 'DMUJICA'
select first_name from fsuser where user_code = 'DMUJICA'
--commit
rollback
The output is:
DAVID
DXXXD
|
|
|
|
|
This is weird...  
You are setting a constant term with another constant..
If you write
Set substring(orderno, 6,2) = 'tt'
It means if order no is 12345678 then you write
78 = tt... 
You can only apply use functions on the left hand side when you are using a comparison...
In your case the query should be
update tale1 set orderno = substring(orderno,1,6) + tt + substring(orderno,8, len(orderno) - 8)
Remember, you are missing with where clause. So it will update every order no...
|
|
|
|
|
Hi, I really cannot understand your requirement.
What you are trying to achieve, is not at all clear in the statement presented here.
I fully agree with Abhishek.
However, if you invoke the statement "update tale1 set substring(orderno,6,2) = 'tt'" you will get the following error
"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','."
Now, I have a table say tblOrder with the following records.
OrderNo
------------
123456789
234567891
345678912
45678123
67
78
89
My goal is to update those orderno's that will satisfy the condition substring(orderno,6,2).
Means, if the order no is 987623456 , then substring('987623456',6,2) will yield me 34. So if any OrderNo 34 is present, that will be updated.
The query is
update tblOrder set OrderNo = 'tt'
where OrderNo in( select substring(OrderNo,6,2) from tblOrder)
The output is
OrderNo
------------
123456789
234567891
345678912
45678123
tt
tt
tt
Hope this helps
Niladri Biswas
|
|
|
|
|
hello there
i want to make a project about central blood bank which can connect to any other small blood bank in other hospitals and take information about available blood units thers and then any bank could connect to central blood bank and make request of the blood it need then the central bank will search in its database if it availabe in it it will reply if not it will connect to others hospitals and ask them about the request then when it find what ht need he will reply immediatle to the bank who request the blood and inform them of what he find. else we should care about doners and donation processes , test , etc.
i want to implement the project with asp.net and mysql for database
help me if any one know how to make it and please i want to get the database or ER-Diagram as soon as possible
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
