Jump to content


- - - - -

Passing results from an SQL statement


28 replies to this topic

#1 Guest_Joseph V.G. Orendain_*

Guest_Joseph V.G. Orendain_*
  • Guests

Posted 01 November 2002 - 10:23 PM

Is it possible to pass the result of a sql statement to PRO-IV?

I am thinking of using the LAST_DAY function of ORACLE using select statement from dual but dont know how to pass it to PRO-IV. I will issue the sql command in the before field logic of field - ENDING DATE. Is this possible?

Thanks!

Joseph

#2 Bill Loven

Bill Loven

    Expert

  • Members
  • PipPipPipPip
  • 147 posts
  • Gender:Male
  • Location:Coppell, United States

Posted 01 November 2002 - 10:49 PM

Very simple. I retrieve User name from dual.

I created a ProIV file definition called DUAL and use direct SQL : Select Username from Dual. Username is 30 characters.

You can do the same thing with last day.

Bill

#3 Guest_Joseph V.G. Orendain_*

Guest_Joseph V.G. Orendain_*
  • Guests

Posted 04 November 2002 - 02:39 PM

thanks Bill.

that means I need to create a dummy pro-isam file with no key and the only field is LAST_DAY? And then define this as the first file in the group or LU? Can you please elaborate or show me some examples. thanks a lot!

Joseph

#4 Guest_Adrian Fuentes_*

Guest_Adrian Fuentes_*
  • Guests

Posted 04 November 2002 - 03:17 PM

I understand you have to use an Oracle table definition, though it can be a 'virtual' table, since you do not have to create if physically.

I have a few 'generic', 'dummy' Oracle tables to retrieve complex data using powerful SQL statements, with 10 or more alpha fields, 30 chars long, where I put anything from actual values of fields to sum, concat or any combination SQL Oracle allows. If the result is numeric, I just do a CONV before I process the info. In those cases, the SELECT keyword must be followed by the actual fields or values I want.

It is a great time saving approach, since I get the “cooked” info without going through slooooow PRO IV code. The only disadvantage is, PRO IV cannot to tell me where I am using a file when inside of a SQL request.

Regards,

#5 Guest_Joseph V.G. Orendain_*

Guest_Joseph V.G. Orendain_*
  • Guests

Posted 04 November 2002 - 03:28 PM

how do you it? Can you show some sample codes?

thanks!

Joseph

#6 Guest_Adrian Fuentes_*

Guest_Adrian Fuentes_*
  • Guests

Posted 04 November 2002 - 04:06 PM

File definition could be:

File Name: MYSQL,
External record Format='Y',
File Type='ORA'
Field1: SQLRES1 ALPHA (30)

DEF LOGIC:

SQL
SELECT COUNT(*) FROM DUAL
ENDSQL

When FILE 01 is MYSQL, ARNE (efter read no error) this characteristic,
CONV(SQLRES1) is 1.

The file definition does not have to match the name nor the structure of the actual Oracle table(s) (in this case I asked for a numeric value ('COUNT(*)'), but I retrieved such a value into an alpha field), the only restriction is, there should be enough fields in the file definition to hold as much values as you are listing after the SELECT statement.

I preferred to create two or three generic SQL tables for these kind of tasks instead of defining a dummy file as 'DUAL', because I use to use this method very often and I do not want to spend time defining different dummy files for different requests. Some times I use two or three of these dummy files in a single function, that's why I use more than one table definition.

Another limitation is, when creating a screen, you cannot set the 'change' mode to a 'dummy SQL file' like these because the file does not really exist.

Hope this helps better.

Regards,

;-)

#7 Guest_Joseph V.G. Orendain_*

Guest_Joseph V.G. Orendain_*
  • Guests

Posted 04 November 2002 - 04:11 PM

Thanks a lot!

Will this work for a multi-user environment? (MYSQL has no key, or PRO-IV only uses it as a temporary storage for the values returned by SQL statement?) thanks!

Joseph

#8 Guest_Adrian Fuentes_*

Guest_Adrian Fuentes_*
  • Guests

Posted 04 November 2002 - 04:17 PM

Absolutely!... it works for muli-users!.

As 'MYSQL' is an Oracle table definition (even if you do not physically create it) there must be at least one key (in my example, SQLRES1 must be a key, but at the end, that is meaningless, because the function will not 'limit' the results to 'non-duplicate' values).

'PRO-IV only uses it as a temporary storage for the values returned by SQL statement?' YES, you got the point.

Regards,

#9 Guest_Joseph V.G. Orendain_*

Guest_Joseph V.G. Orendain_*
  • Guests

Posted 05 November 2002 - 07:02 PM

Thanks D00d!

I tried it in our test environment and it worked! great! I also found out that when passing a date value, it has to be in PRO-IV string format ('20020201') instead of '01-FEB-02. If I try the ORACLE format, I get an error. I tried it with LAST_DAY function.

Also, same function can use the same dummy file to run different SQL statements, which confirms what you mentioned above that it is ok for multi-users....

THANKS A LOT!!

Joseph

#10 Vol Yip

Vol Yip

    ProIV Guru

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

Posted 12 November 2002 - 08:09 AM

Hey Adrian,

Do you have the following experience?

In the Default logic, I do:

SQL
SELECT '', SUM(ASSIGN_QTY)
FROM S_TABLE1
WHERE ...
ENDSQL

When File 01 is another file S_TABLE2 (key is key1 and with an Alpha1 field. Physically, this table is empty). At ARNE, I make user of the CONV(Alpha1) which stores the SUM(ASSIGN_QTY) returned from the default logic.


The above works if the OS is Window but HANGS when the OS UNIX.

Do this similar with what you are doing? Do you have any problems in Unix?

Regards,

Vol

#11 Rob Donovan

Rob Donovan

    rob@proivrc.com

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

Posted 12 November 2002 - 08:20 AM

Hi,

Thats what I do also, and it works in Unix.

But I dont have the Alpha1 var, I just use the Key1 var as my return value.(and I then dont have the '' in the select statement)

Rob D.

#12 Vol Yip

Vol Yip

    ProIV Guru

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

Posted 12 November 2002 - 08:57 AM

Hi Rob,

What is the Oracle version you are using? In my case,

8.1.6 does not hang
8.1.7.0 hangs
8.1.7.3 does not hang

seems like a Oracle issue. But don't know :(

I will try to use your way to test it again. Thanks.

Regards,

Vol

#13 Rob Donovan

Rob Donovan

    rob@proivrc.com

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

Posted 12 November 2002 - 09:05 AM

I'm on 8.1.7.0.0 ...

The DataBase is on a NT machine, but the ProIV kernel is on a Unix box.

Rob D.

#14 chuckl

chuckl

    Member

  • Members
  • PipPip
  • 26 posts
  • Gender:Male
  • Location:Coppell, Texas

Posted 28 January 2010 - 06:17 PM

I hope you guys are still around, I'm having trouble with this one. I created my "dummy" table definition as ORA and did not indicate any physical file. The file definition fields matchmy select statement. I wrote an update function with the select involving two tables:

SQL
SELECT A.ACCOUNT_NO, A.TICK_SHEET_NO, A.PATQUEUE_TYPE,
B.PM_P_NAME, B.PM_P_FNAME, B.PM_P_MNAME
FROM PATQUEUE A, PMPAT B
WHERE A.ACCOUNT_NO = B.PM_R_CODE
AND A.CLINIC_CODE = :$CLINIC_ID
ENDSQL

The name of my dummy file in the update is QUEUETMP and if I have the access mode set to anything except "A" add mode I get a SQL error ORA-00933: 'SQL command not properly ended'. With the file in add mode it hits the before read error. I'm not sure what I am missing.

thanks in advance
chuckl

#15 Chris Mackenzie

Chris Mackenzie

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 368 posts
  • Gender:Male
  • Location:Bristol, United Kingdom

Posted 01 February 2010 - 08:58 AM

]

I hope you guys are still around, I'm having trouble with this one. I created my "dummy" table definition as ORA and did not indicate any physical file. The file definition fields matchmy select statement. I wrote an update function with the select involving two tables:

SQL
SELECT A.ACCOUNT_NO, A.TICK_SHEET_NO, A.PATQUEUE_TYPE,
B.PM_P_NAME, B.PM_P_FNAME, B.PM_P_MNAME
FROM PATQUEUE A, PMPAT B
WHERE A.ACCOUNT_NO = B.PM_R_CODE
AND A.CLINIC_CODE = :$CLINIC_ID
ENDSQL

The name of my dummy file in the update is QUEUETMP and if I have the access mode set to anything except "A" add mode I get a SQL error ORA-00933: 'SQL command not properly ended'. With the file in add mode it hits the before read error. I'm not sure what I am missing.

thanks in advance
chuckl


Hi I think you need to use the DYNAMIC keyword

SQL DYNAMIC SELECT [...]
ENDSQL

Edited by Chris Mackenzie, 01 February 2010 - 09:00 AM.

The content and views expressed in this message are those
of the poster and do not represent those of any organisation.



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users