Jump to content


Photo
- - - - -

#VAR = SQL COUNT(*)


23 replies to this topic

#1 andykay

andykay

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 204 posts
  • Gender:Male
  • Location:Cyberspace...looking for work

Posted 22 June 2006 - 07:21 PM

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

TIA.
THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!

#2 Rob Donovan

Rob Donovan

    rob@proivrc.com

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

Posted 22 June 2006 - 08:19 PM

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.

#3 andykay

andykay

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 204 posts
  • 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?
THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!

#4 Martin Walkinshaw

Martin Walkinshaw

    Newbie

  • Members
  • Pip
  • 2 posts
  • 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.

#5 CSuarezdelReal

CSuarezdelReal

    Advanced

  • Members
  • PipPipPip
  • 91 posts
  • Gender:Male

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

#6 Jeff Hon

Jeff Hon

    Member

  • Members
  • PipPip
  • 29 posts
  • 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

#7 Vol Yip

Vol Yip

    ProIV Guru

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

Posted 26 June 2006 - 04:46 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

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

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


#8 Rob Donovan

Rob Donovan

    rob@proivrc.com

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

Posted 26 June 2006 - 05:16 AM

Oops,

Sorry, forgot the FROM .... :-"

Rob.

#9 mdexter

mdexter

    Advanced

  • Members
  • PipPipPip
  • 70 posts
  • 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

#10 CSuarezdelReal

CSuarezdelReal

    Advanced

  • Members
  • PipPipPip
  • 91 posts
  • Gender:Male

Posted 27 June 2006 - 02:20 PM

I recall SQL DYNAMIC does not work on v4.6.
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."

#11 andykay

andykay

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 204 posts
  • 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?

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

George Macken

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 248 posts
  • 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

#13 andykay

andykay

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 204 posts
  • 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 :rolleyes: .

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
THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!

#14 Joseph Bove

Joseph Bove

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 756 posts
  • Gender:Male
  • Location:Ramsey, United States

Posted 27 June 2006 - 10:50 PM

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

#15 andykay

andykay

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 204 posts
  • 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 ""? :rolleyes:
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