You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Field used in a P/S Query

3 replies [Last post]
Guest's picture
Guest

Hello,
I am interested in determining the queries that use a specific field in the select clause, where clause or group by clause. The field may have been used as a expression or directly.

I have been playing around with the PSQRY% tables. But I am not clear on how to join. ( I seem to be missing fields. )

Can you guys post sqls that can be used to accomplish this ?

CompShack's picture
User offline. Last seen 5 years 35 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Quick query to help you out

I just quickly wrote this so it might need some tweaking but it should be a good start for you. You will need to pass the field name you are looking for to the SQL and your will get back the Query name(s), record(s) and field name used in the "Where Clause".

SELECT   fld.qryname, fld.qryfldname, fld.recname
    FROM psqryfield fld, psqrycriteria crit
   WHERE fld.qryname = crit.qryname
     AND fld.fieldname = :1
     AND (   fld.fldnum = crit.lcrtfldnum
          OR fld.fldnum = crit.r1crtfldnum
          OR fld.fldnum = crit.r2crtfldnum
         )
GROUP BY fld.qryname, fld.qryfldname, fld.recname;

If you end up modifying the SQL to do more, please post it with us to see your modification. Good luck!

n/a
HH's picture
HH (not verified)
Thanks

Thank!
I tried using it w/o the group by clause and found that I am getting more fields than those that are on te P/S query.

I will investigate a little furter and share with board if I find someting that works.

btw, this query does not include a field added as a expression which would be stored in PSQRY expression table ( don't recollect ) the exact name.

CompShack's picture
User offline. Last seen 5 years 35 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
PSQRYEXPR Table

The PSQRYEXPR expression table has the following fields

OPRID
QRYNAME
EXPNUM
FIELDTYPE
LENGTH
DECIMALPOS
USECOUNT
EXPRESSIONTEXT

You can join by the QRYNAME field but the only field that I found to be useful in your case is the EXPRESSIONTEXT BUT not sure how it fits into your requirement.

I tried using it w/o the group by clause and found that I am getting more fields than those that are on te P/S query.
The query above will return 2 rows if you have a where clause like the following:

Where A.emplid = B.emplid

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog