Reporter SQL Assistance

[ebraker]

I am trying to generate labels and receive an error for one of my reports:

The error happens when I access this form: http://reports.arctos.database.museum/reporter/reporter.cfm?action=edit&report_id=5601167&auth_key=5E240F92-F0EF-4B31-A2DB3856F6DB5262

I think it could be due to the recent parsing of part and preservation method, though my other jar reports are working that use similar lines of code (meaning they output a pdf, but do not include the preservation method due to the part/preservation split).

Here's the label that is giving me issue. Please let me know how to fix (replace part_name with preservation_method?).

select flat.scientific_name, flat.family, case when flat.country='Democratic Republic of the Congo' then 'Dem. Rep. Congo' when flat.country='Papua New Guinea' then 'PNG' when flat.country is NULL then 'no higher geography data' when flat.country='Something Else' then 'replacement string' else flat.country END country, case when flat.state_prov like '%Autonomous Region%' then trim(replace(flat.state_prov,'Autonomous Region')) when flat.state_prov='Chimbu Province' then 'Chimbu' when flat.state_prov='Gafsa Governorate' then 'Gafsa Gov.' when flat.state_prov='Guangdong Province' then 'Guangdong' when flat.state_prov='Stann Creek District' then 'Stann Creek Dist.' when flat.state_prov='Languedoc-Roussillon' then 'Languedoc-Rouss.' when flat.state_prov='Something Else' then 'replacement string' else flat.state_prov END state_prov, flat.spec_locality, flat.county, flat.cat_num, case when part_name='whole organism (formalin-fixed, 70% ethanol)' then '70% Ethanol' when part_name='body parts (ethanol)' then '70% Ethanol' when part_name='whole organism (formalin-fixed, 95% ethanol)' then '95% Ethanol' when part_name='whole organism (50% isopropanol)' then '50% isopropanol' else part_name end part_name, contains_part_container.label container_label FROM flat, specimen_part, coll_obj_cont_hist, container part_container, container contains_part_container WHERE flat.collection_object_id=specimen_part.derived_from_cat_item and specimen_part.collection_object_id=coll_obj_cont_hist.collection_object_id and coll_obj_cont_hist.container_id=part_container.container_id and part_container.parent_container_id=contains_part_container.container_id and contains_part_container.container_id in (#container_id#)

[dustymc]

trim(replace(flat.state_prov,'Autonomous Region',''))

PG-flavor replace just doesn't have an implicit "with nothing"

[ebraker]

Thank you! That works. Last thing...

Does when part_name='whole organism (formalin-fixed, 70% ethanol)' then '70% Ethanol' become when preservation_method='ethanol, 70%' then '70% Ethanol'

?

[ebraker]

p.s. I guess I can no longer open cfr files? pay-to-play...

[dustymc]

If you join to part attributes you're going to get a row for each attribute. I'm not sure the query makes sense to me, but maybe you can use a function if at least one attribute triggers your replacement - something like this as your case statements:

when concatPartAttributes(specimen_part.collection_object_id) like '%ethanol, 70%' then '70% Ethanol'

or

when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol, 70%' then '70% Ethanol'

should work if you need to consider part and the attribute.

Let me know (and provide a container_id) if that doesn't make sense, I'll do something and we can go from there.

Not sure what you mean about the cfrs - you can (allegedly!) get the builder from https://www.adobe.com/support/coldfusion/downloads.html

[ebraker]

I can only download the trial version. And apparently it doesn't have all the goods since my jar template now looks like wingdings:

My old labels used to have part.name built into the cold fusion template, and paired with the code above, it would display as, e.g., "70% ethanol". I tried to insert your code but it is defaulting to the 'else part_name' statement:

report: http://reports.arctos.database.museum/reporter/reporter.cfm?action=edit&report_id=5601159&auth_key=5E240F92-F0EF-4B31-A2DB3856F6DB5262

[dustymc]

Try

Download ColdFusion Report Builder (EXE, 52.8 MB)

Adobe ain't great at namin' stuff, there's at least one unrelated "Builder."

Give me your current/latest SQL and a container_id and I'll see what I can figure out.

[ebraker]

Thank you!!!!!

https://arctos.database.museum/EditContainer.cfm?container_id=19672660

select flat.scientific_name, flat.family, case when flat.country='Democratic Republic of the Congo' then 'Dem. Rep. Congo' when flat.country='Papua New Guinea' then 'PNG' when flat.country is NULL then 'no higher geography data' when flat.country='Something Else' then 'replacement string' else flat.country END country, case when flat.state_prov like '%Autonomous Region%' then trim(replace(flat.state_prov,'Autonomous Region','')) when flat.state_prov='Chimbu Province' then 'Chimbu' when flat.state_prov='Gafsa Governorate' then 'Gafsa Gov.' when flat.state_prov='Guangdong Province' then 'Guangdong' when flat.state_prov='Stann Creek District' then 'Stann Creek Dist.' when flat.state_prov='Languedoc-Roussillon' then 'Languedoc-Rouss.' when flat.state_prov='Something Else' then 'replacement string' else flat.state_prov END state_prov, flat.spec_locality, flat.county, flat.cat_num, case when part_name='whole organism (formalin-fixed, 70% ethanol)' then '70% Ethanol' when part_name='body parts (ethanol)' then '70% Ethanol' when part_name='whole organism (formalin-fixed, 95% ethanol)' then '95% Ethanol' when part_name='whole organism (50% isopropanol)' then '50% isopropanol' else part_name end part_name, contains_part_container.label container_label FROM flat, specimen_part, coll_obj_cont_hist, container part_container, container contains_part_container WHERE flat.collection_object_id=specimen_part.derived_from_cat_item and specimen_part.collection_object_id=coll_obj_cont_hist.collection_object_id and coll_obj_cont_hist.container_id=part_container.container_id and part_container.parent_container_id=contains_part_container.container_id and contains_part_container.container_id in (#container_id#)

[ebraker]

Sweet relief that version of coldfusion builder works!

[ebraker]

also, here is the updated SQL code (I accidently sent you the old one above):

select flat.scientific_name, flat.family, case when flat.country='Democratic Republic of the Congo' then 'Dem Rep Congo' when flat.country is NULL then 'no higher geography data' when flat.country='Something Else' then 'replacement string' else flat.country END country, case when flat.state_prov like '%Autonomous Region%' then trim(replace(flat.state_prov,'Autonomous Region','')) when flat.state_prov='Guangdong Province' then 'Guangdong' when flat.state_prov='Something Else' then 'replacement string' else flat.state_prov END state_prov, flat.spec_locality, flat.county, flat.cat_num, case when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol, 70%' then '70% Ethanol' when part_name='body parts' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol' then '70% Ethanol' when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol, 95%' then '95% Ethanol' when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%isopropanol, 50%' then '50% isopropanol' else part_name end part_name, contains_part_container.label container_label FROM flat, specimen_part, coll_obj_cont_hist, container part_container, container contains_part_container WHERE flat.collection_object_id=specimen_part.derived_from_cat_item and specimen_part.collection_object_id=coll_obj_cont_hist.collection_object_id and coll_obj_cont_hist.container_id=part_container.container_id and part_container.parent_container_id=contains_part_container.container_id and contains_part_container.container_id in (#container_id#)

[dustymc]

I think you did what I did, you're just missing a % on '%ethanol' (that's "ends with" not "contains") - let me know if this is what you need or if it needs more adjsutment

select
flat.scientific_name,
flat.family,
case
when flat.country='Democratic Republic of the Congo' then 'Dem. Rep. Congo'
when flat.country='Papua New Guinea' then 'PNG'
when flat.country is NULL then 'no higher geography data'
when flat.country='Something Else' then 'replacement string'
else flat.country
END country,
case
when flat.state_prov like '%Autonomous Region%' then trim(replace(flat.state_prov,'Autonomous Region',''))
when flat.state_prov='Chimbu Province' then 'Chimbu'
when flat.state_prov='Gafsa Governorate' then 'Gafsa Gov.'
when flat.state_prov='Guangdong Province' then 'Guangdong'
when flat.state_prov='Stann Creek District' then 'Stann Creek Dist.'
when flat.state_prov='Languedoc-Roussillon' then 'Languedoc-Rouss.'
when flat.state_prov='Something Else' then 'replacement string'
else flat.state_prov
END state_prov,
flat.spec_locality,
flat.county,
flat.cat_num,
case
when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol, 70%' then '70% Ethanol' 
when part_name='body parts' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol%' then '70% Ethanol' 
when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%formalin%' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol, 95%%' then '95% Ethanol' 
when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%isopropanol, 50%' then '50% isopropanol' 
else part_name
end part_name,
contains_part_container.label container_label
FROM
flat,
specimen_part,
coll_obj_cont_hist,
container part_container,
container contains_part_container
WHERE
flat.collection_object_id=specimen_part.derived_from_cat_item and
specimen_part.collection_object_id=coll_obj_cont_hist.collection_object_id and
coll_obj_cont_hist.container_id=part_container.container_id and
part_container.parent_container_id=contains_part_container.container_id
and contains_part_container.container_id in (#container_id#)

      scientific_name      |   family   | country | state_prov |              spec_locality               | county | cat_num |  part_name  | container_label 
---------------------------+------------+---------+------------+------------------------------------------+--------+---------+-------------+-----------------
 Storeria occipitomaculata | Colubridae | Canada  | Ontario    | Shawanaga Township, Parry Sound District |        | 931     | 70% Ethanol | HERP JAR 2864

[ebraker]

Got it! Thank you so much!

[ebraker]

For some reason a different label size is balking at the code edits you sent me (3 out of 4 labels saved just fine):

http://reports.arctos.database.museum/reporter/reporter.cfm?action=edit&report_id=5601168&auth_key=72790D1A-BFAA-4E2A-9FE8A72BC629B4BE

select flat.scientific_name, flat.family, flat.country, case when flat.state_prov is NULL then 'no higher geography data' when flat.state_prov='Something Else' then 'replacement string' else flat.state_prov END state_prov, flat.spec_locality, case when flat.county='Arapahoe County' then 'Arapahoe' when flat.county='Allegheny County' then 'Allegheny' when flat.county='Barnstable County' then 'Barnstable' when flat.county='Benton County' then 'Benton' when flat.county='Boulder County' then 'Boulder'
when flat.county='Bucks County' then 'Bucks' when flat.county='Carroll County' then 'Carroll' when flat.county='Catron County' then 'Catron' when flat.county='Clear Creek County' then 'Clear Creek' when flat.county='Cheboygan County' then 'Cheboygan' when flat.county='Cheyenne County' then 'Cheyenne' when flat.county='Chisago County' then 'Chisago' when flat.county='Crowley County' then 'Crowley' when flat.county='Delaware County' then 'Delaware' when flat.county='Dolores County' then 'Dolores' when flat.county='Douglas County' then 'Douglas' when flat.county='Durham County' then 'Durham' when flat.county='East Carroll Parish' then 'East Carroll' when flat.county='Fairfax County' then 'Fairfax' when flat.county='Garfield County' then 'Garfield' when flat.county='Gunnison County' then 'Gunnison' when flat.county='Hancock County' then 'Hancock' when flat.county='Harrison County' then 'Harrison' when flat.county='Hinsdale County' then 'Hinsdale' when flat.county='Huerfano County' then 'Huerfano' when flat.county='Imperial County' then 'Imperial' when flat.county='Jackson County' then 'Jackson' when flat.county='Jefferson County' then 'Jefferson' when flat.county='Kit Carson County' then 'Kit Carson' when flat.county='Lincoln County' then 'Lincoln' when flat.county='Mason County' then 'Mason' when flat.county='Middlesex County' then 'Middlesex' when flat.county='La Plata County' then 'La Plata' when flat.county='Las Animas County' then 'Las Animas' when flat.county='Montezuma County' then 'Montezuma' when flat.county='Montgomery County' then 'Montgomery' when flat.county='Montrose County' then 'Montrose' when flat.county='Prowers County' then 'Prowers' when flat.county='Rio Blanco County' then 'Rio Blanco' when flat.county='Riverside County' then 'Riverside' when flat.county='San Diego County' then 'San Diego' when flat.county='Santa Clara County' then 'Santa Clara' when flat.county='Santa Cruz County' then 'Santa Cruz' when flat.county='Socorro County' then 'Socorro' when flat.county='Something Else' then 'replacement string' else flat.county END county, flat.cat_num, case case when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol, 70%' then '70% Ethanol' when part_name='body parts' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol%' then '70% Ethanol' when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%formalin%' and concatPartAttributes(specimen_part.collection_object_id) like '%ethanol, 95%%' then '95% Ethanol' when part_name='whole organism' and concatPartAttributes(specimen_part.collection_object_id) like '%isopropanol, 50%' then '50% isopropanol' else part_name end part_name, contains_part_container.label container_label FROM flat, specimen_part, coll_obj_cont_hist, container part_container, container contains_part_container WHERE flat.collection_object_id=specimen_part.derived_from_cat_item and specimen_part.collection_object_id=coll_obj_cont_hist.collection_object_id and coll_obj_cont_hist.container_id=part_container.container_id and part_container.parent_container_id=contains_part_container.container_id and contains_part_container.container_id in (#container_id#)

[dustymc]

ALTER TABLE cf_report_sql ALTER COLUMN sql_text TYPE varchar;

Should work now, at which point you'll officially have the longest report in Arctos!

[ebraker]

Do I get a ribbon? ;)

Thanks!

[dustymc]

I was thinkin' a very looooooong bear.....