Solved

Select statement not pulling properly

Posted on 2010-09-22
10
374 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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
 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
DTS Connection Failed 7 70
convert null in sql server 12 34
VB.NET 2008 - SQL Timeout 9 24
SQL Server: Unable to remove duplicate sets in Header/Detail 6 23
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

772 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