What is Wrong With This Query?

[txdave35]

Hey guys,

Two part question here. I have this query:

SELECT   DISTINCT PR01.AGTY_ID_NM, PR0.PAYEE_NM, PR0.RECIP_SSN_NBR, 
         PR0.RECIP_RETIR_DT, PR02.ANTY_PYMT_TOT_AMT, 
         PR0.BENEF_STAT_CD 
FROM     DSNP.PR01_T_RECIP_SYS PR0, 
         DSNP.PR01_T_EMPR PR01, 
         DSNP.PR01_T_ANTY_PYMT PR02 
WHERE    PR0.AGTY_ID_CD=PR01.AGTY_ID_CD 
AND      PR0.RECIP_SSN_NBR=PR02.RECIP_SSN_NBR 
AND       PR02.ANTY_PYMT_DT  = RECIP_PAYROLL_DT 
 
AND      PR0.BENEF_SEQ_NBR=PR02.BENEF_SEQ_NBR 
AND      PR0.AGTY_SYS_CD = 'APERS' 
AND      PR0.RECIP_TYPE_CD = '10' 
AND      PR0.RECIP_RETIR_DT BETWEEN '2008-07-01' AND '2009-06-01' 
AND      PRO.RECIP_RETIR_DT2 IS NULL



UNION 

SELECT   DISTINCT PR01.AGTY_ID_NM, PR0.PAYEE_NM, PR0.RECIP_SSN_NBR, 
         PR0.RECIP_RETIR_DT, PR02.ANTY_PYMT_TOT_AMT, 
         PR0.BENEF_STAT_CD 
FROM     DSNP.PR01_T_RECIP_SYS PR0, 
         DSNP.PR01_T_EMPR PR01, 
         DSNP.PR01_T_ANTY_PYMT PR02 
WHERE    PR0.AGTY_ID_CD=PR01.AGTY_ID_CD 
AND      PR0.RECIP_SSN_NBR=PR02.RECIP_SSN_NBR 
AND       PR02.ANTY_PYMT_DT  = RECIP_PAYROLL_DT 
 
AND      PR0.BENEF_SEQ_NBR=PR02.BENEF_SEQ_NBR 
AND      PR0.AGTY_SYS_CD = 'APERS' 
AND      PR0.RECIP_TYPE_CD = '10' 
AND      PR0.RECIP_RETIR_DT2 BETWEEN '2008-07-01' AND '2009-06-01'

I get a syntax error when trying to run it. The bottom half of the UNION works so the problem is on the "PRO.RECIP_RETIR_DT2 IS NULL". I can't see the problem.



Secondly, I am trying to write a query that will do the following:


1. Pull users who have a retir_dt in the date range.
2. Do not pull a user who has a retir_dt in the date range, but also has a retir_dt2 value.

3. Pull users who have a retir_dt2 in the same date range.



Would my UNION query work for this?

 

[genomon]

What is the text of the syntax error?

If quizzes are quizzical, what are tests?

 

[hmckillop]

In answer to 1 and 2

all you need is this without the union, it will return what you need.

SELECT   DISTINCT PR01.AGTY_ID_NM, PR0.PAYEE_NM, PR0.RECIP_SSN_NBR,          PR0.RECIP_RETIR_DT, PR02.ANTY_PYMT_TOT_AMT,          PR0.BENEF_STAT_CD FROM     DSNP.PR01_T_RECIP_SYS PR0,          DSNP.PR01_T_EMPR PR01,          DSNP.PR01_T_ANTY_PYMT PR02 WHERE    PR0.AGTY_ID_CD=PR01.AGTY_ID_CD AND      PR0.RECIP_SSN_NBR=PR02.RECIP_SSN_NBR AND       PR02.ANTY_PYMT_DT  = RECIP_PAYROLL_DT  AND      PR0.BENEF_SEQ_NBR=PR02.BENEF_SEQ_NBR AND      PR0.AGTY_SYS_CD = 'APERS' AND      PR0.RECIP_TYPE_CD = '10' AND      
((PR0.RECIP_RETIR_DT BETWEEN '2008-07-01' AND '2009-06-01'
AND PR0.RECIP_RETIR_DT2 IS NULL)--meets clause 1- Pull users who have a retir_dt and no retir_dt2
OR ((PR0.RECIP_RETIR_DT < '2008-07-01' OR PR0.RECIP_RETIR_DT > '2009-06-01') AND(PR0.RECIP_RETIR_DT2 BETWEEN '2008-07-01' AND '2009-06-01'))--meets clause 3  - pull users who have a retir)dt2 in same date range but retir_dt1 can not be in specified range

Having read your statements, point 2 will be automatically excluded by the above - also I may have got parenthesis slightly wrong as I cant test

"I'm living so far beyond my income that we may almost be said to be living apart

 

[txdave35]

Thanks guys.

I rewrote my query first with just the NULL condition and then added on piece by piece and it ran this time. The error was saying retir_dt2 is not valid in the context.

Looks like my UNION query does work, but I'll definitely check out yours as well.