Sierra DNA: SQL query in Catalog

Generally, when we have a fixed field in a record that has a code and some description, there are a trio of views: x_property, x_property_name and x_property_myuser.  x_property relates the code to an internal database id.  x_property_name relates the internal database id to a description in some language.  x_property_myuser relates the code to the description in the language of the database user (set in Sierra Admin for the Sierra user your using to connect to the database).

If you try to the internal database id, you’ll get the wrong description; as you’ve seen.

Changing your SQL to use ptype_property_myuser, it becomes:

SELECT

   p.barcode                      AS barcode,

   p.ptype_code                   AS ptype,

   pt.name                        AS ptype_name,

   n.first_name                   AS first_name,

   n.last_name                    AS last_name

FROM sierra_view.varfield pz

 JOIN sierra_view.patron_view p

     ON pz.record_id = p.id

 JOIN sierra_view.ptype_property_myuser pt

     ON p.ptype_code = pt.value

 JOIN sierra_view.patron_record_fullname n

     ON n.patron_record_id = p.id

WHERE

 pz.varfield_type_code = 'z' AND UPPER(pz.field_content) LIKE UPPER('$email')

GROUP BY

  1,2,3,4,5

Note: If you start getting no rows at all, check that the Language for the Sierra user you are using is not set to None in Sierra Admin.

 Sierra listserv post from Jim Nicholls