|
CREATE TABLE t1(col1 NUMBER)
CREATE TABLE t2(col1 NUMBER,col2 NUMBER)
INSERT INTO t1 VALUES(1);INSERT INTO t1 VALUES(2);INSERT INTO t1 VALUES(3);
INSERT INTO t1 VALUES(4);INSERT INTO t1 VALUES(5);INSERT INTO t1 VALUES(6);
INSERT INTO t2 VALUES(1,1);INSERT INTO t2 VALUES(2,1);INSERT INTO t2 VALUES(3,1);INSERT INTO t2 VALUES(4,1);INSERT INTO t2 VALUES(5,1);INSERT INTO t2 VALUES(6,1);INSERT INTO t2 VALUES(1,2);INSERT INTO t2 VALUES(3,2);INSERT INTO t2 VALUES(4,2);INSERT INTO t2 VALUES(6,2);INSERT INTO t2 VALUES(1,3);
INSERT INTO t2 VALUES(3,3);INSERT INTO t2 VALUES(6,3);
SELECT col1, col2 FROM t2 d WHERE col1 IN
(SELECT col1 FROM
(SELECT ROWNUM AS rownumber1,col1,col2 FROM t2 WHERE col1 IN
(SELECT col1 FROM (SELECT ROWNUM AS rownumber,col1 FROM t1) WHERE rownumber<=3)--WHERE c.col1=col1
)WHERE rownumber1<=2 AND d.col1=col1)
I want a query which will produce the following result.I just tried with the above one which is failing(no result or not compiling).
1 1
1 2
2 1
3 1
3 2
|
|
|
|
|
Hi
I will try out your in SQL Server 2000 and let you know as Oracle is not installed on my machine.
Harini
|
|
|
|
|
Hi
I tried out this query in SQL Server 2000. It is working as expected and I am not sure whether this will work in Oracle
select t2.col1, t2.col2 from t2
inner join t1 on t1.col1 = t2.col2
and t2.col1 in (select top 2 a.col1 from t2 a where a.col2 = t1.col1 order by a.col1)
Harini
|
|
|
|
|
Thank You Harini. I am trying to convert this into Oracle. There are some problems. I will let you know once done
|
|
|
|
|
hi all
How can i do insert multiple rows on Accsess
INSERT INTO demo(id, name, role) VALUES ('demo', 'demo1', 'demo2');
INSERT INTO demo(id, name, role) VALUES ('demo3', 'demo4', 'demo5');
INSERT INTO demo(id, name, role) VALUES ('demo6', 'demo6', 'demo7');
When i insert by SQL have Exception:
Characters found after and of SQL statement
|
|
|
|
|
Can you bit elaborate your problem.
Thanks and Wishes
Navneet Hegde
Nashik
Develop2Program & Program2Develop
|
|
|
|
|
Access/Jet does not support multi-statement batches. You must send a single statement at a time.
|
|
|
|
|
Houdy all,
I have to take over am application that works with MySQL.
I'm new to MySQL, but have worked quite a lot with SQL Server 2k, and 2005
Now, what i got is a bunch of .frm files and one .opt file,
I have down loaded MySQL 5 and installed it on my console.
What do i do next ?
How to create and import data to the MySQL DB?
Or maybe you guys think its best to just migrate to SQL Server...
is there a helpful free tool to use as a database admin interface ?
Regards,
Tzumer Edo.
|
|
|
|
|
Tzumer Edo wrote:
is there a helpful free tool to use as a database admin interface ?
here you go: http://dev.mysql.com/downloads/gui-tools/5.0.html
|
|
|
|
|
trying to execute this stored procedure using a prg that connects to the server using an ODBC DSN. it doesnot retrieve the result set.
CREATE PROCEDURE PROC_1
AS
CREATE TABLE #abc (
ssn char(30) )
insert into #abc(ssn) select phone from tbl_users
select * from #abc
GO
when executed directly, ie. say, Query Analyser or Excel, there r no probs encountered. also, when i remove the create n insert stmts, n change the #abc to a table abc, am getting the results.
Thanks..
|
|
|
|
|
A slightly off topic question - but why are you creating a temprorary table, populating it with a fairly simple query then retrieving the result set. Would it not be quicker just to retrieve the result set from the SELECT used on the INSERT? The temp table will just disappear at the end of the stored procedure call, so what use is it? Or is there more code you are not telling us about which may help us answer your query.
In other words why are you not just doing:
SELECT phone from tbl_users
|
|
|
|
|
yeah.. there r more operations that r to be performed on this temp table.. modifications, etc.. but i got to get this running b4 i put in the other stmts... SOS!!
|
|
|
|
|
use this
insert into #abc(ssn) select isnull(phone,'') from tbl_users
DAsh04
|
|
|
|
|
Try adding
set nocount on as the first line of your procedure. This stops SQL-Server from sending-back a running commentary of what its up to (which is probably being counted as the resultset for you current procedure).
|
|
|
|
|
|
Hi,
I would like to call an oracle stored procedure from SQL Server. My setup is like this...
Computer A: SQL Server installed.
Computer B: Oracle installed.
I want to call oracle stored procedure in Computer B from computer A. Ideally i want to get some return values/result sets from oracle stored procedure.
Any help would be much appreciated.
Many Thanks
Saurabh Hansalia
|
|
|
|
|
See sp_addlinkedserver. You can use any OLE DB data source as a linked server and query it using linked server syntax.
|
|
|
|
|
hi
Could someone plz help me!
I am getting an "IndexOutOfRangeException not handled by user", in my code when im tryin to get a specific record, selected via a dropdownlist, and show the fields in textboxes.
my code is like this:
reader.Read();
ShipperDetails shipper = new ShipperDetails((int)reader["ShipperID"],
(string)reader["CompanyName"], (string)reader["Phone"]);
reader.Close();
this is where the error comes up.
Please help..
|
|
|
|
|
You have supplied a column name that does not exist.
Also, it is better for debugging if you expand your code like this, because then you will have a better idea about exactly which item failed.
int shipperId = (int)reader["ShipperID"];
string companyName = (string)reader["CompanyName"];
string phone = (string)reader["Phone"];
ShipperDetails shipper = new ShipperDetails(shipperId,
companyName, phone);
|
|
|
|
|
thanks for the reply.
it says im getting an error with the ShipperID. I used an arraylist to store the objects.
the problem is that its returning a shipperId = 0 when it reads the first row. and thats why its IndexoutOfRange.
How can i fix this?
|
|
|
|
|
rcwoods wrote: the problem is that its returning a shipperId = 0 when it reads the first row
From the code that you showed returning a shipperId of zero will not produce the exception you are getting. Is there any code missing?
|
|
|
|
|
here is more of my code
maybe you can see whats wrong.
this is from my web form:
ShipperDetails[] shipperArray = dac.GetAllShippers();
foreach (ShipperDetails shipper in shipperArray)
{
ListItem item = new ListItem();
item.Text = shipper.CompanyName;
item.Value = shipper.ID.ToString();
DropDownList1.Items.Add(item);
}
public ShipperDetails[] GetAllShippers()<br />
{<br />
SqlConnection con = new SqlConnection(connString);<br />
SqlCommand cmd = new SqlCommand("GetAllShippers", con);<br />
cmd.CommandType = CommandType.StoredProcedure;<br />
<br />
ArrayList shippers = new ArrayList();<br />
try<br />
{<br />
con.Open();<br />
SqlDataReader reader = cmd.ExecuteReader();<br />
<br />
while (reader.Read())<br />
{<br />
<br />
ShipperDetails shipper = new ShipperDetails((int)reader["ShipperID"], (string)reader["CompanyName"], (string)reader["Phone"]);<br />
<br />
shippers.Add(shipper);<br />
<br />
}<br />
reader.Close();<br />
<br />
return (ShipperDetails[])shippers.ToArray(typeof(ShipperDetails)); <br />
}<br />
this is called when i select a particular shipper from the dropDownList:
public ShipperDetails GetShipper(int shipperID)<br />
{<br />
SqlConnection con = new SqlConnection(connString);<br />
SqlCommand cmd = new SqlCommand("GetShipper", con);<br />
cmd.CommandType = CommandType.StoredProcedure;<br />
<br />
cmd.Parameters.Add(new SqlParameter("@ShipperID", SqlDbType.Int, 4));<br />
cmd.Parameters["@ShipperID"].Value = shipperID;<br />
<br />
try<br />
{<br />
con.Open();<br />
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);<br />
<br />
reader.Read();<br />
int shipperId = (int)reader["ShipperID"];<br />
string companyName = (string)reader["CompanyName"];<br />
string phone = (string)reader["Phone"];<br />
<br />
ShipperDetails shipper = new ShipperDetails(shipperId,companyName,phone);<br />
<br />
reader.Close();<br />
<br />
return shipper;<br />
}
|
|
|
|
|
In GetShipper(int shipperID), does the reader actually return anything? (i.e. reader.HasRows == true)
If you expect shipperID to be non-zero then perhaps the code that gets the shipperID out of the drop down isn't working properly...
|
|
|
|
|
reader did return true for having rows.
this is how i get the shipperId out of the dropDownlist:
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)<br />
{<br />
<br />
ShipperDetails shipper = dac.GetShipper(Convert.ToInt16(DropDownList1.SelectedItem.Value));<br />
<br />
|
|
|
|
|
You are shoving too much into one line of code. Separate out the method and property calls into separate lines:
ListItem selectedItem = DropDownList1.SelectedItem;
string dropValue = selectedItem.Value;
int shipperId = Convert.ToInt16(dropValue);
ShipperDetails shipper = dac.GetShipper(shipperId);
Now, you can step through the above and see what is actually happening.
Incidentally, what happens if you change SelectedItem for SelectedValue?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
