1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
| <?xml version="1.0"?>
| <!DOCTYPE hibernate-mapping PUBLIC
| "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
| "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
|
| <hibernate-mapping>
| <sql-query name="BranchFullNameQuery">
| <![CDATA[
|
| SELECT
| b1.bbbr_id,
| case
| when b8.bbbr_id is not null THEN
| CONCAT(b8.bbbr_name,:connector,b7.bbbr_name,:connector,b6.bbbr_name,:connector,b5.bbbr_name,:connector,b4.bbbr_name,:connector,b3.bbbr_name,:connector,b2.bbbr_name,:connector,b1.bbbr_name)
| when b7.bbbr_id is not null THEN
| CONCAT(b7.bbbr_name,:connector,b6.bbbr_name,:connector,b5.bbbr_name,:connector,b4.bbbr_name,:connector,b3.bbbr_name,:connector,b2.bbbr_name,:connector,b1.bbbr_name)
| when b6.bbbr_id is not null THEN
| CONCAT(b6.bbbr_name,:connector,b5.bbbr_name,:connector,b4.bbbr_name,:connector,b3.bbbr_name,:connector,b2.bbbr_name,:connector,b1.bbbr_name)
| when b5.bbbr_id is not null THEN
| CONCAT(b5.bbbr_name,:connector,b4.bbbr_name,:connector,b3.bbbr_name,:connector,b2.bbbr_name,:connector,b1.bbbr_name)
| when b4.bbbr_id is not null THEN
| CONCAT(b4.bbbr_name,:connector,b3.bbbr_name,:connector,b2.bbbr_name,:connector,b1.bbbr_name)
| when b3.bbbr_id is not null THEN
| CONCAT(b3.bbbr_name,:connector,b2.bbbr_name,:connector,b1.bbbr_name)
| when b2.bbbr_id is not null THEN
| CONCAT(b2.bbbr_name,:connector,b1.bbbr_name)
| when b1.bbbr_id is not null THEN
| CONCAT(b1.bbbr_name)
| end full_bbbr_name
|
| FROM
| bb_branch b1
| left join bb_branch b2 on b1.bbbr_pid=b2.bbbr_id
| left join bb_branch b3 on b2.bbbr_pid=b3.bbbr_id
| left join bb_branch b4 on b3.bbbr_pid=b4.bbbr_id
| left join bb_branch b5 on b4.bbbr_pid=b5.bbbr_id
| left join bb_branch b6 on b5.bbbr_pid=b6.bbbr_id
| left join bb_branch b7 on b6.bbbr_pid=b7.bbbr_id
| left join bb_branch b8 on b7.bbbr_pid=b8.bbbr_id
| where 1=1
|
| ]]>
| </sql-query>
| </hibernate-mapping>
|
|
