Hi,

I was just wondering if I'm doing something wrong, or if conditions can't include cast statements.

In my SQL I'm wanting to only select records where the weighin timestamp has the same date as the weighout timestamp:

select Cast(Weighin as Date), Cast(Weighout as Date), weighin, weighout, Weighout-WeighIn as Diff
From Tickets
where
(Cast(WeighIn as Date) <> Cast(WeighOut as Date))  /* Make sure they fall on the same day*/
order by diff desc

... which ends up giving me records with different dates.

If Cast isn't permitted in the where statement is there another approach I can use to obtain this information. (Short of using memory tables and selecting into a memory table, and then querying that - I'm trying to avoid memory tables where possible to become more EDB Port friendly).

Adam


Just tried here on my old version of DBISAM and its fine. But, if you want them on the same day shouldn't the condition be

(Cast(WeighIn as Date) = Cast(WeighOut as Date))

Roy Lambert

Hi Roy,

Thanks,and yes - you're right it should be =, and not <>

The problem I seem to be getting is with a larger query. it seems to be giving me records that include dates outside of the range...

select cast('MAX_DAILY_TURNAROUND' as VARCHAR(40)) as Item, Type, max(Weighout-WeighIn) as Diff
Into Memory\X1
from Tickets
where
/*101*/ (weighin > current_date - 600)
/*1001* and (Not Archived)
and (Cast(WeighIn as Date) = Cast(WeighOut as Date))  /* Make sure they fall on the same day*/
and (WeighIn <> WeighOut) /* Exclude any where the same time exists for them both */
and (not Rejected)
group by Type

And I've just found the issue:

it's line:

/*1001* and (Not Archived)

it's missing the closing terminator, and should be:

/*1001*/ and (Not Archived).

So it's actually ignoring the condition directly below it...

I was looking at cast being the issue, but instead it's my sloppy touch-typing.

Adam


What you do now is go into preferences and set comments to dull yellow that you have to strain to read <vbg>

Roy Lambert

Great idea - but I don't think I have that option in XE2 - at least for TStrings, or even SQL Comments (if I 'view in editor').

It works for other comments such as

(* comments *)
// Comments, and
{ Comments}

but not for

/* Comments */

Adam


>Great idea - but I don't think I have that option in XE2 - at least for TStrings, or even SQL Comments (if I 'view in editor').
>
>It works for other comments such as
>
>(* comments *)
>// Comments, and
>{ Comments}
>
>but not for
>
>/* Comments */

I was thinking EDBManager, however, great minds to the rescue - change your sql comments to /* { .... }*/

cue sound of mad cackling laughter

Roy

> I was thinking EDBManager, however, great minds to the rescue - change your sql comments to /* { .... }*/
> cue sound of mad cackling laughter

LOL - Nice one Roy, but wouldn't work if I made the same mistake...

ie

Where
(True)
/* {.......} *   and (Something else)
/* {.......} */  and (Another thing)...


... something else would be ignored because of the bad termination. But I like the way your mind thinks outside the box.