Databases :: Oracle Query Convert To Sql Server Query
Sep 3, 2010
I am a biggner in SQL DB . but i started a complicated and painfull work in SQL SERVER 2008. the problem convert Oracle hierarchical query to SQL query. the query
SELECT DISTINCT
LEVEL LVL,
SCH.NSCHEDULE_SL,
SCH.NSCHEDULE_SL_FM,
SCH.CSHED_CNAME
FROM FA_SCHEDULES SCH
WHERE LEVEL = 1
AND NSCHEDULE_SL_FM IS NULL
AND NBRANCH_SL = 2
CONNECT BY PRIOR SCH.NSCHEDULE_SL_FM = SCH.NSCHEDULE_SL
AND NBRANCH_SL = 2
pic #2 is the involved tables and data that we already tried, it works, but only through sql, how can we make the formula where datagrid will show the results.
i have a salary table that have data like this ID EmpNo mYear Net Salary 29 3 January 2010 29217 1210 3 February 2010 29217 2052 3 March 2010 29217 3102 3 April 2010 29447 4153 3 May 2010 29447 6376 3 July 2010 28803 7231 3 August 2010 28281 7893 3 September 2010 29303 5207 3 June 2010 29197 8874 3 October 2010 29303 10076 3 November 2010 31276 11318 3 December 2010 31216 12503 3 January 2011 31266 13880 3 February 2011 31266 13414 1613 January 2011 19445 12043 1613 December 2010 19445 10563 1613 November 2010 19445 9364 1613 October 2010 18150 6163 1613 June 2010 10075 8433 1613 September 2010 18150 5154 1613 May 2010 18806 But i want to create a view like this ID EmpNo ThisMonth Previous Month mYear ThisNetSalary PreviousNetSalary Net Salary 29 3 January 2010 December 2009 January 2010 29217 0 29217 1210 3 February 2010 January 2010 February 2010 29217 29217 29217 2052 3 March 2010 February 2010 March 2010 29217 29217 29217 3102 3 April 2010 March 2010 April 2010 29447 29217 29447 4153 3 May 2010 April 2010 May 2010 29447 29447 29447 5207 3 June 2010 May 2010 June 2010 29197 29447 29197 6376 3 July 2010 June 2010 July 2010 28803 29197 28803 7231 3 August 2010 July 2010 August 2010 28281 28803 28281 7893 3 September 2010 August 2010 September 2010 29303 28281 29303 8874 3 October 2010 September 2010 October 2010 29303 29303 29303 10076 3 November 2010 October 2010 November 2010 31276 29303 31276 11318 3 December 2010 November 2010 December 2010 31216 31276 31216 12503 3 January 2011 December 2010 January 2011 31266 31216 31266 13880 3 February 2011 January 2011 February 2011 31266 31266 31266 13881 3 March 2011 February 2011 March 2011 0 31266 0 5154 1613 May 2010 April 2010 May 2010 18806 0 18806 6163 1613 June 2010 May 2010 June 2010 10075 18806 10075 8433 1613 September 2010 August 2010 September 2010 18150 10075 18150 9364 1613 October 2010 September 2010 October 2010 18150 18150 18150 10563 1613 November 2010 October 2010 November 2010 19445 18150 19445 12043 1613 December 2010 November 2010 December 2010 19445 19445 19445 13414 1613 January 2011 December 2010 January 2011 19445 19445 19445 13415 1613 February 2011 January 2011 February 2011 0 19445 0.
The problem is the output is getting displayed as : 10/20/2010 1:36:01 PM
I know RegistrationDate is datetime stamp that's why i am getting output in this format.
But i want to display RegistrationDate as: Oct 20, 2010 at 1:36pm -> according to (GMT-05:00) Eastern Time (US & Canada) ORDER BY RegistrationDate in Desc (top most will be last registered user)
By right clicking on my database i created a query in server explorer. But where are this query stored can't find them back. I should aspect that their is a folder query's like there is a folder tables but this isn't the case.
I am trying to create statitics for game downloads and am having dificulty with a sub part of my query. the goup by command is not having the disired result and i think i have been looking at it too long to see what i am doing wrong. [Code]....
At the moment it is producing each result in many rows when only 4 rows should appear. its the download column that is doing it. needs to be sub queried or something to display total in relation to month,gameid,userid.
I am trying to display this query result in an aspx page.
It is very slow in loading. Here is the query. The inner query inside the outer quesry is the problem.(Please see the underlined part in the query) - (If I remove that part it is very fast.)
select
top 500
--This column is the issue ,Governing_Class=( case when exists (select top 1 tqc.class_code from t_quote_class tqc inner join t_quote_class_premium tqcm on tqc.class_code =tqcm. class_code where tqc.appid=pi.appid and tqc.class_code not in('8742' ,'8810','7380') order by tqcm.premium_amt desc ) then ( select top 1 tqc.class_code from t_quote_class tqc inner join t_quote_class_premium tqcm on tqc.class_code =tqcm. class_code where tqc.appid=pi. appid order by tqcm.premium_amt desc ) ......... From tables
I am using following oracle query and using executescalar() to fetch data
sql = "select username from usermst where userid=2" string getusername = command.executescalar(); But It is showing me error "System.NullReferenceException: Object reference not set to an instance of an object" This error comes when there is no row exist in database for userid=2
How can I make this get a specific users data, Example user enters his 3 digit code in the badge text box so that only his information is shown. Can I add a where clause to the select command? or is there a better way to do this? Also is there a good book out there with information about asp.net, c# and oracle.
// string Badge = "100000" + Request.Form["xBadgeTextBox"]; in default.aspx.cs //"SELECT * FROM CLOCK_HISTORY WHERE BADGE ='" + Badge + "'"; would
I have a number of automatically generated data access classes to handle things like inserts & updates. These look like:
cmd.CommandType = CommandType.Text cmd.CommandText = "UPDATE myTable set f1 = :f1, f2 = :f2, f3 = :f3 where id = :id" cmd.Parameters.AddWithValue(":f1", _f1) cmd.Parameters.AddWithValue(":f2", _f2) cmd.Parameters.AddWithValue(":f3", _f3) cmd.ExecuteNonQuery()
If I were to re-write the sql to do what I want, it would look something like
cmd.CommandText = "UPDATE myTable set f1 = pkg.getMyValue, f2 = :f2, f3 = :f3 where id = :id"
Is there any way to do this by setting _f1 to a "special" value, without changing the update SQL or the way the parameters are set? Only in certain cases will I need to set the value of f1 - most of the time, it will be null.
Using .net 2.0, and system.data.oracleclient against oracle 11.
I need to insert values from the frontend into a mysql table. I am using a 3 layer architecture. the below is the code that is written in my dataaccess layer. the query is not being executed.
public int Insert(List<EmployeeUInfo> objInsert) { try { DataTable dtInsertRow = new DataTable();
Trying to modify an existing gridview in an aspx to access a db2 table on an iseries server. The iseries seems to have problems with the parameter specified in the query as @parameter giving a @parameter does not exist in table error. When specifying the gridview in VS the wizard did not recognize that the @parameter was a parm and prompt for the control etc. where it could be found. Of course there is no problem when accessing a mssql server table. Can someone help me with how to connect properly to the iseries and how to pass a parm on my query?
I've developed a web app in Visual Studio 2008 using ASP.NET which connects to an Oracle database and a separate SQL Server 2005 database in the same application.
I want to convert this application to use SQL Server only. I have copied the Oracle tables that I need to SQL Server and am in the process of converting the SQL queries from Oracle format to SQL Server format.
I have also modified all the Connection Strings in web.config to point to the SQL Server database now and not the Oracle one.
However I have come across a problem. The TableAdapters are configured to use the OracleClient in the declarations code behind the scenes, i.e. it is "hard coded". But this causes problems of course while modifying/saving queries, because I don't want any trace of Oracle in my new web app.
The only thing I can think of is to delete all my TableAdapters and re-create them all.
I need to find out the query execution time from the front end .Where should I insert the code for that.
I am using the bleow query:
OracleConnection con = new OracleConnection(ConnStr); con.Open(); OracleCommand cmd = new OracleCommand("Stored_Proc",con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(); .................... ................ OracleDataAdapter oda = new OracleDataAdapter (cmd);
when i am executing a simple query it is takeing 47 sec .... that is only for one table.. other then that remaing table perfromance is good... Help me out... and is there chance to get good perfomance through .net coding..
I am trying to do a SELECT ... WHERE ... IN construct, using a parametrized query with Oracle (odp.net) and I cannot make it work correctly.
[Code]....
The STATUS column is NUMBER(2) in the database. My problem seems to be related to getting the correct OracleDbType. I've tried Varchar2, Long, Decimal... nothing works. Should I go for the ArrayBindSize construct?
I have a the following code to get data from an oracle database using
a parameterized query:
conn = New OracleConnection(connectionstring) comm = New OracleCommand("select name from Cust_name from Name_Table Where last_name = :lastname", conn) parmLastName = New OracleParameter("lastname", OracleDbType.Varchar2) parmLastName.Value = "Jones" comm.Parameters.Add(parmLastName)
I am trying to write an audit that will tell me what users are doing, so I would like to get the actual sql that is submitted. This is just one example of the query, there are many more so I would like to make it as general as possible. I would like to actually see "
select name from Cust_name from Name_Table
Where last_name = "Jones". I know I could just create the text
I am generating page number for my listview control. I am using mysql as databast. Please check the sysntex it is giving error but works fine with sqlserver.Public Function GetPageNumber(ByVal PhotoID As Integer, ByVal AlbumID As Integer) As Integer
Dim con As New MySqlConnection con.ConnectionString = "server=localhost;User Id=root;database=jis;password=papidm;Persist Security Info=True"
[code]...
This is the code. The error giving in the Mysql query syntex heighlighted
