?
Solved

SQL CASE STATEMENT

Posted on 2009-04-30
24
Medium Priority
?
331 Views
Last Modified: 2012-05-06
I am getting many syntax errors while executing it as I am unfamiliar with
the technology I need some help with the syntax. Thanks in advance.

SELECT  

CASE  

    WHEN PS_C_VCHR_CNTL_STG.ACCOUNTING_DT IS NULL

THEN
   
    CASE PS_ORIGIN_AP_OPT.ACCOUNTING_DT_IND
       
                        WHEN 'D' THEN PS_BUS_UNIT_OPT_AP.ACCOUNTING_DT_IND  
               
                --WHEN 'S' THEN ACCOUNTING_DT=PS_BUS_UNIT_OPT_AP.ACCOUNTING_DT
                   
                --WHEN 'C' THEN ACCOUNTING_DT=PS_C_CYCLE_TBL.C_CYCLE_DT
                   
            END

            CASE

                WHEN 'S' THEN ACCOUNTING_DT=PS_ORIGIN_AP_OPT.ACCOUNTING_DT
                        END
                       
            CASE

                WHEN 'C' THEN ACCOUNTING_DT=PS_C_CYCLE_TBL.C_CYCLE_DT
           

END          

ELSE

    ACCOUNTING_DT=PS_C_VCHR_CNTL_STG.ACCOUNTING_DT
   
    CASE

        WHEN PS_C_VCHR_HDR_STG.ACCOUNTING_DT > ACCOUNTING_DT THEN
       
        ACCOUNTING_DT=PS_C_VCHR_HDR_STG.ACCOUNTING_DT
   
    END

    CASE

        WHEN ACCOUNTING_DT <= PS_C_CYCLE_TBL.C_CYCLE_DT THEN
       
        SELECT B.OPEN_PERIOD_FROM
        PS_C_MONTH_TBL A, PS_BU_LED_GRP_TBL B
           WHERE B.BUSINESS_UNIT    = BUSINESS_UNIT
         AND B.LEDGER_GROUP     = 'ACTUALS'
         AND B.OPEN_YEAR_FROM   = A.YEAROFDATE
             AND B.OPEN_PERIOD_FROM <= A.MONTHOFDATE
             AND V_ACCOUNTING_DT BETWEEN A.FROM_DATE AND A.THRU_DATE      
       
    CASE
       
        WHEN V_PERIOD_OPEN_FOUND  = 'N' THEN
               
        SELECT MON.FROM_DATE
           FROM PS_C_MONTH_TBL MON
        WHERE MON.YEAROFDATE  = TO_NUMBER(To_char(C_CYCLE_DT,'YYYY'),'XXXX')
           AND C_CYCLE_DT BETWEEN MON.FROM_DATE AND MON.THRU_DATE

    END

   END

END
   
FROM PS_C_VCHR_STG
0
Comment
Question by:kishorevkk2003
  • 9
  • 7
  • 4
  • +1
23 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24271247
Are you trying to SELECT one of those items AS Accounting_DT?  Or are you trying something else?

Is this a DB2 database?

Thanks.

0
 

Author Comment

by:kishorevkk2003
ID: 24271299
Yes I am trying to select Accounting_DT. Its a nested case statement. I am trying to insert the value of Accounting_DT  depending upon the conditions. It is a DB2 database.

Moreover no comments for these sections.

                WHEN 'S' THEN  ACCOUNTING_DT=PS_BUS_UNIT_OPT_AP.ACCOUNTING_DT
                   
                WHEN 'C' THEN ACCOUNTING_DT=PS_C_CYCLE_TBL.C_CYCLE_DT
                   
Thanks.
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 1000 total points
ID: 24271964
How does this look?

SELECT  
ACCOUNTING_DT=
CASE  
    WHEN PS_C_VCHR_CNTL_STG.ACCOUNTING_DT IS NULL
	THEN
	CASE PS_ORIGIN_AP_OPT.ACCOUNTING_DT_IND
       
                        WHEN 'D' THEN PS_BUS_UNIT_OPT_AP.ACCOUNTING_DT_IND  
			WHEN 'S' THEN PS_ORIGIN_AP_OPT.ACCOUNTING_DT
			WHEN 'C' THEN PS_C_CYCLE_TBL.C_CYCLE_DT
			ELSE PS_C_VCHR_CNTL_STG.ACCOUNTING_DT
	END
    WHEN PS_C_VCHR_HDR_STG.ACCOUNTING_DT > ACCOUNTING_DT THEN
        PS_C_VCHR_HDR_STG.ACCOUNTING_DT
    WHEN ACCOUNTING_DT <= PS_C_CYCLE_TBL.C_CYCLE_DT THEN
       /* Problem here ....
        SELECT B.OPEN_PERIOD_FROM
        PS_C_MONTH_TBL A, PS_BU_LED_GRP_TBL B
           WHERE B.BUSINESS_UNIT    = BUSINESS_UNIT
         AND B.LEDGER_GROUP     = 'ACTUALS'
         AND B.OPEN_YEAR_FROM   = A.YEAROFDATE
             AND B.OPEN_PERIOD_FROM <= A.MONTHOFDATE
             AND V_ACCOUNTING_DT BETWEEN A.FROM_DATE AND A.THRU_DATE      
       */
    WHEN V_PERIOD_OPEN_FOUND  = 'N' THEN
	/* Another problem
        SELECT MON.FROM_DATE
           FROM PS_C_MONTH_TBL MON
        WHERE MON.YEAROFDATE  = TO_NUMBER(To_char(C_CYCLE_DT,'YYYY'),'XXXX')
           AND C_CYCLE_DT BETWEEN MON.FROM_DATE AND MON.THRU_DATE
	*/
END
   
FROM PS_C_VCHR_STG

Open in new window

0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 46

Expert Comment

by:Kent Olsen
ID: 24272042
Hi Kishorevkk,

This looks like a followup to your question yesterday.  :)

The CASE/WHEN block shouldn't be reinitiated when the question changes.  Note that you've got CASE ... END CASE ... END logic with no separator between and END statement and the next CASE.

Also, in DB2, there's no need to call the Oracle to_char method.  YEAR (date) will return the date.

I've rewritten your code below.  I'm sure that there are still issues, but it will be a lot closer.


Good Luck,
Kent

SELECT  
  CASE WHEN PS_C_VCHR_CNTL_STG.ACCOUNTING_DT IS NULL
       THEN CASE PS_ORIGIN_AP_OPT.ACCOUNTING_DT_IND
                 WHEN 'D' THEN PS_BUS_UNIT_OPT_AP.ACCOUNTING_DT_IND   
                 --WHEN 'S' THEN ACCOUNTING_DT=PS_BUS_UNIT_OPT_AP.ACCOUNTING_DT
                 --WHEN 'C' THEN ACCOUNTING_DT=PS_C_CYCLE_TBL.C_CYCLE_DT
                 WHEN 'S' THEN PS_ORIGIN_AP_OPT.ACCOUNTING_DT
                 WHEN 'C' THEN ACCOUNTING_DT=PS_C_CYCLE_TBL.C_CYCLE_DT
            END           
  ELSE
  CASE WHEN PS_C_VCHR_HDR_STG.ACCOUNTING_DT > ACCOUNTING_DT 
       THEN PS_C_VCHR_HDR_STG.ACCOUNTING_DT
       WHEN ACCOUNTING_DT <= PS_C_CYCLE_TBL.C_CYCLE_DT 
       THEN SELECT B.OPEN_PERIOD
            FROM PS_C_MONTH_TBL A, PS_BU_LED_GRP_TBL B
            WHERE B.BUSINESS_UNIT    = BUSINESS_UNIT
              AND B.LEDGER_GROUP     = 'ACTUALS'
              AND B.OPEN_YEAR_FROM   = A.YEAROFDATE
              AND B.OPEN_PERIOD_FROM <= A.MONTHOFDATE
              AND V_ACCOUNTING_DT BETWEEN A.FROM_DATE AND A.THRU_DATE
       WHEN V_PERIOD_OPEN_FOUND  = 'N' 
       THEN SELECT MON.FROM_DATE
            FROM PS_C_MONTH_TBL MON
            WHERE MON.YEAROFDATE  = YEAR (C_CYCLE_DT)
              AND C_CYCLE_DT BETWEEN MON.FROM_DATE AND MON.THRU_DATE
       ELSE
         PS_C_VCHR_CNTL_STG.ACCOUNTING_DT
    END
  END
FROM PS_C_VCHR_STG

Open in new window

0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 24272107
Sorry Daniel...

Thought that you'd left the discussion,
Kent
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 24272982
No problem, Kent.

DB2 is not my best SQL dialect.  If your code is right, DB2's CASE is further from MS SQL's than I realized.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 24273086
Hi Daniel,

The only part of your code that isn't really DB2-like is *SELECT var = ...* (DB2 uses *SELECT ... INTO ...*) and the to_char function.  The rest of it looks like a pretty good attempt at converting some rather ugly COBOL code to SQL.  :)

Actually, I don't expect either of our suggestions to work "right out of the box".  Both have glossed over things like references to tables (objects) other than *PS_C_VCHR_STG*.  And there's a 50/50 chance that the nested select statement will have to be bound by parenthesis.  (I really should test that.)

We'll need some more feedback from Kishorevkk.


Kent
0
 

Author Comment

by:kishorevkk2003
ID: 24273296
Hello Daniel/Kent,

Thanks for your instant replies.  I just figured out that the table is "PS_C_VCHR_CNTL_STG"......The one in the ending.....I  am still working on the case statements but it is giving me an error


SQL0104N  An unexpected token "=" was found following "".  Expected tokens may
include:  "END".  SQLSTATE=42601

SQL0104N  An unexpected token "=" was found following "".  Expected tokens may include:  "END


Thanks,
Kishore.
0
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 1000 total points
ID: 24273343
Hi Kishore,

Using Daniel's SQL, you'll need to modify it just a bit

For now, take the phrase 'ACCOUNTING_DT=' out of the code.  DB2 isn't happy with it.  If you need to store the value in a variable we can add that after debugging the SQL.


Kent
0
 

Author Comment

by:kishorevkk2003
ID: 24273483
Hi Kent,

Tried using Daniels SQL.

SELECT  
CASE  
    WHEN PS_C_VCHR_CNTL_STG.ACCOUNTING_DT IS NULL
        THEN
        CASE PS_ORIGIN_AP_OPT.ACCOUNTING_DT_IND
       
                        WHEN 'D' THEN PS_BUS_UNIT_OPT_AP.ACCOUNTING_DT_IND  
                        WHEN 'S' THEN PS_ORIGIN_AP_OPT.ACCOUNTING_DT
                        WHEN 'C' THEN PS_C_CYCLE_TBL.C_CYCLE_DT
                        ELSE PS_C_VCHR_CNTL_STG.ACCOUNTING_DT
        END
    WHEN PS_C_VCHR_HDR_STG.ACCOUNTING_DT > ACCOUNTING_DT THEN
        PS_C_VCHR_HDR_STG.ACCOUNTING_DT
    WHEN ACCOUNTING_DT <= PS_C_CYCLE_TBL.C_CYCLE_DT THEN
       
        SELECT B.OPEN_PERIOD_FROM
        PS_C_MONTH_TBL A, PS_BU_LED_GRP_TBL B
           WHERE B.BUSINESS_UNIT    = BUSINESS_UNIT
         AND B.LEDGER_GROUP     = 'ACTUALS'
         AND B.OPEN_YEAR_FROM   = A.YEAROFDATE
             AND B.OPEN_PERIOD_FROM <= A.MONTHOFDATE
             AND ACCOUNTING_DT BETWEEN A.FROM_DATE AND A.THRU_DATE      
       
    WHEN PERIOD_OPEN_FOUND  = 'N' THEN
       
        SELECT MON.FROM_DATE
           FROM PS_C_MONTH_TBL MON
        WHERE MON.YEAROFDATE  = TO_NUMBER(To_char(C_CYCLE_DT,'YYYY'),'XXXX')
           AND C_CYCLE_DT BETWEEN MON.FROM_DATE AND MON.THRU_DATE
       
END
   
FROM FS5TDV8V.PS_C_VCHR_CNTL_STG


SQL0104N  An unexpected token "B" was found following "".  Expected tokens may
include:  "END".  SQLSTATE=42601

SQL0104N  An unexpected token "B" was found following "".  Expected tokens may include:  "END  
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 24273527

Try placing parentheses around the nested query.

(
       SELECT B.OPEN_PERIOD_FROM
        PS_C_MONTH_TBL A, PS_BU_LED_GRP_TBL B
           WHERE B.BUSINESS_UNIT    = BUSINESS_UNIT
         AND B.LEDGER_GROUP     = 'ACTUALS'
         AND B.OPEN_YEAR_FROM   = A.YEAROFDATE
             AND B.OPEN_PERIOD_FROM <= A.MONTHOFDATE
             AND ACCOUNTING_DT BETWEEN A.FROM_DATE AND A.THRU_DATE      
)

Kent
0
 

Author Comment

by:kishorevkk2003
ID: 24273584
Hi Kent,

SELECT  
CASE  
    WHEN PS_C_VCHR_CNTL_STG.ACCOUNTING_DT IS NULL
        THEN
        CASE PS_ORIGIN_AP_OPT.ACCOUNTING_DT_IND
       
                        WHEN 'D' THEN PS_BUS_UNIT_OPT_AP.ACCOUNTING_DT_IND  
                        WHEN 'S' THEN PS_ORIGIN_AP_OPT.ACCOUNTING_DT
                        WHEN 'C' THEN PS_C_CYCLE_TBL.C_CYCLE_DT
                        ELSE PS_C_VCHR_CNTL_STG.ACCOUNTING_DT
        END
    WHEN PS_C_VCHR_HDR_STG.ACCOUNTING_DT > ACCOUNTING_DT THEN
        PS_C_VCHR_HDR_STG.ACCOUNTING_DT
    WHEN ACCOUNTING_DT <= PS_C_CYCLE_TBL.C_CYCLE_DT THEN
       {
        SELECT B.OPEN_PERIOD_FROM
        PS_C_MONTH_TBL A, PS_BU_LED_GRP_TBL B
           WHERE B.BUSINESS_UNIT    = BUSINESS_UNIT
         AND B.LEDGER_GROUP     = 'ACTUALS'
         AND B.OPEN_YEAR_FROM   = A.YEAROFDATE
             AND B.OPEN_PERIOD_FROM <= A.MONTHOFDATE
             AND ACCOUNTING_DT BETWEEN A.FROM_DATE AND A.THRU_DATE      
       
         }
    WHEN PERIOD_OPEN_FOUND  = 'N' THEN
       
        SELECT MON.FROM_DATE
           FROM PS_C_MONTH_TBL MON
        WHERE MON.YEAROFDATE  = TO_NUMBER(To_char(C_CYCLE_DT,'YYYY'),'XXXX')
           AND C_CYCLE_DT BETWEEN MON.FROM_DATE AND MON.THRU_DATE
       
END
   
FROM FS5TDV8V.PS_C_VCHR_CNTL_STG

SQL0199N  The use of the reserved word "FROM" following "" is not valid.  
Expected tokens may include:  "END".  SQLSTATE=42601

SQL0199N  The use of the reserved word "FROM" following "" is not valid.  Expected tokens may include:  "END


Thanks,
Kishore.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 24273821
Hi Kishore,

Brackets ('{' and '}') aren't part of the language.  Replace them with parenthesis.

Also, not that the first line in that block is:

  SELECT B.OPEN_PERIOD_FROM

It would appear to be a typo in that I suspect that it should be:

SELECT B.OPEN_PERIOD FROM

The error is actually from the last SELECT sub-statement.  It will need to be encapsulated in parenthesis, too.



Kent
0
 

Author Comment

by:kishorevkk2003
ID: 24274313
Hi Kent

SELECT  
CASE  
    WHEN PS_C_VCHR_CNTL_STG.ACCOUNTING_DT IS NULL
        THEN
        CASE PS_ORIGIN_AP_OPT.ACCOUNTING_DT_IND
       
                        WHEN 'D' THEN PS_BUS_UNIT_OPT_AP.ACCOUNTING_DT_IND  
                        WHEN 'S' THEN PS_ORIGIN_AP_OPT.ACCOUNTING_DT
                        WHEN 'C' THEN PS_C_CYCLE_TBL.C_CYCLE_DT
                        ELSE PS_C_VCHR_CNTL_STG.ACCOUNTING_DT
        END
    WHEN PS_C_VCHR_HDR_STG.ACCOUNTING_DT > ACCOUNTING_DT THEN
        PS_C_VCHR_HDR_STG.ACCOUNTING_DT
    WHEN ACCOUNTING_DT <= PS_C_CYCLE_TBL.C_CYCLE_DT THEN
       (
        SELECT B.OPEN_PERIOD FROM
        PS_C_MONTH_TBL A, PS_BU_LED_GRP_TBL B
           WHERE B.BUSINESS_UNIT    = BUSINESS_UNIT
         AND B.LEDGER_GROUP     = 'ACTUALS'
         AND B.OPEN_YEAR_FROM   = A.YEAROFDATE
             AND B.OPEN_PERIOD_FROM <= A.MONTHOFDATE
             AND ACCOUNTING_DT BETWEEN A.FROM_DATE AND A.THRU_DATE      
         )
    WHEN PERIOD_OPEN_FOUND  = 'N' THEN
       
        SELECT MON.FROM_DATE
           FROM PS_C_MONTH_TBL MON
        WHERE MON.YEAROFDATE  = TO_NUMBER(To_char(C_CYCLE_DT,'YYYY'),'XXXX')
           AND C_CYCLE_DT BETWEEN MON.FROM_DATE AND MON.THRU_DATE
       
END
   
FROM FS5TDV8V.PS_C_VCHR_CNTL_STG

SQL0104N  An unexpected token "MON" was found following "".  Expected tokens
may include:  "END".  SQLSTATE=42601

SQL0104N  An unexpected token "MON" was found following "".  Expected tokens may include:  "END

Thanks,
Kishore.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24274413
try this
SELECT  
CASE  
    WHEN PS_C_VCHR_CNTL_STG.ACCOUNTING_DT IS NULL
        THEN
        CASE PS_ORIGIN_AP_OPT.ACCOUNTING_DT_IND
       
                        WHEN 'D' THEN PS_BUS_UNIT_OPT_AP.ACCOUNTING_DT_IND  
                        WHEN 'S' THEN PS_ORIGIN_AP_OPT.ACCOUNTING_DT
                        WHEN 'C' THEN PS_C_CYCLE_TBL.C_CYCLE_DT
                        ELSE PS_C_VCHR_CNTL_STG.ACCOUNTING_DT
        END
    WHEN PS_C_VCHR_HDR_STG.ACCOUNTING_DT > ACCOUNTING_DT THEN
        PS_C_VCHR_HDR_STG.ACCOUNTING_DT
    WHEN ACCOUNTING_DT <= PS_C_CYCLE_TBL.C_CYCLE_DT THEN
       (
        SELECT B.OPEN_PERIOD FROM
        PS_C_MONTH_TBL A, PS_BU_LED_GRP_TBL B
           WHERE B.BUSINESS_UNIT    = BUSINESS_UNIT
         AND B.LEDGER_GROUP     = 'ACTUALS'
         AND B.OPEN_YEAR_FROM   = A.YEAROFDATE
             AND B.OPEN_PERIOD_FROM <= A.MONTHOFDATE
             AND ACCOUNTING_DT BETWEEN A.FROM_DATE AND A.THRU_DATE      
         )
    WHEN PERIOD_OPEN_FOUND  = 'N' THEN
       
        (SELECT MON.FROM_DATE
           FROM PS_C_MONTH_TBL MON
        WHERE MON.YEAROFDATE  = TO_NUMBER(To_char(C_CYCLE_DT,'YYYY'),'XXXX')
           AND C_CYCLE_DT BETWEEN MON.FROM_DATE AND MON.THRU_DATE)
       
END
   
FROM FS5TDV8V.PS_C_VCHR_CNTL_STG

Open in new window

0
 

Author Comment

by:kishorevkk2003
ID: 24274671
Hi Sharath,

It did not work.

SQL0204N  "FCSONLN.PS_C_MONTH_TBL" is an undefined name.  SQLSTATE=42704

SQL0204N  "FCSONLN.PS_C_MONTH_TBL                                                " is an undefined name.


Thanks,
Kishore.
0
 

Author Comment

by:kishorevkk2003
ID: 24274675
I have the table in my database.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24275004
In which database you have these tables? (FS5TDV8V or FCSONLN)

PS_C_MONTH_TBL,
PS_BU_LED_GRP_TBL
0
 

Author Comment

by:kishorevkk2003
ID: 24275021
FS5TDV8V
0
 

Author Comment

by:kishorevkk2003
ID: 24275056
Hi Sharath,

SELECT  
CASE  
    WHEN PS_C_VCHR_CNTL_STG.ACCOUNTING_DT IS NULL
        THEN
        CASE PS_ORIGIN_AP_OPT.ACCOUNTING_DT_IND
       
                        WHEN 'D' THEN PS_BUS_UNIT_OPT_AP.ACCOUNTING_DT_IND  
                        WHEN 'S' THEN PS_ORIGIN_AP_OPT.ACCOUNTING_DT
                        WHEN 'C' THEN PS_C_CYCLE_TBL.C_CYCLE_DT
                        ELSE PS_C_VCHR_CNTL_STG.ACCOUNTING_DT
        END
    WHEN PS_C_VCHR_HDR_STG.ACCOUNTING_DT > ACCOUNTING_DT THEN
        PS_C_VCHR_HDR_STG.ACCOUNTING_DT
    WHEN ACCOUNTING_DT <= PS_C_CYCLE_TBL.C_CYCLE_DT THEN
       (
        SELECT B.OPEN_PERIOD FROM
        FS5TDV8V.PS_C_MONTH_TBL A, FS5TDV8V.PS_BU_LED_GRP_TBL B
           WHERE B.BUSINESS_UNIT    = BUSINESS_UNIT
         AND B.LEDGER_GROUP     = 'ACTUALS'
         AND B.OPEN_YEAR_FROM   = A.YEAROFDATE
             AND B.OPEN_PERIOD_FROM <= A.MONTHOFDATE
             AND ACCOUNTING_DT BETWEEN A.FROM_DATE AND A.THRU_DATE      
         )
    WHEN PERIOD_OPEN_FOUND  = 'N' THEN
       
        (SELECT MON.FROM_DATE
           FROM FS5TDV8V.PS_C_MONTH_TBL MON
        WHERE MON.YEAROFDATE  = TO_NUMBER(To_char(C_CYCLE_DT,'YYYY'),'XXXX')
           AND C_CYCLE_DT BETWEEN MON.FROM_DATE AND MON.THRU_DATE)
       
END
   
FROM FS5TDV8V.PS_C_VCHR_CNTL_STG


SQL0206N  "B.OPEN_PERIOD" is not valid in the context where it is used.  
SQLSTATE=42703

SQL0206N  "B.OPEN_PERIOD                                                         " is not valid in the context where it is used.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24275393

I don't like the way you have written your code. Instead of those sub-queries in the WHEN clause, i will go for JOINing (INNER JOIN or LEFT JOIN)  those tables in the main FROM clause and use the required columns from FS5TDV8V.PS_C_MONTH_TBL or FS5TDV8V.PS_BU_LED_GRP_TBL tables in the WHEN clause of your CASE statement.
If you have more than one record returned with the sub-queries, you will get a run-time error. Are you using any of the FS5TDV8V.PS_C_VCHR_CNTL_STG columns in those sub-queries?
May be DB2 does not support your kind of writing queries (sub queries in the WHEN clause of CASE statement).
 
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 24276308
Hi Kishore,

Does table FS5TDV8V.PS_BU_LED_GRP_TBL have a column named OPEN_PERIOD or one name OPEN_PERIOD_FROM?

Earlier we changed 'OPEN_PERIOD_FROM' to be 'OPEN PERIOD FROM'.  It may be that the name was correct and we just needed to insert a FROM clause.

And I agree with Sharath.  It is probably faster and certainly neater to join the tables and select a row value from one of the resulting columns.


Kent
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24276505
Also as kdo suggested, use YEAR function in place of TO_CHAR.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question