SELECT INTO for more than one value?

[Laurel]

Is there a syntax to do the following for more than one column/variable?

SELECT first_name into :f_name from person where person_id = 123555;

Something like this (which doesn't work

SELECT (first_name,last_name) into (:f_name, :l_name) where person_id =
123555;

[Terry Dykstra]

Using a cursor you can do it like this:

DECLARE acctstat_curs CURSOR FOR
SELECT ACCOUNT_STATUS, EXPIRY_DATE FROM DBA_USERS
WHERE USERNAME = :as_userid;
OPEN acctstat_curs ;
FETCH acctstat_curs INTO :as_currentstatus, :ad_expirydate;
CLOSE acctstat_curs;

--
Terry Dykstra (TeamSybase)
http://powerbuilder.codeXchange.sybase.com/
http://casexpress.sybase.com/cx/cx.stm
product enhancement requests:
http://my.isug.com/cgi-bin/1/c/submit_enhancement

"Laurel" <Fake...@Hotmail.com> wrote in message
news:479775af$1@forums-1-dub...

[Bruce Armstrong [TeamSybase]]

Use a datawindow object in a datastore instead.

"Laurel" <Fake...@Hotmail.com> wrote in message
news:479775af$1@forums-1-dub...

[Jerry Siegel [TeamSybase]]

Or create the DS dynamically from syntax...

"Bruce Armstrong [TeamSybase]" <NOCANSPAM_br...@teamsybase.com>
wrote in message news:47977e01@forums-1-dub...

[Jerry Siegel [TeamSybase]]

You'd need a cursor if more than 1 row is to be returned, but I'd expect
OP's select (minus the parentheses) to work.
What DBMS?

"Terry Dykstra" <tddy...@forestoil.ca> wrote in message
news:479778c5$1@forums-1-dub...

[Laurel]

Thanks to all for such quick responses.

"Laurel" <Fake...@Hotmail.com> wrote in message
news:479775af$1@forums-1-dub...

[KL]

Since you are providing an id number, I will suspect that
only one row is returned.
If this is true then, this should work:

SELECT first_name, last_name
INTO :f_name, :l_name
FROM person
WHERE person_id = 123555
USING SQLCA;

If you are only bringing one row back, you can retrieve all
the columns, if you want. In that case, a quick datastore
would be about as easy, well actually easier.

If it doesn't work, give more info as to PB version,
database,
error you are getting, etc.

If you expect more than one row to be returned, follow the
other
suggestions.

Good luck.

[KL]

Another GUESS as to what they may be trying to do:

SELECT (first_name + ' ' + last_name)
INTO :name
FROM person
WHERE person_id = 123555;

Just a guess.

Have fun.

[Tyler Cruse]

If you only have a hammer, everything looks like a nail.

"Bruce Armstrong [TeamSybase]" <NOCANSPAM_br...@teamsybase.com>
wrote in message news:47977e01@forums-1-dub...

[Bruce Armstrong [TeamSybase]]

???

Embedded SQL is dificult to maintain. Folks would be wise to abandon
it in favor of stored procedures and datawindow objects.

If you have to drive different kinds of nails, you could choose to use
your bare hands in some cases, but I prefer to use a hammer ever time.

[Tyler Cruse]

Stored procedures are usually benificial and I would agree with that part of
your statement.

However, embedded SQL is many times much easier to maintain, mainly be cause
of a factor of 10 in the amount of code that is involved.

Attempting to "force" every task into a datawindow is a real dis-service to
PB users/supporters and to Sybase that is trying to move the product
forward.

Here is my evidence:

Option 1:
select pat_last, pat_first into :pat_last, :pat_first from patients
where patient_key = 'xxxx' using sqlca ;
<-- check sqlca for errors -->

Option 2:
Create the datawindow:
release 11;
datawindow(units=0 timer_interval=0 color=1073741824 processing=0 HTMLDW=no
print.printername="" print.documentname="" print.orientation = 0
print.margin.left = 110 print.margin.right = 110 print.margin.top = 96
print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0
print.canusedefaultprinter=yes print.prompt=no print.buttons=no
print.preview.buttons=no print.cliptext=no print.overrideprintjob=no
print.collate=yes print.preview.outline=yes hidegrayline=no
showbackcoloronxp=no )
header(height=72 color="536870912" )
summary(height=0 color="536870912" )
footer(height=0 color="536870912" )
detail(height=84 color="536870912" )
table(column=(type=char(20) updatewhereclause=yes name=pat_last
dbname="patients.pat_last" )
column=(type=char(20) updatewhereclause=yes name=pat_first
dbname="patients.pat_first" )
retrieve="PBSELECT( VERSION(400) TABLE(NAME=~"patients~" )
COLUMN(NAME=~"patients.pat_last~")
COLUMN(NAME=~"patients.pat_first~")WHERE( EXP1 =~"patients.patient_key~"
OP =~"=~" EXP2 =~":patient_key~" ) ) ARG(NAME = ~"patient_key~" TYPE =
string) " arguments=(("patient_key", string)) )
text(band=header alignment="2" text="Pat Last" border="0" color="33554432"
x="5" y="4" height="64" width="576" html.valueishtml="0" name=pat_last_t
visible="1" font.face="Arial" font.height="-10" font.weight="400"
font.family="2" font.pitch="2" font.charset="0" background.mode="1"
background.color="536870912" )
text(band=header alignment="2" text="Pat First" border="0" color="33554432"
x="585" y="4" height="64" width="576" html.valueishtml="0" name=pat_first_t
visible="1" font.face="Arial" font.height="-10" font.weight="400"
font.family="2" font.pitch="2" font.charset="0" background.mode="1"
background.color="536870912" )
column(band=detail id=1 alignment="0" tabsequence=32766 border="0"
color="33554432" x="5" y="4" height="76" width="576" format="[general]"
html.valueishtml="0" name=pat_last visible="1" edit.limit=20 edit.case=any
edit.autoselect=yes edit.autohscroll=yes font.face="Arial"
font.height="-10" font.weight="400" font.family="2" font.pitch="2"
font.charset="0" background.mode="1" background.color="536870912" )
column(band=detail id=2 alignment="0" tabsequence=32766 border="0"
color="33554432" x="585" y="4" height="76" width="576" format="[general]"
html.valueishtml="0" name=pat_first visible="1" edit.limit=20 edit.case=any
edit.autoselect=yes edit.autohscroll=yes font.face="Arial"
font.height="-10" font.weight="400" font.family="2" font.pitch="2"
font.charset="0" background.mode="1" background.color="536870912" )
htmltable(border="1" )
htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1"
clientformatting="0" clientscriptable="0" generatejavascript="1"
encodeselflinkargs="1" netscapelayers="0" pagingmethod=0
generatedddwframes="1" )
xhtmlgen() cssgen(sessionspecific="0" )
xmlgen(inline="0" )
xsltgen()
jsgen()
export.xml(headgroups="1" includewhitespace="0" metadatatype=0
savemetadata=0 )
import.xml()
export.pdf(method=0 distill.custompostscript="0" xslfop.print="0" )
export.xhtml()
----

Now add in-line code to create/execute/destroy the datastore:

datastore ds_occ
long i_max

ds_occ = create datastore
ds_occ.dataobject = "ds_test"
ds_occ.SetTransObject ( sqlca )
i_max = ds_occ.retrieve( "XXXX" )
<< check error recovery and i_max = 1 )
if i_max = 1 then
pat_last = ds.object.pat_last[1]
pat_first = ds.objec.pat_first[1]
else
<< -- handle unexpected error >>
endif

destroy ds

-----------------------

On the maintence side of things, if we should need to add another table to
the query the external field names can change so every location that
references the DW would also have to be found and changed. In addition,
since the datawindow object is dynamically assigned (and stored as a string)
there is no chance for the complier to generated an error during the deploy
process. Also, for good coding the datastore should be an instance of a
user object so that the "db error" event can be handled. This also provides
some interesting error handling implications.

Using a global function to move the in-line sql to one place where a common
routine could be supported would also make sense.

I would be more inclined to using a datastore (to execute a stored
procedure) if a large number of rows were being returned and complex sql
involved. A datastore is faster (in win32 deployment) than creating a large
array of structures and using a cursor to return results.

"Bruce Armstrong [TeamSybase]" <NOCANSPAM_br...@teamsybase.com>
wrote in message news:su4hp3tgfl6tl0aru...@4ax.com...

[Paul Horan[TeamSybase]]

"Lines of code" in a 4GL is a useless metric. However, for singleton
selects like this, yes - I wouldn't bat an eye at in-line SQL. If it's a
complex query with all kinds of JOINs and WHERE conditions, I'm putting that
into a graphical datawindow. Exactly where that line is between "simple
enough for in-line" vs. "complex enough to require a dw" is just a gut-feel
thing. I know it when I see it.

Paul Horan[TeamSybase]

"Tyler Cruse" <tcr...@trlx.com> wrote in message
news:4798a92e$1@forums-1-dub...

[Tyler Cruse]

I suspect that the end result between our solutions would not be all that
different.

"Paul Horan[TeamSybase]" <phoran AT sybase DOT com> wrote in message
news:4798f5e8$1@forums-1-dub...