Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

using if condition in sql 2000 Expand / Collapse
Author
Message
Posted Thursday, February 28, 2008 7:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2008 7:47 PM
Points: 5, Visits: 17
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

Post #462122
Posted Thursday, February 28, 2008 9:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 11,265, Visits: 13,027
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.


[color=#4169E1]set @f_val = Null
set @i_val = Null
set @c_val = Null
[/color]

if @f_val = 1
[color=#DC143C]set @f_val = @pParameterValue[/color]

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
[color=#DC143C] 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
)[/color]

end

If the colors don't show I apologize. This is the first time I tried using the coloring on the forum.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #462158
Posted Friday, February 29, 2008 5:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2008 7:47 PM
Points: 5, Visits: 17
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
Post #462270
Posted Friday, February 29, 2008 8:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 11,265, Visits: 13,027
salinasalina2001 (2/29/2008)
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


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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #462418
Posted Saturday, March 1, 2008 10:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2008 7:47 PM
Points: 5, Visits: 17
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
Post #462877
Posted Sunday, March 2, 2008 7:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 11,265, Visits: 13,027
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #462894
Posted Sunday, March 2, 2008 11:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 1, 2008 7:47 PM
Points: 5, Visits: 17
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:)
Post #462933
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse