Jump to content


Photo
- - - - -

#VAR = SQL COUNT(*)


23 replies to this topic

#16 Vol Yip

Vol Yip

    ProIV Guru

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

Posted 28 June 2006 - 12:54 AM

Andy,

I tried the following and it works for me:

$CCN = 'A10000'
SQL
BEGIN
SELECT COUNT(*) INTO :#VAR FROM ITEM_CCN
WHERE CCN = :$CCN;
END;
ENDSQL

The #VAR returns the correct value

:rolleyes:

#17 Jeff Hon

Jeff Hon

    Member

  • Members
  • PipPip
  • 29 posts
  • Gender:Male
  • Location:Melbourne, Australia

Posted 28 June 2006 - 01:04 AM

Hi Andy,

We use v5.5 and our SELECT..INTO..WHERE statements work fine. The issue you are experiencing sounds like v4.6 does not handle these type of SQL statements correctly. If I cast my mind back (way back) to my v4.6 days, I recall we had to use the solution that Martin suggested earlier in his post. Its not hard to implement, it works and it sure beats having to sequentially increment a counter variable in ARNE logic, which kind of defeats the whole purpose of using a RDBMS anyway.

Rgds,
Jeff.

#18 Vol Yip

Vol Yip

    ProIV Guru

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

Posted 28 June 2006 - 01:34 AM

Jeff, my test was on 4.6, it seems worked fine. :rolleyes:

#19 Jeff Hon

Jeff Hon

    Member

  • Members
  • PipPip
  • 29 posts
  • Gender:Male
  • Location:Melbourne, Australia

Posted 28 June 2006 - 01:41 AM

Yes...but can Andy get it working? ;)

And you did have to hardcode the $CCN value...or does the value just have to be delimited by "'" in v4.6? :rolleyes:

#20 George Macken

George Macken

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 248 posts
  • Gender:Male
  • Location:Co. Wicklow, Ireland

Posted 28 June 2006 - 07:59 AM

Hi

The different example of it working or not, maybe also identify the exact versions of Pro-IV, ORACLE, OS, etc., that you are used, as Jeff mentioned it could (probalby) be platform related.

Jeff how are thing with you, send on an e-mail

Rgds

George

#21 andykay

andykay

    ProIV Guru

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

Posted 28 June 2006 - 05:01 PM

Got It ;)

It turn out it was an error on placement of the SQL code. As I originally stated, I had a LU with an incremental counter that counted the records returned from a standard SQL query...that worked fine. So, I placed the new SQL query, with the INTO statement, in the same Default Logic of the LU as the original Sql query was...the new query never worked there and I have a pile of hair on the floor here to prove as testament :rolleyes: .

When I moved the SQL query to After Field Logic, of the field that called the counting LU in the first place, it worked like a charm...lightning quick, too.


Thanks All. :D

Onward and Forward
AK

BTW...Who does England play next in the WorldCup?
THE LIGHT AT THE END OF THE TUNNEL IS THE HEADLAMP OF THE TRAIN THAT'S ABOUT TO HIT YOU!!!

#22 Rob Donovan

Rob Donovan

    rob@proivrc.com

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

Posted 28 June 2006 - 05:57 PM

We play Portugal on Saturday...

Rob.

#23 Shaun Rudland

Shaun Rudland

    Expert

  • Members
  • PipPipPipPip
  • 165 posts
  • Location:Queensland, Australia

Posted 28 June 2006 - 11:12 PM

We play Portugal on Saturday


Well, half of them anyway. The other half are either injured or suspended. As Nelson (Munz, not the one with the big column) would say, "Ha-Ha".
PRO-IV free for 385 Days B)

#24 mdexter

mdexter

    Advanced

  • Members
  • PipPipPip
  • 70 posts
  • Gender:Male

Posted 29 June 2006 - 06:51 PM

Quick question -- Does this work with SQL Server or just with Oracle? I can't get it to work with SQL Server and 5.5. Thanks. Mark Dexter



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users