Is there a simple way to have a SQL COUNT(*) answer returned directly to a variable?
TIA.
Click the link below to see the new game I'm developing!
#VAR = SQL COUNT(*)
Started by
andykay
, Jun 22 2006 07:21 PM
23 replies to this topic
#2
Rob Donovan
-
- Admin
-
- 1,652 posts
rob@proivrc.com
- Gender:Male
- Location:Spain
Posted 22 June 2006 - 08:19 PM
Hi,
The easy way in logic would be....
However, I think this may not be 'supported'... whatever that may mean
... But it has always worked for me.
Rob.
The easy way in logic would be....
SQL BEGIN SELECT COUNT(*) INTO :#VAR; END; ENDSQL
However, I think this may not be 'supported'... whatever that may mean
... But it has always worked for me.Rob.
#3
andykay
-
- Members
-
- 204 posts
ProIV Guru
- Gender:Male
- Location:Cyberspace...looking for work
Posted 22 June 2006 - 11:17 PM
Thanks Rob for the effort, but it doesn't work on our v4.6 machine. <_<
Short of running a LU, any other ideas?
Short of running a LU, any other ideas?
THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!
#4
Martin Walkinshaw
-
- Members
-
- 2 posts
Newbie
- Gender:Male
Posted 23 June 2006 - 03:49 AM
You could create a file definition for Oracle table dual with a single field. This can be added to you function with the SQL in the before read.
SQL
SELECT COUNT(*)
FROM TABLE
ENDSQL
This will return the value to the dummy field in your file definition.
SQL
SELECT COUNT(*)
FROM TABLE
ENDSQL
This will return the value to the dummy field in your file definition.
#5
CSuarezdelReal
-
- Members
-
- 91 posts
Advanced
- Gender:Not Telling
Posted 23 June 2006 - 01:59 PM
I would use Martin's approach, but looks like you do not want to mess with files. When you say Bob's advice does not work, do you mean you get zero, an error ...or what?; have you tried to use :$VAR instead?.
Claudio Suarez del Real "It is not the strongest of the species that survive, nor the most intelligent, but the ones most responsive to change."
#6
Jeff Hon
-
- Members
-
- 29 posts
Member
- Gender:Male
- Location:Melbourne, Australia
Posted 26 June 2006 - 03:57 AM
Rob's code would not have worked if you implemented it as he had it in his post. You would have to also specify the table/view that you are selecting from, i.e.
SQL
BEGIN
SELECT COUNT(*) INTO :#VAR
FROM TABLENAME;
END;
ENDSQL
SQL
BEGIN
SELECT COUNT(*) INTO :#VAR
FROM TABLENAME;
END;
ENDSQL
#7
Vol Yip
-
- Members
-
- 393 posts
ProIV Guru
- Gender:Male
- Location:Hong Kong
Posted 26 June 2006 - 04:46 AM
Yes, it worked in my 4.6 environment. I meant after Jeff's correction.Rob's code would not have worked if you implemented it as he had it in his post. You would have to also specify the table/view that you are selecting from, i.e.
SQL
BEGIN
SELECT COUNT(*) INTO :#VAR
FROM TABLENAME;
END;
ENDSQL
Edited by Vol Yip, 26 June 2006 - 04:47 AM.
#8
Rob Donovan
-
- Admin
-
- 1,652 posts
rob@proivrc.com
- Gender:Male
- Location:Spain
Posted 26 June 2006 - 05:16 AM
Oops,
Sorry, forgot the FROM.... 
Rob.
Sorry, forgot the FROM
Rob.
#9
mdexter
-
- Members
-
- 70 posts
Advanced
- Gender:Male
Posted 26 June 2006 - 11:31 PM
We have asked for this functionality, but PRO-IV said it would be expensive and difficult to program. The work-around that we use is to write the information to a SQL table and then read it back from the table. We have a SQL table that we use for this type of operation, keyed by @TERM. We have a global update that reads this table. So the steps are as follows:
SQL DYNAMIC
UPDATE CL_TEMP_VALUES
SET ALPHA_1 = (SELECT COUNT(*) FROM MY_TABLE
ENDSQL
GLOBAL_LSCALL(GET_SQL,ISQL)
This solution is not perfect but it works well and doesn't require too much extra coding. Hope this helps. Mark Dexter
SQL DYNAMIC
UPDATE CL_TEMP_VALUES
SET ALPHA_1 = (SELECT COUNT(*) FROM MY_TABLE
ENDSQL
GLOBAL_LSCALL(GET_SQL,ISQL)
This solution is not perfect but it works well and doesn't require too much extra coding. Hope this helps. Mark Dexter
#11
andykay
-
- Members
-
- 204 posts
ProIV Guru
- Gender:Male
- Location:Cyberspace...looking for work
Posted 27 June 2006 - 04:42 PM
Alright, here's a new twist.
The INTO statement works, but the entire SQL fails (returns zero) if I try to include a WHERE clause in it.
I have:
SQL
BEGIN
SELECT COUNT(*) INTO :#COUNT_WO FROM WO_RTG
WHERE CCN = :CCN AND WC = :$OLD_WC;
END;
ENDSQL
If I omit the entire WHERE line, the SQL will go out and return the count for the entire file (~634000 records), which is nice, but not exactly what I had in mind. If I include the WHERE line, the count comes back as zero.
If I do it the old fashion way and have a counter in ARNE that increments for every record that is returned from the SQL statement. it works fine and returns the correct number of records. As our WO_RTG file gets larger, I was hoping to have SQL return the counter instead of having to increment it one at a time.
SQL
SELECT * FROM WO_RTG
WHERE CCN = :CCN AND WC = :$OLD_WC
ENDSQL
Any comments on this?
The INTO statement works, but the entire SQL fails (returns zero) if I try to include a WHERE clause in it.
I have:
SQL
BEGIN
SELECT COUNT(*) INTO :#COUNT_WO FROM WO_RTG
WHERE CCN = :CCN AND WC = :$OLD_WC;
END;
ENDSQL
If I omit the entire WHERE line, the SQL will go out and return the count for the entire file (~634000 records), which is nice, but not exactly what I had in mind. If I include the WHERE line, the count comes back as zero.
If I do it the old fashion way and have a counter in ARNE that increments for every record that is returned from the SQL statement. it works fine and returns the correct number of records. As our WO_RTG file gets larger, I was hoping to have SQL return the counter instead of having to increment it one at a time.
SQL
SELECT * FROM WO_RTG
WHERE CCN = :CCN AND WC = :$OLD_WC
ENDSQL
Any comments on this?
Edited by andykay, 27 June 2006 - 04:44 PM.
THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!
#12
George Macken
-
- Members
-
- 248 posts
ProIV Guru
- Gender:Male
- Location:Co. Wicklow, Ireland
Posted 27 June 2006 - 06:24 PM
Hi
I took a look at some examples of where we have used SQL and WHERE clause
where we have used the WHERE clause we've first assigned the search variables/values into scratch fields
for example
$CCN = CCN
SQL
SELECT FROM WO_RTG WHERE CCN = :$CCN AND WC = :$OLD_WC
ENDSQL
hope this helps
George
I took a look at some examples of where we have used SQL and WHERE clause
where we have used the WHERE clause we've first assigned the search variables/values into scratch fields
for example
$CCN = CCN
SQL
SELECT FROM WO_RTG WHERE CCN = :$CCN AND WC = :$OLD_WC
ENDSQL
hope this helps
George
#13
andykay
-
- Members
-
- 204 posts
ProIV Guru
- Gender:Male
- Location:Cyberspace...looking for work
Posted 27 June 2006 - 09:01 PM
Hi George,
There is nothing wrong with using the WHERE clause in standard SQL queries without strings. The problem I'm having occurs when I try to skip a step and attempt to have SQL populate the scratch VAR...the whole SQL statement doesn't run (well, actually it returns a zero count).
What we are doing is looking at all the historical WO_RTG records and seeing which WC's have been deleted thereby making the WO's Operation line invalid in WO_RTG. At the moment, they are only using this for Deleted WC's, but they want to start using this for Valid WC's as well. As this is a no-footstep function that leaves no trace of how the WC was switched (Yes, I know...I issued the same warnings for caution to them and they still want to do it), I issue them a count of how many records will be effected by their change so that if they're only thinking of changing a few and 5000 come up they know they shouldn't do it...hopefully
.
At the moment, my SQL only selects the records that match the WC they are trying to replace with another value, and then runs through a LU incrementing a counter for every record returned by the query. Right now, it's fast because we're only dealing with a few historical WO's that don't have too many errors in them. But as they start to use this for valid WC's, they have the potential of selecting one with 20000 records, and the current LU would then take 3-10 seconds, incrementing the counter from every record, before moving onto the next field...unacceptable in my opinion.
I was hoping to skip this individual record counting step by using the INTO clause inside of the SQL statement, but when I try to use the WHERE clause in conjunction with the INTO cluse, it returns a count of zero, where as my current SQL statement, without the INTO clause, currently returns the correct count of 20000. There is nothing wrong with my current SQL statement...it's working well. But I'd like to see if there is a way to always make sure of a zero lag time from the time the user hits to the time the cursor gets to the next field, and I was hoping that the INTO clause would allow me to do that.
Hope this makes my inquiry a little clearer.
AK
There is nothing wrong with using the WHERE clause in standard SQL queries without strings. The problem I'm having occurs when I try to skip a step and attempt to have SQL populate the scratch VAR...the whole SQL statement doesn't run (well, actually it returns a zero count).
What we are doing is looking at all the historical WO_RTG records and seeing which WC's have been deleted thereby making the WO's Operation line invalid in WO_RTG. At the moment, they are only using this for Deleted WC's, but they want to start using this for Valid WC's as well. As this is a no-footstep function that leaves no trace of how the WC was switched (Yes, I know...I issued the same warnings for caution to them and they still want to do it), I issue them a count of how many records will be effected by their change so that if they're only thinking of changing a few and 5000 come up they know they shouldn't do it...hopefully
.At the moment, my SQL only selects the records that match the WC they are trying to replace with another value, and then runs through a LU incrementing a counter for every record returned by the query. Right now, it's fast because we're only dealing with a few historical WO's that don't have too many errors in them. But as they start to use this for valid WC's, they have the potential of selecting one with 20000 records, and the current LU would then take 3-10 seconds, incrementing the counter from every record, before moving onto the next field...unacceptable in my opinion.
I was hoping to skip this individual record counting step by using the INTO clause inside of the SQL statement, but when I try to use the WHERE clause in conjunction with the INTO cluse, it returns a count of zero, where as my current SQL statement, without the INTO clause, currently returns the correct count of 20000. There is nothing wrong with my current SQL statement...it's working well. But I'd like to see if there is a way to always make sure of a zero lag time from the time the user hits
Hope this makes my inquiry a little clearer.
AK
THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!
#14
Joseph Bove
-
- Members
-
- 756 posts
ProIV Guru
- Gender:Male
- Location:Ramsey, United States
Posted 27 June 2006 - 10:50 PM
George,
Are you getting by without doing:
$CCN = "'" + CCN + "'"
We've always been including the quotes around the value for the where clause. I would love to find out that that is not needed...
Regards,
Joseph
$CCN = CCN
SQL
SELECT FROM WO_RTG WHERE CCN = :$CCN AND WC = :$OLD_WC
ENDSQL
Are you getting by without doing:
$CCN = "'" + CCN + "'"
We've always been including the quotes around the value for the where clause. I would love to find out that that is not needed...
Regards,
Joseph
#15
andykay
-
- Members
-
- 204 posts
ProIV Guru
- Gender:Male
- Location:Cyberspace...looking for work
Posted 27 June 2006 - 11:41 PM
Joseph,
I've never heard of such a requirement, but not knowing your system I can only say "That requirement is not needed here"
BTW, what happens if you remove the leading and trailing ""?
I've never heard of such a requirement, but not knowing your system I can only say "That requirement is not needed here"
BTW, what happens if you remove the leading and trailing ""?
THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!
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!
