9 Replies Latest reply: Oct 15, 2012 12:21 PM by Hangten4881 RSS

    Invalid Alias Format : Table_name.Column_name required

    Hangten4881
      Hi All,

      I am new to the OBIEE 11g and I am trying to get used to the a lot it in one shot. I was hoping that someone could help me answer this question.

      I am trying to replace a value from the database and replace it with another value if a condition is met. I am using the below SQL statement to get there however, when I select ok I get an error message:Invalid Alias Format : Table_name.Column_name required.

      Can anyone tell me how I can accomplish my taskl? I am currently using OBIEE 11g and I am modifying the column measure by editing the formula with the below code. Let me know. Thanks.



      select
      CASE
      WHEN Cmplt Claim - Process Time.Claim Date< 10/30/2010 THEN '10/30/2010'
      END
      From Cmplt Claim - Process Time.Claim Date;
        • 1. Re: Invalid Alias Format : Table_name.Column_name required
          Srini VEERAVALLI
          No need to use select in the expression

          The below exp is good to use, added '' to date.

          CASE
          WHEN Cmplt Claim - Process Time.Claim Date &lt; '10/30/2010' THEN '10/30/2010'
          END

          Pls mark correct/helpful
          • 2. Re: Invalid Alias Format : Table_name.Column_name required
            Hangten4881
            I still get the same error (Invalid Alias Format).

            I am confused about your added "to date statement. What do you mean by that. I am a noob at all this. Sorry.
            • 3. Re: Invalid Alias Format : Table_name.Column_name required
              Srini VEERAVALLI
              I've added single quote to the condition, you suppose to put single quotes.
              • 4. Re: Invalid Alias Format : Table_name.Column_name required
                Hangten4881
                Thanks for your response. I see what you are saying now.

                So I place the following code into the Edit Formula box.

                CASE
                WHEN Cmplt Claim - Process Time.Claim Date < '10/30/2010' THEN '10/30/2010'
                END


                I still come up with the same error. Am I missing a previous step? I think we are close to getting this.
                • 5. Re: Invalid Alias Format : Table_name.Column_name required
                  Srini VEERAVALLI
                  Oh.. the issue is with
                  Cmplt Claim - Process Time.Claim Date it suppose to be as
                  "Cmplt Claim - Process Time"."Claim Date"

                  The below condition is good to use too
                  case when "Cmplt Claim - Process Time"."Claim Date"<cast('10/15/2012' as date) then cast('10/15/2012' as date) else "Cmplt Claim - Process Time"."Claim Date" end

                  Pls mark as correct/helpful
                  • 6. Re: Invalid Alias Format : Table_name.Column_name required
                    Hangten4881
                    Thank you so much. The error no longer shows up. I did however, get a diffrent error message. Not sure what that all means but if you can explain to me for a better understanding that would be super helpful. Or should I post under a diffrent subject?


                    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
                    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. Please have your System Administrator look at the log for more details on this error. (HY000)
                    SQL Issued: SELECT 0 s_0, "VETSNET Operations Reports"."CLOV - End Product"."End Product Code" s_1, "VETSNET Operations Reports"."CLOV - End Product"."Rating Description" s_2, "VETSNET Operations Reports"."CLOV - File Date"."File Date" s_3, "VETSNET Operations Reports"."CLOV - Geography"."Station Name and Number" s_4, "VETSNET Operations Reports"."Cmplt Claim - Report Filter Flag"."Completed Brokered Flag" s_5, "VETSNET Operations Reports"."Cmplt Claim - Report Filter Flag"."Completed Detail Flag" s_6, CASE when "VETSNET Operations Reports"."Cmplt Claim - Process Time"."Claim Date"<('2012-10-15') then ('2012-10-15') else "Cmplt Claim - Process Time"."Claim Date" end s_7 FROM "VETSNET Operations Reports" WHERE (("CLOV - File Date"."File Date" = date '2012-10-09') AND ("CLOV - End Product"."End Product Code" IN ('681', '687')) AND ("Cmplt Claim - Report Filter Flag"."Completed Detail Flag" = 'Y') AND ("CLOV - End Product"."Rating Description" IN ('Control End Products', 'Other C&P End Products', 'Rating-Related End Products'))

                    Edited by: Hangten4881 on Oct 15, 2012 9:41 AM
                    • 7. Re: Invalid Alias Format : Table_name.Column_name required
                      Srini VEERAVALLI
                      If the actual question is answered you can go for new post.

                      Can you try without this condition,

                      CASE when "VETSNET Operations Reports"."Cmplt Claim - Process Time"."Claim Date"<('2012-10-15') then ('2012-10-15') else "Cmplt Claim - Process Time"."Claim Date" end

                      if that is working then add back the condition as below, adding cast to date.

                      CASE when "VETSNET Operations Reports"."Cmplt Claim - Process Time"."Claim Date"<('2012-10-15') then cast('2012-10-15' as date) else "Cmplt Claim - Process Time"."Claim Date" end
                      • 8. Re: Invalid Alias Format : Table_name.Column_name required
                        Hangten4881
                        Still the same error. I did change the below code.

                        SQL Issued: SELECT 0 s_0, "VETSNET Operations Reports"."CLOV - End Product"."End Product Code" s_1, "VETSNET Operations Reports"."CLOV - End Product"."Rating Description" s_2, "VETSNET Operations Reports"."CLOV - File Date"."File Date" s_3, "VETSNET Operations Reports"."CLOV - Geography"."Station Name and Number" s_4, "VETSNET Operations Reports"."Cmplt Claim - Report Filter Flag"."Completed Brokered Flag" s_5, "VETSNET Operations Reports"."Cmplt Claim - Report Filter Flag"."Completed Detail Flag" s_6, CASE WHEN "VETSNET Operations Reports"."Cmplt Claim - Process Time"."Claim Date"<('2012-10-15') then cast ('2012-10-15' as date) else "Cmplt Claim - Process Time"."Claim Date" end s_7 FROM "VETSNET Operations Reports" WHERE (("CLOV - File Date"."File Date" IN (date '2012-10-09', date '2012-10-15')) AND ("CLOV - End Product"."End Product Code" IN ('681', '687')) AND ("Cmplt Claim - Report Filter Flag"."Completed Detail Flag" = 'Y') AND ("CLOV - End Product"."Rating Description" IN ('Control End Products', 'Other C&P End Products', 'Rating-Related End Products')))
                        • 9. Re: Invalid Alias Format : Table_name.Column_name required
                          Srini VEERAVALLI
                          I see missing part in else

                          try this

                          SQL Issued: SELECT 0 s_0,
                          "VETSNET Operations Reports"."CLOV - End Product"."End Product Code" s_1,
                          "VETSNET Operations Reports"."CLOV - End Product"."Rating Description" s_2,
                          "VETSNET Operations Reports"."CLOV - File Date"."File Date" s_3,
                          "VETSNET Operations Reports"."CLOV - Geography"."Station Name and Number" s_4,
                          "VETSNET Operations Reports"."Cmplt Claim - Report Filter Flag"."Completed Brokered Flag" s_5,
                          "VETSNET Operations Reports"."Cmplt Claim - Report Filter Flag"."Completed Detail Flag" s_6,
                          CASE WHEN
                          "VETSNET Operations Reports"."Cmplt Claim - Process Time"."Claim Date"<('2012-10-15') then cast ('2012-10-15' as date) else
                          "VETSNET Operations Reports"."Cmplt Claim - Process Time"."Claim Date" end s_7
                          FROM "VETSNET Operations Reports"
                          WHERE (("CLOV - File Date"."File Date" IN (date '2012-10-09', date '2012-10-15'))
                          AND ("CLOV - End Product"."End Product Code" IN ('681', '687'))
                          AND ("Cmplt Claim - Report Filter Flag"."Completed Detail Flag" = 'Y')
                          AND ("CLOV - End Product"."Rating Description" IN ('Control End Products', 'Other C&P End Products', 'Rating-Related End Products')))

                          Edited by: veeravalli on Oct 15, 2012 1:28 PM