We help IT Professionals succeed at work.

select informtion based on oracle query

kdeutsch
kdeutsch asked
on
I have a query tha pulls information form a sql Db but I need to show only those from the sql db where they are not in the oracle tables with a
FTDHIST_START that is greater than the dtDeploy  Right now I am not sure If I need to do a join on this instead becuase its pulling everything.
To my records it should pull abut 10 personnel but its pulling nothing.


Select	mu.intPositionID,
		mu.strPosnTitle,
		mu.strPara,
		mu.strLine,
		mu.intPositionNum,
		mn.strFullname,
		k.kount,
		m.dtDeploy,
		dt.FTDHIST_START
from    tblMobUnitPosition as mu LEFT JOIN
		tblMobUnitPersonnel as mp on mp.intPositionId = mu.intPositionID LEFT JOIN
		tblMOBUnits as m on m.intUnitMobId = mu.intUnitMobID LEFT JOIN
		MnNgPersonnel.dbo.tblMNNatPersonnel mn on mn.strSSN = mp.strSSN LEFT JOIN
		(Select COUNT(intPermtaskId) kount, strssn from tblPermTask pt INNER JOIN tblSRPQuestion AS q ON q.intQuestionId = pt.intQuestionId where intCategoryID = 1 group by strSSN) k on k.strSSN = mp.strSSN LEFT JOIN
		(Select * from OPENQUERY(SIDPERS, 'SELECT FTDHIST_SSN,FTDHIST_START,FTDHIST_END,FTDHIST_FY FROM COMP.JSSFTDHIST WHERE FTDHIST_TDC = ''MOB'' AND FTDHIST_START  > sysdate')) dt on dt.FTDHIST_SSN = mp.strSSN
Where	mu.intUnitMobID = 20 and mp.bitActive = 1 AND dt.FTDHIST_START > dtDeploy

Open in new window

Comment
Watch Question

Kdeutsch

I'm not much full understand, but from the SQL felt that the some of the Left Join be replaced with Inner Join as Show below
Take a look and if you feel i'm in the right path to your requirment else totally away from the result and we will sync


Select      mu.intPositionID,
            mu.strPosnTitle,
            mu.strPara,
            mu.strLine,
            mu.intPositionNum,
            mn.strFullname,
            k.kount,
            m.dtDeploy,
            dt.FTDHIST_START
from    tblMobUnitPosition as mu
        LEFT JOIN tblMOBUnits as m on m.intUnitMobId = mu.intUnitMobID
       
        LEFT JOIN tblMobUnitPersonnel as mp
                        INNER JOIN MnNgPersonnel.dbo.tblMNNatPersonnel mn on mn.strSSN = mp.strSSN
                        INNER JOIN (Select COUNT(intPermtaskId) kount, strssn
                           from tblPermTask pt
                           INNER JOIN tblSRPQuestion AS q ON q.intQuestionId = pt.intQuestionId
                           where intCategoryID = 1 group by strSSN) k on k.strSSN = mp.strSSN
                INNER JOIN (Select * from
                           OPENQUERY(SIDPERS, 'SELECT FTDHIST_SSN,FTDHIST_START,FTDHIST_END,FTDHIST_FY FROM COMP.JSSFTDHIST
                                       WHERE FTDHIST_TDC = ''MOB'' AND FTDHIST_START  > sysdate')) dt on dt.FTDHIST_SSN = mp.strSSN
        on mp.intPositionId = mu.intPositionID
       
       
Where      mu.intUnitMobID = 20 and mp.bitActive = 1 AND dt.FTDHIST_START > dtDeploy

Author

Commented:
HI,
I have tried the INNER join route as well on some, but it should return about 10 records but it returns nothing, it works up to the point that I put on the openquery  it returns all the personnel but then I waqnt o limit it to personnel that are not in teh oracle tables where the dt.FTDHIST_START > dtDeploy
This may work, let give  a try

Select      mu.intPositionID,
            mu.strPosnTitle,
            mu.strPara,
            mu.strLine,
            mu.intPositionNum,
            mn.strFullname,
            k.kount,
            m.dtDeploy,
            dt.FTDHIST_START
from    tblMobUnitPosition as mu
        LEFT JOIN tblMOBUnits as m on m.intUnitMobId = mu.intUnitMobID
       
        LEFT JOIN tblMobUnitPersonnel as mp
                        INNER JOIN MnNgPersonnel.dbo.tblMNNatPersonnel mn on mn.strSSN = mp.strSSN
                        INNER JOIN (Select COUNT(intPermtaskId) kount, strssn
                           from tblPermTask pt
                           INNER JOIN tblSRPQuestion AS q ON q.intQuestionId = pt.intQuestionId
                           where intCategoryID = 1 group by strSSN) k on k.strSSN = mp.strSSN
                INNER JOIN (Select * from
                           OPENQUERY(SIDPERS, 'SELECT FTDHIST_SSN,FTDHIST_START,FTDHIST_END,FTDHIST_FY FROM COMP.JSSFTDHIST
                                       WHERE FTDHIST_TDC = ''MOB'' AND FTDHIST_START  > sysdate')) dt
                                       on dt.FTDHIST_SSN = mp.strSSN and dt.FTDHIST_START > m.dtDeploy
        on mp.intPositionId = mu.intPositionID
               
Where      mu.intUnitMobID = 20 and mp.bitActive = 1

Author

Commented:
I it told me that m.dtDeploy could not be bound.

I did get the following to return records, when I cut some out of the oracle query, however it does not eliminate those that have a reocrd in the oracle tables with the date.  It when I put on this last part that nothing gets returned.

 AND dt.FTDHIST_START >= dtDeploy

But i was thinking that is technically not what I need becuase I want to eliminate personnel from the list whom are in the tables with a that limitor

dt.FTDHIST_START >= dtDeploy



Select      mu.intPositionID,
            mu.strPosnTitle,
            mu.strPara,
            mu.strLine,
            mu.intPositionNum,
            mn.strFullname,
            k.kount,
            m.dtDeploy,
            dt.FTDHIST_START
from    tblMobUnitPosition as mu LEFT JOIN
            tblMobUnitPersonnel as mp on mp.intPositionId = mu.intPositionID LEFT JOIN
            tblMOBUnits as m on m.intUnitMobId = mu.intUnitMobID LEFT JOIN
            MnNgPersonnel.dbo.tblMNNatPersonnel mn on mn.strSSN = mp.strSSN LEFT JOIN
            (Select COUNT(intPermtaskId) kount, strssn from tblPermTask pt INNER JOIN tblSRPQuestion AS q ON q.intQuestionId = pt.intQuestionId where intCategoryID = 1 group by strSSN) k on k.strSSN = mp.strSSN LEFT JOIN
            (Select * from OPENQUERY(SIDPERS, 'SELECT FTDHIST_SSN,FTDHIST_START FROM COMP.JSSFTDHIST WHERE FTDHIST_TDC = ''MOB''')) dt on dt.FTDHIST_SSN = mp.strSSN
Where      mu.intUnitMobID = 20 and mp.bitActive = 1
Could you pleas share some screen shot with the data for the columns
  m.dtDeploy,      dt.FTDHIST_START
whether both are Datetime else is their difference in datatype

use the below command to check the datatypes

sp_help( SQL Querry)

Author

Commented:
HI,
dtDeploy is a datetime sql field
FTDHIST_START is an oracle datetime field as well

Author

Commented:
HI,
here is some data as long as I do not put hte last part of the where clause to call the AND dt.FTDHIST_START >= dtDeploy
All these should show in except for the last one because they would have a dt.FTDHIST_START >= dtDeploy


26      SQUAD LEADER      103      01      6      Name      NULL      2012-05-25 00:00:00.000      2008-07-14 00:00:00.0000000
6      WHL VEH MECH      101      07      1      Name      NULL      2012-05-25 00:00:00.000      2009-02-10 00:00:00.0000000
52      TEAM LEADER      103      02      20      Name      NULL      2012-05-25 00:00:00.000      NULL
10      EQP REC/PARTS SP      101      13      1      Name      NULL      2012-05-25 00:00:00.000      NULL
105      TEAM LEADER      103      04      1      Name      NULL      2012-05-25 00:00:00.000      NULL
110      TEAM LEADER      103      04      1      Name      NULL      2012-05-25 00:00:00.000      2011-11-10 00:00:00.0000000

Author

Commented:
Sorry example should have looked like this so that the last date matched teh dtDeploy


26      SQUAD LEADER      103      01      6      Name      NULL      2012-05-25 00:00:00.000      2008-07-14 00:00:00.0000000
6      WHL VEH MECH      101      07      1      Name      NULL      2012-05-25 00:00:00.000      2009-02-10 00:00:00.0000000
52      TEAM LEADER      103      02      20      Name      NULL      2012-05-25 00:00:00.000      NULL
10      EQP REC/PARTS SP      101      13      1      Name      NULL      2012-05-25 00:00:00.000      NULL
105      TEAM LEADER      103      04      1      Name      NULL      2012-05-25 00:00:00.000      NULL
110      TEAM LEADER      103      04      1      Name      NULL      2012-05-25 00:00:00.000      2012-05-26 00:00:00.000
could you try this

Sp_help(Select      mu.intPositionID,
            mu.strPosnTitle,
            mu.strPara,
            mu.strLine,
            mu.intPositionNum,
            mn.strFullname,
            k.kount,
            m.dtDeploy,
            dt.FTDHIST_START
from    tblMobUnitPosition as mu LEFT JOIN
            tblMobUnitPersonnel as mp on mp.intPositionId = mu.intPositionID LEFT JOIN
            tblMOBUnits as m on m.intUnitMobId = mu.intUnitMobID LEFT JOIN
            MnNgPersonnel.dbo.tblMNNatPersonnel mn on mn.strSSN = mp.strSSN LEFT JOIN
            (Select COUNT(intPermtaskId) kount, strssn from tblPermTask pt INNER JOIN tblSRPQuestion AS q ON q.intQuestionId = pt.intQuestionId where intCategoryID = 1 group by strSSN) k on k.strSSN = mp.strSSN LEFT JOIN
            (Select * from OPENQUERY(SIDPERS, 'SELECT FTDHIST_SSN,FTDHIST_START FROM COMP.JSSFTDHIST WHERE FTDHIST_TDC = ''MOB''')) dt on dt.FTDHIST_SSN = mp.strSSN
Where      mu.intUnitMobID = 20 and mp.bitActive = 1 )


will check wih the data during the time
instead of dt.FTDHIST_START >= dtDeploy

try with

Datediff(day,dtDeploy , dt.FTDHIST_START) >= 0

Author

Commented:
Hi,
When i did the sp_help(sql) it does not return the data fields, it return alls my datatables, sp's and views and what kind they are and then the regular query, but gives no indication what types of data I am using on the individual query.  i tried the add on but it returned no data, it would help if I could play witht he Oracle tables and change dates but I cannot.

Try with this comparison


convert(datetime,substring( dt.FTDHIST_START,0,24) , 101) > dtDeploy
 

Author

Commented:
Thanks