Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

SQL query creation problem

5 posts in General Discussion Last posting was on 2010-04-29 07:38:21.0Z
Partha Posted on 2010-04-28 17:04:42.0Z
Sender: 6b02.4bd866e0.1804289383@sybase.com
From: Partha
Newsgroups: ianywhere.public.general
Subject: SQL query creation problem
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bd86aaa.6bed.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Apr 2010 10:04:42 -0700
X-Trace: forums-1-dub 1272474282 10.22.241.41 (28 Apr 2010 10:04:42 -0700)
X-Original-Trace: 28 Apr 2010 10:04:42 -0700, 10.22.241.41
Lines: 29
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:8008
Article PK: 7031

Hi All,

I have write the following SQL dynamic query in a store
procedure.


set ls_column_name = 'select sum(isnull(tb_1_' +
convert(varchar(2),(Month(ldt_from_date))) + ', 0.00,tb_1_'
+ convert(varchar(2),(Month(ldt_from_date))) +
')) into ld_amt from tb_1 where
code in(select code from tb_2 where code_yn = N)
;';

execute(ls_column_name);

Now when I am trying to run the query it does not run.

I have read the form query through message, its look like :

select sum(isnull(tb_1_1, 0.00,tb_1_1)) into ld_amt from
tb_1 where
code in(select code from tb_2 where code_yn = N)
;

I think It does not recognize the N.

How I will solve the problem.

I am using sql anywhere 9.0.2


Jeff Albion [Sybase iAnywhere] Posted on 2010-04-28 17:34:44.0Z
From: "Jeff Albion [Sybase iAnywhere]" <firstname.lastname@ianywhere.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: SQL query creation problem
References: <4bd86aaa.6bed.1681692777@sybase.com>
In-Reply-To: <4bd86aaa.6bed.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bd871b4$1@forums-1-dub>
Date: 28 Apr 2010 10:34:44 -0700
X-Trace: forums-1-dub 1272476084 10.22.241.152 (28 Apr 2010 10:34:44 -0700)
X-Original-Trace: 28 Apr 2010 10:34:44 -0700, vip152.sybase.com
Lines: 51
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:8009
Article PK: 7032

Hi Partha,

What's the actual error you're seeing?

---

You will need to double-up the quotes in the original string to insert
single quotes into the computed string:

set ls_column_name = 'select sum(isnull(tb_1_' +
convert(varchar(2),(Month(ldt_from_date))) + ', 0.00,tb_1_' +
convert(varchar(2),(Month(ldt_from_date))) + ')) into ld_amt from tb_1
where code in(select code from tb_2 where code_yn = ''N'') ;';

---

Also, I assume you're intending to perform an "EXECUTE IMMEDIATE" (
http://dcx.sybase.com/index.html#1101en/dbreference_en11/execute-immediate-statement.html
), after this statement, e.g.:

EXECUTE IMMEDIATE ls_column_name;

Regards,

On 4/28/2010 1:04 PM, Partha wrote:
> set ls_column_name = 'select sum(isnull(tb_1_' +
> convert(varchar(2),(Month(ldt_from_date))) + ', 0.00,tb_1_'
> + convert(varchar(2),(Month(ldt_from_date))) +
> ')) into ld_amt from tb_1 where
> code in(select code from tb_2 where code_yn = N)
> ;';
>
> execute(ls_column_name);
>
> Now when I am trying to run the query it does not run.
>
> I have read the form query through message, its look like :
>
> select sum(isnull(tb_1_1, 0.00,tb_1_1)) into ld_amt from
> tb_1 where
> code in(select code from tb_2 where code_yn = N)

--
Jeff Albion, Sybase iAnywhere

iAnywhere Developer Community :
http://www.sybase.com/developer/library/sql-anywhere-techcorner
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
SQL Anywhere Patches and EBFs :
http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0
Report a Bug/Open a Case : http://case-express.sybase.com/cx/


Partha Posted on 2010-04-28 17:50:53.0Z
Sender: 6b02.4bd866e0.1804289383@sybase.com
From: Partha
Newsgroups: ianywhere.public.general
Subject: Re: SQL query creation problem
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bd8757d.6d4f.1681692777@sybase.com>
References: <4bd871b4$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 28 Apr 2010 10:50:53 -0700
X-Trace: forums-1-dub 1272477053 10.22.241.41 (28 Apr 2010 10:50:53 -0700)
X-Original-Trace: 28 Apr 2010 10:50:53 -0700, 10.22.241.41
Lines: 66
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:8010
Article PK: 7036

Sorry it doesnot work.
Its gives me the following error :

Column 'N' not found.

> Hi Partha,
>
> What's the actual error you're seeing?
>
> ---
>
> You will need to double-up the quotes in the original
> string to insert single quotes into the computed string:
>
> set ls_column_name = 'select sum(isnull(tb_1_' +
> convert(varchar(2),(Month(ldt_from_date))) + ', 0.00
> ,tb_1_' + convert(varchar(2),(Month(ldt_from_date))) +
> ')) into ld_amt from tb_1 where code in(select code from
> tb_2 where code_yn = ''N'') ;';
>
> ---
>
> Also, I assume you're intending to perform an "EXECUTE
> IMMEDIATE" (
>
http://dcx.sybase.com/index.html#1101en/dbreference_en11/execute-immediate-statement.html
> ), after this statement, e.g.:
>
> EXECUTE IMMEDIATE ls_column_name;
>
> Regards,
>
> On 4/28/2010 1:04 PM, Partha wrote:
> > set ls_column_name = 'select sum(isnull(tb_1_' +
> > convert(varchar(2),(Month(ldt_from_date))) + ', 0.00
> > ,tb_1_' + convert(varchar(2)
> > ,(Month(ldt_from_date))) + ')) into ld_amt
> > from tb_1 where code in(select code from
> > tb_2 where code_yn = N) ;';
> >
> > execute(ls_column_name);
> >
> > Now when I am trying to run the query it does not run.
> >
> > I have read the form query through message, its look
> like : >
> > select sum(isnull(tb_1_1, 0.00,tb_1_1)) into ld_amt from
> > tb_1 where
> > code in(select code from tb_2 where code_yn
> = N)
>
> --
> Jeff Albion, Sybase iAnywhere
>
> iAnywhere Developer Community :
>
http://www.sybase.com/developer/library/sql-anywhere-techcorner
> iAnywhere Documentation :
> http://www.ianywhere.com/developer/product_manuals SQL
> Anywhere Patches and EBFs :
>
http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0
> Report a Bug/Open a Case :
> http://case-express.sybase.com/cx/


Volker Barth <No_VBarth Posted on 2010-04-29 07:38:21.0Z
From: Volker Barth <No_VBarth@Spam_GLOBAL-FINANZ.de>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; de; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: SQL query creation problem
References: <4bd871b4$1@forums-1-dub> <4bd8757d.6d4f.1681692777@sybase.com>
In-Reply-To: <4bd8757d.6d4f.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bd9376d$1@forums-1-dub>
Date: 29 Apr 2010 00:38:21 -0700
X-Trace: forums-1-dub 1272526701 10.22.241.152 (29 Apr 2010 00:38:21 -0700)
X-Original-Trace: 29 Apr 2010 00:38:21 -0700, vip152.sybase.com
Lines: 91
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:8012
Article PK: 7035


Partha wrote:
> Sorry it doesnot work.
> Its gives me the following error :
>
> Column 'N' not found.

Have you used two single quotes (as Jeff suggested) or a double quote
(which would be interpreted as a column delimiter and as such wrong)?

If the correct static query would be:

select sum(isnull(tb_1_1, 0.00,tb_1_1)) into ld_amt
from tb_1 where code in (select code from tb_2 where code_yn = 'N')

then the EXECUTE IMMEDIATE form would be (ignoring the convert parts):

execute immediate 'select sum(isnull(tb_1_1, 0.00,tb_1_1)) into ld_amt
from tb_1 where code in (select code from tb_2 where code_yn = ''N'')'

Besides that, you could get rid of the explicit convert by using SA's
string concatenation operator || which casts numbers to strings
inherently, such as
...tb_1_' || Month(ldt_from_date) || ', 0.00...

Much easier in SA's SQL dialect than in Transact-SQL, methinks:)



Regards
Volker


>> Hi Partha,
>>
>> What's the actual error you're seeing?
>>
>> ---
>>
>> You will need to double-up the quotes in the original
>> string to insert single quotes into the computed string:
>>
>> set ls_column_name = 'select sum(isnull(tb_1_' +
>> convert(varchar(2),(Month(ldt_from_date))) + ', 0.00
>> ,tb_1_' + convert(varchar(2),(Month(ldt_from_date))) +
>> ')) into ld_amt from tb_1 where code in(select code from
>> tb_2 where code_yn = ''N'') ;';
>>
>> ---
>>
>> Also, I assume you're intending to perform an "EXECUTE
>> IMMEDIATE" (
>>
> http://dcx.sybase.com/index.html#1101en/dbreference_en11/execute-immediate-statement.html
>> ), after this statement, e.g.:
>>
>> EXECUTE IMMEDIATE ls_column_name;
>>
>> Regards,
>>
>> On 4/28/2010 1:04 PM, Partha wrote:
>>> set ls_column_name = 'select sum(isnull(tb_1_' +
>>> convert(varchar(2),(Month(ldt_from_date))) + ', 0.00
>>> ,tb_1_' + convert(varchar(2)
>>> ,(Month(ldt_from_date))) + ')) into ld_amt
>>> from tb_1 where code in(select code from
>>> tb_2 where code_yn = N) ;';
>>>
>>> execute(ls_column_name);
>>>
>>> Now when I am trying to run the query it does not run.
>>>
>>> I have read the form query through message, its look
>> like :>
>>> select sum(isnull(tb_1_1, 0.00,tb_1_1)) into ld_amt from
>>> tb_1 where
>>> code in(select code from tb_2 where code_yn
>> = N)
>>
>> --
>> Jeff Albion, Sybase iAnywhere
>>
>> iAnywhere Developer Community :
>>
> http://www.sybase.com/developer/library/sql-anywhere-techcorner
>> iAnywhere Documentation :
>> http://www.ianywhere.com/developer/product_manuals SQL
>> Anywhere Patches and EBFs :
>>
> http://downloads.sybase.com/swd/summary.do?baseprod=144&client=ianywhere&timeframe=0
>> Report a Bug/Open a Case :
>> http://case-express.sybase.com/cx/


Richard Biffl Posted on 2010-04-29 07:30:12.0Z
From: "Richard Biffl" <sybaseforums@removvethiisphrraseblacklettersoftware.com>
Newsgroups: ianywhere.public.general
References: <4bd86aaa.6bed.1681692777@sybase.com>
In-Reply-To: <4bd86aaa.6bed.1681692777@sybase.com>
Subject: Re: SQL query creation problem
Lines: 1
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="Windows-1252"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8089.726
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8089.726
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bd93584$1@forums-1-dub>
Date: 29 Apr 2010 00:30:12 -0700
X-Trace: forums-1-dub 1272526212 10.22.241.152 (29 Apr 2010 00:30:12 -0700)
X-Original-Trace: 29 Apr 2010 00:30:12 -0700, vip152.sybase.com
X-Authenticated-User: innsbruck_beta
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:8011
Article PK: 7033


"Partha" wrote in message news:4bd86aaa.6bed.1681692777@sybase.com...
> I have read the form query through message, its look like :
>
> select sum(isnull(tb_1_1, 0.00,tb_1_1)) into ld_amt from
> tb_1 where
> code in(select code from tb_2 where code_yn = N)
> ;
>
> I think It does not recognize the N.

The last clause should have single-quote marks around the literal string
value N:

where code_yn = 'N'

Because you are building the query string, you need to escape from the usual
string-parsing by using 2 single-quote marks within the string you are
building. For example:

+ ' where code_yn = ''N'');';

Richard