I should know better, but I made a rookie mistake with SQL that took a long time for me to fix. It’s one of those detail oriented things and if you aren’t detail oriented, you can’t call yourself a programmer.

I was running some SQL code within R, and I could not get what I wanted.

> ms <- dbGetQuery(c_connect,
+ "select patient_num start_date from blueherondata.observation_fact
+ where concept_cd LIKE 'CPT:1930%'")

was giving me just a single column for start_date, and the start_date values looked nothing like a date. Was I spelling a variable wrong? Did I get the wrong capitalization?

> ms <- dbGetQuery(c_connect,
+ "select patient_num from blueherondata.observation_fact
+ where concept_cd LIKE 'CPT:1930%'")

worked just fine, and

> ms <- dbGetQuery(c_connect,
+ "select start_date from blueherondata.observation_fact
+ where concept_cd LIKE 'CPT:1930%'")

but when you ask for more than one field, you need to separate those fields with a comma. If you don’t then this version of SQL considers

> ms <- dbGetQuery(c_connect,
+ "select patient_num start_date from blueherondata.observation_fact
+ where concept_cd LIKE 'CPT:1930%'")

identical to

> ms <- dbGetQuery(c_connect,
+ "select patient_num as start_date from blueherondata.observation_fact
+ where concept_cd LIKE 'CPT:1930%'")

So it was extracting the patient_num field and changing its name to start_date. Boy did I feel stupid when I finally realized my mistake. Those pesky commas are important.

I’m hoping that if I write down my mistake and publish it widely, I won’t forget and make the same mistake again next week.