February 12, 2008 at 4:54 am
I am trying to insert 2 values into a table used for reporting purposes. Leadtime is a col in the destination table and should contain a number based on the value of ForecastName in the source table. Then I want to add the leadtime value to the Billingmonth col. I keep getting an error message telling me Leadtime is not a valid col even though I have end as leadtime in the case statment. Where am i going wrong and how can I get round this
Leadtime is Int
BillingMonth is Int
ForecastName is Varchar(100)
OITDate is Datetime
CASE When ForecastName = 'Products' then 42
When ForecastName = 'Support' then 1
Else 31
End AS Leadtime
(OITDate+Leadtime) as BillingMonth
February 12, 2008 at 5:01 am
HI There,
Please could you provide the full update statement?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 12, 2008 at 5:06 am
INSERT INTO ReportingData
(ProjectNo
, ProjectType
, Title
, CustomerName
, OITDateChr
, ProjProb
, Leadtime
, BillingMonth)
SELECT ProjectNo
, ProjectType
, Title
, CustomerName
, CONVERT(VARCHAR(10), OITDate), 103 AS OITDateChr
, ProjProb
, CASE
When SalesProb = 0 then 10
When SalesProb = 1 then 20
When SalesProb = 2 then 30
When SalesProb = 3 then 40
When SalesProb = 4 then 50
When SalesProb = 5 then 60
When SalesProb = 6 then 70
When SalesProb = 7 then 80
When SalesProb = 8 then 90
When SalesProb = 9 then 100
ELSE null
END
, CASE When ForecastName = 'Products' then 42
When ForecastName = 'Support' then 1
Else 31
End AS Leadtime
, (OITDate+Leadtime) as BillingMonth
FROM Forecast
February 12, 2008 at 5:24 am
Realised the line with convert doesn't work either. Date needs to be stored as dd/mm/yy in reporting table
Msg 242, Level 16, State 3, Line 17
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
February 12, 2008 at 5:30 am
HI ,
The convert should work!
your syntax is wrong though. should be: CONVERT(VARCHAR(10), [DATEVALUE],103) as [VARNAME]
ARe you sure that :Leadtime is a valid col in your ReportingData table?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 12, 2008 at 5:33 am
HI again 🙂
Sorry I didn't read through you code properly.
From my knowledge you can not reference an ALIAS from a select in the same select.
Hope this helps?
you may need to put the case statement in twice.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 12, 2008 at 6:31 am
Convert now works correctly.
Leadtime is int(3) in reportingdata and supposed to be a derived value from ForecastName. Not sure what you mean by using case twice though. Could you clarify? I'm new to this.
Thanks!!
February 12, 2008 at 6:41 am
Hi,
Ok in this line
(OITDate+Leadtime) as BillingMonth
replace Leadtime
With the record the case statement that you used to determine Leadtime.
I'll look into another method as this may not be the best solution.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 12, 2008 at 6:51 am
HI ,
Not sure that this falls into best practise maybe someone can help.
But you could also create BillingMonthas a computed column
e.g
CREATE TABLE dbo.Mytable
(
Col1 INT
,Col2 VARCHAR(100)
,etc....
,Leadtime INT
,OITDate INT
,BillingMonth as [Leadtime] + [OITDate]
)
This way when those values get insert your column will compute, and if the two others are updated then it will recompute.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 12, 2008 at 7:03 am
You cannot reference an aliased column in the same select statement. You have to use the case statement again or use a subquery for your from clause. I believe the better method would be to use a subquery in the from clause, so that you can reference columns in the outer query. The example is the second set of code.
Using the case statement twice should look something like this:
SELECT
OITDate +
CASE When ForecastName = 'Products' then 42
When ForecastName = 'Support' then 1
Else 31
End AS Leadtime
subquery in from clause
INSERT INTO ReportingData
(ProjectNo
, ProjectType
, Title
, CustomerName
, OITDateChr
, ProjProb
, Leadtime
, BillingMonth)
SELECT ProjectNo
, ProjectType
, Title
, CustomerName
, OITDateChr
, ProjProb
, SalesProb
, Leadtime
, (OITDate+Leadtime) as [BillingMonth]
FROM
(SELECT ProjectNo
, ProjectType
, Title
, CustomerName
, CONVERT(VARCHAR(10), OITDate), 103 AS OITDateChr
, ProjProb
, CASE
When SalesProb = 0 then 10
When SalesProb = 1 then 20
When SalesProb = 2 then 30
When SalesProb = 3 then 40
When SalesProb = 4 then 50
When SalesProb = 5 then 60
When SalesProb = 6 then 70
When SalesProb = 7 then 80
When SalesProb = 8 then 90
When SalesProb = 9 then 100
ELSE null
END
, CASE When ForecastName = 'Products' then 42
When ForecastName = 'Support' then 1
Else 31
End AS Leadtime
, OITDate
FROM Forecast)
February 12, 2008 at 7:09 am
Hi Adam,
Out of interest, would a computed col be a bad implimentation for this problem?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
February 12, 2008 at 8:01 am
Nearly there, I think. I have cut & paste exactly as in Query Analyser. Error message is:-
sg 170, Level 15, State 1, Line 44
Line 44: Incorrect syntax near ')'.
INSERT INTO ReportingData
(ProjectNo
, ProjectType
, Title
, CustomerName
, OITDateChr
, ProjProb
, Leadtime
, BillingMonth)
SELECT
ProjectNo,
ProjectType,
Title,
CustomerName,
OITDateChr,
ProjProb,
SalesProb,
Leadtime,
(OITDate+Leadtime) as [BillingMonth]
FROM
(SELECT ProjectNo
,ProjectType
,Title
,CustomerName
,CONVERT(VARCHAR(10), OITDate, 103) AS OITDateChr
,ProjProb
,CASE When SalesProb = 0 then 10
When SalesProb = 1 then 20
When SalesProb = 2 then 30
When SalesProb = 3 then 40
When SalesProb = 4 then 50
When SalesProb = 5 then 60
When SalesProb = 6 then 70
When SalesProb = 7 then 80
When SalesProb = 8 then 90
When SalesProb = 9 then 100
ELSE null
END
,CASE When ForecastName = 'Products' then 42
When ForecastName = 'Support' then 1
Else 31
End AS Leadtime
,OITDate FROM Forecast)
February 12, 2008 at 8:29 am
Sorry, I left out the alias. When you use a subquery in a from clause you must alias the query. This code should work for you.
INSERT INTO ReportingData
(ProjectNo
, ProjectType
, Title
, CustomerName
, OITDateChr
, ProjProb
, Leadtime
, BillingMonth)
SELECT
a.ProjectNo,
a.ProjectType,
a.Title,
a.CustomerName,
a.OITDateChr,
a.ProjProb,
a.SalesProb,
a.Leadtime,
(a.OITDate+ a.Leadtime) as [BillingMonth]
FROM
(SELECT ProjectNo
,ProjectType
,Title
,CustomerName
,CONVERT(VARCHAR(10), OITDate, 103) AS OITDateChr
,ProjProb
,CASE When SalesProb = 0 then 10
When SalesProb = 1 then 20
When SalesProb = 2 then 30
When SalesProb = 3 then 40
When SalesProb = 4 then 50
When SalesProb = 5 then 60
When SalesProb = 6 then 70
When SalesProb = 7 then 80
When SalesProb = 8 then 90
When SalesProb = 9 then 100
ELSE null
END
,CASE When ForecastName = 'Products' then 42
When ForecastName = 'Support' then 1
Else 31
End AS Leadtime
,OITDate
FROM Forecast
) AS a
February 12, 2008 at 8:39 am
Hi Adam,
Out of interest, would a computed col be a bad implimentation for this problem?
The answer depends on usuage really. Since this data is going to be used for reporting we know that it should remain static and will probably be heavily indexed. The problem with computed columns is you cannot index them unless you make them persisted, which means the computed data is actually saved into the table. Once the column is persisted you can index the column. This method actually increases the storage required for the column, but can increase performance. Using persisted computed columns can increase performance if you are doing calculation in queries at run time because the value is already physically stored into the table, thus does not need to be calculated.
This however is not the case here. In this case, we are physcially inserting the data into a table and will not need to derive any data. I would not use a computed column in this scenario.
February 12, 2008 at 9:53 am
Sorry to keep hassling. Here is the full script. This is very important and I am being leant on to get this working. Error message as below:-
Msg 8155, Level 16, State 2, Line 17
No column was specified for column 17 of 'a'.
All cols in the insert (ReportingData) exist
--declare @errorvar as int
DELETE FROM ReportingData
INSERT INTO ReportingData
(ProjectNo
, ProjectType
, Title
, CustomerName
, OwnerID
, ProcessName
, StageName
, ForecastName
, SolutionCode1
, OITDate
, OITDateChr
, OITValueLocal
, OITMonth
, GMpct
, GMValueLocal
, ProjProb
, SalesProb
, WeightedOITValue
, Committed
, OITValueForecast
, Department
, OITProducts
, OITSupport
, OITServices
, GMForecast
, StartBillingMonth
, Spread
, Weighted
, Leadtime
, BillingMonth)
SELECT
a.ProjectNo
, a.ProjectType
, a.Title
, a.CustomerName
, a.OwnerID
, a.ProcessName
, a.StageName
, a.ForecastName
, a.SolutionCode1
, a.OITDate
, a.OITDateChr
, a.OITValueLocal
, a.OITMonth
, a.GMpct
, a.GMValueLocal
, a.ProjProb
, a.SalesProb
, a.WeightedOITValue
, a.Committed
, a.OITValueForecast
, a.Department
, a.OITProducts
, a.OITSupport
, a.OITServices
, a.GMForecast
, a.StartBillingMonth
, a.Spread
, a.Weighted
, a.Leadtime
,(a.OITDate+a.LeadTime) as BillingMonth
FROM
(SELECT ProjectNo
, ProjectType
, Title
, CustomerName
, OwnerID
, ProcessName
, StageName
, ForecastName
, SolutionCode1
, OITDate
, CONVERT(VARCHAR(10), OITDate,103) as OITDateChr
, OITValue_Local
, Month(OITDate) AS OITMonth
, GMpct
, GMValue_Local AS GMValueLocal
, ProjProb
, CASE
When SalesProb = 0 then 10
When SalesProb = 1 then 20
When SalesProb = 2 then 30
When SalesProb = 3 then 40
When SalesProb = 4 then 50
When SalesProb = 5 then 60
When SalesProb = 6 then 70
When SalesProb = 7 then 80
When SalesProb = 8 then 90
When SalesProb = 9 then 100
ELSE null
END
, WeightedOITValue_Local
, Committed
, OITValue_Local*SalesProb AS OITValueForecast
, Department
, CASE When ForecastName = 'Products' then FcOITValue Else 0 END AS OITProducts
, CASE When ForecastName = 'Support' then FcOITValue Else 0 END AS OITSupport
, CASE When ForecastName = 'Services' then FcOITValue Else 0 END AS OITServices
, FcOITValue*GMPct as GMForecast
, StartBillingMonth = 1
, Spread
, Weighted = null
, CASE When ForecastName = 'Products' then 42
When ForecastName = 'Support' then 1
Else 31 End AS leadtime
, OITDate
FROM stg_tuk_crm_forecast) As a
--RETURN O
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply