Select statement not pulling properly

I have a tbl name recruit which I pull personnle information from and then insert into a table called ACN in an oracle Db.

I always need to pull all recruits from tblRecruit where they need an action in 8 days.  However if they are in the ACN table I don't want to show them becuase this means they have an active record, however if that record gets canceled i want them to show up agian.

here is my current code.  the problem is that they do not show up agian if they have a record that is cancelled in the ACN table.
sql = "SELECT r.intRecruitID, r.strLastName + ' ' + r.strFirstName + ' ' + r.strMiddlename as Fullname, r.strSSN, r.dtProcessing, r.strMOS, m.strMeps, r.strAFQT, r.strZip " _
            & "FROM	tblRecruit as r LEFT OUTER Join tblMeps as m on m.intMepsId = r.intMepsId Left Outer Join OPENQUERY(SIDPERS, 'Select SSN_SM from ACN.ACN_CNTL_NBR_TBL') as S " _
            & "on r.strSSN = s.SSN_SM WHERE Convert(varchar(10), r.dtProcessing, 121) >= Convert(varchar(10), DATEADD(d, 4, '2002-09-27'), 121) AND Convert(varchar(10), r.dtProcessing, 121) " _
            & "<= Convert(varchar(10), DATEADD(d, 5, '2002-09-27'), 121) AND s.SSN_SM IS NULL"

So if DT_EXPIRe is greater than or equall to today they should not show but if its expired and within the 8 day window above they should show up.
ACN Table
ACN#
NAme
SSN
DT_EXPIRE
DT_ASSIGN

Open in new window

kdeutschAsked:
Who is Participating?
 
maxy88Connect With a Mentor Commented:
In that case just adjust the query from the WITH clause to pick up only the records where DT_EXPIRED > system date.

WITH ACN_SSN(SSN_SM) as
(
   select SSN_SM from OPENQUERY(SIDPERS, 'Select SSN_SM from ACN.ACN_CNTL_NBR_TBL where DT_EXPIRED > TRUNC(SYSDATE)) s
)

0
 
maxy88Commented:
What you need to do is take out the ACN table from the main query and add a condition in the where clause such that if the record from tblRecruit is in ACN then it should not be included in the result. See the last line in the query below starting with "AND NOT EXISTS":

SELECT r.intRecruitID, r.strLastName + ' ' + r.strFirstName + ' ' + r.strMiddlename as Fullname, r.strSSN, r.dtProcessing, r.strMOS, m.strMeps, r.strAFQT, r.strZip FROM      tblRecruit as r LEFT OUTER Join tblMeps as m on m.intMepsId = r.intMepsId WHERE Convert(varchar(10), r.dtProcessing, 121) >= Convert(varchar(10), DATEADD(d, 4, '2002-09-27'), 121) AND Convert(varchar(10), r.dtProcessing, 121) <= Convert(varchar(10), DATEADD(d, 5, '2002-09-27'), 121)

AND NOT EXISTS (Select SSN_SM from ACN.ACN_CNTL_NBR_TBL s WHERE r.strSSN = s.SSN_SM)
0
 
gsiricCommented:
Your query is little messy but i think you should change:

OPENQUERY(SIDPERS, 'Select SSN_SM from ACN.ACN_CNTL_NBR_TBL
to

OPENQUERY(SIDPERS, 'Select SSN_SM from ACN.ACN_CNTL_NBR_TBL WHERE DT_EXPIRE >= TRUNC(SYSDATE)')

This will return only active recruits instead of all recruits.

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
kdeutschAuthor Commented:
gsiric:,
Change did not work, it is still not pulling personnel back into my select list if I cancel a record and change DT_EXPIRE to a date before todays date.

maxy88:
If I use yours like is I get oracle conversion errors that is why i use the OPENQUERY so i don't have to translate everytime I go to oracle, the tblRecruit sits on a sql server and the ACN table that personnel are put into sits on a oracle server.  When I redo code to put back in the openquery i get this error.

OLE DB provider "MSDAORA" for linked server "SIDPERS" returned message "ORA-00904: "R"."STRSSN": invalid identifier
".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "Select SSN_SM from ACN.ACN_CNTL_NBR_TBL s WHERE r.strSSN = s.SSN_SM" for execution against OLE DB provider "MSDAORA" for linked server "SIDPERS".
0
 
kdeutschAuthor Commented:
Here is the code

SELECT      r.intRecruitID, r.strLastName + ' ' + r.strFirstName + ' ' + r.strMiddlename as Fullname,
            r.strSSN, r.dtProcessing, r.strMOS, m.strMeps, r.strAFQT, r.strZip
FROM      tblRecruit as r LEFT OUTER Join tblMeps as m on m.intMepsId = r.intMepsId
WHERE      Convert(varchar(10), r.dtProcessing, 121) >= Convert(varchar(10), DATEADD(d, 4, '2002-09-27'), 121) AND
            Convert(varchar(10), r.dtProcessing, 121) <= Convert(varchar(10), DATEADD(d, 5, '2002-09-27'), 121)

AND NOT EXISTS (Select * from OPENQUERY(SIDPERS, 'Select SSN_SM from ACN.ACN_CNTL_NBR_TBL s WHERE r.strSSN = s.SSN_SM'))
0
 
maxy88Commented:
Aha... try using a with clause:

WITH ACN_SSN(SSN_SM) as
(
   Select SSN_SM from ACN.ACN_CNTL_NBR_TBL
)
SELECT      r.intRecruitID, r.strLastName + ' ' + r.strFirstName + ' ' + r.strMiddlename as Fullname,
            r.strSSN, r.dtProcessing, r.strMOS, m.strMeps, r.strAFQT, r.strZip
FROM      tblRecruit as r LEFT OUTER Join tblMeps as m on m.intMepsId = r.intMepsId
WHERE      Convert(varchar(10), r.dtProcessing, 121) >= Convert(varchar(10), DATEADD(d, 4, '2002-09-27'), 121) AND Convert(varchar(10), r.dtProcessing, 121) <= Convert(varchar(10), DATEADD(d, 5, '2002-09-27'), 121)
AND NOT EXISTS (select 'x' from ACN_SSN s WHERE r.strSSN = s.SSN_SM)
0
 
maxy88Commented:
Oh, I forgot about that open query again:

WITH ACN_SSN(SSN_SM) as
(
   select SSN_SM from OPENQUERY(SIDPERS, 'Select SSN_SM from ACN.ACN_CNTL_NBR_TBL') s
)
SELECT      r.intRecruitID, r.strLastName + ' ' + r.strFirstName + ' ' + r.strMiddlename as Fullname,
            r.strSSN, r.dtProcessing, r.strMOS, m.strMeps, r.strAFQT, r.strZip
FROM      tblRecruit as r LEFT OUTER Join tblMeps as m on m.intMepsId = r.intMepsId
WHERE      Convert(varchar(10), r.dtProcessing, 121) >= Convert(varchar(10), DATEADD(d, 4, '2002-09-27'), 121) AND Convert(varchar(10), r.dtProcessing, 121) <= Convert(varchar(10), DATEADD(d, 5, '2002-09-27'), 121)
AND NOT EXISTS (select 'x' from ACN_SSN s WHERE r.strSSN = s.SSN_SM)
0
 
kdeutschAuthor Commented:
maxy88,

Ok but I have originally 10 personnel to select for the dates in my code, I added all ten to the acn table and then I cancelled 2 of them to see if they should show up agian i the code and they don't show up agian as personnel whom are eligible, it show nobody.

When a person is cancelled i just update teh DT_EXPIRED in ACN table to a day before the current day but then they need to show up agian in the query above, but only if they fall inbetween the dates.
0
 
kdeutschAuthor Commented:
maxy88:

Ok it looks like it is working to this point, I need to have my oracle guy delete all records and start fom scratch and start over agian to make sure.  He is gone for day so i will get back tommorrow.
0
 
kdeutschAuthor Commented:
Sorry forgot to mark this as answered, its worksing great.
0
All Courses

From novice to tech pro — start learning today.