Resume Videos... Projects... Certificates... More...
 
 

Mike M Eghtebas
Database Development, Web Programming

 

 

Sample Projects

Data Scraping:   arrow
Task: Over million rows of data per year for the span of two decades was required to be downloaded for water contamination analysis by an environmental consulting firm in Santa Monica, CA. The client, state of Louisiana, was expecting the consultant retrieve all of the data from their website.
Solution: After a brief analysis of the data, I did the following:
  • Developed the relational table structure needed in SQL Server 2012 to maintain the data.
  • Developed a Data Scraping module in ASP.net and C# using XPath to collect the drinking water data from the firm's website.
  • Cleaned up the scraped data.
  • Loaded the cleaned data into the relational tables
Once developed, this solution was able to be utilized easily to handle the same process for retrieving and loading the data in the remaining 49 states. The following is a part of the code utilized to accomplish this task:
SQL Server tables and stored procedures (Data Scraping):
-- connect to swapeLAdata
use swapeLAdata
GO
-- create LabSampleData
create table LabSampleData(
  IDinAccess char(7) Not NULL
  , excludeYN bit default 0
  , WaterSystemNo varchar(15)  
  , WaterSystemName  varchar(100)
  , PrincipalParishServed  varchar(15)
  , [Status]  varchar(5)
  , LabSampleNo  varchar(15)
  , FederalType  varchar(5)
  , StateType  varchar(5)
  , PrimarySource  varchar(15)
  , ActivityDate date
  , CollectionDate date
  , Note  varchar(500)
  , [Year] nvarchar(4)
  , CONSTRAINT PK_IDinAccess PRIMARY KEY CLUSTERED 
(
 [IDinAccess] ASC
)) ON [PRIMARY]

--alter table LabSampleData add [Year] nvarchar(4);

-- create table LabAnalyteData
create table LabAnalyteData(
  labAnalyteID int identity
  , IDinAccess char(7)
  , AnalyteCode  varchar(10)
  , Analyte  varchar(100)
  , MethodCode  varchar(5)
  , LessThanIndicator varchar(5)
  , LevelType varchar(5) 
  , ReportingLevel decimal(16,8)
  , ReportingLevelUnit varchar(5) 
  , ConcentrationLevel decimal(16,8)
  , ConcentrationLevelUnit varchar(5) 
  , MonitoringPeriodBeginDate date
  , MonitoringPeriodEndDate date

  , CONSTRAINT PK_labAnalyteID 
		PRIMARY KEY (labAnalyteID)
  , CONSTRAINT FK_IDinAccess 
		FOREIGN KEY (IDinAccess) 
			REFERENCES LabSampleData(IDinAccess)
)


GO

/****** Object:  Table [dbo].[LADATA2005-2015a]    Script Date: 3/6/2015 3:24:28 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- imported table from MS Access
CREATE TABLE [dbo].[LADATA2005-2015a](
	[ID] [int] NOT NULL,
	[IDinAccess] char(7) NULL,
	[DoneYN] [bit] NOT NULL,
	[IDinExcel] char(7) NULL,
	[Year] [nvarchar](4) NULL,
	[PrincipalCountyServed] [nvarchar](30) NULL,
	[WaterSystemNo] [nvarchar](30) NULL,
	[WaterSystemName] [nvarchar](100) NULL,
	[LabSampleNo] [nvarchar](255) NULL,
	[SiteURL] [nvarchar](max) NULL,
	[SampleType] [nvarchar](10) NULL,
	[CollectionDate] [nvarchar](10) NULL,
	[SamplingPoint] [nvarchar](30) NULL,
	[excludeYN] [bit] NOT NULL,
	[ActionCategory] [nvarchar](10) NULL,
	[htnlDoneOn] [datetime] NULL,
	[UploadDoneOn] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

go
-- create ErrorTable
create table ErrorTable(
  ErrID int identity
  , IDinAccess char(7) 
  , ErrorCategory varchar(25)
  , ErrorDescription varchar(500)
  , [DoneYN] [bit] default 0
  , Time_Stamp datetime
  );

GO

Create Proc spAddToErrorTable
@IDinAccess char(7)
, @ErrorCategory varchar(25)
, @ErrorDescription varchar(500)
, @Time_Stamp datetime
As
Begin
 Insert ErrorTable(IDinAccess, ErrorCategory, ErrorDescription, Time_Stamp)
   Values(@IDinAccess, @ErrorCategory, @ErrorDescription, @Time_Stamp);
end

Create TYPE [dbo].[TVP_AnalyteData] AS TABLE(
  [IDinAccess] char(7)
  , AnalyteCode  varchar(10)
  , Analyte  varchar(100)
  , MethodCode  varchar(5)
  , LessThanIndicator varchar(5)
  , LevelType varchar(5) 
  , ReportingLevel decimal(16,8)
  , ReportingLevelUnit varchar(5) 
  , ConcentrationLevel decimal(16,8)
  , ConcentrationLevelUnit varchar(5) 
  , MonitoringPeriodBeginDate date
  , MonitoringPeriodEndDate date
)

GO

-- create sp_InsertLabSampleData to enter both sample and analyte data
Create PROCEDURE [dbo].[sp_InsertLabSampleData] 
   @IDinAccess char(7)               -- single sample-specific data
  , @WaterSystemNo varchar(15)  
  , @WaterSystemName  varchar(100)
  , @PrincipalParishServed  varchar(15)
  , @Status  varchar(5)
  , @LabSampleNo  varchar(15)
  , @FederalType  varchar(5)
  , @StateType  varchar(5)
  , @PrimarySource  varchar(15)
  , @ActivityDate date
  , @CollectionDate date
  , @message VARCHAR(100) OUTPUT
AS
BEGIN
Declare @ExistIDinAccess int;
Declare @msg varchar(500);

Select @ExistIDinAccess = Count(*) From LabSampleData Where IDinAccess=@IDinAccess;

if (@ExistIDinAccess = 0) 
Begin
-- insert sample-specific data to SampleData table
 INSERT INTO dbo.LabSampleData (
  IDinAccess 
  , WaterSystemNo   
  , WaterSystemName 
  , PrincipalParishServed 
  , [Status] 
  , LabSampleNo 
  , FederalType 
  , StateType 
  , PrimarySource
  , ActivityDate 
  , CollectionDate)
Values (
  @IDinAccess 
  , @WaterSystemNo   
  , @WaterSystemName 
  , @PrincipalParishServed 
  , @Status 
  , @LabSampleNo 
  , @FederalType 
  , @StateType 
  , @PrimarySource
  , @ActivityDate 
  , @CollectionDate
);
End
Else
  Set @msg = @IDinAccess + ' already exists; the second attempt was ignored.';

if @@Error>0 
begin
     if @msg =''
	    Set @msg = 'SQL Server error: ' + CAST(@@Error AS NVARCHAR(10))
     else
	   Set @msg = @msg + '; additionally there was a SQL Server error: ' + CAST(@@Error AS NVARCHAR(10))
end

Set @message = @msg;

Return @@Error

END

-- create sp_InsertSampleAnalyteData to enter both sample and analyte data
Create PROCEDURE [dbo].[sp_InsertLabAnalyteData] 
  @AnalyteTVP TVP_AnalyteData READONLY  

AS
BEGIN
--Select * From @AnalyteTVP;


-- multiple records of Anaylte data to AnalyteData table
 INSERT INTO dbo.LabAnalyteData (
  IDinAccess 
  , AnalyteCode
  , Analyte
  , MethodCode
  , LessThanIndicator
  , LevelType 
  , ReportingLevel
  , ReportingLevelUnit 
  , ConcentrationLevel
  , ConcentrationLevelUnit 
  , MonitoringPeriodBeginDate
  , MonitoringPeriodEndDate)
 SELECT 
  IDinAccess 
  , AnalyteCode
  , Analyte
  , MethodCode
  , LessThanIndicator
  , LevelType 
  , ReportingLevel
  , ReportingLevelUnit 
  , ConcentrationLevel
  , ConcentrationLevelUnit 
  , MonitoringPeriodBeginDate
  , MonitoringPeriodEndDate
 FROM @AnalyteTVP;
 

Return @@Error

END

select * from LabSampleData  Where IDinAccess >'0135000';
select * from LabAnalyteData Where Analyte like 'Mtb%' ;
Select * From ErrorTable;
select * from LabAnalyteData Where IDinAccess >'0048923';

--delete from LabAnalyteData  ;;
--delete from LabSampleData Where IDinAccess ='0048924';
--delete from ErrorTable;

Select e.IDinAccess,a.LabSampleNo, a.[year], a.PrincipalCountyServed, a.WaterSystemNo  from ErrorTable as e 
inner join [LADATA2005-2015a] as a
on e.IDinAccess=a.IDinAccess


select s.[year], a.AnalyteCode, a.Analyte
, Min(a.reportingLevel) as min_Reporting_Level
, Max(a.reportingLevel) as max_Reporting_Level
, a.ReportingLevelUnit as Reporting_Unit
from LabAnalyteData as a inner join 
LabSampleData as s on s.IDinAccess= a.IDinAccess
Where a.Analyte like 'METHYL TERT%'
Group By s.[year], a.AnalyteCode, a.Analyte, a.ReportingLevelUnit
Order By a.Analyte

select s.IDinAccess,s.LabSampleNo, d.SamplingPoint ,s.[year], a.AnalyteCode, a.Analyte
, a.reportingLevel 
, ReportingLevelUnit
, a.ConcentrationLevel
, a.ConcentrationLevelUnit
, d.SiteURL
from LabAnalyteData as a inner join 
LabSampleData as s on s.IDinAccess= a.IDinAccess
inner join [LADATA2005-2015a] as d
on d.IDinAccess=s.IDinAccess
Where a.AnalyteCode = '2251' and s.[year] IN('2006', '2007', '2008')
Order By s.[year]


select AnalyteCode, Analyte
from LabAnalyteData
Where IDinAccess = '0009517' --Analyte like 'methyl tertiaryl%'
Order By AnalyteCode

select distinct ReportingLevelUnit
from LabAnalyteData
Order By ReportingLevelUnit 

select distinct ConcentrationLevelUnit
from LabAnalyteData
Order By ConcentrationLevelUnit 

Select d.IDinAccess, d.[Year], s.IDinAccess, s.[Year] 
From [LADATA2005-2015a] d inner join LabSampleData s
on d.IDinAccess = s.IDinAccess


UPDATE s
  SET s.[Year] = d.[Year] 
  FROM LabSampleData as s 
  INNER JOIN [LADATA2005-2015a] d
  ON d.IDinAccess = s.IDinAccess

  Select PrincipalCountyServed
,d.SiteURL
,d.SampleType
,d.SamplingPoint
,d.ActionCategory
,s.IDinAccess
,s.excludeYN
,s.WaterSystemNo
,s.WaterSystemName
,s.PrincipalParishServed
,s.[Status]
,s.LabSampleNo
,s.FederalType
,s.StateType
,s.PrimarySource
,s.ActivityDate
,s.CollectionDate
,s.Note
,s.[Year]
,a.AnalyteCode
,a.Analyte
,a.MethodCode
,a.LessThanIndicator
,a.LevelType
,a.ReportingLevel
,a.ReportingLevelUnit
,a.ConcentrationLevel
,a.ConcentrationLevelUnit
,a.MonitoringPeriodBeginDate
,a.MonitoringPeriodEndDate
from [LADATA2005-2015a] d
Inner join LabSampleData s on d.IDinAccess =s.IDinAccess
Inner join LabAnalyteData a on a.IDinAccess =s.IDinAccess
Where s.[Year] < '2006'
Order By s.IDinAccess, a.[labAnalyteID]					
					
C# code, XPath, and others (Data Scraping):
using System;
using HtmlAgilityPack;

namespace _SWAPE
{
    class Program
    {
        static void Main(string[] args)
        {
            HtmlDocument hdoc = new HtmlDocument();
            HtmlNode abcTable;

            hdoc.Load("test1.html");
            abcTable = hdoc.DocumentNode.SelectSingleNode("//table[@summary='ABC']");

            foreach (HtmlNode row in abcTable.SelectNodes("tr"))
            {
                HtmlNodeCollection rowCells = row.SelectNodes("td");

                if (rowCells != null)
                {
                    for (int i = 0; i < rowCells.Count; i += 2)
                    {
						Response.Write(String.Format("{0} | {1} |", rowCells[i].InnerText, rowCells[i + 1].InnerText))
                    }
                }

                Console.WriteLine();
            }

            Console.ReadKey();
        }
    }
}
--------------------------					
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel; 

namespace SWAPE_201502
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string urlAddress = "https://sdw.oph.dhh.la.gov/DWW/JSP/NonTcrSampleResultsGlobal.jsp?detectionFlag=Y&sample_number=%201364347&collection_date=%2001-15-2015&tinwsys_is_number=28&tinwsys_st_code=LA&tsasampl_is_number=278912&tsasampl_st_code=LA";

            HttpWebRequest request = (HttpWebRequest)WebRequest.Create(urlAddress);
            HttpWebResponse response = (HttpWebResponse)request.GetResponse();
                Stream receiveStream = response.GetResponseStream();
                StreamReader readStream = null;

                if (response.CharacterSet == null)
                {
                    readStream = new StreamReader(receiveStream);
                }
                else
                {
                    readStream = new StreamReader(receiveStream, Encoding.GetEncoding(response.CharacterSet));
                }

                string data = readStream.ReadToEnd();
                //string htmlCode = client.DownloadString(urlAddress);
                System.IO.File.WriteAllText(@"C:\Users\Mike\Documents\RobHesse\Feb2015\test.txt", data);

                response.Close();
                readStream.Close();
              
            }

        private void button2_Click(object sender, EventArgs e)
        {
            //http://csharp.net-informations.com/excel/csharp-create-excel.htm
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("Excel is not properly installed!!");
                return;
            }

            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;

            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xlWorkSheet.Cells[1, 1] = "Sheet 1 content";

            xlWorkBook.SaveAs("C:\\Users\\Mike\\Documents\\RobHesse\\Feb2015\\csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Excel file created , you can find the file C:\\Users\\Mike\\Documents\\RobHesse\\Feb2015\\csharp-Excel.xls");
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
        }
 } 
					
					

Sample Websites:   arrow
Developed Performance Dashboard and KP ReConnect applications in ASP.net using C#  and VB.net, respectively. These sites will be available for demo soon.
Kaiser Permanente Decision Support Application, Oracle backend:   arrow
-- Notes:
-- Tags Words: Some comment fields are tagged to facilitate Slice & Dice system.
--             In tDefinition, with category = 'SearchTag' there is a precompiled 
--             tag words (produced by Peggy). An stored procedure will be coded to
--             be triggered (tbd later) to automatically tag these comment fields.
--             The word tag code has been developed in VBA and will be converted to Oracle.
--
--             Tag Word Store Procedure:
--             Scans comment fields, and if a tag word is located, enters the matching ID value in 
--
--             tResponseSpecific.response_Id =  tResponse.response_Id 
--             tResponseSpecific.definition_Id =  tDefinition.definitionId
--             tResponseSpecific.fieldName =  "comment field name being searched"
--			     
--             This procedure, in the meantime, is also expected to concatenate the tagged words 
--             and put it in tResponse.buildIssuesTag for example: 
-- 			   tResponse.buildIssuesTag = the concatenated values returned by: Select Abbreviation From tDefinition
--             inner join tResponseSpecific on definitionId = definition_Id Where 
--             tResponseSpecific.fieldName = 'buildIssuesTag' Order By sort;
--			   
--             Note: This display of the tag words will be used to facilitate manual tagging or untagging later.  

-- create tPeople.
-- users are expected to enter only tPeople.nuid, the rest of the information will be refreshed 
-- nightly or on demand using the existing tables in the Oracle database.  

CREATE TABLE tPeople (
	peopleId			number(6)		not null, 			-- auto generated for internal use
	admin				char(1)			not null default 'N', 
	submitter			char(1)			not null default 'N', 
	operator			char(1)			not null default 'N', 
	builder				char(1)			not null default 'N', 
	nuid				char(7)			not null, 
	firstName			varchar2(50)	null, 
	lastName			varchar2(50)	null, 
	mI      			varchar2(5)	null, 
	email				varchar2(50)	null, 
	amailAlternate			varchar2(50)	null,
	address1			varchar2(50)	null, 
	address2			varchar2(50)	null, 
	City				varchar2(50)	null, 
	State				varchar2(25)	null, 
	zip				varchar2(10)	null, 
	urlBusiness			varchar2(100)	null, 
	urlPeronal			varchar2(100)	null, 
	homephone			varchar2(100)	null, 
	exclude				char(7)			not null, 			-- if Y retires, doesn't show int he combo boxes any more
	officephone			char(12)		null, 
	cellPhone			char(12)		null, 
	fax				char(12)		null, 
	OldRequestId			varchar(20)		not null,			-- ?  not necessary after data conversion is compelted
	--
	constraint ckAdmin check(admin in('Y', 'N')),
	constraint ckSubmitter check(submitter in('Y', 'N')),
	constraint ckOperator check(operator in('Y', 'N')),
	constraint ckBuilder check(builder in('Y', 'N')),
	constraint pk_tPeople primary key (peopleId),
	constraint ckNuid check(nuid like '[A-Z][0-9][0-9][0-9][0-9][0-9]')
)

-- create tPosition
-- one pesron may play multiptle roles in the same or more than one location
-- individual people_Id/credential_Id/role_Id/group_Id/location_Id combination produces a unique 
-- positionId. These 5 fields need to be indexed to proven duplicate position for the same person.******************
-- the application admin to compose credential_Id/role_Id/group_Id/location_Id combination
-- using dropboxes with rowsouce like "Select Abbreviation From tDefinition Where Category = 'Credential' 
-- Order By sort;" for example to make selection for the credential field..
CREATE TABLE tPosition ( 
	positionId			number(6)		not null, 		-- auto generated for internal use
	credential_Id		number(6) 		not null,		-- matches tDefinition.definitionId
	role_Id				number(6)		not null,		-- matches tDefinition.definitionId 
	group_Id			number(6)		not null,		-- matches tDefinition.definitionId 
	location_Id			number(6)		not null,		-- matches tDefinition.definitionId
	people_Id			number(6)		not null,		-- matches tPepole.pepoleId 
	comment				varchar2(1000)	null,
	--
	constraint pk_tPosition primary key (positionId),
	constraint fk_tPosition_people_Id foreign key (people_Id) references tPeople(peopleId)
)

-- create tRequest	
-- to avaoid dupplication request entry, we may be handle it by
-- ndexing dsReqDescriptionTag. Also requestDate filed may have to be renamed
-- to indicate when the record entry has been made. A single entery my be sponsored
-- by different groups each with different request date,******************************
CREATE TABLE tRequest ( 
	requestId			number(6)		not null, 			-- auto generated for internal use
	dsReqDescription	varchar2(1000)	not null, 
	dsReqDescriptionTag	varchar2(500)	not null, 			-- see note titled "Tags Words" 
	submitter_Id2		number(6)		not null, 			-- ?
	sponsorMdLead_Id	number(6)		not null,			-- matches tPosition.positionId  
	submitter_Id		number(6)		not null, 			-- matches tPosition.positionId  
	timeStamp			date			default SYSDATE, 	--stores date and time to the second
	lastEditedOn		date, 
	comment				varchar2(1000)	null,	 			-- ? 		
	oldRequestId		varchar2(25)	null, 				-- ?  not necessary afte data conversion is compelted
	tempReqId			number(6)		null, 				-- ?  not necessary afte data conversion is compelted
	requestDate			date			not null,			-- see notes for this table
	--
	constraint pk_tRequest primary key (requestId),
	constraint fk_tRequest_submitter_Id foreign key (submitter_Id) references tPosition(positionId)
)

-- create tPositionRequest
-- Select definitionId, abbreviation from tDefinition Where Category = 'Hat' produces list of:
-- Leader, co-leader, etc. to be pick by application admin thus storing
-- tPositionRequest.hat_Id   	= tDefinition.definitionId along with
-- tPositionRequest.position_Id = tPosition.positionId
-- tPositionRequest.response_Id = tResponse.responseId 
-- these 3 fields (position_Id, response_Id, hat_Id) need to be indexed to proven 
-- duplicate position for the same person.********************************************
CREATE TABLE tPositionRequest (
	positionRequestId	number(6)		not null, 
	position_Id			number(6)		not null, 		-- matches tPosition.positionId
	response_Id			number(6)		not null, 		-- matches tResponse.responseId
	hat_Id				number(6)		not null,		-- matches tDefinition.definitionId
	--
	constraint pk_tPositionRequest primary key (positionRequestId),
	constraint fk_tPositionRequest_position_Id foreign key (position_Id) references tPosition(positionId),
	constraint fk_tPositionRequest_position_Id foreign key (response_Id) references tResponse(responseId)
)

-- create tDefinition

CREATE TABLE tDefinition ( 
	definitionId		number(6)		not null, 				-- auto generated for internal use
	exclude				char(1)			not null default 'N', 	-- retire, don't show in the combo boxes any more
	sort				varchar2(5)		null default 'Z', 
	abbreviation		varchar2(25)	not null, 
	description			varchar2(100)	not null,, 
	category			varchar2(25)	not null, 
	subCategory			varchar2(25)	not null,
	comment				varchar2(1000)	null,		-- ? could be used for Los_Angles for Los Angles to facilitate tag word search.
	lastEdited			date			null,		-- ? 
	userName			varchar2(50)	null,		-- ? 
	tempId				number(6)		null,		-- ?
	--
	constraint pk_tDefinition primary key (definitionId)
)

-- create tResponse
CREATE TABLE tResponse ( 
	responseId					number(6)		not null,	-- auto generated for internal use
	request_Id					number(6)		not null, 	-- matches tRequest.requestId
	oldRequestId				varchar2(25)	null, 		-- ?
	old_responseId				varchar2(25)	null, 		-- ? 
	reviewDate					date			null, 
	approvalDate				date			null, 
	startDate					date			null, 
	targetDate					date			null, 
	specCallCompletedDate		date			null, 
	concept_POC_Date			date			null, 
	parentSrDate				date			null, 
	buildCompletion_RESC_Date	date			null, 
	buildCompletion_WITS_Date	date			null, 
	buildCompletion_PSUP_Date	date			null, 
	buildCompletion_PROD_Date	date			null, 
	pilotDate					date			null, 
	deploymentDate				date			null, 
	status_Id					number(6)		not null, 	-- matches tDefinition.definitionId
	comment						varchar2(1000)	null, 		-- ?
	temp_sponsorshipGroup_10	varchar2(1000)	null, 		-- ?
	requestorName_11			varchar2(1000)	null, 
	requestorTitle_12			varchar2(1000)	null, 
	dsReqPurpose_15				varchar2(1000)	null, 
	dsReqPurposeTag_15			varchar2(1000)	null, 
	currentPerformanceWoDs_16	varchar2(1000)	null, 
	currentPerformanceWoDsTag_16 varchar2(1000)	null, 
	dsPatientPopulation_17		varchar2(1000)	null, 
	dsPatientPopulationTag_17	varchar2(1000)	null, 
	dsmpactedUsr_19				varchar2(1000)	null, 
	dsmpactedUsrTag_19			varchar2(1000)	null, 
	dsImpactSpecialties_20		varchar2(1000)	null, 
	dsImpactSpecialtiesTag_20	varchar2(1000)	null, 
	dsRelatedToExistingDsTag_21	varchar2(1000)	null, 
	dsRelatedToExistingDs_21	varchar2(1000)	null, 
	dsMethodForSuccess_22		varchar2(1000)	null, 
	dsMethodForSuccessTag_22	varchar2(1000)	null, 
	dsPilot_23					varchar2(1000)	null, 
	dsPilotTag_23				varchar2(1000)	null, 
	dsDeployment_24				varchar2(1000)	null, 
	dsEducation_25				varchar2(1000)	null, 
	dsTraining_26				varchar2(1000)	null, 
	dsMonitor_27				varchar2(1000)	null, 
	dsMonitorTag_27				varchar2(1000)	null, 
	monitoringResultsResponseResponsiblility_28			varchar2(1000)	null, 
	submittedBy					varchar2(50)	null, 
	cluster_Id					number(6)		null, 	-- matches tDefinition.definitionId
	altContactPersonTitle_30	varchar2(1000)	null, 	-- ?
	dsRecommendationScore		varchar2(1000)	null, 
	dsPanelRecommendation		varchar2(1000)	null, 
	dsPanelComment				varchar2(1000)	null, 
	dsIssuesForseen				varchar2(1000)	null, 
	builder						varchar2(1000)	null, 
	setting						varchar2(1000)	null, 
	numericPriority				char(1)			null, 
	buildDoc					varchar2(1000)	null, 
	keyMessage					varchar2(1000)	null, 
	talkingPoint				varchar2(1000)	null, 
	specCallNeeded				varchar2(1000)	null, 
	buildComment				varchar2(1000)	null, 
	buildIssues					varchar2(1000)	null, 
	buildIssuesTag				varchar2(1000)	null, 
	releasedRegionallyComment	varchar2(1000)	null, 
	releasedRegionallyCommentTag varchar2(1000)	null, 
	alignment					varchar2(1000)	null, 
	proofOfConceptRequired		char(1)	not null default 'N', 
	pasAlignmentRequired		char(1)	not null default 'N', 
	pocRequired					char(1)	not null default 'N',
	temp_Requestor_Name			varchar2(250)	null, 
	temp_Lead_MD				varchar2(250)	null, 
	temp_Lead_MD_Title			varchar2(250)	null, 
	temp_Submitter				varchar2(250)	null, 
	temp_Submitter_Title		varchar2(250)	null, 
	chkAgree					char(1)			not null default 'N', 
	isDsRelatedToExistingDsYn	char(1)			not null default 'N', 
	KnownInTheArea				varchar2(1000)	null,
	--
	constraint pk_tResponse primary key (responseId),
	constraint fk_tResponse_request_Id foreign key (request_Id) references tRequest(requestId)
)

-- create tResponseSpecific
CREATE TABLE tResponseSpecific (
	responseSpecificId	number(6)	not null,			-- auto generated for internal use
	definition_Id	number(6)		not null, 			-- matches tDefinition.definitionId
	fieldName 		varchar2(250)	null, 				--
	comment			varchar2(1000)	null,   			--?
	response_Id		number(6)		not null, 			-- matches tResponse.responseId
	startPosition	number(6)		null, 				--?
	abbreviation	varchar2(25)	not null, 			--?
	description		varchar2(100)	not null, 			--?
	position_Id		number(6)		null, 				--?
	fieldValue		varchar2(100)	null, 				--?
	timeStamp		date			default sysdate, 	--?
	definition2_Id	number(6)		null, 				--?
	--
	constraint pk_tResponseSpecific primary key (responseSpecificId),
	constraint fk_tResponseSpecific_response_Id foreign key (response_Id) references tResponse(responseId)
)