Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
create table #tempTable
(
NM char,
FactorName varchar (200),
FactorVal float,
docID int
)
 
exec
('insert into #tempTable 
   select
    case when charindex(''new'',lower(F.FactorName))>0 then ''N'' 
   else 
   case when charindex(''modif'',lower(F.FactorName))>0 then ''M''
   else ''O'' 
   end 
   end 
   as ''NM'', F.FactorName as ''FactorName'', 
   I.FactorVal as ''FactorVal'',
   p.DocID as ''docID'' 
  
       from pdInventory I, Factor F, Projects P , EstimatorDesign E
       where      I.PrjActualID=P.PrjActualID and
                  E.ModelType = ''H'' and F.ModelId=E.ModelID and
                  P.DocId in (''201'') and
                  F.FactorName not in (select FactorName from EstDefaultFactors)')
 
This is the code I have written to get FactorNames, Factor Value ,Factor Type(new'N' or modified 'M') from Projects Table and store in Temporary table . But this return lacs of factors and each factor is repeated multiple times.
Posted 15-Oct-12 4:58am
Edited 15-Oct-12 4:59am
Wes Aday94.3K
v2
Comments
digimanus at 15-Oct-12 10:33am
   
is this #tempTable temporarely needed or permanent? You can do:
select ....
into #temptable
from ...
 
in that case you do not have to define your #temptable
 
and why you do exec?
Arunprasath Natarajan at 15-Oct-12 11:20am
   
Instead of using #tempTable before, place the same before "from"

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Your joins are incorrect. When I update your query the table Factor or table EstimatorDesign has no matching joins to have a good result.
Based on your query this is what you ask
select
	case when charindex('new',lower(F.FactorName))>0 then 'N' 
	else 
	case when charindex('modif',lower(F.FactorName))>0 then 'M'
	else 'O' 
	end as 'NM', 
	F.FactorName as 'FactorName', 
	I.FactorVal as 'FactorVal',
	p.DocID as 'docID' 
	into #tempTable
    from pdInventory as I
    JOIN Factor as F on f.?? = ??
    JOIN Projects as P on P.PrjActualID = I.PrjActualID 
    JOIN EstimatorDesign as E on f.modelID = e.ModelID 
     where f.Factorname  not in (select FactorName from EstDefaultFactors)
     and  E.ModelType = 'H'
     and p.DocID in ('201')

 

don't for get to end with DROP TABLE #tempTable when you do not need the #temptable anymore.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 274
1 Sergey Alexandrovich Kryukov 255
2 Shweta N Mishra 216
3 PIEBALDconsult 200
4 BillWoodruff 174
0 OriginalGriff 7,630
1 Sergey Alexandrovich Kryukov 7,022
2 DamithSL 5,586
3 Manas Bhardwaj 4,946
4 Maciej Los 4,525


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 15 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100