Database

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

Coming soon: The Second EuroCPian Event

OracleConnection  dbConn;
dbConn  = new OracleConnection( "DataSource=NameOfDatabase; User ID=USERID; Password=PASSWORD" );
dbConn.Open()

string  SomeSelect( "select sysdate from dual" );
OracleCommand  command  = new OracleCommand ( SomeSelect, dbConn);
OracleDataReader  query  = command.ExecuteReader();

query.Read();

DateTime  DBDateTime  = query.GetDateTime(0);

CREATE FUNCTION fn_SamplesTerminalPortWellLocationText
(
	@SampleID int
)
RETURNS varchar(1000)
AS
/*
	DROP FUNCTION fn_SamplesTerminalPortWellLocationText
	GRANT EXEC ON fn_SamplesTerminalPortWellLocationText TO xxxx

	select dbo.fn_SamplesTerminalPortWellLocationText(1) as test

	--GO
	DECLARE @SampleID int
	SELECT  @SampleID = 1
	Select  SampleID, sp_SamplesTerminalPortWellLocationText(SampleID)
	FROM	tblSamplesSample WHERE SampleID = @SampleID
*/
BEGIN

	DECLARE @RowCount int,
		@Debug bit,
		@Results varchar(1000)
	SELECT  @Debug = 0,
		@Results = ''

	-- Loop thru all of the terminal port and concantenate them together
	DECLARE @TerminalPortWellLocation varchar(75)

	DECLARE TPWL CURSOR FAST_FORWARD FOR
		SELECT	tp.TerminalPortWellLocation
		FROM 	tblSamplesSampleTerminalPortWellLocation stp,
			tblSamplesTerminalPortWellLocation tp
		WHERE	stp.TerminalPortWellLocationID = tp.TerminalPortWellLocationID
		AND	stp.SampleID = @SampleID
		AND	(stp.Deleted Is Null Or stp.Deleted = 0)
		ORDER BY TerminalPortWellLocation
	
	OPEN TPWL
	FETCH FROM TPWL 
	  INTO @TerminalPortWellLocation

	While @@Fetch_Status = 0
	BEGIN
		
		SELECT @Results = @Results + @TerminalPortWellLocation + ', '
	
		FETCH FROM TPWL 
	  	  INTO @TerminalPortWellLocation
	END
	-- Close & Deallocate Cursor
	Close TPWL
	Deallocate TPWL

	-- Trim the last comma
	-- Return the results
	IF (DataLength(@Results) > 2)
		SELECT @Results = SubString(@Results, 1, DataLength(@Results) - 2) 

	RETURN(@Results)

END

SELECT   tp.OrderBy, tp.PropertyTestID, Replace(tp.PropertyTestName, ',', '') AS PropertyTestName,
  SUM(CASE WHEN S.SampleID = 109 THEN ResultDecimal ELSE NULL END) AS [N/A109 03/31/2004],  
  SUM(CASE WHEN S.SampleID = 108 THEN ResultDecimal ELSE NULL END) AS [N/A108 03/30/2004],  
  SUM(CASE WHEN S.SampleID = 107 THEN ResultDecimal ELSE NULL END) AS [N/A107 03/17/2004],  
  SUM(CASE WHEN S.SampleID = 106 THEN ResultDecimal ELSE NULL END) AS [N/A106 03/01/2004],  
  SUM(CASE WHEN S.SampleID = 105 THEN ResultDecimal ELSE NULL END) AS [N/A105 01/05/2004],  
  SUM(CASE WHEN S.SampleID = 104 THEN ResultDecimal ELSE NULL END) AS [N/A104 11/17/2003]
FROM     tblSamplesPropertyTest tp, 
         ........[the rest of the query blah, blah blah...]