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.

asked 16 Jan '18, 00:29

gg99's gravatar image

gg99
227293038
accept rate: 10%

edited 16 Jan '18, 00:43

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?

(16 Jan '18, 02:10) Volker Barth
Replies hidden

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

(16 Jan '18, 07:37) gg99

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.

(16 Jan '18, 09:10) Breck Carter

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.

(16 Jan '18, 09:33) Chris Keating

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
end
(16 Jan '18, 09:55) gg99

In this simplified case, are the last two columns still the problem? What type is tmoney and tmoney_2?

(16 Jan '18, 11:45) Chris Keating

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

(16 Jan '18, 13:42) gg99
1

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?

(17 Jan '18, 09:37) Chris Keating

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

(17 Jan '18, 22:22) gg99
More comments hidden
showing 5 of 9 show all flat view

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

permanent link

answered 17 Jan '18, 23:21

gg99's gravatar image

gg99
227293038
accept rate: 10%

edited 19 Jan '18, 16:34

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×143
×125
×106

question asked: 16 Jan '18, 00:29

question was seen: 1,432 times

last updated: 19 Jan '18, 16:34