Jump to content


Photo
- - - - -

SQL in PROIV SL


12 replies to this topic

#1 siobi

siobi

    Member

  • Members
  • PipPip
  • 48 posts
  • Gender:Male

Posted 29 September 2004 - 06:38 AM

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?

#2 Rob Donovan

Rob Donovan

    rob@proivrc.com

  • Admin
  • 1,640 posts
  • Gender:Male
  • Location:Spain

Posted 29 September 2004 - 06:48 AM

Hi,

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

Rob D.

#3 siobi

siobi

    Member

  • Members
  • PipPip
  • 48 posts
  • Gender:Male

Posted 29 September 2004 - 08:01 AM

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!!!!

:D

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

#4 Larry Siemer

Larry Siemer

    Member

  • Members
  • PipPip
  • 17 posts
  • Gender:Male
  • Location:Cincinnati, United States

Posted 29 September 2004 - 11:11 AM

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

Then nothing 'EXISTS'.

#5 siobi

siobi

    Member

  • Members
  • PipPip
  • 48 posts
  • Gender:Male

Posted 30 September 2004 - 01:08 AM

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

#6 Larry Siemer

Larry Siemer

    Member

  • Members
  • PipPip
  • 17 posts
  • Gender:Male
  • Location:Cincinnati, United States

Posted 30 September 2004 - 12:14 PM

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

#7 CSuarezdelReal

CSuarezdelReal

    Advanced

  • Members
  • PipPipPip
  • 91 posts
  • Gender:Male

Posted 30 September 2004 - 12:55 PM

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.
Claudio Suárez del Real
"It is not the strongest of the species that survive, nor the most intelligent, but the ones most responsive to change."

#8 Vol Yip

Vol Yip

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 393 posts
  • Gender:Male
  • Location:Hong Kong

Posted 30 September 2004 - 01:08 PM

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???)

#9 CSuarezdelReal

CSuarezdelReal

    Advanced

  • Members
  • PipPipPip
  • 91 posts
  • Gender:Male

Posted 30 September 2004 - 01:22 PM

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.
Claudio Suárez del Real
"It is not the strongest of the species that survive, nor the most intelligent, but the ones most responsive to change."

#10 Neil Hunter

Neil Hunter

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 414 posts
  • Gender:Male
  • Location:Johannesburg, South Africa

Posted 30 September 2004 - 03:22 PM

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

#11 CSuarezdelReal

CSuarezdelReal

    Advanced

  • Members
  • PipPipPip
  • 91 posts
  • Gender:Male

Posted 30 September 2004 - 09:16 PM

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, (B) 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,
Claudio Suárez del Real
"It is not the strongest of the species that survive, nor the most intelligent, but the ones most responsive to change."

#12 siobi

siobi

    Member

  • Members
  • PipPip
  • 48 posts
  • Gender:Male

Posted 01 October 2004 - 01:13 AM

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 ?

#13 Guest_Pinoy Ako_*

Guest_Pinoy Ako_*
  • Guests

Posted 01 October 2004 - 03:50 AM

Hi Siobi,

The internet is a very useful source of information, please use it. ;-)

http://www.1keydata.com/sql/sql.html


Cheers



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users