Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories
by Richard1521662995 » Fri Feb 19, 2021 6:19 pm
I have created a relation
table1.field1 = table2.field1
table1.field2 = table2.field2
table1.field3 ^||= table2.field3
table1.field4 ^||= table2.field4
if for example table1.field4 and table2.field4 are both null then the relation doesn't find any data.
However, with the same data
if ( table1.find()) {
table1.field1 = table2.field1
table1.field2 = table2.field2
table1.field3 = '^||=' + table2.field3
table1.field4 = ' ^||=' + table2.field4
returns the expected data.
Can anyone help with why the relation doesn't return any data.
TIA
Richard Clarke
acss.co.uk
-
Richard1521662995
-
- Posts: 40
- Joined: Wed Mar 21, 2018 10:09 pm
by Joas » Mon Feb 22, 2021 3:51 pm
Hi Richard,
What query do you get when you do:
- Code: Select all
databaseManager.getSQL(relatedFoundSet)
Joas de Haan
Yield Software Development
Need help on your project?
yieldsd.com
-
Joas
- Site Admin
-
- Posts: 842
- Joined: Mon Mar 20, 2006 4:07 pm
- Location: Leusden, NL
-
by Richard1521662995 » Tue Feb 23, 2021 10:25 am
Hi Joas,
It returns
select
Table2.Table2_id
from
Table2
where
(Table2.Field1 = ?
and Table2.Field2 = ?
and (Table2.Field3 = ?
or Table2.Field3 is null)
and (Table2.Field4 = ?
or Table2.Field4 is null))
order by
Table2.Table2_id asc
for Table2.find()
select
Table2.Table2_id
from
Table2
where
(Table2.Field3 is null
or Table2.Field3 = ?)
and Table2.Field2 = ?
and (Table2.Field4 is null
or Table2.Field4 = ?)
and Table2.Field1 = ?
order by
Table2.Table2_id asc
Table2.find() is coded in field order
Thanks
Richard Clarke
acss.co.uk
-
Richard1521662995
-
- Posts: 40
- Joined: Wed Mar 21, 2018 10:09 pm
by Joas » Tue Feb 23, 2021 11:39 am
That looks OK to me too.
If you run that query yourself, do you get the right data?
And if you perform the find()-search() and afterwards do databaseManager.getSQL() on that foundset, what query do you get?
Joas de Haan
Yield Software Development
Need help on your project?
yieldsd.com
-
Joas
- Site Admin
-
- Posts: 842
- Joined: Mon Mar 20, 2006 4:07 pm
- Location: Leusden, NL
-
by Richard1521662995 » Tue Feb 23, 2021 12:04 pm
After
if ( table1.find()) {
table1.field1 = table2.field1
table1.field2 = table2.field2
table1.field3 = '^||=' + table2.field3
table1.field4 = ' ^||=' + table2.field4
select
Table2.Table2_id
from
Table2
where
(Table2.Field3 is null
or Table2.Field3 = ?)
and Table2.Field2 = ?
and Table2.Field4 is null
and Table2.Field1 = ?
and (Table2.Field3 is null
or Table2.Field3 = ?)
and Table2.Field2 = ?
and Table2.Field4 is null
and Table2.Field1 = ?
order by
Table2.Table2_id asc
after the search is the one in the previous post.
Richard Clarke
acss.co.uk
-
Richard1521662995
-
- Posts: 40
- Joined: Wed Mar 21, 2018 10:09 pm
Return to Programming with Servoy
Who is online
Users browsing this forum: Google [Bot], harbourj, robert.edelmann and 6 guests