|
|
|
You have to use Package insted of procedure.
Search in oracle or MSDN to get help about package.
here is an example of package (Collected from MSDN):
Drop table Emp;
drop table DEPT;
CREATE TABLE DEPT
(DEPTNO NUMBER(2,0) NOT NULL,
DNAME VARCHAR2(14) NULL,
LOC VARCHAR2(13) NULL,
PRIMARY KEY (DEPTNO)
);
INSERT INTO Dept VALUES(11,'Sales','Texas');
INSERT INTO Dept VALUES(22,'Accounting','Washington');
INSERT INTO Dept VALUES(33,'Finance','Maine');
CREATE TABLE EMP
(EMPNO NUMBER(4,0) NOT NULL,
ENAME VARCHAR2(10) NULL,
JOB VARCHAR2(9) NULL,
MGR NUMBER(4,0) NULL,
HIREDATE DATE NULL,
SAL NUMBER(7,2) NULL,
COMM NUMBER(7,2) NULL,
DEPTNO NUMBER(2,0) NULL,
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
PRIMARY KEY (EMPNO)
);
INSERT INTO Emp VALUES(123,'Bob','Sales',555,'28-JAN-79',35000,12,33);
INSERT INTO Emp VALUES(321,'Sue','Finance',555,'12-MAY-83',42000,12,11);
INSERT INTO Emp VALUES(234,'Mary','Account',555,'14-AUG-82',33000,12,22);
CREATE OR REPLACE PACKAGE curspkg_join AS
TYPE t_cursor IS REF CURSOR ;
Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor);
END curspkg_join;
/
CREATE OR REPLACE PACKAGE BODY curspkg_join AS
Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor)
IS
v_cursor t_cursor;
BEGIN
IF n_EMPNO <> 0
THEN
OPEN v_cursor FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.EMPNO = n_EMPNO;
ELSE
OPEN v_cursor FOR
SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;
END IF;
io_cursor := v_cursor;
END open_join_cursor1;
END curspkg_join;
Convert the following VB Code into C# to execute that package:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim x As Exception
Dim Ds As New DataSet()
Dim Oraclecon As New OracleConnection("Server=giit;Uid=scott;Pwd=tiger")
Oraclecon.Open()
Dim myCMD As New OracleCommand()
myCMD.Connection = Oraclecon
myCMD.CommandText = "curspkg_join.open_join_cursor1"
myCMD.CommandType = CommandType.StoredProcedure
myCMD.Parameters.Add(New OracleParameter("n_empno", OracleType.Number)).Value = TextBox1.Text
myCMD.Parameters.Add(New OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output
'MyDA is your dataadapter
Dim MyDA As New OracleDataAdapter(myCMD)
Try
MyDA.Fill(Ds)
Catch x
MessageBox.Show(x.Message.ToString)
End Try
DataGrid1.DataSource = Ds.Tables(0)
Ds.WriteXml("C:\packqry.xml")
Oraclecon.Close()
End Sub
|
|
|
|
|
thanks that is exactly what i want
|
|
|
|
|
I create some tables (the same ones mentioned below), but when i try to do select * from tablename (in a query window in SQL Manager Express), it tells me this:
Invalid object name 'tableName'.
I can see the table there, and I can modify it, but I can't run a query on it. I've even tried putting "dbo." at the beginning of the table name, but it still gives that error. Is it a database permissions thing? I haven't expressly set any permissions because I figured I didn't have to.
SOLUTION ==============
I guess I didn't have the database selected in the Object Explorer when I created the query window...
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
modified on Saturday, March 20, 2010 9:49 AM
|
|
|
|
|
you can set all kinds of user permissions, such as create table, create record, read table, etc. I don't know the details, Management Studio should offer a way to see them all. Myself, I mostly use MySQL and MyPHPAdmin.
FWIW: one way to get into trouble is by using a reserved word as a table name or field name; error messages aren't always very informative here.
|
|
|
|
|
None of my table names are reserved words... :/
Do I *need* to set permissions? Like I said, I can add/modify tables and stored procs, but I can't run a simple query in a query window.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
John Simmons / outlaw programmer wrote: Do I *need* to set permissions?
Guess so. In PHPMyAdmin, when you create a database, you have to specify what permissions you give everyone, based on username and hostname (e.g. you can restrict a user to localhost only, no remote); there are over 20 checkboxes detailing all privileges.
Are you sure you are logged into your SQL Manager with the same username/password your app is running? (Not that you have to, but that should yield the same privileges).
|
|
|
|
|
Solution - See original post in this thread
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
I have a table that has multiple instances of a given item, and the date on which that record was inserted into the table.
How do I retrieve the newest copy of all unique items in the table?
I'm an almost complete newbie regarding SQL, so please don't be harsh.
SOLUTION ===========
select field1, max(field2)
from table
group by field1
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Hi John,
try something like:
SELECT TOP 1 selectedFields FROM tablename WHERE matchConditions ORDER BY createdField DESC
|
|
|
|
|
I'd love to try that, but I can't even run a simple query (see next question above this thread).
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Here's my situation:
0) There are a number of records for a given "id", and there is more than one "id" in the table. So, I could have 10 records for id1, and 5 records for id2.
1) These records contain a datetime column on which I want to base by query.
2) I will be retrieving data from two tables.
3) I tried a simple query first (which worked fine):
select id, max(dateColumn) from tabelname group by id
The problem is that I want additional columns from each table that shouldn't be aggregated, but sql server says I need aggregate functions for all of the other columns. Do I have to run a query and put the results into a temp table, and then run a second query against the actual data to retrieve the records I want, or is there a magic generic aggregate function I can use on the other fields?
I am of the current opinion that SQL sucks.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Wow. Your question well exceeds what I know about SQL.
I've never used temp tables, I very rarely use nested selects.
I'm still learning, mostly by reading the SQL forum.
Did you try the TOP 1 ... ORDER BY ... approach? that one typically works for me.
John Simmons / outlaw programmer wrote: I am of the current opinion that SQL sucks.
I'm afraid that will never change. At best, one gets used to it.
Suggestion: post your exact SQL statement, and wait for Mycroft to comment on it. He's probably the most knowledgeable around here.
|
|
|
|
|
I had to do something like this:
create temp table
select into temp table using group by
select from permanent AD temp table for desired result
Here's the whole this
if object_id('tempdb..#DUAL')is not null
DROP TABLE #DUAL
create table #DUAL
(
ID int,
datevalue datetime
)
insert into #DUAL (ID, datevalue)
select id, max(datevalue)
from metrics
group by ID
SELECT
a.ID
,a.itemType
,a.Title
,a.Description
,m.DateValue
FROM tableA a, metrics m, #Dual d
WHERE a.id = d.id AND a.ID = m.ID AND m.datevalue = d.datevalue
drop table #DUAL
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
SELECT a.Id,
a.ItemType,
a.Title,
a.Description,
m.DateValue
FROM TableA a
LEFT JOIN
(
SELECT Id,
MAX(DateValue) as DateValue
FROM Metrics
GROUP BY Id
) m ON m.Id = a.Id ;
If you only want Ids that have metrics then you can remove the LEFT to make it an inner join.
|
|
|
|
|
Actually, an ID won't be in the tables unless it has a metric. I want the data from both table a and table b, but I want only the latest data for each ID.
I tried to adapt the code you posted, but It SME complains with <sarcasm> it's way to verbose <sarcasm> error message:
Incorrect syntax near the keyword 'ON'.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
if object_id('tempdb..#TableA')is not null
DROP TABLE #TableA
go
if object_id('tempdb..#Metrics')is not null
DROP TABLE #Metrics
go
create table #TableA
(
Id int,
ItemType char(1),
Title varchar(50),
Description varchar(100)
)
go
create table #Metrics
(
ID int,
TableAId int,
datevalue datetime
)
go
insert into #TableA (ID, ItemType, Title, Description) values (1, 'A', 'First Title', 'First Description');
insert into #TableA (ID, ItemType, Title, Description) values (2, 'A', 'Second Title', 'Second Description');
insert into #TableA (ID, ItemType, Title, Description) values (3, 'B', 'Third Title', 'Third Description');
go
insert into #Metrics (ID, TableAId, datevalue) values (1, 1, '2010-01-01');
insert into #Metrics (ID, TableAId, datevalue) values (2, 1, '2010-01-02');
insert into #Metrics (ID, TableAId, datevalue) values (3, 3, '2010-01-03');
insert into #Metrics (ID, TableAId, datevalue) values (4, 2, '2010-01-04');
insert into #Metrics (ID, TableAId, datevalue) values (5, 2, '2010-01-05');
insert into #Metrics (ID, TableAId, datevalue) values (6, 3, '2010-01-06');
insert into #Metrics (ID, TableAId, datevalue) values (7, 1, '2010-01-07');
insert into #Metrics (ID, TableAId, datevalue) values (8, 2, '2010-01-08');
insert into #Metrics (ID, TableAId, datevalue) values (9, 1, '2010-01-09');
insert into #Metrics (ID, TableAId, datevalue) values (10, 3, '2010-01-10');
insert into #Metrics (ID, TableAId, datevalue) values (11, 3, '2010-01-11');
insert into #Metrics (ID, TableAId, datevalue) values (12, 1, '2010-01-12');
go
SELECT
a.ID
,a.itemType
,a.Title
,a.Description
,m.DateValue
FROM #TableA a
left join
(
select tableaid,
MAX(datevalue) as datevalue
from #Metrics
group by tableaid
) m on m.tableaid = a.id
go
drop table #Metrics
go
drop table #TableA
go
|
|
|
|
|
Hmmm... that ran just fine.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Can you post the code that you have tried that gives the syntax error.
|
|
|
|
|
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ArticleMetrics](
[ArticleID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Votes] [int] NOT NULL,
[Bookmarks] [int] NOT NULL,
[Downloads] [int] NOT NULL,
[Popularity] [decimal](5, 2) NOT NULL,
[Rating] [decimal](5, 2) NOT NULL,
[LastUpdated] [smalldatetime] NOT NULL,
[Views] [int] NOT NULL,
[DateScraped] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ArticleMetrics] WITH CHECK ADD CONSTRAINT [FK_ArticleMetrics_Articles] FOREIGN KEY([ArticleID])
REFERENCES [dbo].[Articles] ([ArticleID])
GO
ALTER TABLE [dbo].[ArticleMetrics] CHECK CONSTRAINT [FK_ArticleMetrics_Articles]
USE [CPAM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Articles](
[ArticleID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ArticleType] [int] NOT NULL,
[Title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DatePosted] [smalldatetime] NOT NULL,
[Active] [bit] NOT NULL,
[LastScraped] [datetime] NOT NULL,
CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED
(
[ArticleID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Essentially, I want to combine the appropriate rows but I only want one record returned per ArticleID that represents the LAST record that was added (determined by MAX(ArticleMetrics.DateScraped)).
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
I think that this is what you need;
SELECT a.*,
lm.*
FROM dbo.Articles AS a
CROSS APPLY
(SELECT TOP 1 m.*
FROM dbo.ArticleMetrics AS m
WHERE m.ArticleId = a.ArticleId
ORDER BY m.DateScraped DESC) AS lm
I would also add a compound primary key to ArticleMetrics on ArticleId, DateScraped.
|
|
|
|
|
But doesn't a primary key have to be unique? If that's the case, I can't make the DateScraped column a primary key.
BTW, I've never even heard of "Cross Apply" before.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
Yes PKs do have to be unique, but I was assuming that the combination of ArticleId and DateScrapped would be unique so you could include them in a compound primary key. Otherwise, you should add a identity field to uniquely identify each row.
Cross Apply and Outer Apply came in with Sql Server 2005. They are great for situations like 'Show me the last 5 orders per customer' that were very difficult before with ansi sql.
|
|
|
|
|
I simply can't get that to work. T-SQL is being as touchy as a ex-wife seeking more child support.
.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
