Hi all,
I'm writing a SQL expression which is expected to return a date for each order. So I wrote one and drag it into Detail section:
(SELECT Max(Coalesce("ORDER_SIGNED_MED"."CSGN_SIGNED_TIME","ORDER_SIGNED_MED"."VERB_SIGNED_TIME", "ORDER_SIGNED_PROC"."CSGN_SIGNED_TIME","ORDER_SIGNED_PROC"."VERB_SIGNED_TIME"))
FROM "ORDER_SIGNED_MED","ORDER_SIGNED_PROC"
WHERE Coalesce("ORDER_SIGNED_MED"."SIGNED_TYPE_C",","ORDER_SIGNED_PROC"."SIGNED_TYPE_C")=1 or Coalesce("ORDER_SIGNED_MED"."SIGNED_TYPE_C",","ORDER_SIGNED_PROC"."SIGNED_TYPE_C")=2)
However, this one doesn't work: it always return the same date for all orders. I guess the date it returns is really the maximum date of all orders. So I wrote a new and the only difference is to use table alias in FROM and WHERE clause:
(SELECT Max(Coalesce("ORDER_SIGNED_MED"."CSGN_SIGNED_TIME","ORDER_SIGNED_MED"."VERB_SIGNED_TIME", "ORDER_SIGNED_PROC"."CSGN_SIGNED_TIME","ORDER_SIGNED_PROC"."VERB_SIGNED_TIME"))
FROM "ORDER_SIGNED_MED" "MED","ORDER_SIGNED_PROC" "PROC"
WHERE Coalesce("MED"."SIGNED_TYPE_C","PROC"."SIGNED_TYPE_C")=1 or Coalesce("MED"."SIGNED_TYPE_C","PROC"."SIGNED_TYPE_C")=2)
I use table alias for "ORDER_SIGNED_MED" and "ORDER_SIGNED_PROC" and now the SQL expression returns the actual date for each order. But why? Anyone would explain to me?
I also try to have another expression which is supposed to return a value from a table left outer joined to "ORDER_SIGNED_MED" or "ORDER_SIGNED_PROC" tables. But this time it failed even I used alias: it only returns the same value for all orders. Any suggestions?
Thanks!
I'm writing a SQL expression which is expected to return a date for each order. So I wrote one and drag it into Detail section:
(SELECT Max(Coalesce("ORDER_SIGNED_MED"."CSGN_SIGNED_TIME","ORDER_SIGNED_MED"."VERB_SIGNED_TIME", "ORDER_SIGNED_PROC"."CSGN_SIGNED_TIME","ORDER_SIGNED_PROC"."VERB_SIGNED_TIME"))
FROM "ORDER_SIGNED_MED","ORDER_SIGNED_PROC"
WHERE Coalesce("ORDER_SIGNED_MED"."SIGNED_TYPE_C",","ORDER_SIGNED_PROC"."SIGNED_TYPE_C")=1 or Coalesce("ORDER_SIGNED_MED"."SIGNED_TYPE_C",","ORDER_SIGNED_PROC"."SIGNED_TYPE_C")=2)
However, this one doesn't work: it always return the same date for all orders. I guess the date it returns is really the maximum date of all orders. So I wrote a new and the only difference is to use table alias in FROM and WHERE clause:
(SELECT Max(Coalesce("ORDER_SIGNED_MED"."CSGN_SIGNED_TIME","ORDER_SIGNED_MED"."VERB_SIGNED_TIME", "ORDER_SIGNED_PROC"."CSGN_SIGNED_TIME","ORDER_SIGNED_PROC"."VERB_SIGNED_TIME"))
FROM "ORDER_SIGNED_MED" "MED","ORDER_SIGNED_PROC" "PROC"
WHERE Coalesce("MED"."SIGNED_TYPE_C","PROC"."SIGNED_TYPE_C")=1 or Coalesce("MED"."SIGNED_TYPE_C","PROC"."SIGNED_TYPE_C")=2)
I use table alias for "ORDER_SIGNED_MED" and "ORDER_SIGNED_PROC" and now the SQL expression returns the actual date for each order. But why? Anyone would explain to me?
I also try to have another expression which is supposed to return a value from a table left outer joined to "ORDER_SIGNED_MED" or "ORDER_SIGNED_PROC" tables. But this time it failed even I used alias: it only returns the same value for all orders. Any suggestions?
Thanks!