stored procedure to change nulls in data 1

[2969]

hi,

I have 15 tables that have NULLS entered in most of the records for 10 different fields in each of those tables. Is there a stored procedure I can make that would go in each of the record for each field and replace those nulls with 0.

I have this stored procedure that does not replaces the nulls with 0 or blanks..

PROCEDURE SetNullInv
USE Inv EXCLUSIVE
FOR i = 1 TO FCOUNT()
fieldname = FIELD(i)

IF TYPE('inv.'+ fieldname) = 'C' then
for j = 1 to 1000
REPLACE ('inv.'+ fieldname)with ' ' FOR ISNULL(('inv.'+ fieldname))
next j
ENDIF

IF TYPE('inv.'+ fieldname) = 'N' then
for j = 1 to 1000
REPLACE ('inv.'+ fieldname)with 0 FOR ISNULL(('inv.'+ fieldname))
next j
ENDIF

next i

ENDPROC

 

[Stella740pl]

2969,

I didn't understand what purpose does counter j serve in your procedure.

If you mean it to go through all records, then REPLACE ... FOR has already default scope ALL, or you can just type REPLACE ALL ... FOR.

Don't think it's the reason why the procedure doesn't work, though. Do you get any error messages or it just goes through doing nothing?

 

[2969]

hi,

i changed the SP..but it goes without any errors but does not do anything. The values are still NULLS when i open the table.

PROCEDURE SetNullInv

USE Inv EXCLUSIVE

FOR i = 1 TO FCOUNT()
fieldname = FIELD(i)


IF TYPE('inv.'+ fieldname) = 'C' then
REPLACE ALL ('inv.'+ fieldname)with ' ' FOR ISNULL(('inv.'+ fieldname))
ENDIF


IF TYPE('inv.'+ fieldname) = 'N' then
REPLACE ALL ('inv.'+ fieldname)with 0 FOR ISNULL(('inv.'+ fieldname))
ENDIF

next i

ENDPROC

 

[Stella740pl]

Can it be that your fields have type differnt from N or C? What's the structure of the table(s)?

Just as a side note, it would be more efficient to first call FCOUNT(), then run the loop. This way, the function will be called only once, not FCOUNT() times:

lnFields=FCOUNT()
FOR i=1 TO lnFields
...
NEXT

 

[Stella740pl]

Also, make sure that you are doing FCOUNT() and FIELD() in the right work area. Better supply the alias parameter when calling the functions, even though it's optional.

And use IN clause to supply alias when doing REPLACE. I am not 100% sure, but I think it would be better in some cases then specifying work area with the field name.

 

[2969]

hi,

the fields that i am trying to replace are either character or integer. so i am certain in this aspect that the code sure executes but nothing happens.

 

[2969]

is there a way I can debug it just like in VB. Actually I took the code; REPLACE ALL ('inv.'+ fieldname)with ' ' FOR ISNULL(('inv.'+ fieldname)) and executed it in command prompt, it worked fine but with stored procedure it does not do anything.

 

[Stella740pl]

Sure. Call from menu TOOLS -> DEBUGGER. You can read Help on how to use it. It slightly different from VB (well, not that much).

Meanwhile, check if your procedure works in the appropriate work area. It could be the issue. See my last post.

 

[2969]

hi, i am total newbie. When u say work area. what do you meean by that.

 

[Stella740pl]

I mean that you could be working not on the table that you expect. Tables open in different work areas indentified by numbers or aliases (preferred way). You have to be sure that you are working in the right one either by selecting it or specifying the parameters in functions you call. Try to do the following:

PROCEDURE SetNullInv

USE Inv EXCLUSIVE IN 0

lnFields=FCOUNT("Inv")

FOR i = 1 TO  lnFields

fieldname = FIELD(i, "Inv"))
 
IF TYPE('Inv.'+ fieldname) = 'C' 
       REPLACE ALL (fieldname) WITH ' ' FOR ISNULL(( fieldname)) IN Inv
ENDIF
 
 
IF TYPE('Inv.'+ fieldname) = 'N'
       REPLACE ALL (fieldname) WITH 0 FOR ISNULL(( fieldname)) IN Inv
ENDIF

NEXT

ENDPROC

OR

PROCEDURE SetNullInv

USE Inv EXCLUSIVE IN 0
SELECT Inv

lnFields=FCOUNT()

FOR i = 1 TO  lnFields

fieldname = FIELD(i))
 
IF TYPE(fieldname) = 'C' 
       REPLACE ALL (fieldname) WITH ' ' FOR ISNULL(( fieldname))
ENDIF
 
 
IF TYPE(fieldname) = 'N'
       REPLACE ALL (fieldname) WITH 0 FOR ISNULL(( fieldname))
ENDIF

NEXT

ENDPROC

Note the differences in each case. The two cases are not mutually exclusive. You can SELECT the work area, then still specify it in all the proper places.

 

[Stella740pl]

Sorry, I put an extra right parenthesis after FIELD function. Just take it off.

 

[2969]

hi, i noticed that there is some problem with isNull() becuase if i removed the isnull() and executed it say like this..

REPLACE ALL (fieldname) WITH 5 IN Inv

this executed fine..but if I add that isnull(), no execution. Any ideas

 

[Stella740pl]

But you said it works with IsNull() from Command Window? So it's IsNull() function that doesn't work, and it doesn't work from stored procedure only? Did I understand you correctly?

 

[2969]

yes, you are right. It worked from command window but from stored procedure isnull(fieldname) doesnt works...


As I mentioned i tried using with the isnull requirement, it worked like a charm but as soon as I added isnull, it didnt do anything

 

[Stella740pl]

Well, not sure, but try to use isnull(&fieldname) instead.

 

[2969]

hi, it worked with isnull(&fieldname)...

Just for knowledge sake, what is the role of & in this equation.

 

[Stella740pl]

& is for macro substitution. You can read Help file on it. But in short, it evaluates character expression, or kind of extracts the expression from the quotation marks. So, if you value of fieldname was "MyField", with &fieldname we issued

ISNULL(MyField)
instead of
ISNULL("MyField")

You can also try if it would work with

IsNull(Evaluate(fieldname))

Also, did you remove double parenthesis from IsNull((fieldname)) in your procedure? If so, try it in your procedure. Parenthesis can be used instead of & in some cases with more efficiency.

Glad it worked.

 

[2969]

thanks a lot.. you have been very considerate.

 

[2969]

hi, i would like to make this procedure a generic one where i can pass tablename as paramter and then work with it. How do we do that..

 

[Stella740pl]

You just declare your parameter in the beginning of your procedure (see help on PARAMETERS statement). When you call your procedure, call it with your parameter (it would be a character variable):

DO SetNullInv WITH "Inv"

or

theTable="D:\Data\Inv"
DO SetNullInv WITH theTable

In your procedure, it could be something like

PROCEDURE SetNullInv
PARAMETERS tableName
USE (tableName) EXCLUSIVE IN 0 ALIAS MyTable

And you are set to go. Note, that I open the table IN 0, so lowest available work area would be used and it wouldn't close any other file; put the variable in parenthesis, so it would be evaluated, and the value would be used; and gave it an alias, so I would be able to refer to the table. By the way, EXCLUSIVE opening is not necessary for this purpose, SHARED will do.

Now, you can use alias instead of the table name, like here:

SELECT MyTable
lnFields=FCOUNT("MyTable")

You might want to close it at the end of your procedure:

USE IN MyTable

More info you can get in Help, particularly in the topics on PROCEDURE and PARAMETERS.

Stella