Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009
    Posts
    6

    Unanswered: needs to add where statement

    Hi All,

    How can I add a where condition statement in the 3rd and 4th flag?

    Code:
    FROM jnl_detail jd, service s, jnl_custom jc
    				 INNER JOIN (select kb_settlement_inc_plp.* ,'1' as flag 
    				 from kb_settlement_inc_plp union all select kb_settlement_inc_plp.*,'2' as flag
     				 from kb_settlement_inc_plp union all select kb_settlement_inc_plp.*,'3' as flag 
     				 from kb_settlement_inc_plp union all select kb_settlement_inc_plp.*,'4' as flag  
    				 from kb_settlement_inc_plp) se  
    					ON ((se.product_code = trim(jc.ps_code_db))
                           OR (se.product_code = trim(jc.ps_code_cr))),
    				 cmf c 
    				 	INNER JOIN kb_customer_account ca ON (c.mkt_code = ca.mkt_code
                                					  -- 	  AND 'M11' = ca.bill_period)
                                                          AND ('M22' = ca.bill_period or 'M25' = ca.bill_period))
    I tried this:
    Code:
    from kb_settlement_inc_plp where s.emf_config_id = 111 union all  select kb_settlement_inc_plp.*,'3' as flag 
     				 from kb_settlement_inc_plp where s.emf_config_id = 111 union all  select kb_settlement_inc_plp.*,'4' as flag
    However, received ORA-00904: "S"."EMF_CONFIG_ID": invalid identifier error.

    Thanks much!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I take it that you only want the se rows with flags '2' and '3' when s.emf_config_id = 111?

    If so add a WHERE clause at the end of the entire query:

    Code:
    WHERE (s.emf_config_id = 111 OR se.flag not in ('2','3')

  3. #3
    Join Date
    Mar 2009
    Posts
    6
    yah. it worked as needed.
    Thanks a lot andrewst!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •