Click here to Skip to main content
13,193,970 members (48,278 online)
Rate this:
Please Sign up or sign in to vote.
See more:
create table #tempTable
NM char,
FactorName varchar (200),
FactorVal float,
docID int
('insert into #tempTable 
    case when charindex(''new'',lower(F.FactorName))>0 then ''N'' 
   case when charindex(''modif'',lower(F.FactorName))>0 then ''M''
   else ''O'' 
   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 3:58am
Updated 15-Oct-12 3:59am
Wes Aday104.2K
digimanus 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 15-Oct-12 11:20am
Instead of using #tempTable before, place the same before "from"

1 solution

Rate this: bad
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
	case when charindex('new',lower(F.FactorName))>0 then 'N' 
	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.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web02 | 2.8.171018.2 | Last Updated 15 Oct 2012
Copyright © CodeProject, 1999-2017
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