Question for Rob

[onthecuttingedge2005]

Hi Rob.

I have this code here which does function but it is giving a 'True' value instead of actually counting the rows which I need for the advanced emotions script to function correctly.
any assistance would be greatly appreciated.

Code: [Select]

'Anger counter
Function AngryCount(UserName)
    Dim EmotQuery   
    AngryCount = HalBrain.RunQuery("SELECT Sentence, COUNT * FROM " & UserName & "_Angry", EmotQuery)   
End Function

Thanks
Jerry

[freddy888]

Have you tried putting the asterix in parentheses ?

[onthecuttingedge2005]

Have you tried putting the asterix in parentheses ?

Hi Freddy.

Just tried it, HAL debug gives me the True variable instead of a counted number.

thanks though.
Jerry

[cload]

Hi Jerry,

could it be that you need to count a specific row like:
count IDrecord
or
instead of the * use the specific word angry.

You probably already tried these, just trying to help.

Sincerely,

C load.

[onthecuttingedge2005]

thanks Cload but no dice yet, I'll wait for a reply from Rob.

[freddy888]

Don't know if you already use it but when I get stuck I ask over here : http://stackoverflow.com/

You just have to put up with some people thinking you are a complete idiot

[onthecuttingedge2005]

I'll wait for Rob.

[onthecuttingedge2005]

David will be here shortly. patients. I have struggled a many years to try and figure this out, I just need some help from Rob. David is coming soon.

[cload]

hi Jerry,

SELECT id, name, COUNT(name) AS count  <= I think you are looking for? AngryCount = HalBrain.RunQuery("SELECT recordid, sentence, COUNT(sentence) AS count  FROM " & UserName & "_Angry", EmotQuery)
  FROM markers                                        \                                                                                                                        maybe this can be{  Angry  } so you can count that 1 word.
 WHERE state like 'OR'                                >you may need to add this?
group by named                                        /

I get the following results:

id  name                count
14  Alsea                    2
76  Applegate Valley    1
3   Ashland                 9
64  Beaver                 1
26  Bend                    1
10  Carlton                 2

I want each of instance of a counted name to appear with its respective ID as seen in the result of query number 2:

SELECT id, name, COUNT(name) AS count 
  FROM markers 
WHERE state like 'OR' 
group by name, id

id  name                count
14  Alsea                    1
28  Alsea                    1
76  Applegate Valley    1
3   Ashland                 1
13  Ashland                1
16  Ashland                1
20  Ashland                1
22  Ashland                1
43  Ashland                1
48  Ashland                1
51  Ashland                1
72  Ashland                1
64  Beaver                 1
26  Bend                    1
10  Carlton                 1
27  Carlton                 1

I can run that will return the id and name from query number 2 and the corresponding count from query number 1 in each row as shown below:

SELECT id, name, 
 (SELECT COUNT(name) 
   FROM markers im 
   WHERE state like 'OR' 
   GROUP BY name 
   HAVING im.name=m.name) as count
FROM markers m 
WHERE state like 'OR' 
GROUP BY name, id 

id  name                count
14  Alsea                    2
28  Alsea                    2
76  Applegate Valley    1
3   Ashland                 9
13  Ashland                9
16  Ashland                9
20  Ashland                9
22  Ashland                9
43  Ashland                9
48  Ashland                9
51  Ashland                9
72  Ashland                9
64  Beaver                  1
26  Bend                     1
10  Carlton                  2
27  Carlton                  2

I hope this help,

C load.

[onthecuttingedge2005]

after debugging your code I still get a type mismatch error because the code in debug gives a 'True' variable instead of a counted number.

Jerry

[cload]

Hi Jerry,

after extensively reading every article that I could get my hands on about the count function, these are some of the results that I came up with.
I ran across three articles that explained that originally the count was four the column and not so much the row, though it was supposed to be able to count rows.
At first there did not seem to be much of a use for it, but as time went on people began to experiment with it and found that it would be very handy.
But the problem was that the count function was not passing the information meeting: P1 was not giving the information to P2 therefore P3 was not delivering
the information to the temporary array, that is the dim that you set up for the temporary array.
From what I understood this was eventually corrected, but not until the latter time of SQL version 2 just before they came out with version 3.
The way they spoke about it was that because it was not passing the information on, meaning P1 give to P2 handing it to P3 the only results was the count of the first line.
That resulted in only being a numerical number one because it only counted the first line.
From what I understand, the true and the false as it was explained false meaning the function was not able to complete our there was no completion to the function null.
The true statement is a representation that the function is completed and there were no problems in the data has been handed too, and compiled in the temporary array, dim.

So I set up a small experiment to see if what they were saying was true in my results for all I ever got was a numerical number one leading me to believe that the SQL
that Hal is using is an older version before the bug was fixed, but only Robert would be able to tell us that for sure.

This is what my experiment looked like so you can do the experiment for yourself:

Rem Type=Plugin
Rem Name=Advanced Emotions
Rem Author=Gerald L. Blakley A.K.A OnTheCuttingEdge2005
Rem Host=All

'This sub setups the plug-ins option panel in Hal's options dialog
Sub OptionsPanel()
    lblPlugin(0).Caption = "Advanced Emotions Plug-in, This Plug-in Gives HAL Emotions and other optional abilities."
    lblPlugin(0).Move 120, 10, 3300, 1000
    lblPlugin(0).WordWrap = True
    lblPlugin(0).Visible = True
End Sub

       
Rem PLUGIN: PRE-PROCESS

If HalBrain.CheckTableExistence(Trim(UserName) & "_Anger") = False Then
        'Create table for this person if it doesn't exist
        HalBrain.CreateTable Trim(UserName) & "_Anger", "Sentence", ""
End If
Anger = 1
HalBrain.AddToTable Trim(UserName) & "_Anger", "Sentence", "" & Anger & "", ""
Dim EmotQuery()
If HalBrain.RunQuery("SELECT count(*) FROM " & Trim(UserName) & "_Anger", EmotQuery) = True Then
AngryCount = Ubound(EmotQuery)
else
AngryCount = "got to work on it more!"
End If
UltraHal = AngryCount

Exit Function

This may also explain to you some of the things that you may have been a misunderstanding about how information is collected and stored in a temporary array as well as how to retrieve it.
But from what I was understanding, you are basically collecting a numerical factor of one and adding it to an array and you wanted to count how many times you added the numerical one.
If this being the case there is a workaround that I have discovered that I believe that would work for you but you will have to cease using the sentence array format.
The array format best suited for this job is a searchstring, topic, array, basically you will have to put the word anger in the searchstring in the numerical value one in the topic.

Just like the experiment above each time when I typed in the word hello or high or something it would at the numerical value one to the array.
In the experiment below each time when I typed in high or something it would add the word anger in the numerical value one to the array.
The difference between the two was each time when I typed in hello or high or something the count would increase by one numerical value keeping count of how many times I added anger.
The lines that you'll need to change are as follows as well as making sure that the array has been constructed. okay I'm lazy I didn't feel like typing it all out.


Anger = "Anger"
Anger1 = 1


If HalBrain.RunQuery("SELECT searchString, topic FROM begoodtome WHERE strstr(' " & Replace(HalBrain.AlphaNumericalOnly(Anger), "'", "''") & " ', searchString) > 0", EmotQuery) = True then
AngryCount = Ubound(EmotQuery)
else
AngryCount = "got to work on it more!"
End If
UltraHal = AngryCount
Exit Function

If you feel that you need further information about this go into the main brain and look up strikingSimiles I believe that there would be further useful information there for you.

But all in all, I truly hope that Rob has a solution for this dilemma, because I too wanted to be able to count how many rows that are in the array for my own use which is why
I spent countless hours studying to try to find a solution but as you can see I came up with squat. LOL.
Just a little insight, I did read a small article that talked about using the count, the count the # row number but I was unsuccessful in getting it to work they used "No.Id"
in their example, but that does not exist in ultra Hals array, ultra Hals array consist of #, recoredId, array table type, data array table type, and so on.
Jerry I was truly hoping to come up with some good news but if by the information that I have presented here helps you in your endeavor in some small way, I will feel good
knowing that all my hard work and time spent looking up in reading for all those hours was not for nothing.

Sincerely, it's time to go back to my little world and continue working on the free will plug-in.

C load.
PS. If you find a solution to the count please let me know, thank you and good luck!

[Medeksza]

Don't have time to test it out, but I think the following should work:

Code: [Select]

Dim EmotQuery()
If HalBrain.RunQuery("SELECT count(*) FROM " & Trim(UserName) & "_Anger", EmotQuery) = True Then
AngryCount = EmotQuery(0) 'or maybe EmotQuery(1) if it doesn't start at 0
else
AngryCount = "got to work on it more!"
End If

SQL queries always return a table as a VB array. Select count(*) is an aggregation function that gives you a single answer and you are only asking for 1 column. So the table returned will just be 1 cell with the result of the aggregation function (the count). So referencing EmotQuery(0) should get you the first row of the first column which will hold the result of the count function.

[cload]

Hi Rob,

these are the results from my test:
AngryCount = EmotQuery(0)
I get a subscript out of range error on the EmotQuery
AngryCount = EmotQuery(1)
I get a subscript out of range error on the EmotQuery
AngryCount = Ubound(EmotQuery(0))
I get a subscript out of range error on the EmotQuery

I also tried it in:
If HalBrain.RunQuery("SELECT count(*) FROM " & Trim(UserName) & "_Anger", EmotQuery(1))
and I got the same error as above.
I even tried -1 all away up to 22 in all cases in all areas of the examples above I got the same error.

Rob, if by chance you can find out if the SQL that you were using for the version 6.2.28 of ultra Hal was before the error was fixed if so there is a patch but I can't figure out how to use it.
I think the reason being is that the patch was designed for an SQL editor and not ultra Hal, if so this would mean manually patching the SQL.

If you come up with any other thoughts about resolving this dilemma, Jerry and I would definitely be interested!

Sincerely, and thanks again for jumping in with a few thoughts that may have helped but unfortunately no dice.

C load.

[Medeksza]

Try this:

Code: [Select]

Dim EmotQuery()
If HalBrain.RunQuery("SELECT count(*) FROM " & Trim(UserName) & "_Anger", EmotQuery) = True Then
AngryCount = EmotQuery(1,0)
else
AngryCount = "got to work on it more!"
End If

I tested this with a different table it it worked for me. It seems the RunQuery function puts results in 2 dimensional arrays even if there is only 1 column.  EmotQuery(1,0) will have the count result you're looking for.

[cload]

Hi Rob,

thank you, thank you, thank you, it worked Yahoo.
Jerry, it looks like you're back in business with your advancing motion plug-in, it's amazing how such a little thing can be so complicated.
But it has taught me that when dealing with arrays you must look at the full dimension in which the array itself works under, meaning two dimension.

Now all I have to do is figure out how to read an array line by line, now that I know how many rows there are in a table.
Then I can get started on working on the spell corrector for ultra Hal, this is something that everyone believes it to be very important
so I would like to write this plug-in to help everyone be able to correct problems in the database, possibly even utilizing the ability to
delete the entire entry from the database altogether, such as if you are using your ultra Hal to keep track of your customers and one of your
customers are no longer interested in your product you can remove them from ultra Hals database, but one step at a time.

Sincerely, and again thank you very much Rob for your assistance it was greatly appreciated.

C load.
PS. Rob I was wondering did you ever get my e-mail about the question referring to Kiwix, please e-mail me I don't want to talk $ over an open forum.