Solved

Select statement not pulling properly

Posted on 2010-09-22
10
371 Views
Last Modified: 2012-08-14
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
Comment
Question by:kdeutsch
  • 5
  • 4
10 Comments
 
LVL 6

Expert Comment

by:maxy88
ID: 33734400
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
 
LVL 7

Expert Comment

by:gsiric
ID: 33734448
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
 

Author Comment

by:kdeutsch
ID: 33734807
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
 

Author Comment

by:kdeutsch
ID: 33734810
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
 
LVL 6

Expert Comment

by:maxy88
ID: 33735753
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 6

Expert Comment

by:maxy88
ID: 33735787
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
 

Author Comment

by:kdeutsch
ID: 33735942
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
 
LVL 6

Accepted Solution

by:
maxy88 earned 500 total points
ID: 33736219
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
 

Author Comment

by:kdeutsch
ID: 33737676
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
 

Author Closing Comment

by:kdeutsch
ID: 33753608
Sorry forgot to mark this as answered, its worksing great.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now