Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Adding And cluase to where statement gives oracle error

I am trying to pull names and shwo in datagrid but I only want to show a name if both records are in the table and both are not expired.  I added and AND statement instead of an or into my code and when I did this it broke the statment and not gives me a no data found  but if I put back in the or it gives me data.  I belive its becuase the 2 differnet where clause statments have a different amount of reocrds if they are missing a record from here or one is expired.

OLE DB provider "OraOLEDB.Oracle" for linked server "SIDPERS" returned message "ORA-01403: no data found".
Msg 7346, Level 16, State 2, Line 2
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "SIDPERS".



Here is whole code
Select  DISTINCT SSN_SM as SSN, name_ind, Rank, POSC, AUTH_PARA_DSG, AUTH_LINE_DSG, 
		COnvert(varchar(10), DOB, 111) DOB from OPENQUERY(SIDPERS, 'Select pt.SSN_SM, pt.name_ind, substr(st.GR_ABBR_CODE,1,3) Rank , dt.POSC, 
		dt.AUTH_PARA_DSG, dt.AUTH_LINE_DSG, pt.DOB from PERS_PERSON_TBL pt LEFT JOIN PERS_SVCMBR_TBL st on st.mpc = pt.mpc and 
		st.ASG_SEQ_NBR = pt.ASG_SEQ_NBR LEFT JOIN PERS_DUTY_POSN_TBL dt on dt.mpc = st.mpc and dt.ASG_SEQ_NBR = st.ASG_SEQ_NBR LEFT JOIN 
		PERS_SVCMBR_ATCHMT_TBL at on at.mpc = pt.mpc and at.ASG_SEQ_NBR = pt.ASG_SEQ_NBR Where pt.REC_PREC = ''99999999'' and st.REC_STAT <> ''M'' 
		AND (dt.UPC IN (''PUNC0'') or at.UPC IN (''PUNC0''))') 
		Where SSN_SM IN (Select strssn from tblUnitSRPPersonnelHist where intSRpId = 156 and dtExpire > GETDATE() and bitActive = 1) AND 
		SSN_SM IN (Select strssn from tblUnitSRPPersonnelHist where intSRpId = 158 and dtExpire > GETDATE() and bitActive = 1) order by name_ind


here is just limiting the where clause on the table, here is works like I want but not with the openquery.

Select distinct strssn from tblUnitSRPPersonnelHist
Where strSSN IN (Select strssn from tblUnitSRPPersonnelHist where intSRpId = 156 and dtExpire > GETDATE() and bitActive = 1) AND 
		strSSN IN (Select strssn from tblUnitSRPPersonnelHist where intSRpId = 158 and dtExpire > GETDATE() and bitActive = 1)

Open in new window

0
kdeutsch
Asked:
kdeutsch
  • 2
  • 2
1 Solution
 
David ToddSenior DBACommented:
Hi,

That and that you have added should be an or. You appear to be combining the two  (or) rather than adding several conditions (and)

select distinct strssn
from tblUnitSRPPersonnelHist
where
      strSSN in (
            select strssn
            from tblUnitSRPPersonnelHist
            where
                  intSRpId in (
                        156
                        , 158
                        )
                  and dtExpire > GETDATE()
                  and bitActive = 1
            )


Regards
  David

PS There are some other things that need work in your code - for SQL generally good practice to specify the schema which defaults to dbo. That is, the tables should be dbo.tblUnitSRPPerssonnelHist; dbo.tblUnitSRPPerssonnelHist is referenced twice when I don't think it needs to - effectively creating a self join; distinct should only be used when needed but as there is no sample data I can't test to see if it can be removed ...
0
 
kdeutschAuthor Commented:
dtodd:

I have tried this already, what I am trying to do witht he and clause is to eliiminate the name if both files are not in the table 156, 158, or one of the files is expired.
0
 
David ToddSenior DBACommented:
Hi kdeutsch,

Some sample data that illustrates your problem would help.

What version of SQL are you using? You mention an Oracle error, but this is the SQL forum.

Anyway, try this:
 select a.strssn
 from
       (
       select aa.strSSN
       from dbo.tblUnitSRPPersonnelHist aa
       where
             aa.intSRpId = 156
             and aa.dtExpire > getdate()
             and aa.bitActive = 1
       ) a
 inner join
       select bb.strSSN
       from dbo.tblUnitSRPPersonnelHist bb
       where
             bb.intSRpId = 158
             and bb.dtExpire > getdate()
             and bb.bitActive = 1
       ) b
      on b.strSSN = a.strSSN

To explain:
To put this in production you will need to pick one derived table - a or b - and add all the columns that you want in the result. The outer select only sees the columns that are returned from the inner selects.

What this does:
selects a name if it is in the table and not expired, joined to if it exists with the other number and not expired. The results will show only those that have entries for 156 and 158 and are not expired.

Regards
  David
0
 
kdeutschAuthor Commented:
thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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