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
Click the link below to see the new game I'm developing!
Passing results from an SQL statement
Started by
Guest_Joseph V.G. Orendain_*
, Nov 01 2002 10:23 PM
28 replies to this topic
#2
Bill Loven
-
- Members
-
- 147 posts
Expert
- 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
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
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,
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,
#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,
;-)
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,
;-)
#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,
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
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
-
- Members
-
- 393 posts
ProIV Guru
- 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
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
-
- Admin
-
- 1,652 posts
rob@proivrc.com
- 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.
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
-
- Members
-
- 393 posts
ProIV Guru
- 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
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
-
- Admin
-
- 1,652 posts
rob@proivrc.com
- 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.
The DataBase is on a NT machine, but the ProIV kernel is on a Unix box.
Rob D.
#14
chuckl
-
- Members
-
- 29 posts
Member
- 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
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
-
- Members
-
- 372 posts
ProIV Guru
- Gender:Male
- Location:Bristol, United Kingdom
Posted 01 February 2010 - 08:58 AM
]
Hi I think you need to use the DYNAMIC keyword
SQL DYNAMIC SELECT [...]
ENDSQL
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.
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
Click the link below to see the new game I'm developing!
