How do I solve this error: ORA-01741: illegal zero-length identifier

Question

I have a view in my schema that lists the staff information.

I want to calculate the RETIRE_DATE from these two fields: BIRTH_DATE and RETIRE_AGE.

Using the RETIRE_DATE I want to query the staff retiring in a certain period.

Running the query gives me an error

ORA-01741: illegal zero-length identifier

I think it's beacuse I am using the RETIRE_DATE as a column of my view in the query. How do I correct this?

Here's my code:

    $start_period = strtotime("-100 week");
    $stop_period = strtotime("+100 week");

    $query = StaffEmploymentListView::find()
        ->SELECT([
          'PAYROLL_NO', 
          'BIRTH_DATE',
          'RETIRE_AGE',
          "ADD_MONTHS(BIRTH_DATE, RETIRE_AGE * 12) AS RETIRE_DATE"]);
    
    $query->andFilterWhere(['>=',$this->RETIRE_DATE, $start_period])
            ->andFilterWhere(['<=', $this->RETIRE_DATE, $stop_period])
            ->all();

My table looks like below:

Answer 1

Here is some information on the error:

ORA-01741: illegal zero-length identifier

Cause: An attempt was made to use two double quotes ("") as an identifier. An identifier must be at least one character long.

Your query has a problem in the way you are quoting the elements. I made a test using oci_connect and oci_execute, and I have no issues.

Perhaps you need to rewrite it as follows, or use the same quotation for all elements. In your case you are quoting some columns with ' , and the last part with "

<?php
$conn = oci_connect("username","password","database");

$query = "SELECT
           PAYROLL_NO, 
           BIRTH_DATE,
           RETIRE_AGE,
           ADD_MONTHS(BIRTH_DATE, RETIRE_AGE * 12) AS RETIRE_DATE 
          FROM YOUR_TABLE_OR_VIEW";


$stmt = oci_parse($conn,$query);
oci_execute($stmt);
?>

Answer 2

The problem is in this part of your code

$query->andFilterWhere(['>=',$this->RETIRE_DATE, $start_period])
    ->andFilterWhere(['<=', $this->RETIRE_DATE, $stop_period])
    ->all();

If you put it like that, the $this->RETIRE_DATE is evaluated and the value of RETIRE_DATE property is used. Because the property is empty the resulting condition will be something like "" >= $start_period.

The condition you've probably wanted is something like this RETIRE_DATE >= $start_period. To achieve that you need to pass RETIRE_DATE into andFilterWhere as string instead of passing value of RETIRE_DATE property.

$query->andFilterWhere(['>=', 'RETIRE_DATE', $start_period])
    ->andFilterWhere(['<=', 'RETIRE_DATE', $stop_period])
    ->all();