Jkuroda: template

2015-10-20T04:13:32Z

template

Jkuroda: edits

2015-10-15T05:12:58Z

edits

Show changes

Jkuroda: first edit

2015-10-15T03:31:28Z

first edit

New page

==Creating Tables==

To begin the assignment, I first downloaded pgAdmin3 to my computer and created my own database with which I could work. I used the curl command in the terminal to download the FDA files to my directory in <code>my.cs.lmu.edu</code> then I set up the tables for both the application and product lists using the following commands:
create table application (id int primary key, applicationType varchar, sponsorApplicant varchar,
mostRecentLabelAvailableFlag boolean, currentPatentFlag boolean, actionType varchar, chemicalType int,
therPotential varchar, orphanCode varchar)

create table product (int references application(id), productNumber int, form varchar, dosage varchar,
productMktStatus int, TECode varchar, referenceDrug int, drugName varchar, activeIngredient varchar)

==SQL Insert Statements==

Now I had to get the data from the FDA into these formatted tables, so I drew upon my experience from using the command line in previous weeks and came up with the following:

cat application.txt | sed "1D" | sed "s/\t/','/g" | sed "s/\r$/');/g" | sed "s/ *'/'/g" |
sed "s/''/null/g" | sed "s/'//1" | sed "s/^/insert into
application(id,applicationType,sponsorApplicant,mostRecentLabelAvailableFlag,
currentPatentFlag,actionType,chemicalType,therPotential,orphanCode)/g" > ~/public_html/application.sql.txt

← Older revision		Revision as of 04:13, 20 October 2015
Line 344:		Line 344:
	select sponsorApplicant from application inner join product on (product.applNumber = application.id) where		select sponsorApplicant from application inner join product on (product.applNumber = application.id) where
	(sponsorApplicant like '%LABS%' and product.activeIngredient like '%CAFFEINE%' and activeIngredient like '%ASPIRIN%')		(sponsorApplicant like '%LABS%' and product.activeIngredient like '%CAFFEINE%' and activeIngredient like '%ASPIRIN%')
		+
		+	{{Template:Journal Template}}

Jkuroda Week 6 - Revision history

Jkuroda: template

Jkuroda: edits

Jkuroda: first edit