Re: Can you do a QBE on formonly field

lgrieser@crash.cts.com (Liam Grieser) writes: >On Sat, 25 Jun 1994, Jonathan Leffler wrote: >> >From: lgrieser@crash.cts.com (Liam Grieser) >> >Subject: Can you do a QBE on formonly field >> >Date: Fri, 24 Jun 1994 18:16:33 GMT >> >X-Informix-List-Id: <news.7350> >> > >> >Is it possible to perform a query by example on a formonly field? >> >> Yes. You can do: >> >> CONSTRUCT somestring ON >> A.Column01, A.Column02, A.Column03, B.Column04, C.Column05 >> FROM s_query.* >> >> All the fields in the s_query.* screen record can be formonly fields, >> or they can be references to any table you like. >How do you retrieve s_query.*? How would it be defined? Is s_query a >replacement for FORMONLY? s_query is a SCREEN RECORD in the .per file. See the manuals for details. See below for other details. >What if I have the SAME field on the screen twice? You can use aliasing, but really the solution is to allow the user into only one of the two fields during CONSTRUCT, since you'd wouldn't want them to put conflicting search criteria for the same database column anyway. >The first occurance of the field uses the table.fieldname identifier and the >second uses the formonly.fieldname identifier. Example: >Form Specification File >DATABASE yahoo >SCREEN >{ >Party Number:[f001 ] >Subject:[f002 ] > [f002 ] >Recommendation: > [f003 ] > [f003 ] > [f003 ] >... >} >end >TABLES >party >ATTRIBUTES >f001 = party.party_no TYPE SMALLINT; >f002 = party.remarks TYPE TEXT, PROGRAM = "vi"; >f003 = formonly.recommend TYPE LIKE party.remarks, PROGRAM = "vi"; >INSTRUCTIONS >DELIMITERS " " >Table structure >party.party_no KEY >party.party_type KEY >party.remarks >I can easily DISPLAY both fields when I have control of the query (4GL) as >the party_type field tells me what type of remark it is (i.e.- 1 is >Subject, 2 is recommendation, etc.). Here's the real problem. You can't put much search criteria on a BLOB, anyway. I would get NULL/NOT NULL would work, but putting string matching or anything else won't work. Maybe the table design could be modified to make remarks a CHAR or VARCHAR (If 255 characters is enough), and recommend as a TEXT blob. Then you could search on remarks and leave recommend out of the CONSTRUCT. >The problem occurs when I try to SELECT the appropriate fields >and retrieve them. As you show below the WHERE is filled in by the query. >> Typically, the SELECT statement ends up looking like: >> >> SELECT A.PkColumn >> FROM SomeTable A, SomeOtherTable B >> WHERE A.PkColumn = B.FkColumn >> AND ...>> >> where the ... is what CONSTRUCT returns. >The problem: >LET s1 = "SELECT party.party_no, party.remarks, party.remarks ", > "FROM party WHERE ", constructed_query clipped >- How can I pull up both remarks from the same table.field? >Perhaps by the following?: >LET s1 = "SELECT party.party_no, party.remarks, party.remarks ", > "FROM party WHERE ", constructed_query clipped, > " AND party.party_type = 1 OR part.party_type = 2" If the above doesn't work (I'm unsure if you can use numeric references in the WHERE clause), try aliasing: LET s1 = "SELECT party.party_no, party.remarks rem1, party.remarks rem2", "FROM party WHERE ", constructed_query clipped, " AND party.party_type = rem1 OR part.party_type = rem2" or whatever. ============================================================ Dennis J. Pimple Informix Software Inc Senior Consultant Denver Colorado USA dennisp@informix.com Voice:303-850-0210 Fax:303-779-4025