SQL Related Articles: Using Views in a procedure to retrieve data
2004-08-21 -- Erik Pepping
Newsgroups: sv.clarion.bugreports
> Anyone willing to share a couple of view statements??? the one in the
> example aint cuttin the mustard.... i want to select records from a field
> that is a string....
>
> something like this one....
>
> select *
> from inventory
> where status = 'A' and releasenumber > 0
We are using a make Sql function for this.
This function returns your Select field21,field 2 from a file you pass it.
We inspect the file lables for this.
here is the code. (part of our connection class, commercially available,
contains many more functions!)
Connection.MakeSql Procedure(*FILE In_file, String in_where,Byte
in_perform_next, , )
NumLabels Short
i Short
Sql Cstring(5000)
Label Cstring(50)
Fieldsingroup Ushort
TableName CString(62)
FileRecord &Group
Code
TableName = in_file{Prop:Name}
If Instring('.', TableName,1,1)
TableName = TableName[Instring('.', TableName,1,1) + 1 : Len(TableName)]
end
Numlabels = in_file{prop:fields}
FileRecord &= In_File{Prop:Record}
Loop i = 1 To Numlabels
Label = Who(FileRecord,i)
If fieldsingroup <> 0
FieldsIngroup = fieldsingroup - 1
Cycle
End
If in_file{Prop:Type,i} = 'GROUP'
If in_file{Prop:Over,i} <> 0
fieldsInGroup = in_file{PROP:Fields,I}
End
Cycle
End
If in_file{Prop:Over,i} <> 0
Cycle ! Over some field
End
If Instring(':',Label,1,1) <> 0
Label = Clip(Sub(Label,InString(':',Label,1,1)+1,255))
End
If Instring('|',Label,1,1) <> 0
Label = Clip(Sub(Label,1,InString('|',Label,1,1)-1))
End
Sql = Sql & ',' & TableName & '.' & Label
end
If Size(Sql) > 2
If AddDistinct = 1
SQL = 'SELECT DISTINCT ' & Sql [2: Size(Sql)]
Else
Sql = 'SELECT ' & Sql [2: Size(Sql)]
End
End
If in_from = ''
SQL = Sql & ' FROM ' & in_file{Prop:Name}
Else
SQL = Sql & ' FROM ' & Clip(in_from)
End
If in_where <> ''
SQl = SQL & ' WHERE ' & in_where
End
If In_Perform_next = 2
Message(Sql) ! Hidden debug option
End
If in_perform_next <> 0
in_file{prop:sql} = Sql
If ErrorCode()
Message('ConOracle.MakeSql Failed 1:' & Error() & Clip(Fileerror()) &|
Sql & '| Length = ' & len(Clip(Sql)) )
SetClipboard(sql)
Return(0)
End
Next(in_file)
If ErrorCode()
If ErrorCode() = 35 or ErrorCode() = 33
Return(0)
Else
Message('ConOracle.MakeSql Next Failed:' & Error() &|
Clip(Fileerror()) & Sql & '| Length = ' & len(Clip(Sql)) )
SetClipboard(sql)
Return(0)
End
Else
Return(1)
End
Else
in_file{prop:sql} = Sql
If ErrorCode()
Message('ConOracle.MakeSql Failed 2:' & Error() & Clip(Fileerror()) & Sql)
SetClipboard(sql)
Return(0)
Else
Return(1)
End
End
Return(1)
Printed May 5, 2024, 9:32 am
This article has been viewed/printed 35119 times.
Google search
has resulted in 31 hits on this article since January 25, 2004.