Arctos Report SQL Request: set of conditional SQL needed for MSB Mamm labels

[mkoo]

listing all the needed help after meeting with Adrienne (let me know if you want separate issues, too, @dustymc )

For fluid tags (https://arctos.database.museum/Reports/reporter.cfm?action=edit&report_id=147) 1) Right now we have a list of measurements as attributes:

concatattributevalue(flat.collection_object_id,'total length') attribute1,
        concatattributevalue(flat.collection_object_id,'tail length') attribute2,
        concatattributevalue(flat.collection_object_id,'hind foot with claw') attribute3,
        concatattributevalue(flat.collection_object_id,'ear from notch') attribute4,
        concatattributevalue(flat.collection_object_id,'weight') attribute5,

These need to be display units only if NOT "mm" and if NULL, replace with "X"

2) Currently we select parts from flat, but we need to distinguish between tissues and nontissues (eg skull, skin). IF any tissues are present, then print "tissues" And do not add any preservation value with parts (part value seems to occur parenthetically with part)

So I dont know what's the lesser of evils: a list of things that are 'tissues' or a list of things that are NOT 'tissues' since this is impacted by our CT mess (eg. https://arctos.database.museum/info/ctDocumentation.cfm?table=ctspecimen_part_name#heart__kidney__lung) I think I should give you a list of "not tissues" for now but let me know about this one....

let me know if this makes sense or not! Thx!

[dustymc]

display units only if NOT "mm" and if NULL, replace with "X"

That's better done with something that isn't SQL, and there's no reason to wrap things up into a string if they're going to be unwrapped.

And I'm not seeing anything like that in https://arctos.database.museum/Reports/reporter.cfm?action=edit&report_id=147??

ASSUMING one assertion per type per record, do something like:

<cfquery name="d" datasource="user_login" username="#session.dbuser#" password="#decrypt(session.epw,session.sessionKey,'AES/CBC/PKCS5Padding','hex')#">
select
    flat.scientific_name,
    tl.attribute_value length,
    tl.attribute_units length_unit
from
    flat
    inner join #table_name# on flat.collection_object_id=#table_name#.collection_object_id
    left outer join attributes tl on flat.collection_object_id=tl.collection_object_id and tl.attribute_type='total length'
</cfquery>

<cfoutput>
    <cfloop query="d">
        <hr>
        #d.scientific_name#
        <cfif len(d.length) is 0>
            X
        <cfelseif d.length_unit is 'mm'>
            #d.length#
        <cfelse>
            #d.length##d.length_unit#
        </cfif>
    </cfloop>
</cfoutput>

if you can't make that assumption then let me know if you want to deal with cardinality or concatenations.

Or the easier option is probably to grab flat.attributedetail and pull whatever you need out of the JSON, let me know if you want to explore anything there.

IF any tissues are present, then print "tissues"

if has_tissues > 0...

And do not add any preservation value with parts

That's going to be the same as attributes, don't start with the concatenation if you want to manipulate the bits. I'd need a LOT more detail to write any SQL, or flat.partdetail is full data as JSON and again might be the easier pathway (if anyone likes structs, anyway!)

[dustymc]

want unique part names as string

[dustymc]

select
    flat.scientific_name,
    p.parts
from
    flat
    left outer join (
        select derived_from_cat_item,string_agg(distinct part_name,', ' order by part_name) parts from specimen_part group by derived_from_cat_item
    ) p on flat.collection_object_id=p.derived_from_cat_item
where cat_num='12'

[mkoo]

select
    flat.scientific_name,
    p.parts
from
    flat
    left outer join (
        select derived_from_cat_item,string_agg(distinct part_name,', ' order by part_name) parts from specimen_part group by derived_from_cat_item
    ) p on flat.collection_object_id=p.derived_from_cat_item
where cat_num='12'

ok this works (parts no preservation) but now (yes there's more) how do I have all the parts that are flagged as tissues appear as simply 'tissues' ? In other words, they dont want to have "kidney, spleen, liver" listed just "tissue". Of course if it says "tissue", put that. This saves space on the tiny labels (CT is still a mess).

Eg. typical entry might be "skin, skull, tissues"

[mkoo]

oh shoot I didnt try to has_tissue>0 option... just saw that and probably/might will work?!

[dustymc]

skin, skull, tissues

select
    flat.scientific_name,
    flat.has_tissues,
    string_agg(distinct part_name,', ' order by part_name) || case when flat.has_tissues > 0 then ', tissues' else '' end partsishkinda
from
    flat
    left outer join specimen_part on flat.collection_object_id=specimen_part.derived_from_cat_item and  is_part_tissue(specimen_part.collection_object_id) is false
where 
    upper(cat_num)='12' 
group by 
    flat.scientific_name,
    flat.has_tissues
;

[dustymc]

Done?