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.

using IF EXISTS SQl in powerscript

7 posts in Powerscript Last posting was on 2009-09-10 19:05:06.0Z
AR Posted on 2009-09-03 20:16:14.0Z
Sender: 10f9.4aa0232f.1804289383@sybase.com
From: AR
Newsgroups: sybase.public.powerbuilder.powerscript
Subject: using IF EXISTS SQl in powerscript
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4aa0240e.1116.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 3 Sep 2009 13:16:14 -0700
X-Trace: forums-1-dub 1252008974 10.22.241.41 (3 Sep 2009 13:16:14 -0700)
X-Original-Trace: 3 Sep 2009 13:16:14 -0700, 10.22.241.41
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.powerscript:27044
Article PK: 793527

Can I use "IF EXISTS" SQL command in Powerscript. I want to
use something like below in Powerscript. I am using Sybase
ASE 12.5 DB.
If i cant put this in powerscript, then I am planning to put
the below code in a SP & call the SP from Powerscript.

if exists(select 1 from avt_po_invoicing where line_id =
671156 ) or
exists (select 1 from avt_po_rebilling where line_id =
671156 )
select 4 INTO :ll_ret ELSE select 5 into :ll_ret ;

Thnx for your response
AR


Roland Smith [TeamSybase] Posted on 2009-09-03 20:45:54.0Z
From: "Roland Smith [TeamSybase]" <rsmith_at_trusthss_dot_com>
Newsgroups: sybase.public.powerbuilder.powerscript
References: <4aa0240e.1116.1681692777@sybase.com>
Subject: Re: using IF EXISTS SQl in powerscript
Lines: 38
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5512
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa02b02$1@forums-1-dub>
Date: 3 Sep 2009 13:45:54 -0700
X-Trace: forums-1-dub 1252010754 10.22.241.152 (3 Sep 2009 13:45:54 -0700)
X-Original-Trace: 3 Sep 2009 13:45:54 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.powerscript:27046
Article PK: 761702

I use the following code all the time:

String ls_exists, ls_name

ls_name = "something"

ls_exists = "FALSE"

SELECT 'TRUE' INTO :ls_exists
FROM master.dbo.syslogins A
WHERE A.name = suser_name()
AND EXISTS (
SELECT * FROM dba.TABLE_NAME
WHERE COLUMN_NAME = :ls_name
);

If ls_exists = "TRUE" Then
...
End If

<AR> wrote in message news:4aa0240e.1116.1681692777@sybase.com...
> Can I use "IF EXISTS" SQL command in Powerscript. I want to
> use something like below in Powerscript. I am using Sybase
> ASE 12.5 DB.
> If i cant put this in powerscript, then I am planning to put
> the below code in a SP & call the SP from Powerscript.
>
> if exists(select 1 from avt_po_invoicing where line_id =
> 671156 ) or
> exists (select 1 from avt_po_rebilling where line_id =
> 671156 )
> select 4 INTO :ll_ret ELSE select 5 into :ll_ret ;
>
> Thnx for your response
> AR


AR Posted on 2009-09-03 21:21:53.0Z
Sender: 10f9.4aa0232f.1804289383@sybase.com
From: AR
Newsgroups: sybase.public.powerbuilder.powerscript
Subject: Re: using IF EXISTS SQl in powerscript
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4aa03371.1315.1681692777@sybase.com>
References: <4aa02b02$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 3 Sep 2009 14:21:53 -0700
X-Trace: forums-1-dub 1252012913 10.22.241.41 (3 Sep 2009 14:21:53 -0700)
X-Original-Trace: 3 Sep 2009 14:21:53 -0700, 10.22.241.41
Lines: 55
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.powerscript:27047
Article PK: 793523

Thnak you a lot. This is really a smart solution. I liked it
very much. I modified the SQl a bit so that it doesnt refer
to the master DB, doesnt have to scan all rows of the table
& refers to one of our own DB:

String ls_exists
ls_exists = "FALSE"

SELECT 'TRUE' INTO :ls_exists
FROM avt_system_environment
WHERE 1 = 2
OR EXISTS (
SELECT 1 FROM avt_po_invoicing
WHERE line_id = :adb_line_id ) OR exists (select 1
from avt_po_rebilling where line_id = :adb_line_id );

AR

> I use the following code all the time:
>
> String ls_exists, ls_name
>
> ls_name = "something"
>
> ls_exists = "FALSE"
>
> SELECT 'TRUE' INTO :ls_exists
> FROM master.dbo.syslogins A
> WHERE A.name = suser_name()
> AND EXISTS (
> SELECT * FROM dba.TABLE_NAME
> WHERE COLUMN_NAME = :ls_name
> );
>
> If ls_exists = "TRUE" Then
> ...
> End If
>
>
> <AR> wrote in message
> > news:4aa0240e.1116.1681692777@sybase.com... Can I use
> > "IF EXISTS" SQL command in Powerscript. I want to use
> > something like below in Powerscript. I am using Sybase
> > ASE 12.5 DB. If i cant put this in powerscript, then I
> > am planning to put the below code in a SP & call the SP
> from Powerscript. >
> > if exists(select 1 from avt_po_invoicing where line_id
> > = 671156 ) or
> > exists (select 1 from avt_po_rebilling where line_id
> > = 671156 )
> > select 4 INTO :ll_ret ELSE select 5 into :ll_ret ;
> >
> > Thnx for your response
> > AR
>
>


Jeremy Lakeman Posted on 2009-09-04 11:07:11.0Z
From: Jeremy Lakeman <jeremy.lakeman@gmail.com>
Newsgroups: sybase.public.powerbuilder.powerscript
Subject: Re: using IF EXISTS SQl in powerscript
Date: Fri, 4 Sep 2009 04:07:11 -0700 (PDT)
Organization: http://groups.google.com
Lines: 57
Message-ID: <9453c42a-b0e5-47b5-8c6c-5368ef441e4a@e4g2000prn.googlegroups.com>
References: <4aa02b02$1@forums-1-dub> <4aa03371.1315.1681692777@sybase.com>
NNTP-Posting-Host: 122.49.168.173
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1252062432 30535 127.0.0.1 (4 Sep 2009 11:07:12 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 4 Sep 2009 11:07:12 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: e4g2000prn.googlegroups.com; posting-host=122.49.168.173; posting-account=euaBtgoAAAC_vDxfsxmpMKlBxHaHpmRS
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-GB; rv:1.9.1.2) Gecko/20090729 Firefox/3.5.2 (.NET CLR 3.5.30729),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!newsfeed.yul.equant.net!novia!news-out.readnews.com!news-xxxfer.readnews.com!postnews.google.com!e4g2000prn.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.powerscript:27050
Article PK: 761703


On Sep 4, 6:21 am, AR wrote:
> Thnak you a lot. This is really a smart solution. I liked it
> very much. I modified the SQl a bit so that it doesnt refer
> to the master DB, doesnt have to scan all rows of the table
> & refers to one of our own DB:
>
> String ls_exists
> ls_exists = "FALSE"
>
> SELECT 'TRUE' INTO :ls_exists
>    FROM avt_system_environment
>    WHERE 1 = 2
>    OR EXISTS (
>        SELECT 1 FROM avt_po_invoicing
>        WHERE line_id = :adb_line_id  ) OR exists (select  1
> from avt_po_rebilling where   line_id  = :adb_line_id );
>
> AR
>
> > I use the following code all the time:
>
> > String ls_exists, ls_name
>
> > ls_name = "something"
>
> > ls_exists = "FALSE"
>
> > SELECT 'TRUE' INTO :ls_exists
> >    FROM master.dbo.syslogins A
> >    WHERE A.name = suser_name()
> >    AND EXISTS (
> >        SELECT * FROM dba.TABLE_NAME
> >        WHERE COLUMN_NAME = :ls_name
> >    );
>
> > If ls_exists = "TRUE" Then
> >    ...
> > End If
>
> > <AR> wrote in message
> > >news:4aa0240e.1116.1681692777@sybase.com... Can I use
> > > "IF EXISTS" SQL command in Powerscript. I want to use
> > > something like below in Powerscript. I am using Sybase
> > > ASE 12.5 DB. If i cant put this in powerscript, then I
> > > am planning to put the below code in a SP & call the SP
> > from Powerscript. >
> > > if exists(select  1 from avt_po_invoicing where line_id
> > > = 671156 ) or
> > >  exists (select  1 from avt_po_rebilling where line_id
> > > = 671156 )
> > >  select  4 INTO :ll_ret ELSE select 5 into :ll_ret ;
>
> > > Thnx for your response
> > > AR

Or you could use execute immediate or one of the dynamic sql cursor
formats...


"Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel Posted on 2009-09-03 20:41:33.0Z
From: "Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel@yahoo!.com>
Newsgroups: sybase.public.powerbuilder.powerscript
References: <4aa0240e.1116.1681692777@sybase.com>
Subject: Re: using IF EXISTS SQl in powerscript
Lines: 28
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa029fd$1@forums-1-dub>
Date: 3 Sep 2009 13:41:33 -0700
X-Trace: forums-1-dub 1252010493 10.22.241.152 (3 Sep 2009 13:41:33 -0700)
X-Original-Trace: 3 Sep 2009 13:41:33 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.powerscript:27045
Article PK: 793522

You can do embedded SQL in a script like SELECT 1 into :i_fubar FROM ....
but most experienced PBers consider it bad practice - inefficient and hard
to maintain. I'd go with the SP and declare it in the transaction object as
RPCFUNC. Much nicer encapsulation!

--
Report Bugs to Sybase: http://case-express.sybase.com/cx/welcome.do
Product Enhancement Requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement

<AR> wrote in message news:4aa0240e.1116.1681692777@sybase.com...
> Can I use "IF EXISTS" SQL command in Powerscript. I want to
> use something like below in Powerscript. I am using Sybase
> ASE 12.5 DB.
> If i cant put this in powerscript, then I am planning to put
> the below code in a SP & call the SP from Powerscript.
>
> if exists(select 1 from avt_po_invoicing where line_id =
> 671156 ) or
> exists (select 1 from avt_po_rebilling where line_id =
> 671156 )
> select 4 INTO :ll_ret ELSE select 5 into :ll_ret ;
>
> Thnx for your response
> AR


Chris Fauvel Posted on 2009-09-10 17:32:45.0Z
From: Chris Fauvel <avachet1965@gmail.com>
Newsgroups: sybase.public.powerbuilder.powerscript
Subject: Re: using IF EXISTS SQl in powerscript
Date: Thu, 10 Sep 2009 10:32:45 -0700 (PDT)
Organization: http://groups.google.com
Lines: 37
Message-ID: <518ffea9-5b3e-4644-98d9-22acb9672bf0@e8g2000yqo.googlegroups.com>
References: <4aa0240e.1116.1681692777@sybase.com> <4aa029fd$1@forums-1-dub>
NNTP-Posting-Host: 64.203.106.198
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1252603965 9717 127.0.0.1 (10 Sep 2009 17:32:45 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 10 Sep 2009 17:32:45 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: e8g2000yqo.googlegroups.com; posting-host=64.203.106.198; posting-account=DJjFPAoAAAAeQvDjk3ABu2l0irywGuZP
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Trident/4.0; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022; MS-RTC LM 8; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; yie8),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!e8g2000yqo.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.powerscript:27054
Article PK: 793526

On Sep 3, 4:41 pm, "Jerry Siegel [TeamSybase]"

<jNOsSPAMsiegel@yahoo!.com> wrote:
> You can do embedded SQL in a script like SELECT 1 into :i_fubar FROM ....
> but most experienced PBers consider it bad practice - inefficient and hard
> to maintain. I'd go with the SP and declare it in the transaction object as
> RPCFUNC. Much nicer encapsulation!
>
> --
> Report Bugs to Sybase:http://case-express.sybase.com/cx/welcome.do
> Product Enhancement Requests:http://my.isug.com/cgi-bin/1/c/submit_enhancement
>
>
>
> <AR> wrote in messagenews:4aa0240e.1116.1681692777@sybase.com...
> > Can I use "IF EXISTS" SQL command in Powerscript. I want to
> > use something like below in Powerscript. I am using Sybase
> > ASE 12.5 DB.
> > If i cant put this in powerscript, then I am planning to put
> > the below code in a SP & call the SP from Powerscript.
>
> > if exists(select  1 from avt_po_invoicing where line_id  =
> > 671156 ) or
> >  exists (select  1 from avt_po_rebilling where line_id  =
> > 671156 )
> >  select  4 INTO :ll_ret ELSE select 5 into :ll_ret ;
>
> > Thnx for your response
> > AR- Hide quoted text -
>
> - Show quoted text -

I agree that;s how I would have done it....easier to modify the stored
procedure than modify the PB app and recompile and redeploy.


"Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel Posted on 2009-09-10 19:05:06.0Z
From: "Jerry Siegel [TeamSybase]" <jNOsSPAMsiegel@yahoo!.com>
Newsgroups: sybase.public.powerbuilder.powerscript
References: <4aa0240e.1116.1681692777@sybase.com> <4aa029fd$1@forums-1-dub> <518ffea9-5b3e-4644-98d9-22acb9672bf0@e8g2000yqo.googlegroups.com>
Subject: Re: using IF EXISTS SQl in powerscript
Lines: 54
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4aa94de2@forums-1-dub>
Date: 10 Sep 2009 12:05:06 -0700
X-Trace: forums-1-dub 1252609506 10.22.241.152 (10 Sep 2009 12:05:06 -0700)
X-Original-Trace: 10 Sep 2009 12:05:06 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.powerscript:27057
Article PK: 793530

Reusable too! Anyplace in the application that needs to know (guessing at
the semantics) if billing is still open for an item can use the same logic.
And if the business rule changes - like ignore if the item was part of a
special offer - you change it in one place and don't have to hunt down all
uses, change *and test* each.
It also makes the patch process more modular, as you can deploy the script
to update the SP separately. With an application build you need to be sure
all changes in that code line are sufficiently tested to make a release.

--
Report Bugs to Sybase: http://case-express.sybase.com/cx/welcome.do
Product Enhancement Requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement


"Chris Fauvel" <avachet1965@gmail.com> wrote in message
news:518ffea9-5b3e-4644-98d9-22acb9672bf0@e8g2000yqo.googlegroups.com...
On Sep 3, 4:41 pm, "Jerry Siegel [TeamSybase]"

<jNOsSPAMsiegel@yahoo!.com> wrote:
> You can do embedded SQL in a script like SELECT 1 into :i_fubar FROM ....
> but most experienced PBers consider it bad practice - inefficient and hard
> to maintain. I'd go with the SP and declare it in the transaction object
> as
> RPCFUNC. Much nicer encapsulation!
>
> --
> Report Bugs to Sybase:http://case-express.sybase.com/cx/welcome.do
> Product Enhancement
> Requests:http://my.isug.com/cgi-bin/1/c/submit_enhancement
>
>
>
> <AR> wrote in messagenews:4aa0240e.1116.1681692777@sybase.com...
> > Can I use "IF EXISTS" SQL command in Powerscript. I want to
> > use something like below in Powerscript. I am using Sybase
> > ASE 12.5 DB.
> > If i cant put this in powerscript, then I am planning to put
> > the below code in a SP & call the SP from Powerscript.
>
> > if exists(select 1 from avt_po_invoicing where line_id =
> > 671156 ) or
> > exists (select 1 from avt_po_rebilling where line_id =
> > 671156 )
> > select 4 INTO :ll_ret ELSE select 5 into :ll_ret ;
>
> > Thnx for your response
> > AR- Hide quoted text -
>
> - Show quoted text -

I agree that;s how I would have done it....easier to modify the stored
procedure than modify the PB app and recompile and redeploy.