SQL Related Articles: Using Set and Prop:Where to limit a file loop
2004-04-10 -- Dan Pressnell
Newsgroups: sv.clarion.drivers
> >I agree. Using the table works fine if you have 2-10 columns or the
> >few columns you need are all in the first 5 columns or so. If you
> >need to start counting on your toes, you better break down and use a
> >view
...
> I use the following method to generate a string of fields in the proper
order
> that can be used in place of *
> It is part of a derived file manager so it is available for all the files
in the system.
> Not real pretty but it works. I always use DATEGrp in the name of a MSSQL
date
> time group structure so this method can properly handle the fields.
>
> SharpeFileManager.SelectString PROCEDURE()
> Ndx USHORT,AUTO
> FieldName CSTRING(50)
> Sel CSTRING(2000)
> TestString STRING(10)
> CODE
> Sel = ''
> LOOP Ndx = 1 TO Self.File{PROP:Fields}
> If INSTRING('DATEGRP',UPPER(Self.File{Prop:Label,Ndx}),1,1) AND
Self.File{PROP:Over,Ndx}
> Ndx += 2
> Cycle
> End
> TestString = Self.File{PROP:Over,Ndx}
> FieldName = Self.File{Prop:Label,Ndx}
> FieldName[INSTRING(':',FieldName,1,1)] = '.'
> IF Sel = ''
> Sel = FieldName
> ELSE
> Sel = Sel & ', ' & FieldName
> Assert( ( LEN(Sel) + LEN(FieldName)+2) <= 2000 ,'SelectString
Maximum length exceeded')
> END
> END
> Sel = UPPER(Sel)
> RETURN Sel
If I need to do something like that, I just do this:
set(accounts)
accounts{prop:where} = 'customerid = ' & loc:customerid
loop
next(accounts)
if errorcode() <> 0
break
end
! do processing here
end
The set(accounts) will generate the SQL according to the dictionary layout,
and the prop:where supplies the where clause.
Dan
Printed April 29, 2024, 12:42 am
This article has been viewed/printed 35107 times.
Google search
has resulted in 16 hits on this article since January 25, 2004.