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
View 1 Replies
Similar Messages:
Jan 8, 2010
i have a SQL Query. It works fine in Sql server. let me know how to make it work in Oracle.
You can run below code in Sql server and check. It will work fine. But in Oracle,( I am using TOAD for Oracle )it is giving some errors.
Code]....
View 4 Replies
Mar 10, 2010
select Groupid,GroupName,onorusername from palgroup where groupid in (select distinct Groupid
View 5 Replies
Mar 30, 2011
convert the query in pic #3.
pic #1 is where the user will put the 3 fields.
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.
1. http://i1188.photobucket.com/albums/z403/hell91131/8udh1nd12e2.png
2. http://i1188.photobucket.com/albums/z403/hell91131/wqrfefcwerfewf.png
3. http://i1188.photobucket.com/albums/z403/hell91131/qewrfewfewrgferge.png
View 2 Replies
Mar 2, 2011
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.
View 4 Replies
Oct 20, 2010
In my database when anyone registers - By default the registration time is saved as UTC universal time zone.
But i want to display everything on the website in (GMT-05:00) Eastern Time (US & Canada)
I am using this SQL QUERY to display registration date:
SELECT TOP (5) Username, CustomerID, RegistrationDate = CONVERT(VARCHAR(12),RegistrationDate,107) FROM Customer ORDER BY RegistrationDate DESC
And, i am displaying it like this:
<asp:Label ID="nameLabeldate" runat="server" Text='<%# Eval("RegistrationDate") %>' />
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)
View 11 Replies
May 17, 2010
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.
View 10 Replies
Mar 2, 2011
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.
Month-download-GameID-UserID
1 1
1 1
1 1
1 1
View 2 Replies
Feb 1, 2011
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
View 7 Replies
Nov 4, 2010
i have tow query in one stored procedure
first query and the second one every of them return only value
i want to copy the result of tow values in a new row in new table
as i want to check on this tow values in ado.net
i want to get only this tow values as a row not first and secon query
only a row has result of first and second query results
[Code]....
View 3 Replies
Oct 14, 2010
How can I SELECT second if first SELECT returns nothing?
View 8 Replies
Jan 4, 2010
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
View 4 Replies
Jun 28, 2010
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
something like this work?
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionStrings %>"
ProviderName="<%$ ConnectionStrings:ConnectionStrings.ProviderName %>"
SelectCommand="SELECT "CLOCK_IN_TIME", "CLOCK_OUT_TIME" FROM "CLOCK_HISTORY"">
</asp:SqlDataSource>
View 1 Replies
Nov 19, 2010
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.
View 1 Replies
Dec 17, 2010
Using sql server 2005.
I have an Query A of fields that returns X number of records.
I create a Query B of same fields that returns X-80 number of records.
I want to find out the 80 records that are in Query A but are not in Query B. what is the syntax for this?
View 4 Replies
Jan 29, 2010
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();
[Code]....
View 1 Replies
Jan 10, 2011
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?
View 1 Replies
Feb 1, 2010
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.
View 6 Replies
Feb 7, 2011
I need to transfer SQL Server databse to Oracle database.
View 2 Replies
Sep 2, 2010
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);
View 2 Replies
Mar 16, 2011
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..
View 3 Replies
Apr 6, 2010
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?
View 5 Replies
Jul 12, 2010
I run queries in my stored procedure and pass values to it as a perameter e.g.
SELECT p.Profile_Id
FROM Profile p
WHERE (p_Gender = p.gender_id)
now is there a way that i can get the actual query executed with paramaters value like this:-
SELECT p.Profile_Id
FROM Profile p
WHERE ('M' = p.gender_id)
View 3 Replies
Apr 9, 2010
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
View 2 Replies
Aug 11, 2010
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
View 1 Replies
