February 28, 2008 at 7:13 pm
i am unable to fetch the datas from define_mapping table and match the parameterid from table tblflightdata.
whats my error can anyone help me out pls:)
declare @pparameterid int,
@pParametername varchar(50),
@pparameter_name varchar(10),
@pengine_position int,
@pinteger_value int,
@pfloat_value int,
@pchar_value int
declare @f_val varchar(50)
declare @c_val varchar(50)
declare @i_val varchar(50)
declare @pParameterValue float
declare @CruiseTimestamp datetime,
@CruiseTimestampCsv varchar(50)
select @CruiseTimestamp = alerttimestamp from [dbo].[tblflightalert]
where alertid = 600210 and flightid = 900003
set @CruiseTimestamp = dateadd(mi, -15, @CruiseTimestamp)
set @CruiseTimestampCsv = convert(varchar(20), @CruiseTimestamp, 101) + ' ' + convert(varchar(10), @CruiseTimestamp, 108)
declare kvar2 cursor for select parameterid, Parametername, parameter_name,
engine_position, integer_value, float_value, char_value
from dbo.define_mapping
select @pparameterid = parameterid,
@pParametername = Parametername,
@pparameter_name = parameter_name,
@pengine_position = engine_position,
@pinteger_value = integer_value,
@pfloat_value = float_value,
@pchar_value = char_value from t_ecm_define_mapping
select @CruiseTimestampCsv = ParameterTimestamp from tblflightdata
where @pparameterid = ParameterId
open kvar2
fetch next from kvar2 into @pparameterid,
@pParametername,
@pparameter_name,
@pengine_position,
@pinteger_value,
@pfloat_value,
@pchar_value
while @@fetch_status = 0
begin
print @pparameterid
print @pParametername
print @pparameter_name
print @pengine_position
print @pinteger_value
print @pfloat_value
print @pchar_value
select top 1 @pParameterValue = ParameterValue from tblflightdata
where flightid = 900003 and @pparameterid = parameterid
set @f_val = Null
set @i_val = Null
set @c_val = Null
if @f_val = 1
set @f_val = @pParameterValue
begin
insert into [ISMS].[dbo].[t_ecm_aircraftinput]([aircraft_id], [Flight_phase], [flight_datetime], [parameter_name], [integer_value], [float_value], [char_value] )
VALUES('"AP-BEQ"', '"CRUISE"', @CruiseTimestampCsv, @pparameter_name,@i_val,@f_val,@c_val)
end
--else
if @i_val = 1
begin
insert into [ISMS].[dbo].[t_ecm_engineinput]([aircraft_id], [engine_position], [Flight_phase], [flight_datetime], [parameter_name], [integer_value], [float_value], [char_value] )
VALUES('"AP-BEQ"', @pengine_position, '"CRUISE"', @CruiseTimestampCsv, @pparameter_name,@i_val,@f_val,@c_val)
end
fetch next from kvar2 into @pparameterid,
@pParametername,
@pparameter_name,
@pengine_position,
@pinteger_value,
@pfloat_value,
@pchar_value
end
close kvar2
deallocate kvar2
go
February 28, 2008 at 9:43 pm
Your problem probably has to do with the blue/italic code below. Because of the blue/bold code (setting the _val variables to null) means the red/italic code never runs.
set @f_val = Null
set @i_val = Null
set @c_val = Null
if @f_val = 1
set @f_val = @pParameterValue
begin
insert into [ISMS].[dbo].[t_ecm_aircraftinput]
(
[aircraft_id],
[Flight_phase],
[flight_datetime],
[parameter_name],
[integer_value],
[float_value],
[char_value]
)
VALUES
(
'"AP-BEQ"',
'"CRUISE"',
@CruiseTimestampCsv,
@pparameter_name,
@i_val,
@f_val,
@c_val
)
End
--else
if @i_val = 1
begin
insert into [ISMS].[dbo].[t_ecm_engineinput]
(
[aircraft_id],
[engine_position],
[Flight_phase],
[flight_datetime],
[parameter_name],
[integer_value],
[float_value],
[char_value]
)
VALUES
(
'"AP-BEQ"',
@pengine_position,
'"CRUISE"',
@CruiseTimestampCsv,
@pparameter_name,
@i_val,
@f_val,
@c_val
)
end
If the colors don't show I apologize. This is the first time I tried using the coloring on the forum.:unsure:
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 29, 2008 at 5:17 am
sorry i am not dealing with colors
i am tryin to match parameterid of the first table to second table
if the datas are int i convert it to int and place it to a variable so that i can call that variable in the 3rd table
thank you
February 29, 2008 at 8:31 am
salinasalina2001 (2/29/2008)
sorry i am not dealing with colorsi am tryin to match parameterid of the first table to second table
if the datas are int i convert it to int and place it to a variable so that i can call that variable in the 3rd table
thank you
I am not dealing with colors either. I tried to use the color tags in the forum to highlight the sections of code that I think are causing your problem. If you read my original post and look at the BOLD section you will see that you are setting the variables to NULL then using them in an IF statement. Becasue of this the code that is in ITALICS will never run.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 1, 2008 at 10:53 pm
Thank you Jack Corbett
i got it,
can you please tell me how to assign a string to a column Name
like
if the parameterid to be "parameterid"
and how to use the break and continue in this procedure
because when i use the break at last statement near the end ofthe cursor it takes only the first value and nothing is displayed
thank you again
March 2, 2008 at 7:23 am
I'm not sure what you mean by "assign a string to a column name". Do you mean you want to dynamically select the columns based on a parameter? For example:
If @parameter = 'Jack'
Begin
Set @column_name = 'FirstName'
End
Else
Begin
Set @column_name = 'LastName'
End
Select
@column_name
From
table1
If this is what you want to do, you need to use dynamic SQL like this:
Declare @sql varchar(8000)
Set @sql = 'Select '
If @parameter = 'Jack'
Begin
Set @sql = @sql + 'FirstName '
End
Else
Begin
Set @sql = @sql + 'LastName '
End
Set @sql = @sql + 'From table1'
Exec (@sql)
Have never really used Break and Continue in SQL Server. Look them up in Books on Line. I do know that if you do the Break within the cursor loop it will stop iterating through the cursor when you hit it.
Best Practices in SQL Server are to use cursors as little as possible as they are more resource intensive and slower than set-based solutions. If you were to post what you need to accomplish with the table schemas and some data someone may be able help you find a better solution than the cursor.
See this article, http://www.sqlservercentral.com/articles/Best+Practices/61537/ for advice on posting that will allow for fast and accurate anwers.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 2, 2008 at 11:19 am
Thank you
i needed was something like '"'+cast(columnname as varchar(20))+'"'
is that the column name is ParameterID it should be displayed as "ParameterID" ie within quotes.
Thank you again:)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply