23 replies to this topic

[andykay]

Is there a simple way to have a SQL COUNT(*) answer returned directly to a variable?

TIA.

[Rob Donovan]

Hi,

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.

[andykay]

Thanks Rob for the effort, but it doesn't work on our v4.6 machine. <_<

Short of running a LU, any other ideas?

[Martin Walkinshaw]

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.

[CSuarezdelReal]

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

[Jeff Hon]

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

[Vol Yip]

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

Yes, it worked in my 4.6 environment. I meant after Jeff's correction.

Edited by Vol Yip, 26 June 2006 - 04:47 AM.

[Rob Donovan]

Oops,

Sorry, forgot the FROM ....

Rob.

[mdexter]

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

[CSuarezdelReal]

I recall SQL DYNAMIC does not work on v4.6.

[andykay]

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?

Edited by andykay, 27 June 2006 - 04:44 PM.

[George Macken]

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

[andykay]

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

[Joseph Bove]

George,

$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

[andykay]

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