How to set default value for LEFT JOIN

[thangnguyen.Olbius]

Hi Everyone,

In SQL, we can using case-when, ISNULL or another way to set default value
for a column if returned NULL. So how we can do this with view-entity ?

Thanks!

--
*THANG NGUYEN (Mr)*

*OLBIUS DEVELOPER**Email:* [hidden email]| *Mobile:* (+84)
1674636641

*OLBIUS., JSC*
*Tel:* ([phone]
*Address: *25th Fl., No 91, Nguyen Chi Thanh St., Ha Noi City, VietNam
*Website:* http://olbius.com

[Sakke Ferraris]

Hi,

If I understand your question correctly It can be done in exactly the same way as when you run the SQL.
In the view you specify a SELECT statement in which you can use case/ISNULL etc.

CREATE VIEW view_name AS
SELECT table_right.column1
        IFNULL(table_left.column2, 0),
        Table_right.column3,
         etc...
FROM table_name1 As table_right
Left outer join table_name2 As table_left
ON table_right.column1 = table_left.column1
WHERE condition;

BR, Sakke Ferraris

-----Ursprungligt meddelande-----
Från: Trà Đá Một Mình [mailto:[hidden email]]
Skickat: den 26 juli 2017 08:51
Till: [hidden email]
Ämne: How to set default value for LEFT JOIN

Hi Everyone,

In SQL, we can using case-when, ISNULL or another way to set default value for a column if returned NULL. So how we can do this with view-entity ?

Thanks!

--
*THANG NGUYEN (Mr)*

*OLBIUS DEVELOPER**Email:* [hidden email]| *Mobile:* (+84)
1674636641

*OLBIUS., JSC*
*Tel:* ([phone]
*Address: *25th Fl., No 91, Nguyen Chi Thanh St., Ha Noi City, VietNam
*Website:* http://olbius.com

[thangnguyen.Olbius]

Thanks Sakke,
But I want to mention to view-entity on ofbiz framework.
Ex:
<view-entity entity-name="ReturnHeaderAndParty"
package-name="org.ofbiz.party.party">
<member-entity entity-alias="PG" entity-name="PartyGroup"></member-entity>
<member-entity entity-alias="RH" entity-name="ReturnHeader"></member-entity>
<alias-all entity-alias="RH"></alias-all>
<alias-all entity-alias="PG"></alias-all>
<view-link rel-entity-alias="PG" entity-alias="RH" rel-optional="true">
<key-map field-name="toPartyId" rel-field-name="partyId" />
</view-link>
</view-entity>

We using: rel-optional="true" to define LEFT JOIN but I don't know how to
set default value for field empty (null) in this view.


On Sat, Jul 29, 2017 at 3:44 AM, Sakke Ferraris <[hidden email]> wrote:

> Hi,
>
> If I understand your question correctly It can be done in exactly the same
> way as when you run the SQL.
> In the view you specify a SELECT statement in which you can use
> case/ISNULL etc.
>
> CREATE VIEW view_name AS
> SELECT table_right.column1
>         IFNULL(table_left.column2, 0),
>         Table_right.column3,
>          etc...
> FROM table_name1 As table_right
> Left outer join table_name2 As table_left
> ON table_right.column1 = table_left.column1
> WHERE condition;
>
> BR, Sakke Ferraris
>
> -----Ursprungligt meddelande-----
> Från: Trà Đá Một Mình [mailto:[hidden email]]
> Skickat: den 26 juli 2017 08:51
> Till: [hidden email]
> Ämne: How to set default value for LEFT JOIN
>
> Hi Everyone,
>
> In SQL, we can using case-when, ISNULL or another way to set default value
> for a column if returned NULL. So how we can do this with view-entity ?
>
> Thanks!
>
> --
> *THANG NGUYEN (Mr)*
>
> *OLBIUS DEVELOPER**Email:* [hidden email]| *Mobile:* (+84)
> 1674636641
>
> *OLBIUS., JSC*
> *Tel:* ([phone]
> *Address: *25th Fl., No 91, Nguyen Chi Thanh St., Ha Noi City, VietNam
> *Website:* http://olbius.com
>

--
*THANG NGUYEN (Mr)*

*OLBIUS DEVELOPER**Email:* [hidden email]| *Mobile:* (+84) 96
955 0905

*OLBIUS., JSC*
*Tel:* ([phone]
*Address: *25th Fl., No 91, Nguyen Chi Thanh St., Ha Noi City, VietNam
*Website:* http://olbius.com

[Jacques Le Roux]

Hi,

You need to have your hands dirty and set something new in code (if possible).

For a start have a look at https://issues.apache.org/jira/browse/OFBIZ-4393

Not sure a solution is possible, but that's where I'd start...

If you come with a generic enough solution please contribute in a Jira, thanks

HTH

Jacques


Le 26/07/2017 à 08:50, Trà Đá Một Mình a écrit :
> Hi Everyone,
>
> In SQL, we can using case-when, ISNULL or another way to set default value
> for a column if returned NULL. So how we can do this with view-entity ?
>
> Thanks!
>