|
in design mode select Fi_Sym, CUSIP, fi_Name, fi_short_desc, rbc_sym, fi_class, fi_Exchg, Exp_dt, Start_dt,
fi_desc, target_dt, Target_Price, fi_sym_US, secur_no, last_split_dt, created_dt,
updated_dt, updated_by, target_Issued_Dt, target_latest_By, target_currency,
high_52Week, low_52Week, target_note,//
time_stamp
into @Fi_Sym, @CUSIP, @fi_Name, @fi_short_desc, @rbc_sym, @fi_class, @fi_Exchg,
@Exp_dt, @Start_dt, @fi_desc, @target_dt, @Target_Price, @fi_sym_US, @secur_no, @last_split_dt, @created_dt,
@updated_dt, @updated_by, @target_Issued_Dt,
@target_latest_By, @target_currency,
@high_52Week, @low_52Week, @target_note,
@time_stamp
from "dba"."Fi_security" where Fi_Sym = @Fi_Sym
in debug mode select @Fi_Sym = Fi_Sym,@CUSIP = CUSIP,@fi_Name = fi_Name,@fi_short_desc = fi_short_desc,@rbc_sym = rbc_sym,@fi_class = fi_class,@fi_Exchg = fi_Exchg,@Exp_dt = Exp_dt,@Start_dt = Start_dt,@fi_desc = fi_desc,@target_dt = target_dt,@Target_Price = Target_Price,@fi_sym_US = fi_sym_US,@secur_no = secur_no,@last_split_dt = last_split_dt,@created_dt = created_dt,
@updated_dt = updated_dt,@updated_by = updated_by,@target_Issued_Dt =
target_Issued_Dt,@target_latest_By = target_latest_By,@target_currency = target_currency,
@high_52Week = high_52Week,@low_52Week = low_52Week,@target_note = @target_note, --
@time_stamp = @time_stamp
from dba.Fi_security where Fi_Sym = @Fi_Sym
the problem occurs only for the last two columns - they both get changed in variable name the version is SA 11 (11.01.2506). using sybase central 6.0.0.4773 dropping the SP and recreate did not help solve the problem. |
|
Since I can get correct result from isql window not using into clause begin
Declare @Fi_Sym VARCHAR(20)
Declare @CUSIP VARCHAR(16)
Declare @fi_Name VARCHAR(76)
Declare @fi_short_desc VARCHAR(80)
Declare @RowsFound int
Declare @rbc_sym VARCHAR(16)
Declare @fi_class VARCHAR(4)
Declare @fi_Exchg VARCHAR(8)
Declare @Exp_dt DATE
Declare @Start_dt DATE
Declare @fi_desc VARCHAR(254)
Declare @target_dt DATE
Declare @Target_Price T_money
Declare @fi_sym_US VARCHAR(16)
Declare @secur_no VARCHAR(16)
Declare @last_split_dt datetime
Declare @created_dt datetime
Declare @updated_dt datetime
Declare @updated_by VARCHAR(16)
Declare @target_Issued_Dt date
Declare @target_latest_By varchar(24)
Declare @target_currency varchar(4)
Declare @high_52Week T__money_2
Declare @low_52Week T__money_2
Declare @target_note varchar(256)
Declare @time_stamp timestamp
select 'NTR.TO', null, null, null into @Fi_Sym, @CUSIP, @fi_Name, @fi_short_desc
select first @Fi_Sym = Fi_Sym,@CUSIP = CUSIP,@fi_Name = fi_Name,@fi_short_desc = fi_short_desc,@rbc_sym = rbc_sym,@fi_class = fi_class,@fi_Exchg = fi_Exchg,@Exp_dt = Exp_dt,@Start_dt = Start_dt,@fi_desc = fi_desc,@target_dt = target_dt,@Target_Price = Target_Price,@fi_sym_US = fi_sym_US,@secur_no = secur_no,@last_split_dt = last_split_dt,@created_dt = created_dt,
@updated_dt = updated_dt,@updated_by = updated_by,@target_Issued_Dt = target_Issued_Dt,@target_latest_By = target_latest_By,@target_currency = target_currency,
@high_52Week = high_52Week,@low_52Week = low_52Week,@target_note = "target_note"
from dba.Fi_security where fi_sym = @fi_sym
select @Fi_Sym, @CUSIP, @fi_Name, @fi_short_desc, @rbc_sym, @fi_class, @fi_Exchg, @Exp_dt,
@Start_dt, @fi_desc, @target_dt, @Target_Price, @fi_sym_US, @secur_no, @last_split_dt, @created_dt,
@updated_dt, @updated_by, @target_Issued_Dt, @target_latest_By, @target_currency,
@high_52Week, @low_52Week, @target_note
end
I decided from the same Isql window execute the following: alter procedure dba.p_Fi_security_selIntoOutputVarByFiSym_Name_ShortDesc_or_Cusip(
inout @Fi_Sym VARCHAR(20) default null, inout @CUSIP VARCHAR(16) default null, inout @fi_Name VARCHAR(76) default null
, inout @fi_short_desc VARCHAR(80) default null
, out @RowsFound int, out @rbc_sym VARCHAR(16), out @fi_class VARCHAR(4), out @fi_Exchg VARCHAR(8), out @Exp_dt DATE
, out @Start_dt DATE, out @fi_desc VARCHAR(254), out @target_dt DATE, out @Target_Price T_money, out @fi_sym_US VARCHAR(16)
, out @secur_no VARCHAR(16), out @last_split_dt datetime, out @created_dt datetime, out @updated_dt datetime
, out @updated_by VARCHAR(16), out @target_Issued_Dt date, out @target_latest_By varchar(24)
, out @target_currency varchar(4), out @high_52Week T__money_2, out @low_52Week T__money_2, out @target_note varchar(256)
, out @time_stamp timestamp)
as
begin
declare @fi_sym_isWild int
declare @Fi_Sym_orig varchar(20)
if (charIndex('%',@fi_sym)>0)
select 1, @fi_sym into @fi_sym_isWild, @Fi_Sym_orig
if (length(@Fi_Sym)>0)
begin
set @Fi_sym="dba"."f_fi_sym_shorten"(@Fi_sym)
if (exists (select Fi_sym from "dba"."Fi_security" where Fi_Sym=@Fi_Sym))
select first @Fi_Sym = Fi_Sym,@CUSIP = CUSIP,@fi_Name = fi_Name,@fi_short_desc = fi_short_desc,@rbc_sym = rbc_sym,@fi_class = fi_class,@fi_Exchg = fi_Exchg,@Exp_dt = Exp_dt,@Start_dt = Start_dt,@fi_desc = fi_desc,@target_dt = target_dt,@Target_Price = Target_Price,@fi_sym_US = fi_sym_US,@secur_no = secur_no,@last_split_dt = last_split_dt,@created_dt = created_dt,
@updated_dt = updated_dt,@updated_by = updated_by,@target_Issued_Dt = target_Issued_Dt,@target_latest_By = target_latest_By,@target_currency = target_currency,
@high_52Week = high_52Week,@low_52Week = low_52Week,@target_note = "target_note"
from dba.Fi_security where fi_sym = @fi_sym
else if (exists (select Fi_sym from "dba"."Fi_security" where rbc_sym=@Fi_Sym))
select first @Fi_Sym = Fi_Sym,@CUSIP = CUSIP,@fi_Name = fi_Name,@fi_short_desc = fi_short_desc,@rbc_sym = rbc_sym,@fi_class = fi_class,@fi_Exchg = fi_Exchg,@Exp_dt = Exp_dt,@Start_dt = Start_dt,@fi_desc = fi_desc,@target_dt = target_dt,@Target_Price = Target_Price,@fi_sym_US = fi_sym_US,@secur_no = secur_no,@last_split_dt = last_split_dt,@created_dt = created_dt,
@updated_dt = updated_dt,@updated_by = updated_by,@target_Issued_Dt = target_Issued_Dt,@target_latest_By = target_latest_By,@target_currency = target_currency,
@high_52Week = high_52Week,@low_52Week = low_52Week,@target_note = "target_note"
from dba.Fi_security where rbc_sym = @fi_sym
else if (exists (select Fi_sym from "dba"."Fi_security" where fi_sym_US=@Fi_Sym))
select first @Fi_Sym = Fi_Sym,@CUSIP = CUSIP,@fi_Name = fi_Name,@fi_short_desc = fi_short_desc,@rbc_sym = rbc_sym,@fi_class = fi_class,@fi_Exchg = fi_Exchg,@Exp_dt = Exp_dt,@Start_dt = Start_dt,@fi_desc = fi_desc,@target_dt = target_dt,@Target_Price = Target_Price,@fi_sym_US = fi_sym_US,@secur_no = secur_no,@last_split_dt = last_split_dt,@created_dt = created_dt,
@updated_dt = updated_dt,@updated_by = updated_by,@target_Issued_Dt = target_Issued_Dt,@target_latest_By = target_latest_By,@target_currency = target_currency,
@high_52Week = high_52Week,@low_52Week = low_52Week,@target_note = "target_note"
from dba.Fi_security where fi_sym_US = @fi_sym
else
select first @Fi_Sym = Fi_Sym,@CUSIP = CUSIP,@fi_Name = fi_Name,@fi_short_desc = fi_short_desc,@rbc_sym = rbc_sym,@fi_class = fi_class,@fi_Exchg = fi_Exchg,@Exp_dt = Exp_dt,@Start_dt = Start_dt,@fi_desc = fi_desc,@target_dt = target_dt,@Target_Price = Target_Price,@fi_sym_US = fi_sym_US,@secur_no = secur_no,@last_split_dt = last_split_dt,@created_dt = created_dt,
@updated_dt = updated_dt,@updated_by = updated_by,@target_Issued_Dt = target_Issued_Dt,@target_latest_By = target_latest_By,@target_currency = target_currency,
@high_52Week = high_52Week,@low_52Week = low_52Week,@target_note = "target_note"
from dba.Fi_security where (Length(@Fi_Sym)>0 and
(Fi_Sym like @Fi_Sym_orig or (charindex('%' ,@Fi_Sym_orig)>0 and Fi_Sym like @Fi_Sym_orig) -- 151201 added wildcard search
or Fi_Sym like @Fi_Sym_orig+'%' ))
and (length(fi_name)=0 or // 140530 chgd form >0
(fi_name = @fi_Name or fi_name like @fi_name+'%'))
and (length(fi_desc)=0 or // ibid
(fi_desc = @fi_short_desc or fi_short_desc like @fi_short_desc+'%'))
and (length(fi_short_desc)=0 or // ibid
(fi_short_desc = @fi_short_desc or fi_short_desc like @fi_short_desc+'%'))
end
set @RowsFound = @@RowCount
return @RowsFound
end
and tested without any problem |
Is that just a display error, or does the SELECT during debugging really return the former value of the variable @target_node instead that of the column target_note?
I was hoping that was a display error but it returned null value. the reason I started debug mode was that I got nulll value in executing the procedure while a isql select return the proper value
It may not be problem with debug mode, since you say the error occurs when the procedure is executed normally... debug mode may just be exposing a flaw elsewhere.
Please show us the whole stored procedure, or at least the context showing (1) how the @variables are declared and (2) how the SELECT is executed.
Can you confirm also the dialect of the procedure - Watcom or TSQL. The TSQL will be of the form:
create procedure foo ... as ...
Could you try removing the // between the last two columns to see if that has any effect? It is the only time that occurs in the SQL and just happens to separate the columns of interest.
looks like the // and the position of target_note has no significance. I corrected a mistake(no such column as "time_stamp". I tried a simplified stored procedure with Tsql and also watcom sql, I got the same result. the only store procedure that that will yield correct result is using result clause
here is a simplified one:
create procedure dba.p_fi_security_selIntoVarBy_fiSym_Name_shortDesc_Or_Cusip( /* @parameter_name parameter_type [= default_value] [OUTPUT], ... */ @Fi_Sym varchar(20) output, /* std 8, up to 16 for option*/ @CUSIP varchar(16) output, @fi_Name varchar(76) output,@fi_short_desc varchar(80)= null output,@RowsFound integer output, @rbc_sym varchar(16)= null output,@fi_class varchar(4)= null output,@fi_Exchg varchar(8)= null output, @Exp_dt date= null output,@Start_dt date= null output,@fi_desc varchar(254)= null output, @target_dt date= null output,@Target_Price T_money= null output, @fi_sym_US varchar(16)= null output,@secur_no varchar(16)= null output,@last_split_dt datetime= null output, @created_dt datetime= null output,@updated_dt datetime= null output, @updated_by varchar(16)= null output,@target_Issued_Dt date= null output, @target_latest_By varchar(24)= null output,@target_currency varchar(4) output, @high_52Week T__money_2 output,@low_52Week T__money_2 output, @target_note varchar(252) output ) as begin --test mode - does not work, target_note should not be null select first @Fi_Sym = Fi_Sym,@CUSIP = CUSIP,@fi_Name = fi_Name,@fi_short_desc = fi_short_desc,@rbc_sym = rbc_sym,@fi_class = fi_class,@fi_Exchg = fi_Exchg,@Exp_dt = Exp_dt,@Start_dt = Start_dt,@fi_desc = fi_desc,@target_dt = target_dt,@Target_Price = Target_Price,@fi_sym_US = fi_sym_US,@secur_no = secur_no,@last_split_dt = last_split_dt,@created_dt = created_dt, @updated_dt = updated_dt,@updated_by = updated_by,@target_Issued_Dt = target_Issued_Dt,@target_latest_By = target_latest_By,@target_currency = target_currency, @target_note = @target_note,@high_52Week = high_52Week,@low_52Week = low_52Week from dba.Fi_security where fi_sym = @fi_sym endIn this simplified case, are the last two columns still the problem? What type is tmoney and tmoney_2?
not the last two column only the column for target_note has the same problem
as for t_money and t_money they are defined domain for numeric(13,4) and numeric(13,2)
as for the target_Note column it created in fi_security "target_note" VARCHAR(256) NULL
it is the last column of the table
I cannot reproduce this issue in 11.0.1.2506 server. I CREATE PROCEDURE that you posted appears to already modified from the SELECT INTO form to the @var = COLUMN form and has the problem @target_note = @target_note. So, I converted it back to the SELECT INTO form and created the Fi_security table based on the information in that procedure. I have also tried this in supported releases and am unable to reproduce.
Are you able to reproduce this issue in a newly created database?
thank you Chris. I tried careating a new database. but then I thought I could try something else. I had an isql window open and it has begin //... all the required variable declaration here select first @Fi_Sym = Fi_Sym,@CUSIP = CUSIP,@fi_Name = fi_Name,@fi_short_desc = fi_short_desc,@rbc_sym = rbc_sym,@fi_class = fi_class,@fi_Exchg = fi_Exchg,@Exp_dt = Exp_dt,@Start_dt = Start_dt,@fi_desc = fi_desc,@target_dt = target_dt,@Target_Price = Target_Price,@fi_sym_US = fi_sym_US,@secur_no = secur_no,@last_split_dt = last_split_dt,@created_dt = created_dt, @updated_dt = updated_dt,@updated_by = updated_by,@target_Issued_Dt = target_Issued_Dt,@target_latest_By = target_latest_By,@target_currency = target_currency, @high_52Week = high_52Week,@low_52Week = low_52Week,@target_note = "target_note" from dba.Fi_security where fi_sym = @fi_sym select @Fi_Sym, @target_note, @CUSIP, @fi_Name, @fi_short_desc, @rbc_sym, @fi_class, @fi_Exchg, @Exp_dt, @Start_dt, @fi_desc, @target_dt, @Target_Price, @fi_sym_US, @secur_no, @last_split_dt, @created_dt, @updated_dt, @updated_by, @target_Issued_Dt, @target_latest_By, @target_currency, @high_52Week, @low_52Week, @time_stamp end
that worked without trouble. so I went ahead and modified to create a procedure:
create procedure dba.testFi_securitySel( inout @Fi_Sym VARCHAR(20) default null , inout @CUSIP VARCHAR(16) default null , inout @fi_Name VARCHAR(76) default null , inout @fi_short_desc VARCHAR(80) default null , out @RowsFound int , out @rbc_sym VARCHAR(16) , out @fi_class VARCHAR(4) , out @fi_Exchg VARCHAR(8) , out @Exp_dt DATE , out @Start_dt DATE , out @fi_desc VARCHAR(254) , out @target_dt DATE , out @Target_Price T_money , out @fi_sym_US VARCHAR(16) , out @secur_no VARCHAR(16) , out @last_split_dt datetime , out @created_dt datetime , out @updated_dt datetime , out @updated_by VARCHAR(16) , out @target_Issued_Dt date , out @target_latest_By varchar(24) , out @target_currency varchar(4) , out @high_52Week Tmoney_2 , out @low_52Week Tmoney_2 , out @target_note varchar(256) , out @time_stamp timestamp) as //Nondeterministic begin select first @Fi_Sym = Fi_Sym,@CUSIP = CUSIP,@fi_Name = fi_Name,@fi_short_desc = fi_short_desc,@rbc_sym = rbc_sym,@fi_class = fi_class,@fi_Exchg = fi_Exchg,@Exp_dt = Exp_dt,@Start_dt = Start_dt,@fi_desc = fi_desc,@target_dt = target_dt,@Target_Price = Target_Price,@fi_sym_US = fi_sym_US,@secur_no = secur_no,@last_split_dt = last_split_dt,@created_dt = created_dt, @updated_dt = updated_dt,@updated_by = updated_by,@target_Issued_Dt = target_Issued_Dt,@target_latest_By = target_latest_By,@target_currency = target_currency, @high_52Week = high_52Week,@low_52Week = low_52Week,@target_note = "target_note" from dba.Fi_security where fi_sym = @fi_sym end
and the test result was good so I'm in the process of expanding to cover other scenario. so far so good and still worked.
once I add alll the code required and is works then I will drop the original proc and use this new proc text to create from isql with the correct name and test if that go well, I will post a reply accordingly