Is there an equivalent of datatool's \DTLifhaskey for value?

Question

I'm looking for a way to test if a (non-key) value in a column does exist. I basically want to check if

\DTLfetch{db name}{col1 name}{col1 value}{col2 name}

is giving me a value from the db or an 'Undefined Value' because 'col1 value' does not exist in this column. Not because a specific value in the column is null but because the queried value is just not part of the db. datatool has a function

\DTLifhaskey{db name}{key}{true}{false}

to check for the 'col1 name' and 'col2 name' but I can't seem to find a simple way to check for the 'col1 value'.

I tried to catch the 'Undefined Value' return as well but nothing worked so far and at the point it returns 'Undefined Value', it already throws the error as well. So recognizing the queried value 'col1 value' is not part of the csv before fetch is called would be preferable.

Answer 1

The macro

 \DTLgetvalueforkey{⟨cmd⟩}%
                   {⟨column2 name⟩}%
                   {⟨db name⟩}%
                   {⟨column1 name⟩}%
                   {⟨column1 value⟩}%

in the database ⟨db name⟩ seeks the row where the column whose name is ⟨column1 name⟩ the first time holds the value ⟨column1 value⟩. In that row the value belonging to ⟨column2 name⟩ is looked up and the macro ⟨cmd⟩ is defined to expand to that value.

In case ⟨column1 value⟩ cannot be found for whatever reason, the macro ⟨cmd⟩ is defined to yield \@dtlnovalue (, which in turn usually expands to Undefined Value).

Therefore ⟨cmd⟩ can be used and/or be examined via \DTLifnull{⟨cmd⟩}{⟨true part⟩}{⟨false part⟩}.

Thus instead of

\DTLfetch{⟨db name⟩}%
         {⟨column1 name⟩}%
         {⟨column1 value⟩}%
         {⟨column2 name⟩}%

you can do something like

\DTLgetvalueforkey{⟨cmd⟩}%
                  {⟨column2 name⟩}%
                  {⟨db name⟩}%
                  {⟨column1 name⟩}%
                  {⟨column1 value⟩}%
⟨cmd⟩

or

\DTLgetvalueforkey{⟨cmd⟩}%
                  {⟨column2 name⟩}%
                  {⟨db name⟩}%
                  {⟨column1 name⟩}%
                  {⟨column1 value⟩}%
\DTLifnull{⟨cmd⟩}{⟨tokens in case of value not being available⟩}{⟨cmd⟩}%

or

\DTLgetvalueforkey{⟨cmd⟩}%
                  {⟨column2 name⟩}%
                  {⟨db name⟩}%
                  {⟨column1 name⟩}%
                  {⟨column1 value⟩}%
\DTLifnull{⟨cmd⟩}{⟨tokens in case of value not being available⟩}%
                 {⟨tokens in case of value being available⟩}%

---

---

If you insist in catching the situation "manually" so you can introduce branches for different reasons for the database-value in question not being available:

\show\DTLfetch yields:

> \DTLfetch=\long macro:
#1#2#3#4->\edtlgetrowforvalue {#1}{\dtlcolumnindex {#1}{#2}}{#3}\dtlgetentryfro
mcurrentrow {\dtlcurrentvalue }{\dtlcolumnindex {#1}{#4}}\dtlcurrentvalue .

Example 21 (Joining Two Databases in a Single Table) of the User Manual for datatool bundle version 2.32 says:

\DTLfetch{students}⁠{regnum}⁠{\RegNum}⁠{forename}
is equivalent to
\edtlgetrowforvalue{students}⁠{\dtlcolumnindex{students}⁠{regnum}}⁠{\RegNum}%
\dtlgetentryfromcurrentrow{\dtlcurrentvalue}⁠{\dtlcolumnindex{students}⁠{f⁠o⁠r⁠e⁠n⁠a⁠m⁠e⁠}⁠}%
\dtlcurrentvalue

Section "6.11.1 Operating on Current Row" of the User Manual for datatool bundle version 2.32 says:

\dtlgetrowindex{⟨row cs⟩}{⟨db name⟩}{⟨col idx⟩}{⟨value⟩}
Similar to \DTLgetrowindex but doesn't produce an error if no match is found. You can test the result by using \ifx⟨row cs⟩\dtlnovalue.

Instead of having \dtlcurrentvalue delivered you can test via \DTLifnull{⟨cmd⟩}{⟨true part⟩}{⟨false part⟩} whether \dtlcurrentvalue yields a null value and via \ifx⟨row cs⟩\dtlnovalue whether ⟨row cs⟩ is a null value:

%\DTLfetchifnull{<db name>}%
%               {<column1 name>}%
%               {<column1 value>}%
%               {<column2 name>}%
%               {<tokens in case <column2-value> is not a null-value>}%
%               {<tokens in case <column2-value> is a null-value>}%
%               {<tokens in case <column1-value> does not exist>}%
%
% Both with <tokens in case <column2-value> is not a null-value> and with
% <tokens in case <column2-value> is a null-value> the macro
% \dtlcurrentvalue is defined to yield the value of the column whose name
% is <column2 name>.
%
\makeatletter
\@ifundefined{NewDocumentCommand}%
{\newcommand\DTLfetchifnull[7]}%
{\NewDocumentCommand\DTLfetchifnull{mmmmmmm}}%
{%
  \begingroup
  \protected@edef\@dtl@dogetrowforvalue{%
    \endgroup\noexpand\dtlgetrowindex{\noexpand\dtl@rowidx}{#1}{\dtlcolumnindex{#1}{#2}}{#3}%
  }\@dtl@dogetrowforvalue
  \ifx\dtl@rowidx\dtlnovalue\expandafter\@secondoftwo\else\expandafter\@firstoftwo\fi
  {%
    \edtlgetrowforvalue{#1}{\dtlcolumnindex{#1}{#2}}{#3}%
    \dtlgetentryfromcurrentrow{\dtlcurrentvalue}{\dtlcolumnindex{#1}{#4}}%
    \DTLifnull{\dtlcurrentvalue}{#6}{#5}%
  }{%
    #7%
  }%
}%
\makeatother

The following example illustrates the usage of \DTLfetchifnull:

%=====Let LaTeX create a .csv-file holding a database.=====================
%     This could as well be done via export to .csv-file by a
%     professional database management system

\begin{filecontents*}{database.csv}
PrimaryKey,FirstName,LastName
1,Homer,Simpson
2,Ned,Flanders
3,The Crazy Cat Lady
4,Clancy,Wiggum
5,Moe,Szyslak
6,Barney,Gumble
\end{filecontents*}

%==========================================================================

\documentclass{article}
\usepackage{datatool}

%\DTLfetchifnull{<db name>}%
%               {<column1 name>}%
%               {<column1 value>}%
%               {<column2 name>}%
%               {<tokens in case <column2-value> is not a null-value>}%
%               {<tokens in case <column2-value> is a null-value>}%
%               {<tokens in case <column1-value> does not exist>}%
%
% Both with <tokens in case <column2-value> is not a null-value> and with
% <tokens in case <column2-value> is a null-value> the macro
% \dtlcurrentvalue is defined to yield the value of the column whose name
% is <column2 name>.
%
\makeatletter
\@ifundefined{NewDocumentCommand}%
{\newcommand\DTLfetchifnull[7]}%
{\NewDocumentCommand\DTLfetchifnull{mmmmmmm}}%
{%
  \begingroup
  \protected@edef\@dtl@dogetrowforvalue{%
    \endgroup\noexpand\dtlgetrowindex{\noexpand\dtl@rowidx}{#1}{\dtlcolumnindex{#1}{#2}}{#3}%
  }\@dtl@dogetrowforvalue
  \ifx\dtl@rowidx\dtlnovalue\expandafter\@secondoftwo\else\expandafter\@firstoftwo\fi
  {%
    \edtlgetrowforvalue{#1}{\dtlcolumnindex{#1}{#2}}{#3}%
    \dtlgetentryfromcurrentrow{\dtlcurrentvalue}{\dtlcolumnindex{#1}{#4}}%
    \DTLifnull{\dtlcurrentvalue}{#6}{#5}%
  }{%
    #7%
  }%
}%
\makeatother

\DTLloadrawdb{Simpsons}{database.csv}

\begin{document}

\DTLfetchifnull{Simpsons}%
               {FirstName}%
               {The Crazy Cat Lady}%
               {LastName}%
               {The last name of the Crazy Cat Lady is \dtlcurrentvalue.}%
               {The Crazy Cat Lady dosen't have a last name.}%
               {There is no  First Name ``Crazy Cat Lady'' in the database.}%


\DTLfetchifnull{Simpsons}%
               {FirstName}%
               {Homer}%
               {LastName}%
               {The last name of Homer is \dtlcurrentvalue.}%
               {Homer dosen't have a last name.}%
               {There is no  First Name ``Homer'' in the database.}%


\DTLfetchifnull{Simpsons}%
               {FirstName}%
               {Marge}%
               {LastName}%
               {The last name of Marge is \dtlcurrentvalue.}%
               {Marge dosen't have a last name.}%
               {There is no  First Name ``Marge'' in the database.}%


\end{document}