How can I filter on a SQL DATE which is NULL ?
Maarten
I have a browse with several, user controlled, filters.
Some of those filters show the rows that have a DATE
colomn that is 0 or, the opposite, that is > 0.
I do a SETNULL() on an empty DATE field.
Now my filter doesn't work.
e.g. 'Pre:SomeDate = 0'
or 'Pre:SomeDate > 0'
This will translate into ...where SomeDate > ToDate('0-0-0',dd-mm-yyy)
but since SomeDate is NULL this will never work.
I tried the filter expression:
'Pre:SomeDate is NULL'
but this isn't passed to the select statement and causes my
browse to fill up with mucho duplicate, non relevant, records
although I'm using a unique key.
So how do I get all rows with a DATE-field is NULL or with
a DATE-field is NON NULL, using ABC filters ?
--
Best regards,
Maarten
CDD3.1, C5EEb - ABC & C5.5.08EE
Certainly Clarion Developer
"I know what I know but I do not know what you don't know"
Rick Martin
Use the filter expression 'SQL(SQLAlias.SomeDate is NULL')
HTH,
--
Rick Martin
Sharpe Software, Inc.
"Maarten" <maarve...@planet.nl> wrote in message
news:3f7c...@news.softvelocity.com...
Maarten
> Use the filter expression 'SQL(SQLAlias.SomeDate is NULL')
Thanks, I'll try that (tomorrow<g>).
Do you know of any way to get the SQLalias ?
(the view has multiple JOIN()'s)
I can look in the trace but I do not know of that Alias
will be consistent.
Geoff Bomford
Geoff Bomford
www.comformark.com.au
Computer services For Marketing
"Maarten" <maarve...@planet.nl> wrote in message
news:3f7c...@news.softvelocity.com...
Guy Cauwenbergh
To be sure of the alias to use, set it yourself using MyTable{Prop:Alias} =
'XX'
Guy
"Maarten" <maarve...@planet.nl> wrote in message
news:3f7c...@news.softvelocity.com...
Maarten
> To be sure of the alias to use, set it yourself using
> MyTable{Prop:Alias} = 'XX'
Wow, learned two new, usable, statements in one day<g>
Thanks Guy and Rick.
I think I must issue that right after opening the view (?)
The view, however, is opened by the view manager.
Do you happen to know the closest embed, after opening
the View, before processing it?
Btw: Do you know if the Clarion generated Alias is consitent?
Guy Cauwenbergh
the file.
> Btw: Do you know if the Clarion generated Alias is consitent?
It seems that it's a single letter from A to Z allotted in the chronological
order of opening of the files.
When I have to do SQL requests in a procedure, I prefer to give to alias a
more logical name.
Guy
"Maarten" <maar...@planet.nl> wrote in message
news:3f7d...@news.softvelocity.com...
Rick Martin
I use a template to set the prop:Alias of all my SQL files to their Prefix
during the init of the data dll. That way I always know what the file's
alias is.
HTH,
Rick Martin
Sharpe Software, Inc.
"Maarten" <maar...@planet.nl> wrote in message
news:3f7d...@news.softvelocity.com...
Maarten
> Not after opening the view, but after opening the file;
> the property is on the file.
Yes, of course, duh.
I'm having a rough day trying to find a GPF that is introduced
into my app when I switched from TPS to Oracle so my mind
is a little dryed out at the moment.
> It seems that it's a single letter from A to Z allotted in the chronological
> order of opening of the files.
Hmmmm, when looking at the trace's it looks like they are
in chronological order of the JOIN's in the VIEW, starting
with 'A' for the parent.
> When I have to do SQL requests in a procedure, I prefer to give to alias a
> more logical name.
It sure beats relying on Clarion for supplying the same Alias
after each change/compile of the app.
Tnx.
--
Best regards,
Maarten
CDD3.1, C5EEb - ABC & C5.5.08EE
Certainly Clarion Developer
"I know what I know but I do not know what you don't know"
Maarten
> I use a template to set the prop:Alias of all my SQL files to their Prefix
> during the init of the data dll. That way I always know what the file's
> alias is.
Clever! I like that.
I've already got strange errors because I (automatically) added
the prefix to the field-labels in the SQL() clause<g>
Does this mean that the Alias is consistent for as long as
the app runs, or as long as we are connected to the backend?
(because tables are not really opened/closed in SQL ?)
Tnx
--
Best regards,
Maarten
CDD3.1, C5EEb - ABC & C5.5.08EE
Certainly Clarion Developer
"I know what I know but I do not know what you don't know"
Rick Martin
> Does this mean that the Alias is consistent for as long as
> the app runs, or as long as we are connected to the backend?
> (because tables are not really opened/closed in SQL ?)
I haven't tested it, but I'm pretty sure it is for the entire run of the
application.