I guess I'll throw in my $0.02. I have a routine that I think does
something similar to what you're trying to do. Here are the two code
snippets that accomplish this:
This sets the variable list:
SET VAR vheat TEXT = ('R311392,R210560,R310725,R410789')
And this is a view that uses the variable:
CREATE TEMPORARY VIEW temp_ship_view (coilnum,lbs_ship) +
AS SELECT t1.coilnum,SUM(t1.pounds) +
FROM shipmast t1,heatmast t2 +
WHERE t1.meltreq=t2.meltreq AND t2.heatnum IN (&vheat) +
GROUP BY coilnum
Note the syntax. Technically the parens around the heat number list are
probably not required.
Hope that helps.
Mike Ramsour
-----Original Message-----
From: Alastair Burr [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 01, 2002 6:46 PM
To: [EMAIL PROTECTED]
Subject: Re: Forms: Pop-Up menu - Where clause variable
Nice idea, Mike, but still no luck - with both brackets I get no data found
and with only the outer set I get a brackets needed around in clause
message.
Thanks & regards,
Alastair.
----- Original Message -----
From: "MikeB" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, July 01, 2002 10:51 PM
Subject: Re: Forms: Pop-Up menu - Where clause variable
> This "In List" is made up of Integers and is not surrounded by quotes, but
> note when a variable is part of a line of code and is to replace written
> text it is to be used with ampersand instead of dot variable. The value
> vinlist contains the Parentheses, delimiter and in the case of the second
> example, the single quotes, hence:
>
> PRINT authlog WHERE (sauthnum IN (&vinlist)) ORDER BY sauthnum DESC
>
> and
>
> UNLOAD DATA FOR item USING item, price AS ASCII +
> WHERE (item IN &vinlist)
>
> ----- Original Message -----
> From: "Alastair Burr" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Monday, July 01, 2002 3:50 PM
> Subject: Re: Forms: Pop-Up menu - Where clause variable
>
>
> > Thanks, Mike, but that doesn't solve it. I get a "No rows exist or
satisfy
> > the specified clause" message but the choose works at the R:> as
expected
> > (with or without the extra quotes).
> >
> > It is as if the form pop-up menu box can only accept column names or
text
> > and cannot evaluate a dotted or ampersand variable:
> >
> > WHERE Name_Type IN (A,B,C) ORDER BY Full_Name works but
> > WHERE Name_Type IN (.vLookUp) ORDER BY Full_Name does not work -
> with
> > whatever variations that I've tried for the variable.
> >
> > Albert, I can't figure out how to get your example into the where clause
> in
> > the box on the form but thanks anyway. Again, I think that this
particular
> > box just cannot evaluate a variable...
> >
> > Dennis, your idea would work for me if I used an EEP to pop-up the
choices
> > but I would still be stuck getting the variable into the where clause
box
> on
> > the pop-up menu box for the field on the form.
> >
> > It looks as if I'll have to pop the menu up from the EEP which is easy
> > enough but, in this case, means quite a big change to the form which I
was
> > trying to avoid. It's the change that probably breaks the camel's back
and
> > means I really ought to re-construct the form from first principles to
> > consolidate various changes and I was just feeling put off by the
thought
> of
> > the work involved.
> >
> > Thanks, everyone,
> > Regards,
> > Alastair.
> >
> >
> > ----- Original Message -----
> > From: "MikeB" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Monday, July 01, 2002 7:31 PM
> > Subject: Re: Forms: Pop-Up menu - Where clause variable
> >
> >
> > > How about single quotes surrounding each of the IN LIST parts?
> > >
> > > ----- Original Message -----
> > > From: "Alastair Burr" <[EMAIL PROTECTED]>
> > > To: "R:Base ListServer" <[EMAIL PROTECTED]>
> > > Sent: Monday, July 01, 2002 12:08 PM
> > > Subject: Forms: Pop-Up menu - Where clause variable
> > >
> > >
> > > > Hi everyone,
> > > >
> > > > I'm trying to get a pop-up menu to work in a form using this syntax
in
> > the
> > > > "where clause" box:
> > > >
> > > > WHERE Name_Type IN (.vLookUp) ORDER BY Full_Name
> > > >
> > > > The value of vLookUp is defined as text and has a value of:
> "(A,B,C)" -
> > > the
> > > > bit between the double quotes.
> > > >
> > > > I've tried a number of variations such as "&vLookUp" both with and
> > without
> > > > the brackets.
> > > > I've also tried putting the IN in the variable: "IN (A,B,C)" and
> > removing
> > > it
> > > > from the where clause.
> > > >
> > > > Nothing I've tried seems to work. Is there some limitation on
> variables
> > in
> > > > the where clause here? R:Base gives me various error messages
> depending
> > on
> > > > the syntax but the real problem seems to be that it can't evaluate
the
> > > > variable.
> > > >
> > > > Thanks in advance for any help or suggestions,
> > > > Regards,
> > > > Alastair.
> > > >
> > > >
> > > > ----------------------------------
> > > > A D B Burr,
> > > > St. Albans, UK.
> > > > ----------------------------------
> > > > [EMAIL PROTECTED]
> > > > ----------------------------------
> > > >
> > > > ================================================
> > > > TO SEE MESSAGE POSTING GUIDELINES:
> > > > Send a plain text email to [EMAIL PROTECTED]
> > > > In the message body, put just two words: INTRO rbase-l
> > > > ================================================
> > > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> > > > In the message body, put just two words: UNSUBSCRIBE rbase-l
> > > > ================================================
> > > > TO SEARCH ARCHIVES:
> > > > http://www.mail-archive.com/rbase-l%40sonetmail.com/
> > > >
> > >
> > > ================================================
> > > TO SEE MESSAGE POSTING GUIDELINES:
> > > Send a plain text email to [EMAIL PROTECTED]
> > > In the message body, put just two words: INTRO rbase-l
> > > ================================================
> > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> > > In the message body, put just two words: UNSUBSCRIBE rbase-l
> > > ================================================
> > > TO SEARCH ARCHIVES:
> > > http://www.mail-archive.com/rbase-l%40sonetmail.com/
> >
> > ================================================
> > TO SEE MESSAGE POSTING GUIDELINES:
> > Send a plain text email to [EMAIL PROTECTED]
> > In the message body, put just two words: INTRO rbase-l
> > ================================================
> > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> > In the message body, put just two words: UNSUBSCRIBE rbase-l
> > ================================================
> > TO SEARCH ARCHIVES:
> > http://www.mail-archive.com/rbase-l%40sonetmail.com/
> >
>
> ================================================
> TO SEE MESSAGE POSTING GUIDELINES:
> Send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: INTRO rbase-l
> ================================================
> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: UNSUBSCRIBE rbase-l
> ================================================
> TO SEARCH ARCHIVES:
> http://www.mail-archive.com/rbase-l%40sonetmail.com/
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
