search

jakegross808 / pacn-veg-package

all pacn veg code
Creative Commons Zero v1.0 Universal
1 stars 1 forks source link

Add cycle column in initial data import #23

Closed wright13 closed 1 month ago

jakegross808 commented 3 years ago

I'm having an issue with adding cycle column to the EIPS database. I keep getting following error from lines 525-551:

` Error: nanodbc/nanodbc.cpp:1655: HY000: [Microsoft][ODBC Microsoft Access Driver] Circular reference caused by alias 'S_Cycle' in query definition's SELECT list.

'SELECT `Year`, `S_Cycle`, `Unit_Code`, `Sampling_Frame`, `Transect_Type`, `Transect_Number`, `Entered_Date`, `Updated_Date`, `Verified`, `Verified_By`, `Verified_Date`, `Certified`, `Certified_By`, `Certified_Date`, `Transect_Notes`, `Event_Notes` FROM (SELECT `Event_ID`, `Transect_ID`, `Start_Date`, `End_Date`, `Start_Time`, `End_Time`, `Entered_By`, `Entered_Date`, `Updated_By`, `Updated_Date`, `Verified`, `Verified_By`, `Verified_Date`, `Certified`, `Certified_By`, `Certified_Date`, `Year`, `S_Cycle`, `Location_ID`, `Transect_Type`, `Transect_Number`, `Azimuth_Transect`, `Lat`, `Lat_Dir`, `Long`, `Long_Dir`, `GCS`, `LHS`.`Site_ID` AS `Site_ID`, `Community`, `Sampling_Frame`, `Zone`, `Management_Unit`, `Event_Notes`, `Transect_Notes`, `Unit_Code`, `Site_Name` FROM (SELECT `Event_ID`, `Transect_ID`, `Start_Date`, `End`
jakegross808 commented 3 years ago

I think it is something microsoft access is doing when I create the 'Cycle' column

I tried changing the column name thinking that the name 'Cycle' was causing some sort of problem. So I changed column name to 'S_Cycle' but that didn't fix problem.

open to any ideas... I tried to troubleshoot for awhile now and I'm stuck.

wright13 commented 3 years ago

Just pushed a fix to the review_utils branch. It's an MS Access thing where a calculated column can't reference itself, so the fix was to use nested ifelse in a single mutate instead of referencing the cycle column each time. Definitely not an obvious bug - I've run into this with Access in the past so the error was familiar to me!