using lookup and display only field as control field

[jim.murphy]

I am not sure If I can do this with a lookup, but what I would like to
do is perhaps use lookup to retrieve a control date from an
unassociated table to control what date is entered in another table.
For example :
the main table , table 1 has many entries with a field called date_
enter which is the date the record was entered.
table 2 has a control_date
If the date entered in table 1 is less than or = to the control date we

want to give the user a error message.
I am thinking of using display only field and lookup to set it
Would need to have the date value from the control table available to
the active table of table 1 when entering the the date_enter.
However as there is no join field between the two tables am not sure
how to do it. Was thinking might have to add a key field that was
allways null and in the BEFORE EDITADD EDITUPDATE section set it so
that the key would be null

Am using Informix 5 , Any help would be apprecia

[Erland Sommarskog]

(ji********@travelinsurance.ca) writes:

I am not sure If I can do this with a lookup, but what I would like to
do is perhaps use lookup to retrieve a control date from an
unassociated table to control what date is entered in another table.
For example :
the main table , table 1 has many entries with a field called date_
enter which is the date the record was entered.
table 2 has a control_date
If the date entered in table 1 is less than or = to the control date we

want to give the user a error message.
I am thinking of using display only field and lookup to set it
Would need to have the date value from the control table available to
the active table of table 1 when entering the the date_enter.
However as there is no join field between the two tables am not sure
how to do it. Was thinking might have to add a key field that was
allways null and in the BEFORE EDITADD EDITUPDATE section set it so
that the key would be null
I can't see how a NULL key field would help you. Besides a NULL key value
sounds like an oxymoron.

If table2 has a single row, the check can easily be implemented as a
trigger. If there are multiple rows in table2, you will have to have
some set of rules to determine which row to use. And sorry, we can't
assist you, since we don't know the tables nor data.
Am using Informix 5 , Any help would be apprecia

What's wrong with comp.databases.informix? This newsgroups is for
MS SQL Server, so the syntax you would get in this newsgroup may not
work for you.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[jim.murphy]

Thanks for reply . I believe there was a post from someone else here
on Informix lookups. At the moment just brainstorming so thought I
would try a post here and see what I could get. Perhaps someone has
done something similar. One idea was:
The control table would have only one row. Since there is no explicit
assosciation between the two tables if the key field was null in both
tables the date field in the control table could be looked up

Will see if I get anything from your suggestion

[Erland Sommarskog]

(ji********@travelinsurance.ca) writes:

Thanks for reply . I believe there was a post from someone else here
on Informix lookups. At the moment just brainstorming so thought I
would try a post here and see what I could get. Perhaps someone has
done something similar. One idea was:
The control table would have only one row. Since there is no explicit
assosciation between the two tables if the key field was null in both
tables the date field in the control table could be looked up

Will see if I get anything from your suggestion

With a single-row table it's easy as I said. You would use a cross
join:

CREATE TRIGGER jimstrigger ON tbl AFTER INSERT, UPDATE AS
IF EXISTS (SELECT *
FROM inserted i
CROSS JOIN controltable c
WHERE i.date_enter <= c.controldate)
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Date_entered before controldate not permitted!, 16, 1)
RETURN
END

"inserted" is a virtual table that holds the inserted rows.

Of course, the syntax above is specific to SQL Server, but it's the
only RDBMS I know.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx