Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


using if condition in sql 2000


using if condition in sql 2000

Author
Message
salinasalina2001
salinasalina2001
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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 plsSmile

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
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
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.Unsure



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
salinasalina2001
salinasalina2001
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
salinasalina2001
salinasalina2001
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
salinasalina2001
salinasalina2001
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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 againSmile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search