Convert Ms Access Sql Staement To Oracle Sql Statement
Aug 31, 2005
Hi,
I currently have a ms access update query that runs
perfectly well and quicly in access however I now need to add this query
to convert this qeryu to oracles equivelant sql syntax and add it to the end
of an oracle sql script.
Unfortunately Im not having much success although i seem to be able to
convert it to a working oracle sql. it takes hours to run the statement in
oracle where as in access it runs in seconds
any help is appreciated.
Ms Access sql :
UPDATE (PRO_STY_TPRICES INNER JOIN PRO_STYLE_COLOURS ON PRO_STY_TPRICES.
STY_ID = PRO_STYLE_COLOURS.STY_ID) INNER JOIN PRO_TST_RV3X_RPT_WRK ON
(PRO_STYLE_COLOURS.SEASON = PRO_TST_RV3X_RPT_WRK.SEASON) AND
(PRO_STYLE_COLOURS.STY_NUM = PRO_TST_RV3X_RPT_WRK.STY_NUM) AND
(PRO_STYLE_COLOURS.STY_QUAL = PRO_TST_RV3X_RPT_WRK.STY_QUAL) AND
(PRO_STYLE_COLOURS.BF_MAT_CHAR_VAL = PRO_TST_RV3X_RPT_WRK.BF_MAT_CHAR_VAL)
SET PRO_TST_RV3X_RPT_WRK.MKD_DATE = pro_sty_tprices.new_active_date,
PRO_TST_RV3X_RPT_WRK.MKD_PRICE = pro_sty_tprices.new_tprice
WHERE (((PRO_STY_TPRICES.NEW_ACTIVE_DATE) Is Not Null));
Oracle SQL :
update pro.tst_rv3x_rpt_wrk x
set(x.mkd_date, x.mkd_price) =
(Select a.new_active_date, a.new_tprice
from pro.sty_tprices a, pro.style_colours b
where a.sty_id=b.sty_id
and b.bf_mat_char_val = x.bf_mat_char_val
and b.season = x.season
and b.sty_num = x.sty_num
and b.sty_qual = x.sty_qual
and a.new_active_date is not null
)
we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.
what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?
I have a delete statement that works on some servers/databases and not on others. I am running this on SQL Server 2000/Windows 2000. The statment is a very simple DELETE x FROM y WHERE x = 1234. It is only 1 record in one table. The staement begins but never completes. It looks as if it just keeps trying to recompile but goes nowhere. Have any of you ever seen this before? It works on a restore of the database on 1 server, but not on another. It will not run on either production server/database. Is there a parameter or setting that I need to look for that might be different and causing this? I am running the Enterprise edition of SQL Server on some servers, and standard edition on others. Would that make a difference? If so, how and why. I've never had this issue before. We are running Windows Advanced Server on most all of our servers.
Any help on this would be greatly appreciated. Thanks. Deanna
create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK. Browse to the Access database file and connection set up---all good!!!
Dataflow task Add an OLE DB Source component Double-click the icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager that I created . Select Table from the Data Access Mode drop-down list. I cannot see the tables set up as set up as pass-through table types to a Oracle 9i db
Hi, Is there a tool that anyone here is aware of which converts SQL, stored procedures and such stuff from Oracle compatibility to SQL Server compatibility? I have some Oracle based SQL that I wud like to convert into T-sql instead of re writing the whole T-SQL.. Thanks,
I have this update working in an Oracle database and I need to make it run on SQL Server.
UPDATE table3 C SET C.column1 = 'A' WHERE (C.column2, C.column3) IN (SELECT B.column2, B.column3 FROM table1 A, table2 B WHERE A.column2 = B.column2 AND A.column3 = B.column3 AND B.column4 = 1)
Hi, Can any one change this oracle proc. to SQL Server procedure.
Any help will be appreciated.
PROCEDURE CALC_PERC (DB_ID IN NUMBER, LAT_TYPE IN CHAR) IS Tot_work_all number(12,2); Bid_tot number(12,2); Ewo number(12,2); Overruns number(12,2); Underruns number(12,2); Contr_tot_all number(12,2); sContractType ae_contract.contr_type%type; BEGIN select sum(nvl(tamt_ret_item,0) + nvl(tamt_paid_item,0)) into Tot_work_all from valid_item Where db_contract = db_id; Select sum(Contq * Contr_Price) into Bid_tot From Valid_item Where nvl(New_Item,'N') <> 'Y' and db_contract = db_id; Select sum(Qtd * Contr_price) into Ewo From Valid_item Where nvl(New_item,'N') = 'Y' and db_contract = db_id; Select Sum((Qtd-Nvl(Projq,0))*Contr_Price) into Overruns From Valid_item Where Qtd > Nvl(Projq,0) and db_contract = db_id and nvl(New_Item,'N') = 'N'; IF LAT_type <> 'R' THEN Select Sum((Nvl(Projq,0)-Contq) * Contr_Price) into Underruns From Valid_item Where Nvl(Projq,0) < Contq and db_contract = db_id and nvl(New_Item,'N') = 'N'; ELSE Select Sum((Nvl(Qtd,0)-Contq) * Contr_Price) into Underruns From Valid_item Where Nvl(Qtd,0) < Contq and db_contract = db_id and nvl(New_Item,0) = 'N'; end if; Contr_tot_all:= NVL(Bid_tot,0) +NVL(ewo,0) +NVL(overruns,0) +NVL(underruns,0);
IF Contr_tot_all = 0 THEN
Select Contr_type into sContractType from ae_contract where db_contract = db_id;
IF sContractType = 'A' OR sContractType = 'T' THEN --If the divisor is zero here, it's not an error. update ae_contract set perc_compu = 0 where db_contract = db_id;
ELSE --If the divisor is zero here, it would be an error update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id; END IF; Else --Here we have a real number to calculate, so go ahead and do your stuff! update ae_contract set perc_compu = 100 * tot_work_all/contr_tot_all where db_contract = db_id; END IF; END;
i had worked on oracle 8i and i am planning to work on sql server 2000,i am requested by a company to help in converting there pl/sql code of oracle 8.0 to something equivalent which works on sql server 7.0 as they want to have similar code on both..i had not worked on sql server 7.0 ,but as pl/sql code works only on oracle stuff..so could kindly anyone guide me in this as to whether there is any product which coverts pl/code (the existing pl/code runs into thousands of line) automatically..i will be very grateful if anyone can enlighten me with such a product(software) or script.. along with its information and site address..any resources and any guidance as to how to go about about this conversion will be very invaluable..hope to hear soon from you guys...early response....will be appreciated..
I need to handle this conversion in SSIS and not on oracle.
The following expression is executed on a datatype of dt_str with a length of 8000.
SUBSTRING((DT_STR,8000,1252)Column_name,1,8000)
Records longer then 4000 bytes take an error path
The next expression with 4000 bytes works but there is truncation.
SUBSTRING((DT_STR,4000,1252)Column_name,1,4000)
Basically I need to know how to cast a text or ntext into a varchar or nvarchar using ssis but I need to capture the first 8000 byes without truncation.
is this possible?
Using SSIS Reading From oracle I can convert to a text or ntext field but I am having a hard time going directly to a varchar.
In SQL Server I've created a linked server to an Oracle database. I am trying to insert (within the context of an sql server table trigger) an SQL Server datetime to an Oracle column with similar precision. Oracle timestamps are not compatible with sql server datetimes and I don't know how to convert the data (or if I should use a different type of column to store the data in Oracle). I have full control over the structure of the Oracle table so I can use a different type if timestamp is not best, but I need the destination column to have at least the same precision as the sql server datetime value. What is the easiest way to do this?
Hi Guys, I am trying to manipulate an SQL statement based on the return from a menu with auto postback enabled.Simply I am working with dates, so I will declare variables for today, next week, last week and so on.How do I then use this in the SQL? I assumed that if I declared the following Dim my_today As String = Format(Date.Now(), "dd/MMM/yyyy")Then in the SQL I used SELECT * FROM my_table item_date = @my_todayI would get a result, but I get an error. What is the right way to do this? Many thanksSteve
SQL Server 2005 SP2 Windows XP Pro SP2 Oracle 10g Client 10.2 Oracle Data Access Components 10.2 VB6 Oracle Rdb 7.1 (not Oracle RDBMS).
I trying to access a remote Oracle Rdb database via a linked server in SQL Server 2005 management studio but having no luck. The remote database is accessed via an Oracle alias in TNSNAMES.ORA called my_oledb (see below) and I know it works ok becuase I'm able to access the remote database via VB6 (ADODB) using a very simple app whose connection string looks like this...
Maybe because I have worked mainly with VB as a front end to SQL Server so I am biased, but I now need definite reasons (I am on a committee for potential future directions) for using VB as opposed to Access for front ending SQL/Oracle, etc. I would also like to use ADO as oppose to DAO. Right now we are using Access with DAO.
The following is my code for Access... can someone help me convert it to sql: My Connectionstring is "server=(local);database=Database;trusted_connection=true"
<%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script language="VB" runat="server"> Sub btnLogin_OnClick(Src As Object, E As EventArgs) Dim myConnection As OleDbConnection Dim myCommand As OleDbCommand Dim intUserCount As Integer Dim strSQL As String strSQL = "SELECT COUNT(*) FROM tblLoginInfo " _ & "WHERE username='" & Replace(txtUsername.Text, "'", "''") & "' " _ & "AND password='" & Replace(txtPassword.Text, "'", "''") & "';" myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " _ & "Data Source=" & Server.MapPath("login.mdb") & ";") myCommand = New OleDbCommand(strSQL, myConnection) myConnection.Open() intUserCount = myCommand.ExecuteScalar() myConnection.Close() If intUserCount > 0 Then lblInvalid.Text = "" FormsAuthentication.SetAuthCookie(txtUsername.Text, True) Response.Redirect("login_db-protected.aspx") Else lblInvalid.Text = "Sorry... try again..." End If End Sub </script>
Dear All, I would like to convert from Access To SQL DB undervisual Studio.Net 2005... How can I do it easily, or if there any software to do this automaticlly, please your help.. Awaiting your valuable reply. Many thanks in advance for your cooperation and continuous support....
I am currently in the process of writing an application to convert an Access database to SQL. Basically, I have created an odbc link in access and then I transfer the data from the access table to the linked table.
The problem is where I have to transfer the Identity keys. I can transfer all the data but the keys. They will auto increment. I tried using INSERT_IDENTITY tablename ON but it just returns an error mosty of the time. I have gotten it to transfer once or twice by stepping through.
Here is the basic code:
The code that causes everything to error is commented out.
Private Sub CopySQLRecordSet(ByVal stTableName As String, _ Optional ByVal blIdentity As Boolean = True, _ Optional ByVal stSearch As String = "") Dim cat As ADOX.Catalog Dim tbl As ADOX.Table Set cat = New ADOX.Catalog Set tbl = New ADOX.Table Dim adoCommand As New ADODB.Command, stCommand As String Dim adoSQLCommand As New ADODB.Command Dim errorString As String
On Error GoTo ErrorCopying PrgPart.Value = PrgPart.Value + 1 If blCancelPressed Then End '???fix later cat.ActiveConnection = db1 'This doesn't seem to work with our normal settings for spectrumDbase tbl.ParentCatalog = cat tbl.Name = "dbo_" & stTableName tbl.Properties("Temporary Table") = False 'possibly, this line will work for Oracle as well tbl.Properties("Jet OLEDB:Link Provider String") = "odbc;DSN= ;DATABASE=database;" tbl.Properties("Jet OLEDB:Remote Table Name") = stTableName tbl.Properties("Jet OLEDB:Create Link") = True tbl.Properties("Jet OLEDB:Table Hidden In Access") = False tbl.Properties("Jet OLEDB:Cache Link Name/Password") = False cat.Tables.Append tbl Suspend 1 adoCommand.CommandType = adCmdText adoCommand.ActiveConnection = database adoSQLCommand.CommandType = adCmdText adoSQLCommand.ActiveConnection = rsDbase
'spectrumDBase.BeginTrans 'stCommand = "BEGIN TRANSACTION " & vbNewLine _ ' & "go" & vbNewLine 'If there is an identity field in the table, it must be temporarily disabled 'to insert from an foreign DB. 'If blIdentity Then ' stCommand = stCommand & "SET IDENTITY_INSERT dbo_" & stTableName & " ON " & vbNewLine & "GO" & vbNewLine 'adoCommand.CommandText = stCommand 'adoCommand.Execute 'End If
'insert the records from the source table stCommand = "" stCommand = stCommand & "INSERT INTO dbo_" & stTableName _ & " SELECT * FROM " & stTableName & vbNewLine _ & "GO" & vbNewLine
Set adoCommand = Nothing Set adoSQLCommand = Nothing Exit Sub ErrorCopying: 'need to save this to a string so it doesn't reset when Resume occurs errorString = Err.Description ' MsgBox "Error copying the [" & stTableName & "] table." & vbNewLine _ ' & "Error: " & errorstring Resume errorCatch errorCatch: On Error Resume Next 'set a log Dim fso As FileSystemObject, fStream As TextStream Set fso = New FileSystemObject Set fStream = fso.OpenTextFile(App.Path & "DBTransfer.log", ForAppending, True) fStream.WriteLine "***Error copying the [" & stTableName & "] table." fStream.WriteLine " Error: " & errorString fStream.WriteLine " " fStream.Close Set fStream = Nothing Set fso = Nothing 'clean up adoCommand.CommandText = "DROP TABLE dbo_" & stTableName adoCommand.Execute If blIdentity Then 'reinsert identity property adoSQLCommand.CommandText = "SET IDENTITY_INSERT " & stTableName & " OFF" adoSQLCommand.Execute End If Set adoCommand = Nothing Set adoSQLCommand = Nothing End Sub
Who convert access dba to ms sql 2000 standard. I try with access conversion, but some buttons commands and rules donīt work. The dba contains tabs, forms, relīs, macros, modules and relations with tabs and examination images. Itīs possible convert everything without damage or transform this itens? The dba contains critical information about identification and personal clinic story about patients, but the access capacity itīs out (2GB) and i need to expand this dba.
I have some urgency to this problem, itīs depends to buy a windows 2008 server and mssql server 2008.
The data I have has a 1 in for each Yes answer and a 2 for each no answer. I just want the select statement to show the word yes when there's a 1 and the word no when there's a 2. I don't need to update or change the database. Could anyone lead me in the right direction here? Thanks
Can you guys help me out? I m trying to sum up some varchar-typed field. I need to convert it to float before doing the summing up so I m using "Cast".
I do get the answer but its not the correct figure. My SQL statement is as follow:
SELECT Sum((Cast(Qty1 as float)) + (Cast(Qty2 as float))) as intAnswer FROM TableName
How to convert an INT value from the registry to a VARCHAR but not sure how to convert from INT to VARCHAR within a CASE statement. Below is my code which current fails on "Conversion failed when converting the varchar value 'Automatic' to data type int".
I am trying to write an update t-SQL statement from the following select statement:
SELECT EduNextContacts.ssn FROM EduNextContacts Left JOIN EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code GROUP BY EduNextContacts.ssn HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) + SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2)
I tried many versions of writing my update statement with no luck. My most recent version is as follows:
UPDATE EduNextContacts
SET EduNextContacts.overLMLimit = 'Y'
FROM EduNextContacts Left JOIN
EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND
HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) +
SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2))
And gives the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Can anyone help shed some light on how to make an update statement from my SELECT query above?
