Using OR instead of UNION in a subselect

Using OR instead of UNION in a subselect

I created the following SQL that is to be embedded into a DB2 program: 
SELECT * FROM HACPRP.EQ_POOL_ACT_VIEW
WHERE ACTVTY_DT IN
( SELECT ACTVTY_DT FROM 
  HACPRP.EQ_POOL_ACT_VIEW 
  WHERE SUBSTR(ACTVTY_DT,5,2) < '03' 
 UNION 
  SELECT ACTVTY_DT FROM 
  HACPRP.EQ_POOL_ACT_VIEW 
  WHERE SUBSTR(ACTVTY_DT,1,2) < '09' 
  AND SUBSTR(ACTVTY_DT,5,2) = '03' 
 UNION 
  SELECT ACTVTY_DT FROM 
  HACPRP.EQ_POOL_ACT_VIEW 
  WHERE SUBSTR(ACTVTY_DT,3,2) < '29' 
  AND SUBSTR(ACTVTY_DT,1,2) = '09' 
  AND SUBSTR(ACTVTY_DT,5,2)= '03' );

The reason I have to do it like this is that someone created the table with ACTVTY_DT as CHAR(06) instead of DATE. Why does the above SQL not run? It states that it is not suitable for the DB2 Manager. My aim is to simply select a list of entries that are less than the date given on the JCL job.


    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Not having a DB2 system of my own to test on, the only thing I can see that DB2 might object to is the use of UNION in a subselect. You do not need to do it that way anyway, because you can rewrite this query as:

select * 
  from HACPRP.EQ_POOL_ACT_VIEW 
 where substr(ACTVTY_DT,5,2) < '03' 
    or ( 
       substr(ACTVTY_DT,1,2) < '09' 
   and substr(ACTVTY_DT,5,2) = '03' 
       ) 
    or ( 
       substr(ACTVTY_DT,3,2) < '29' 
   and substr(ACTVTY_DT,1,2) = '09' 
   and substr(ACTVTY_DT,5,2) = '03' 
       )

For More Information


This was first published in October 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top