SQL quirk?

[FoxNovice]

workWk = "Monday,Tuesday,Wednesday,Thursday,Friday"

I have a database "pict" with a DATETIME field "datestamp"

The SQL statement
select cdow(datestamp) AS WkDay, * ;
from pict.
where cdow(datestamp) $ workWK

The output cursor:
WkDay
=======
Tuesda
Monday
Wednes
Thursd
Friday

Why did the SQL statement limit the output Column to 6 characters?

Thanks

(VFP)FoxNovice

 

[fluteplr]

SQL tends to create the column size using the value in the first row received when it is retrieving an expression or udf. The first row probably had Friday or Monday which have six characters in them. You can work around this as follows.

The SQL statement
select padr(cdow(datestamp),7) AS WkDay, * ;
from pict.
where cdow(datestamp) $ workWK

You might also look at the dow() function.

As in.

select padr(cdow(datestamp),7) AS WkDay, * ;
from pict.
where dow(datestamp) between 1 and 5

 

[FoxNovice]

Thanks fluteplr

Yea I padded it with spaces and took the left(9) characters and that worked for me..but it's always better to know why something is happening.

Thanks

(VFP)FoxNovice

 

[fluteplr]

BTW, you can have the same issue with a function that results in a numeric value.

 

[FoxNovice]

Huh?

Example please )

 

[chpicker]

Example:

SELECT val(cCount) FROM myTable

Given these records:

125
1688
12
99632

Your resulting cursor will contain the following:

125
***
 12
***

The reason? The first time VAL(cCount) is evaluated, it comes up with a field of type N(3,0) and that becomes the type for the column. Now the two numbers that are larger than 3 digits will come up as numeric overflows.

Ian

 

[fluteplr]

assume you have two numeric fields num1 and num2
the data in these fields varies wildly from 1 to 999999999

do a select num1 * num2 as answer from mytable

if the first row selected has two small numbers in it say 1 and 2 the answer will be 2. VFP will assume that all the numbers will be small and create a small field in the cursor to put them in. The next line has 999999999 and 888888888 the answer is some huge number, I am two lazy to calculate. VFP may then return an overflow because the field it created earlier is too small to contain the answer.

 

[ramani]

HI fluteplr,
I think you are wrong... in telling num1 * num2 case above.

VFP will use the field sizes while picking up records from a table.
SO... SELECT field1, field2 etc picks up the same data type and length/sizes of these fields in the resulting query. So even if num1 is 0 in the first record, the field type and size will remain same.

The problem arises when the fields are manipulated and the result is incorporated .. such as num1*num2, text1+text2, DOW() etc etc... In these cases, num1*num2 will definetly takes the fieldSize*fieldSize probable vale.. (Example.. fieldswith 2 decimals num1*num2 will generate a four decimal resultField). But in the text1+text2 field also goes correctly... because the padding takes place autonmaticaly ..i.e. field1Size+Field2Size (The contect can be spaces !).

But when a function is used like DOW(), the first record returns 'n' number of charaters and the selected field obtains this size 'n' for that column. The remaining processing follows that standard. So we have to judge and take care of the sizes when we use functions or non-field variables in an SQL.

Hope this explains the behaviour of the select statement ramani :-9
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[FoxNovice]

Thanks guys

Yes it definately gives me more amunition when I'm creating cursors. When you know what affect the SQL will have on your output saves a lot of confusion.

I would like to thank all who gave of his time to address the above problem.

I got a lot more out of this thread than I origionaly intended

Thanks

FoxNovice

 

[fluteplr]

I may have simplified the example above a bit too much, but it you look at it I was returning the product of the two fields num1 * num2. I have seen this result when returning a data from a UDF.

 

[chpicker]

Hmm...it looks like my example doesn't do as I expected. It actually created a column of n(8,2) in the query.

/shrug

Ian