Database

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;

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

John Simmons / outlaw programmer wrote:

Do I *need* to set permissions?

SELECT TOP 1 selectedFields FROM tablename WHERE matchConditions ORDER BY createdField DESC

select id, max(dateColumn) from tabelname group by id

John Simmons / outlaw programmer wrote:

I am of the current opinion that SQL sucks.

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

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 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

/****** Object:  Table [dbo].[ArticleMetrics]    Script Date: 03/21/2010 17:20:15 ******/
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


/****** Object:  Table [dbo].[Articles]    Script Date: 03/21/2010 17:20:46 ******/
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

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