1 2 Previous Next 17 Replies Latest reply: Nov 10, 2009 6:37 PM by Hoek RSS

    Error:Illegal Zero Length Identifier

    user11961230
      Hello folks,
      Am trying to run a query in sql*plus but its throwing the above error i.e Illegal zero length identifier.Can anybody throw some light on this. I believe its throwing error at line 11 i.e (CASE WHEN patientname ="",
      select  "CUBS SNAPSHOT".TYPE,
             "CUBS SNAPSHOT".SUBTYPE,
             "CUBS SNAPSHOT".EVENT_ID AS FILENUM,
             "CUBS SNAPSHOT".EVENT_ID,
             Client.Client_Name,
             "CUBS SNAPSHOT".CLIENT,
             "CUBS SNAPSHOT". Provider ID,
             "CUBS SNAPSHOT".GROUPNUMBER,
             "CUBS SNAPSHOT".STATUS,
      (CASE
                WHEN patientname ="", THEN
                 'subscribername'
                ELSE
                 'patientname'
              END) AS "Patient-Member-Test",  
              (CASE
                WHEN patient - member - test Is Null THEN
                 'NA'
                ELSE
                 'PATIENT-MEMBER-TEST'
              END) AS "PatientMemberName",
         TRUNC (SYSDATE, 'MONTH') - 1  AS ThruDate,
            to_char(sysdate, 'FMMon YYYY') AS Period
               FROM "CUBS SNAPSHOT",Client
       WHERE ((("CUBS SNAPSHOT".STATUS) = 'OPEN')  AND
             (("CUBS SNAPSHOT".LOB) != 'PBA' And ("CUBS SNAPSHOT".LOB) != 'PBR'));
      Edited by: user11961230 on Nov 10, 2009 2:46 PM

      Edited by: user11961230 on Nov 10, 2009 2:46 PM

      Edited by: user11961230 on Nov 10, 2009 3:03 PM
        • 1. Re: Error:Illegal Zero Length Identifier
          2889
          instead of
          (CASE
          WHEN patientname ="", THEN
          'subscribername'
          ELSE
          'patientname'
          END) 
          try
          (CASE
          WHEN patientname  is null , THEN
          'subscribername'
          ELSE
          'patientname'
          END) 
          • 2. Re: Error:Illegal Zero Length Identifier
            user11961230
            hey thanks for getting bak. I have modified but now it says ORA:00905 missing keyword. Any idea why this is happening.
            • 3. Re: Error:Illegal Zero Length Identifier
              Frank Kulash
              Hi,
              user11961230 wrote:
              Hello folks,
              Am trying to run a query in sql*plus but its throwing the above error i.e Illegal zero length identifier.Can anybody throw some light on this. I believe its throwing error at line 11 i.e (CASE WHEN patientname ="",
              Why do you believe that? Is it because you're getting an error message that indicates that line? Why not share that information with the whole class. Whenever you have a problem and it involves an error message, post the complete error message,

              You're right: the problem is with
              WHEN patientname ="", 
              Double-quotes surround table or column names; for example "CUBS SNAPSHOT" is a 13-character long identifier.
              What is the table or column name in this expression?
              WHEN patientname ="", 
              It's nothing, 0 characters long. That's not a valid identifier.

              Are you trying to test if patientname is NULL?
              If so, then say
              WHEN patientname IS NULL
              You do not want a comma after the condition.
              • 4. Re: Error:Illegal Zero Length Identifier
                user11961230
                well thanks Frank that really did helped a lot.But it still has an error and i know why it is.Here am using an alias as a condition.and its a nested Case and i have no clue how to break that nested Case. Thanks a million
                (CASE
                          WHEN patientname ="", THEN
                           'subscribername'
                          ELSE
                           'patientname'
                        END) AS "Patient-Member-Test",  
                        (CASE
                          WHEN Patient-Member-Test Is Null THEN  -- Alias used here as i mentioned
                           'NA'
                          ELSE
                           'PATIENT-MEMBER-TEST'
                        END) AS "PatientMemberName",
                • 5. Re: Error:Illegal Zero Length Identifier
                  Frank Kulash
                  Hi,
                  user11961230 wrote:
                  well thanks Frank that really did helped a lot.But it still has an error and i know why it is.Here am using an alias as a condition.and its a nested Case and i have no clue how to break that nested Case. Thanks a million
                  Actually, there's nothing nested about either CASE expression; they are completely independent.
                  The problem is you can't use a column alias, like "Patient-Member-Test", in the same SELECT clause where it is defined.
                  See [this thread|http://forums.oracle.com/forums/thread.jspa?messageID=3892696#3892696] for work-arounds.
                  >
                  (CASE
                  WHEN patientname ="", THEN
                  ...
                  How did this get back again?
                  Drive a wooden stake through that line!
                  • 6. Re: Error:Illegal Zero Length Identifier
                    Hoek
                    Well, you're making things harder than necessary for yourself by using:

                    quoted identifiers

                    "A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object."

                    Keep in mind:

                    "Oracle does not recommend using quoted identifiers for database object names. These quoted identifiers are accepted by SQL*Plus, but they may not be valid when using other tools that manage database objects."

                    And imo they're just overcomplicating things and make debugging a nightmare sometimes....why do you use them?

                    http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements008.htm#SQLRF51129

                    You're not using double quotation marks here:
                    WHEN Patient-Member-Test Is Null THEN -- Alias used here as i mentioned
                    And you're referring to the alias on the same (wrong) level, you need an extra SELECT around the existing one.
                    • 7. Re: Error:Illegal Zero Length Identifier
                      user11961230
                      oops sorry abt tht i forgot to change tht one. I have changed the code like this below
                      without modification:
                      (CASE
                                WHEN patientname Is Null THEN
                                 'subscribername'
                                ELSE
                                 'patientname'
                              END) AS "Patient-Member-Test",  
                              (CASE
                                WHEN Patient-Member-Test Is Null THEN  -- Alias used here as i mentioned
                                 'NA'
                                ELSE
                                 'PATIENT-MEMBER-TEST'
                              END) AS "PatientMemberName"
                      
                      
                      changes i have made:
                      
                       (CASE
                                WHEN patientname Is Null THEN
                                 'subscribername'
                                ELSE
                                 'patientname'
                              END) AS "Patient-Member-Test",  
                                (CASE
                                WHEN (CASE
                                WHEN patientname Is Null THEN
                                 'subscribername'
                                ELSE
                                 'patientname'
                              END) Is Null THEN
                                 'NA'
                                ELSE
                                 'Patient-Member-Test '          -- Do i need to replace this again with a case stmt 
                              END) AS "PatientMemberName"
                      Edited by: user11961230 on Nov 10, 2009 3:40 PM
                      • 8. Re: Error:Illegal Zero Length Identifier
                        William Robertson
                        There should be no commas within the CASE expression. IT's just <tt>CASE WHEN x = y THEN z ELSE 'bananas' END</tt>. You have a comma after the illegal zero-length identifier.
                        • 9. Re: Error:Illegal Zero Length Identifier
                          user11961230
                          @william: Thanks for correcting me. Probably it was a typing mistake but yeah the comma was not there originally.
                          • 10. Re: Error:Illegal Zero Length Identifier
                            William Robertson
                            You don't need any brackets here. Also a bit for formatting makes it easier so follow the logic:
                              CASE
                                  WHEN patientname IS NULL THEN
                                      'subscribername'
                                  ELSE
                                      'patientname'
                              END AS "Patient-Member-Test"
                            , CASE
                                  WHEN CASE
                                           WHEN patientname IS NULL THEN
                                               'subscribername'
                                           ELSE
                                               'patientname'
                                       END IS NULL
                                       THEN 'NA'
                              ELSE
                                      'Patient-Member-Test  '
                              END AS "PatientMemberName"
                            The expression you are testing with "IS NULL" can only ever evaluate to 'subscribername' or 'patientname' and never null, so there is no need for the <tt>"WHEN ... IS NULL THEN 'NA'"</tt> construction.
                            • 11. Re: Error:Illegal Zero Length Identifier
                              Hoek
                              Try something like this:

                              no sample data, so unable to test
                              select *
                              ,     (case
                                       when "patient-member-test" is null
                                       then 'NA'
                                       else 'PATIENT-MEMBER-TEST'
                                      end
                                    ) as "PatientMemberName"
                              from ( select  "CUBS SNAPSHOT".TYPE
                                     ,       "CUBS SNAPSHOT".SUBTYPE
                                     ,       "CUBS SNAPSHOT".EVENT_ID AS FILENUM
                                     ,       "CUBS SNAPSHOT".EVENT_ID
                                     ,       Client.Client_Name
                                     ,       "CUBS SNAPSHOT".CLIENT
                                     ,       "CUBS SNAPSHOT". Provider ID
                                     ,       "CUBS SNAPSHOT".GROUPNUMBER
                                     ,       "CUBS SNAPSHOT".STATUS
                                     ,      (case
                                               when patientname is null
                                               then 'subscribername'  -- do you really want a string here or do you want a column value?
                                               else 'patientname' -- do you really want a string here or do you want a column value?
                                             end
                                            ) as "patient-member-test"
                                     ,       TRUNC (SYSDATE, 'MONTH') - 1  AS ThruDate
                                     ,       to_char(sysdate, 'FMMon YYYY') AS Period
                                     FROM    "CUBS SNAPSHOT" 
                                     ,       client
                                     WHERE (    "CUBS SNAPSHOT".STATUS = 'OPEN'
                                            and "CUBS SNAPSHOT".LOB != 'PBA' 
                                            and "CUBS SNAPSHOT".LOB  != 'PBR'
                                           )
                                   );
                              And don't you need a join between the "CUBS SNAPSHOT" and Client tables?

                              And see if you can do without the double quotation marks.
                              Use a simple table alias, and improve the quality of your life ;)

                              Edited by: hoek on Nov 11, 2009 1:04 AM changed two you you's into do you's
                              • 12. Re: Error:Illegal Zero Length Identifier
                                Frank Kulash
                                Hi,
                                user11961230 wrote:
                                ...
                                changes i have made:
                                (CASE
                                WHEN patientname Is Null THEN
                                'subscribername'
                                ELSE
                                'patientname'
                                END) AS "Patient-Member-Test",  
                                Assuming you really mean to return the string literals 'subscribername' and 'patientname', there is no need to test whether "Patient-Member-Test" is NULL or not; it can not possiblly be NULL.
                                Since there is an ELSE clause that returns a non-NULL value, and all the THEN clauses return non-NULL values, there is no way for the CASE expression to return NULL.

                                If you do not mean to return the literals 'patientname' and 'subscribername', but rather the columns patientname and subscribername, then you do have to worry about NULLs, since subscribername could be NULL when it is returned.
                                Can't you just use NVL or COALESCE instead of CASE?
                                I think this does the same thing:
                                ...     COALESCE ( patientname
                                           , subscribername
                                           )     AS "Patient-Member-Test",
                                     COALESCE ( patientname
                                           , subscribername
                                           , 'NA'
                                           )     AS "PatientMemberName",
                                Like Hoek, I don't have versions of your tabes, so I can't test anything.

                                Edited by: Frank Kulash on Nov 10, 2009 7:02 PM
                                • 13. Re: Error:Illegal Zero Length Identifier
                                  Hoek
                                  it can not possiblly be NULL.
                                  face -> desk

                                  Ofcourse!
                                  How could I miss that...
                                  Must be a combination of bedtime over here plus the fact that all the quotation marks dazzled/oblivioused me ;)



                                  Hopefully Salim won't take notice of this
                                  • 14. Re: Error:Illegal Zero Length Identifier
                                    William Robertson
                                    Also this particular test for NULL can be implemented more concisely (although perhaps less readably) with NVL2:
                                    NVL2(patientname,'patientname','subscribername') AS "Patient-Member-Test"
                                    I agree with hoek, it looks a bit like you might want the actual patientname and subscribername columns, not just the words 'patientname' and 'subscribername', but that's a guess.

                                    Also as a general rule I would avoid using labels that require double quotes, unless they are really useful to the application that calls the query.
                                    1 2 Previous Next