23 replies to this topic

[guesty_mcguest]

The "steamroller" method works...

In your pl/sql multiply the result by 100.

In your pro-iv divide the result returned by pl/sql by 100.



Now, anybody got any clues on returning strings from pl/sql??

[Chris Mackenzie]

Might be too obvious, but you can define a pl/sql
function that returns any string and use it in an
SQL query in BR of a dummy file def with an alpha
field to take the result.

[guesty_mcguest]

Well whaddya know!

I'm sure I tried this years ago and I couldn't get it to work with a function as a column - must have just been doing something wrong.

So, the easiest, and most easy to maintain method for solving kwong's original problem is just to have the pl/sql routine convert the number to a string and return that and then pro-iv can convert it to a number.

[Chris Mackenzie]

So, the easiest, and most easy to maintain method for solving kwong's original problem is just to have the pl/sql routine convert the number to a string

Not sure about that, seems easy enough to write it as a function
that returns a number. Maybe PL/SQL functions didn't work in an
earlier P4 release but it is fine now (v5.5 rel 2.1).

BTW: PLSQLIN & PLSQLOUT -what do they do?

[Joseph Bove]

Chris,

Thanks for asking about PSQLIN and PSQLOUT.

I can't find reference to them in the Logic guide, but are hoping that they may solve a different issue for me. I am looking to a simple one line SQL statement and have access to the numerical result of it:

select total(column_name) from table

I haven't found the way to do this yet in looking through the manual.

Regards,

Joseph

[Rob Donovan]

Hi,

PSQLIN & PSQLOUT will be global logics that are specific to his application.

As for your query...

If you setup a File Def in ProIV for the oracle table DUAL (just one column say DUMMY-COL, Alpha, length 30), you can then use this to get info back from SQL statements.

If you put something like

SELECT COUNT(*) FROM MYTABLE

In default logic of a LS, and then put DUAL as the primary file, you can then put some logic in ARNE, and look at the DUMMY-COL to get the value from COUNT(*). You will have to do a CONV() to convert it from a string.

HTHs,

Rob D.

[Cleve Haynes]

Joseph - I think you'll find that PSQLIN and PSQLOUT are Glovia specific global logics.

Cleve

[Chris Mackenzie]

If you put something like

SELECT COUNT(*) FROM MYTABLE

In default logic of a LS, and then put DUAL as the primary file, you can then put some logic in ARNE, and look at the DUMMY-COL to get the value from COUNT(*). You will have to do a CONV() to convert it from a string.

Why primary file? It can be any file.
What has DUAL got to do with it? The P4 file def need not
point to any physical table as the explicit SQL overrides it.
And you can have a fildef with a numeric result, no need to
convert.

/pedant off.

[Joseph Bove]

Rob, Cleve,

Thanks for the posts.

Rob,

At a conceptual level I'm missing or not appreciating is how DUAL will store the answer of the select statement. Would the SQL be something like this instead?

DUMMY is one a row table.
Structure DUMMY.ALPHA_ANSWER varchar2(30), DUMMY.NUMERIC_ANSWER number(12.6)

SQL
update DUMMY set NUMERIC_ANSWER= select count(*) from table
commit
select * from DUMMY
ENDSQL

Then simply read the NUMERIC_ANSWER column?

Regards,

Joseph

[Rob Donovan]

Hi,

I just thought that this was a more 'complete' way of doing it, since its exloting the way ProIV does stuff anyway, and it may not work one day, but putting in a valid table name might make it scrape past any 'bugs'.

I used to get a problem with numerics, in past versions before... that was why I did it with a Alpha.

Rob D.

[Joseph Bove]

Rob,

Ok - thanks I now appreciate what is happening. A statement like:

select count (*) from table

returns 1 row. If you use a dummy table, then the column contains the results of the sql statement.

Thanks for the tip. It works well.

Regards,

Joseph

[Rob Donovan]

Hi,

I like that... thanks for the tip

Rob D.