SQL for an existing field name

[eric43]

I am creating an SQL SELECT from a number of tables but the final criteria will be whether the 'last' table has a field of a certain name eg 'FLAG9'.

	SELECT  DISTINCT ref_id,srnamedisp,perno,a.dsid,nprime,G_toneplnu,;
			G_Efoot,g_recno,b.primary as e_primary;
			FROM;
			TEMP_1 as a,TEMP_EE as b,TEMP_LL as c;
			WHERE g_recno = b.gnum;
			AND b.primary;
			AND exist(c.myflagfield);
			AND g_etype = tag1num;
			INTO TABLE aMynamelist2

I have used AND exist(c.myflagfield); as a dummy entry.

Anybody help here?

ERIC

 

[craigsboyd]

Check the variable type of the flag field. So you would add something like the following to your criteria in the SQL Select statement:

TYPE('TEMP_LL.myflagfield') != "U"

SweetPotato Software Website
My Blog

 

[eric43]

Thanks Craig,

Using the above in the given SELECT above I get a cursor but with no records when the flagfield does exist in TEMP_LL. However using ='C' gives me the records.

The last part of the puzzle is to add the flagfield FIELD to the statement for the flagfield to contain a certain value ie 'Y'

so in the above SELECT for example I need something like -

AND myflagfield = 'Y'

Many thanks for your invaluable assistance again.

Eric

 

[jimstarr]

Hi Eric,

What works for me to test if a field exists is:

IF FSIZE('myfield') = 0


Jim

 

[baltman]

FSIZE if affected by SET COMPATIBLE so be careful. I usually issue a SET COMPATIBLE OFF before using this solution just in case.

<help>
The current setting of SET COMPATIBLE determines if FSIZE( ) returns the size of a field or a file. If SET COMPATIBLE is set to OFF or FOXPLUS (the default), FSIZE( ) returns the size of a field. If SET COMPATIBLE is set to ON or DB4, FSIZE( ) returns the size of a file.
<\help>

 

[eric43]

Thnks to all above - I've used

SELECT TEMP_LL 
SELECT  DISTINCT per_no,&myflagfield as flagval FROM TEMP_LL  where FSIZE('myflagfield') = 0 INTO table TEMP_A


SELECT  DISTINCT flagval,a.ref_id,srnamedisp,a.perno,a.dsid,nprime,G_toneplnu,;
	G_Efoot,g_recno,b.primary as e_primary;
	FROM;
	TEMP_1 as a,TEMP_EE as b,TEMP_A as c;
	WHERE b.gnum = g_recno;
	AND b.primary;
	AND g_etype = tag1num;
	AND c.per_no = a.perno ;
	AND flagval = flagvalue;
	INTO TABLE aMynamelist2

Eric