I get the following error:
An error has occurred. Problem Executing search query: Error #00928: missing
SELECT keyword
When I try to run the following SQL Statement (this is from the log file. i
have run this from the command line and it works find)
SELECT incident.incident_key AS Incident_Key,
incident_type.description AS Incident_Type,
status.description AS Status,
client.description AS Client,
program.description AS Program,
contact.fname || ' ' || contact.lname AS Contact,
SQL.username AS Assigned_To
FROM (((((incident INNER JOIN incident_type ON incident.incident_type_key
= incident_type.incident_type_key)
INNER JOIN status ON incident.status_key = status.status_key)
INNER JOIN program ON incident.program_key =
program.program_key)
INNER JOIN contact ON incident.contact_key =
contact.contact_key)
INNER JOIN client ON program.client_key = client.client_key)
INNER JOIN ( (SELECT unanet_employee.username,
assignment.incident_key,
assignment.employee_key
FROM assignment INNER JOIN
unanet_employee ON assignment.employee_key = unanet_employee.employee_key
WHERE assignment.iscurrent_yn = 'Y' and
assignment.employee_key = 27) SQL) ON incident.incident_key =
sql.incident_key
here is the embperl code that I am using to build the above:
[$ if ($action eq "search") $]
[-
#-------------------
# identify the html tags that have the search criteria
#-------------------
@searchFieldArray =
(
'incident.incident_type_key',
'incident.status_key',
'incident.program_key',
'incident.contact_key',
'incident.logged_employee_key'
);
#-------------------
# identify what we want back from the sql statement
#-------------------
@listFieldArray =
(
"incident.incident_key AS Incident_Key",
"incident_type.description AS Incident_Type",
"status.description AS Status",
"client.description AS Client",
"program.description AS Program",
"contact.fname || ' ' || contact.lname AS Contact",
"SQL.username AS Assigned_To"
);
@listTableArray =
(
'incident',
'incident_type',
'status',
'client',
'program',
'contact',
'unanet_employee',
);
#-------------------
# build our table list
#-------------------
$table = join(', ', @listTableArray);
#-------------------
# build our field clause
#-------------------
$field = join(', ', @listFieldArray);
#-------------------
# build our from clause
#-------------------
$from = "(((((incident INNER JOIN incident_type ON
incident.incident_type_key = incident_type.incident_type_key) " .
"INNER JOIN status ON incident.status_key =
status.status_key) " .
"INNER JOIN program ON incident.program_key =
program.program_key) " .
"INNER JOIN contact ON incident.contact_key =
contact.contact_key) " .
"INNER JOIN client ON program.client_key =
client.client_key) ";
if ($fdat{'assignment.employee_key'})
{
$from = $from . " INNER JOIN ( (SELECT
unanet_employee.username,assignment.incident_key, assignment.employee_key "
.
"FROM assignment INNER JOIN
unanet_employee ON assignment.employee_key = unanet_employee.employee_key "
.
"WHERE assignment.iscurrent_yn = 'Y'
and assignment.employee_key = $fdat{'assignment.employee_key'}) SQL" .
") ON incident.incident_key =
sql.incident_key";
}
else
{
$from = $from . " LEFT JOIN ( (SELECT unanet_employee.username, " .
"assignment.incident_key, "
.
"assignment.employee_key "
.
"FROM assignment INNER JOIN
unanet_employee ON assignment.employee_key = unanet_employee.employee_key ".
"WHERE assignment.iscurrent_yn = 'Y')
SQL " .
") ON incident.incident_key =
sql.incident_key";
}
#-------------------
# build our where clause of the SQL statement
#-------------------
$bFirst = 1;
foreach $searchField (@searchFieldArray)
{
if( (defined $fdat{$searchField}) &&
($fdat{$searchField} != 0) )
{
if (defined $bFirst)
{
$whereSQL = "$searchField =
$fdat{$searchField}";
undef $bFirst;
}
else
{
$whereSQL .= " and $searchField =
$fdat{$searchField}";
}
}
}
#-------------------
# build our Order By clause
#-------------------
$order = " ORDER BY incident.incident_key";
push @debugMessage, "field = $field";
push @debugMessage, "from = $from";
push @debugMessage, "table = $table";
push @debugMessage, "whereSQL = $whereSQL";
push @debugMessage, "order = $order";
eval
{
DBIx::Recordset::Undef ('searchRS');
*searchRS =
DBIx::Recordset->Setup({'!DataSource'=>$DSN,'!Table'=>$table,'!TabRelation'
=> $whereSQL,'!TabJoin'=>$from});
$searchRS -> Search({'$fields' => $field});
};
if( $@ )
{
push @outputMessage, "Problem Executing search
query: ";
Validation::WriteOracleError(\$@, \@outputMessage);
}
-]
[$ endif $]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
