select d.dmedocno from document d JOIN custprop c
where (c.strdata is not null
or c.datedata is not null or c.numdata is not null
or c.booldata is not null or c.blobdata is not null)
group by d.dmedocno having count(c.propertyname)=4
This above returns returns about 63000 rows each containing one document number like eg
now for each of this document number i have to find specific property and each of this document number may be associated with 4 of the properties
Why don't you tell us what your tables look like? Maybe then somebody can help you write the query. But if your document attributes are not in a table, then obviously SQL queries won't help much (unless you want to get into things like Oracle external tables).