ALL:
Some days it really pays to come to the office. Recap:
a) If you really can afford to roll the dice on a handful of
dangerous assumptions and be indiscriminate about
what you get (if anything), then:
SELECT humpty FROM wall WHERE LIMIT = 1 or
SELECT humpty FROM wall WHERE COUNT = 1
... are the handy Saturday Night Specials of SQL coding.
b) If you need to at least ensure that theres something there to retrieve,
then:
SET VAR vCount INTEGER = 0
SELECT COUNT(humptyID) INTO vCount +
FROM wall WHERE ColName CONTAINS humpty
--or such criteria as pins things down a bit
IF COUNT > 0 THEN
SELECT humptyID INTO vHumptyID FROM wall +
WHERE ColName CONTAINS humpty AND COUNT = 1
ELSE
--<Whatever for 'No records found'>
ENDIF
c) If you're working in an indexed world and you actually care about
what you're going to get and have been frightfully burned
by your easy assumptions in the past then:
SELECT TOP 1 humpty FROM wall +
WHERE humpty IS NOT NULL +
ORDER BY wallID ASC
IF SQLCODE=100 THEN
...
... and other integrity structures are the only way to fly.
I started this thread with the desire to pull the first field of the first
row of a temporary table receiving data from an Excel file which itself is
an un-edited dump of a QuickBooks Chart of Account List report. The
"Account" field is a concatenation of all [Parent | Child ] gl codes, with
an upper-order ASCII character embedded as delimiter between levels.
Every Account field in every row contains at lease one of these characters
("guaranteed"), so that's why it doesn't matter which one I would grab. The
intent was to SGET this character into a variable, use that as input to a
SRPL process within a cursor looping through all rows to replace the
un-typeable character with a "="; simply an easier delimiter to work with.
The routine then works forward to parse these all out into a well-structured
CHILD:PARENT gl code table.
But the bonus from this thread is that the SELECT TOP1 humpty FROM wall
WHERE humpty ... process gives me the tool to jump into that gl code table
later, select a gl code subset and have a guaranteed entry point for walking
the PARENT:CHILD code chain.
Again, I am totally in all-your debt.
Bruce
