Page 1 of 1

Sql update possible

PostPosted: Sat Mar 28, 2015 2:27 pm
by BRobinsonS
Is this statement possible?
Code: Select all
dbpExecSQL "AddrBook" "UPDATE Contacts SET DrawNumber = [AddrBook.Contacts.$RecNum] Where ActiveStatus = True " ""


Not sure if you can use $RecNum variable in this statement.
I want to generate numbers from 1 to Total records after a Query.
These are draw numbers for Loonie draw for a membership.

Re: Sql update possible

PostPosted: Sat Mar 28, 2015 3:24 pm
by Gaev
Brian Robinson:

Not sure if you can use $RecNum variable in this statement.

I don't think so ... at best you might get the value of the current record when the command is issued.

I want to generate numbers from 1 to Total records after a Query.

In SQL, there is a value called ROW_NUMBER() ... but it looks like it returns the physical row number (not the row number within the Query).

You can try ...
Code: Select all
dbpExecSQL "AddrBook" "UPDATE Contacts SET DrawNumber = ROW_NUMBER() Where ActiveStatus = True " ""

... to see if it returns contiguous numbers or not.

Otherwise, you can always setup a Loop/EndLoop in NeoBook ... how many total/active records does the Table have ?

Re: Sql update possible

PostPosted: Sat Mar 28, 2015 3:46 pm
by BRobinsonS
Currently approx 380 for my Legion, but for other Legions might be more.

I'll try your suggestions about Loop but is there a way so you don't see all the activity (records changing) as it goes through the records?

Re: Sql update possible

PostPosted: Sat Mar 28, 2015 5:34 pm
by Gaev
Brian Robinson:

is there a way so you don't see all the activity (records changing) as it goes through the records?

If you are refering to the Grid, how about dbpHideGrid ? ... or HideObject the "Rectangle hosting the Grid" ?

Code: Select all
dbpQuery ""AddrBook"" "Contacts" "ActiveStatus = True"
SetVar "[QueryRecordCount]" "[AddressBook.Contacts.$RecCount]"

dbpFirst "AddrBook" "Contacts"

Loop "1" "[QueryRecordCount]" "[NextDrawNumber]"
   SetVar "[AddressBook.Contacts.DrawNumber]" "[NextDrawNumber]"
   dbpNext "AddrBook" "Contacts"
EndLoop

Re: Sql update possible

PostPosted: Sun Mar 29, 2015 11:00 am
by BRobinsonS
I used the Loop to do the job.
I am not using a grid but fields on the screen where the activity would occurs.
At least when the option is picked the user would know something is happening.

BTW, The Row_Number() came up as undefined.

I like the quietness of SQL. I use SQL first to replace non-active members with DrawNumber = 0 THEN the loop....
So SQL just does its job quietly before the Loop does its job.

Re: Sql update possible

PostPosted: Sun Mar 29, 2015 12:45 pm
by Gaev
Brian Robinson:

I am not using a grid but fields on the screen where the activity would occurs.

Make up a CustomWindow that is large enough to cover the offending fields ... with perhaps a Text box with the message "Please Wait" ... and code its Subroutine like so ...
Code: Select all
:Container1_OnOpen

dbpQuery ""AddrBook"" "Contacts" "ActiveStatus = True"
SetVar "[QueryRecordCount]" "[AddressBook.Contacts.$RecCount]"

dbpFirst "AddrBook" "Contacts"

Loop "1" "[QueryRecordCount]" "[NextDrawNumber]"
   SetVar "[AddressBook.Contacts.DrawNumber]" "[NextDrawNumber]"
   dbpNext "AddrBook" "Contacts"
EndLoop

CloseCustomWindow "Container1"
Return


BTW, The Row_Number() came up as undefined.

Perhaps it is picky ... try (all caps) ROW_NUMBER()