[Evergreen-reports] Question about nullability

[Boyer, Jason A]

The good news about location is that depending on the "direction" you're going, there's no need for a special join, it's not possible to have an item with a null location. 

But if you want every location and some may not have items to follow, you have to start from a different source. The copy location table is available from the Sources dropdown, All Available Sources, Copy/Shelving Location. It allows you to do left outer joins against item (because you can't have an item without a location, but you can have a location without items).

Jason

--
Jason Boyer
Indiana State Library
http://library.in.gov/
317-234-2128

-----Original Message-----
From: evergreen-reports-bounces at list.evergreen-ils.org [mailto:evergreen-reports-bounces at list.evergreen-ils.org] On Behalf Of Beth Willis
Sent: Tuesday, April 22, 2014 12:55 PM
To: evergreen-reports at list.evergreen-ils.org
Subject: Re: [Evergreen-reports] Question about nullability

Jason,

Thank you for your reply.  I did not realize that I could select the raw value of the "link" data type as part of my template.  This works well for the report template that I am working on.  I can see that it may not work in all cases, though.

For example, I want to create a similar template using copy location rather than circ modifier.  Again, it appears that it is not possible to create an outer join between the item source and the shelving location source.  If I select the item source and select the raw value for copy location, my report output includes only the copy location id and not the name.  This is not a problem with circ modifiers which do not have ids.

Thanks again.

beth

Beth Willis
Digital & Catalog Librarian
NOBLE, Inc.
26 Cherry Hill Drive
Danvers, MA 01923

On 4/14/2014 3:53 PM, Boyer, Jason A wrote:
> The Source Specifier box in the lower right shows what joins it will choose depending on the "Nullable" column for the link you followed. If you use Default, it doesn't list anything (so you don't know the join without consulting the debug page, they're not all the same.); if the Parent is nullable it is a right outer join; if the is child is nullable it's a left outer; and if None are nullable that's inner. The bad news is that it appears that a right outer join is all you get with circ_mods from copies, which still leaves the null circ mod items out.
>
> I just throw the raw value of circ_modifier from the fields displayed when Item is selected on the left instead of following the Circulation Modifier link. That displays or filters on the code and if that's all you're doing with the circ mod it's one less thing to worry about.
>
> Jason
>
> --
> Jason Boyer
> Indiana State Library
> http://library.in.gov/
> 317-234-2128
>
> -----Original Message-----
> From: evergreen-reports-bounces at list.evergreen-ils.org 
> [mailto:evergreen-reports-bounces at list.evergreen-ils.org] On Behalf Of 
> Beth Willis
> Sent: Monday, April 14, 2014 11:51 AM
> To: evergreen-reports >> Evergreen Community Reports Email List
> Subject: [Evergreen-reports] Question about nullability
>
> I am working on a report template that uses asset.copy as the initial source.  From there, I want to create joins to asset.call_number, config.circ_modifier, and config.copy_status sources.
>
> I need to create an outer join to the config.circ_modifier source because some of our copy records have null values for circ_modifier.
> But, I cannot seem to generate SQL with this outer join.
>
> As I understand it, if I select the "default" option I should get an inner join, but if I select the "none" option I should get an outer join.  This is not the case.
>
> Has anyone else run into this?  If so, can you tell me what I am doing incorrectly?  I have attached the SQL to this e-mail.
>
> Thanks in advance for any assistance.
> Beth
>
>
> --
> Beth Willis
> Digital & Catalog Librarian
> NOBLE, Inc.
> 26 Cherry Hill Drive
> Danvers, MA 01923
> _______________________________________________
> Evergreen-reports mailing list
> Evergreen-reports at list.evergreen-ils.org
> http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-repor
> ts
>
_______________________________________________
Evergreen-reports mailing list
Evergreen-reports at list.evergreen-ils.org
http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-reports