Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

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

0
kdeutsch
Asked:
kdeutsch
  • 5
  • 4
1 Solution
 
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
 
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
 
maxy88Commented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now