select distinct question

[Glowworm27]

is there anyway I can put a Select Distinct on the following query?

Select Clock_AdjustMents_ID_Seq.nextval, Tmp.Company_Code, Tmp.StoreNbr, Tmp.UniqueKey, Tmp.Technician_nbr, Tmp.Pay_Date ,Tmp.Day_Of_Week, Tmp.Adjust_Type_Ind, Tmp.Hours_Adj, Tmp.Reason_Adj, Tmp.Universal_ID
from TMP_clock_Adjustments Tmp
Left Join Clock_Adjustments CA On Tmp.Company_Code = CA.Company_Code And Tmp.StoreNbr = CA.StoreNbr
And Tmp.Uniquekey = CA.UniqueKey And Tmp.Technician_Nbr = CA.Technician_Nbr
Where CA.ID is Null

This does not work becasue of the sequence

Select Distinct Clock_AdjustMents_ID_Seq.nextval, Tmp.Company_Code, Tmp.StoreNbr, Tmp.UniqueKey, Tmp.Technician_nbr, Tmp.Pay_Date ,Tmp.Day_Of_Week, Tmp.Adjust_Type_Ind, Tmp.Hours_Adj, Tmp.Reason_Adj, Tmp.Universal_ID
from TMP_clock_Adjustments Tmp
Left Join Clock_Adjustments CA On Tmp.Company_Code = CA.Company_Code And Tmp.StoreNbr = CA.StoreNbr
And Tmp.Uniquekey = CA.UniqueKey And Tmp.Technician_Nbr = CA.Technician_Nbr
Where CA.ID is Null

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[Turkbear]

Hi,
Try a subquery ( could not test, so just a guess, but something like

Select Clock_AdjustMents_ID_Seq.nextval,(select DISTINCT Tmp.Company_Code, Tmp.StoreNbr, Tmp.UniqueKey, Tmp.Technician_nbr, Tmp.Pay_Date ,Tmp.Day_Of_Week, Tmp.Adjust_Type_Ind, Tmp.Hours_Adj, Tmp.Reason_Adj, Tmp.Universal_ID
from TMP_clock_Adjustments Tmp
Left Join Clock_Adjustments CA On Tmp.Company_Code = CA.Company_Code And Tmp.StoreNbr = CA.StoreNbr
And Tmp.Uniquekey = CA.UniqueKey And Tmp.Technician_Nbr = CA.Technician_Nbr
Where CA.ID is Null) SUBQ
from TMP_clock_Adjustments TMP
where TMP.Uniquekey = SUBQ.Uniquekey

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Glowworm27]

Turkbear,

That is close to what I did, I figured it out just a minute ago,

Here is the working query

Select Clock_AdjustMents_ID_Seq.nextval, Company_Code, StoreNbr, UniqueKey, Technician_nbr, Pay_Date
  ,Day_Of_Week, Adjust_Type_Ind, Hours_Adj, Reason_Adj, Universal_ID From (
Select Distinct Tmp.Company_Code, Tmp.StoreNbr, Tmp.UniqueKey, Tmp.Technician_nbr, Tmp.Pay_Date
  ,Tmp.Day_Of_Week, Tmp.Adjust_Type_Ind, Tmp.Hours_Adj, Tmp.Reason_Adj, Tmp.Universal_ID 
  from TMP_clock_Adjustments Tmp
  Left Join Clock_Adjustments CA On Tmp.Company_Code = CA.Company_Code And Tmp.StoreNbr = CA.StoreNbr
  And Tmp.Uniquekey = CA.UniqueKey And Tmp.Technician_Nbr = CA.Technician_Nbr
  Where CA.ID is Null)

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net