TOPIC: join and derived column in a query
#1113
join and derived column in a query 2 Years, 6 Months ago
I have two tables

1)
tab_items
with these fields: id_item, description, qty_on_hand, Qty_available

Qty_available is a derived column with a rule

Rule_qty_available
function()
{
return .qty_on_hand - .qty_reserved
}


2)
tab_reserved
with these fields: id_reserved, id_item, qty_reserved



i want to print the records with Qty_available < 0


with this query

Code:

tab_items join tab_reserved where qty_available < 0 

is returned nothing


with this query

Code:

tab_items join tab_reserved extend qty_available where qty_available < 0

are returned the exactly records


"extend" is the right manner to retrive derived columns with "join" and "where"?
it is the only manner?


thanks in advance

Claudio

Post edited by: claudio08, at: 2008/02/07 09:44
 
 
#1121
Re:join and derived column in a query 2 Years, 6 Months ago
Extend is one way to add derived columns.

But it should work if they are part of the schema. Does Qty_available show up in Schema View? No spelling mistakes?

You also may need parenthesis:

(tab_items join tab_reserved) where qty_available < 0

Otherwise it might be interpreted as:

tab_items join (tab_reserved where qty_available < 0 )

which won't work.

Hope that helps!
 
 
andrew
 
#1125
Re:join and derived column in a query 2 Years, 6 Months ago
There are not spelling mistakes.
The exact field name is Pr1qdi(Qty_available) in my table, very simple
and in the query i use pr1qdi

now i added the parenthesis
(pre_nat_piatti join pre_nat_piatqr) where pr1qdi < 0 sort pr1pos, pr1des
but does not work

this work
(pre_nat_piatti join pre_nat_piatqr) extend pr1qdi where pr1qdi < 0 sort pr1pos, pr1des


Qty_available is showed in Schema View as:
Field_pr1qdi /* cm_prenotazioni class : Field_number */
Field_number /* stdlib class */
Prompt: q/disp
Control: #("EuroNumber", width: 9, size: 12, mask: "-#.###,##")
Format: #("EuroNumber", width: 9, size: 12, mask: "-#.###,##")

the complete table definition is:
pre_nat_piatti
(pr1des, pr1id, pr1note, pr1pos, pr1prz, Pr1qdi,
pr1qpr, pr1rad, pr1raz, Pr1tot, pr1unm)
key (pr1id)

thanks
 
 
#1127
Re:join and derived column in a query 2 Years, 6 Months ago
Sorry, I did not recognize the problem at first.

Your rule involves fields from both tables. But Suneido does not "know" this. All it knows is that the qty_available is a rule on the first table.

If you look at the strategy that Suneido uses for the query you should see that it is applying the where to just the first table.

The query optimizer moves where's "down" to specific tables e.g. so it can use indexes.

You should not "attach" rules to a table if they depend on fields from other tables. Use extend on the query instead.

Another alternative would be for the rule to look up the reserved quantity (instead of using join). But this is often less efficient.
 
 
andrew
 
#1135
Re:join and derived column in a query 2 Years, 6 Months ago
i have eliminated JOIN from the query. Now i have:
1)
pre_nat_piatti schema is:
(pr1des, pr1id, pr1note, pr1pos, pr1prz, Pr1qdi,
pr1qpr, Pr1qri, pr1rad, pr1raz, Pr1tot, pr1unm)
key (pr1id)

2)
Rule_pr1qri
function()
{
try r= Query1(.tabpiatqr$' where pr1id = '$Display(.pr1id))
catch r= false
if (r is false) return 0
return r.pr4qri //value from pre_nat_piatqr
}

3)
Rule_pr1qdi
function()
{
return .pr1qpr - .pr1qri
}

Queries:
a) this does not work
pre_nat_piatti extend tabpiatqr = "pre_nat_piatqr" where pr1qdi
QueryStrategy=> 'pre_nat_piatti^(pr1id) WHERE^(pr1id) pr1qdi EXTEND tabpiatqr = "pre_nat_piatqr"'

b) this work
pre_nat_piatti extend tabpiatqr = "pre_nat_piatqr",pr1qdi where pr1qdi < 0
QueryStrategy=> 'pre_nat_piatti^(pr1id) EXTEND pr1qdi, tabpiatqr = "pre_nat_piatqr" WHERE (pr1qdi < 0)'

c) i expected that this does not work, but work
(i expected to use tabpiatqr = "pre_nat_piatqr",pr1qdi,pr1qri)
pre_nat_piatti extend tabpiatqr = "pre_nat_piatqr" where pr1qdi > pr1qri
QueryStrategy=> 'pre_nat_piatti^(pr1id) WHERE^(pr1id) (pr1qdi > pr1qri) EXTEND tabpiatqr = "pre_nat_piatqr"'


problem solved, but i am a little confused
 
 
#1136
Re:join and derived column in a query 2 Years, 6 Months ago
It can be difficult to understand what the query optimizer is doing sometimes - it is quite complex.

If you have:

... extend rule where rule ...

the optimizer knows it cannot move the where to before the extend.
 
 
andrew