using IF EXISTS SQl in powerscript

[AR]

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

[Jerry Siegel [TeamSybase]]

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.111...@sybase.com...

[Roland Smith [TeamSybase]]

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.111...@sybase.com...

[AR]

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

[Jeremy Lakeman]

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

[Chris Fauvel]

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.111...@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]]

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" <avach...@gmail.com> wrote in message
news:518ffea9-5b3e-4644...@e8g2000yqo.googlegroups.com...