Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Select statement not pulling properly

Posted on 2010-09-22
10
376 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Virtual SQL Server 2014 Standard 35 90
Row insertion failed. Array 5 46
backup and restore 21 29
SQL create line numbers for data sampling 11 27
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

856 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