WIth clause
Last Post 03 Dec 2012 11:24 AM by gregwga50. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
gregwga50
Advanced Member
Advanced Member
Posts:472

--
03 Dec 2012 11:05 AM
with cx as 
(SELECT a.MMKT, a.MCLNO,a.mclsuf FROM files/tpclp01 a , 
files/tpclp01 b 
where a.mclno =b.mclno and a.mclsuf=b.mclsuf and a.mmkt <> b.mmkt) 
select d.MMKT, d.MCLNO,d.mclsuf from files/tpclp01 d 
where d.mclno =cx.mclno and d.mclsuf=cx.mclsuf 

 Does anyone see anyting wrong with this. The SELECT inside the WITH statement works all by itself outside the WITH and the fields are all valid fields in the TPCLP01 file.

I keep getting Column qualifier or table CX undefined

I figure I must be overlooking something obvious as I have used the WITH statment before without incident.

 

 

 

Michael Price
Advanced Member
Advanced Member
Posts:362
Avatar

--
03 Dec 2012 11:12 AM
you need to reference CX in your main ( 2nd ) select ?
gregwga50
Advanced Member
Advanced Member
Posts:472

--
03 Dec 2012 11:24 AM
I think I just solved it:

with cx as
(SELECT a.MMKT, a.MCLNO,a.mclsuf FROM files/tpclp01 a ,
files/tpclp01 b
where a.mclno =b.mclno and a.mclsuf=b.mclsuf and a.mmkt <> b.mmkt)
select d.MMKT, d.MCLNO,d.mclsuf from cx as g, files/tpclp01 d
where d.mclno =g.mclno and d.mclsuf=g.mclsuf
You are not authorized to post a reply.

Acceptable Use Policy