Hi,

1)

I have a postgre query as follows:-

select 1 as srl,genre_id,genre_parent_id,genre_name,description,record_version,depth
from genre
where genre_id <> 0
union
select 2 as srl,genre_id,genre_parent_id,genre_name,description,record_version,depth
from genre
where genre_id = 0
order by srl,genre_name;


Now,I want to convert it into HQL Query.How do i do it.Is the following way correct:-

String sqlQuery="select 1 as srl,g.genre_id,g.genre_parent_id,g.genre_name,g.description,g.record_version,g.depth" +
" from genre as g" +
" where g.genre_id <> 0" +
" union" +
" select 2 as srl,g.genre_id,g.genre_parent_id,g.genre_name,g.description,g.record_version,g.depth" +
" from genre as g" +
" where g.genre_id = 0" +
" order by srl,g.genre_name";



2)
How do i deal with the associations of the following table structure(postgres) in mapping files & their POJOs:-

CREATE TABLE member
(
member_id serial NOT NULL,
member_role_id int4 NOT NULL,
username varchar(50) NOT NULL,
passwd varchar(50) NOT NULL,
first_name varchar(50) NOT NULL,
last_name varchar(50),
email varchar(50),
active char(1),
last_login_dt timestamp,
last_logout_dt timestamp,
admin_flag char(1) NOT NULL DEFAULT 'N'::bpchar,
CONSTRAINT member_pk PRIMARY KEY (member_id),
CONSTRAINT member_role_member_fk1 FOREIGN KEY (member_role_id) REFERENCES member_role (member_role_id) ON UPDATE NO ACTION ON DELETE NO ACTION
)


CREATE TABLE member_role
(
member_role_id serial NOT NULL,
member_role varchar(20) NOT NULL,
member_role_desc varchar(50),
CONSTRAINT member_role_pk PRIMARY KEY (member_role_id)
)


CREATE TABLE team
(
team_id serial NOT NULL,
team_desc varchar(50) NOT NULL,
CONSTRAINT team_pk PRIMARY KEY (team_id)
)


CREATE TABLE team_member
(
team_member_id serial NOT NULL,
team_id int4 NOT NULL,
member_id int4 NOT NULL,
member_role_id int4 NOT NULL,
start_dt timestamp NOT NULL DEFAULT ('now'::text)::date,
end_dt timestamp,
CONSTRAINT team_member_pk PRIMARY KEY (team_member_id),
CONSTRAINT member_role_team_member_fk1 FOREIGN KEY (member_role_id) REFERENCES member_role (member_role_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT member_team_member_fk1 FOREIGN KEY (member_id) REFERENCES member (member_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT team_team_member_fk1 FOREIGN KEY (team_id) REFERENCES team (team_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT team_member_uq001 UNIQUE (team_id, member_id, member_role_id, start_dt)
)


Kindly help on both the above problems.It is very urgent.I have a release today.