CAST and Blank fields 1

[jwxpnd]

How do I handle blank fields with CAST?

My code:
SELECT CUSID AS CUSTOMER_ ID,;
CAST(UTKVAL1 AS C(50)) as (UTKDESC),;
CAST(UTKVAL2 AS C(50)) as (UTKDESC2); && UTKDESC2 can be blank.
FROM trda;
INTO CURSOR Trak1a


Thanks!
CJ

 

[DSummZZZ]

What do you mean?
What are you trying to handle?
What is the data type you are trying to CAST()?


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[jwxpnd]

UTKDESC (field) and UTKDESC2 (field) are my captions for UTKVAL1 (field) and UTKVAL2 (field).

Sometimes UTKDESC2 is blank (nothing in the field). When this happens I receive an error. This what I'm trying to handle.

 

[Imaginecorp]

SELECT CUSID AS CUSTOMER_ ID,;
CAST(UTKVAL1 AS C(50)) as (UTKDESC),;
CAST(UTKVAL2 AS C(50)) as (UTKDESC2);
FROM trda;
where not empty(UTKVAL2) ;
INTO CURSOR Trak1a

The c(50) was an example use the UTKVAL1 & 2 field sizes...

 

[jwxpnd]

Hello Imaginecorp.

If I use this code (which works - thank you Imaginecorp):
where not empty(UTKVAL2) ;

Then the code will ignore the whole record.
I still want to see the record just basically ignore the column that is blank.

 

[Olaf Doschke]

Well you can't have a column name for each row anyway. So UTKDESC and UTKDESC2 are not fields in which you hold the name of the resultset field, or are they.

In fact I would keep this seperate, field names are technical names to adress a field of a table, not captions.

Bye, Olaf.

 

[jwxpnd]

UTKDESC (field) and UTKDESC2 (field) are my captions for UTKVAL1 (field) and UTKVAL2 (field).

Sometimes UTKDESC2 is blank (nothing in the field). When this happens I receive an error.


UTKDESC (field) is the caption field. The data in this is variable.
Example: Test1

UTKDESC2 (field) is the caption field. The data in this is variable.
Example: Test2

UTKVAL1 (field) is the result field. The data in this is variable.
Example: ABCDEFG

UTKVAL2 (field) is the result field. The data in this is variable.
Example: This is a test


When I create the cursor that will be eventually put to a CSV file. I it looks like this:

CUSTOMER_ ID Test1 Test2
X9903212 ABCDEFG This is a test

This works. However, occasionally I get a data that doesn’t have anything in UTKDESC2 (field) so this causes and error in the program.

So, the cursor needs to look like this:

CUSTOMER_ ID Test1
X9903212 ABCDEFG

 

[Dan Freeman]

What is the error? Where is it coming from?

 

[jwxpnd]

There error occurs when UTKDESC2 (field) is blank/empty.

The error is:
Syntax error

 

[Imaginecorp]

Ok: here is how you would do it, though this is not the right way...If utkdesc has spaces in its value you will get an error as well

SELECT CUSID AS CUSTOMER_ ID,; 
CAST(UTKVAL1 AS C(50)) as (IIF(EMPTY(UTKDESC),"test1",UTKDESC)),; 
CAST(UTKVAL2 AS C(50)) as ((IIF(EMPTY(UTKDESC),"test2",UTKDESC2)));  
FROM trda;
INTO CURSOR Trak1a

If you think it has spaces, use STRTRAN() to get rid of them, you can also put this in the IIF statement

 

[jwxpnd]

Thanks Imaginecorp! It works.

 

[Imaginecorp]

Glad to help...

 

[Olaf Doschke]

I'd still strongly suggest you move away from your ways. You can of course danymically name fields by data, but of course you MUST have a non empty name. Your data simply is corrupt, if you don't have a field name. But what worries me more is that you have the caption per record, but you can't have two field names for the same field, if you have two different names in UTKDESC or UTKDESC2 you'll also get an error.

eg think of this table:

UTKVAL1 , UTKDESC
1 , fieldone
2 , fieldtwo
3 , fieldthree

You can't do the SQL you do with that data, because a field name is once per result, you can't have three field names in the same cursor as the same field number.

That logic of naming fields is ill.

Bye, Olaf.

 

[jwxpnd]

Olaf,

Originally UTKVAL1 and UTKDESC come from 2 different tables which I put in to temporary cursor that is eventually going to a CSV file. It’s not a permanent table that I am manipulating.

 

[Olaf Doschke]

Okay, I see. Still I'd expect this to be variables holding the names of fields. It's weird to read a field name from a table or cursor multiplied to each row.

Bye, Olaf.