12 replies to this topic

[siobi]

dear all...

SELECT FROM SO_DEL DEL
WHERE DEL.SALES_CCN = :SALES_CCN
AND EXISTS (SELECT 'X' FROM SO LIN
WHERE LIN.SALES_CCN = DEL.SALES_CCN)


guys...
any idea on the select 'X' part?

why I change it to select TRUE from SO LIN and is still works fine?

is that just a dummy variable?

[Rob Donovan]

Hi,

Yes its just a dummy value, so that the EXISTS statement gets something to test against.

Rob D.

[siobi]

Hi,

Yes its just a dummy value, so that the EXISTS statement gets something to test against.

Rob D.

thanks for your reply, really appreciate!!!!



by the way, what happend if my nested IF block returns no data?

[Larry Siemer]

by the way, what happend if my nested IF block returns no data?

Then nothing 'EXISTS'.

[siobi]

besides EXIST, any other command can be used at that particular point?

[Larry Siemer]

besides EXIST, any other command can be used at that particular point?

The following can also be used in place of EXIST:

ANY
ALL
IN
NOT EXISTS

[CSuarezdelReal]

In theory you can replace any SEL-XXX by a SQL SELECT statement that might return rows or in any Before Read logic, where you might define table keys. Depending on the ProIVKernel version and the DBMS you use some of them do not work but usually it is just a matter of knowing what you want, code the SQL statement you know would give you the result, and if that does not work, try an alternate SQL statement.

Most of the times I get the result in the first trail. I have used very complex queries to multiple tables, unions, dummy tables, sorting and so forth, and they work great, saving a few LS and gaining noticeable response time.

[Vol Yip]

In theory you can replace any SEL-XXX by a SQL SELECT statement that might return rows or in any Before Read logic, where you might define table keys.

By "in any Before Read Logic" do you mean the SQL SELECT can be put in NON PRIMARY file?

I thought SEL-XXX or SQL SELECT only works in DEFAULT Logic (which means it affect only the primary file).

In fact, I found that SEL-XXX works also in BEFORE logic (which made me confused at the first time when I found this) I do not know if SQL SELECT can be put in BEFORE logic though. (Primary and Secondary file???)

[CSuarezdelReal]

I use the Default Logic for SQL in primary files. As when you define Table Keys, depending on several aspects, some times they do not work on BEFORE READ logics. I guess it is the same with SQL statements. If defining table keys works in BEFORE READ logic, so should work a SQL select, otherwise, it would be ignored.

Yes, I use often SQL select in BEFORE READ logic for secondary files, and again, that saves a few nestings and gains time.

[Neil Hunter]

From what I remember you can do a SQL select in before read. In theory you could have one LS then, performance would probably be a bit on the slow side

[CSuarezdelReal]

I would summarize my point as follows:

If you are using a RDBMS (Oracle, for instance) ANY request for data MUST BE translated into a SQL SELECT statement (this is the unique interface any application can talk to a RDBMS). If you use (a) SEL-XXX, ( define manually keys or © issue a SQL SELECT in a Default Logic/Before Read Logic, the final result will be a SQL SELECT statement, defined automatically by the ProIV Kernel, or specifically tailored by yourself.

Giving that, I can hardly imagine a case where a SQL SELECT statement adds overhead to a Pro IV function.

If you create the SQL SELECT statement by yourself, it is up to you to make it fit to the referred table, though,

[siobi]

The following can also be used in place of EXIST:

ANY
ALL
IN
NOT EXISTS

dear guys,
I found that "MINUS" also being used in one of the function...

whats the MINUS for ?