M
Michael Postmann
Hi!
I have 2 Tables g_m_customers and g_a_customers_origins in a sybase
adaptive serer anywhere (ASA) database.
CREATE TABLE "DBA"."G_M_CUSTOMERS"
(
"CUSTOMERID" integer NOT NULL DEFAULT autoincrement ,
"MANAGERID" integer NULL ,
"SALUTATION" varchar(50) NULL ,
"TITLE" varchar(50) NULL ,
"FIRST_NAME" varchar(100) NULL ,
"LAST_NAME" varchar(100) NULL ,
"PROVISORY" bit NOT NULL DEFAULT 0 ,
"COUNTRY" varchar(50) NULL ,
"BIRTHDAY" date NULL ,
"STREET" varchar(50) NULL ,
"ZIP_CODE" varchar(8) NULL ,
"CITY" varchar(50) NULL ,
"PHONE" varchar(25) NULL ,
"MOBILPHONE" varchar(25) NULL ,
"FAX" varchar(25) NULL ,
"EMAIL" varchar(50) NULL ,
"GENDER" integer NOT NULL DEFAULT 0 ,
"DELETED" bit NOT NULL ,
"DEBITID" integer NULL ,
"CONTACTPERSON" varchar(100) NULL ,
"INTEREST" varchar(50) NULL ,
"HOME_CLUB" char(25) NULL ,
"TRAINER" char(25) NULL ,
"GOLFHANDICAP" integer NULL ,
"HEALTHINSURANCEID" integer NULL ,
"RESSORTGROUPID" integer NULL ,
"CUSTOMERLOGIN" varchar(25) NULL ,
"PASSWORD" varchar(25) NULL ,
"languageid" integer NULL ,
"phone2" varchar(25) NULL ,
"ISCONTACT" bit NULL ,
"INSURANCEID" varchar(25) NULL ,
"COINSURED" integer NULL ,
"HI_STATE" varchar(10) NULL ,
"HI_STATE_ADD" varchar(10) NULL ,
"STREET2" varchar(50) NULL ,
"COUNTY" varchar(50) NULL ,
"GROUPTYPE" integer NULL ,
"GENDER_PREF" integer NULL DEFAULT 2 ,
"COINSURED_TYPEID" integer NULL ,
"HI_VPNUMMER" integer NULL ,
"HI_FACHGEBIET" integer NULL ,
PRIMARY KEY ("CUSTOMERID"),
)
CREATE TABLE "DBA"."G_A_CUSTOMERS_ORIGINS"
(
"CUSTOMERORIGINID" integer NOT NULL DEFAULT autoincrement ,
"CUSTOMERID" integer NOT NULL ,
"ORIGINID" varchar(5) NOT NULL ,
"HOTEL_CUSTOMERID" integer NULL ,
"ARRIVAL" timestamp NULL ,
"DEPARTURE" timestamp NULL ,
"HOTEL_RESERVATION_NUMBER" integer NULL ,
"RESERVATION_LINE_NUMBER" integer NULL ,
"ROOM_NUMBER" varchar(10) NULL ,
"TYPE" integer NULL ,
"CHECKIN" bit NOT NULL ,
"NUMBER_PAX" integer NULL ,
"PROPERTYID" integer NULL ,
"GUESTPROPERTYID" integer NULL ,
"LAST_MODIFIED" timestamp NULL ,
"CONTACTNUMBER" integer NULL ,
"IF_STATUS" integer NULL DEFAULT 0 ,
"FIDELIO_RESERVATION_NUMBER" integer NULL ,
"LAST_STATE" tinyint NULL DEFAULT 0 ,
PRIMARY KEY ("CUSTOMERORIGINID"),
)
I'm creating J2EE-Beans for that and I'm currently trying to develop an
Entity-Bean for g_m_customers.
Now I need a finder which should be able to limit the resultset by the
following criteria:
customerid, firstname, lastname, street, zip, city (up until here it's easy)
But I need to be able to filter by
hotel_customerID, hotel_reservation_number, and room_number too.
I'll give you an example:
I want all customers which firstname is 'Michael' which have been in
room number 4711 on the 2nd December 2005.
This is:
SELECT c.customerid
FROM g_m_customers c
LEFT JOIN g_a_customers_origins o
ON (c.customerid = o.customerid)
WHERE c.first_name LIKE 'Michael'
AND o.room_number = 4711
AND o.arrival <= '2005-12-02'
AND o.departure >= '2005-12-02';
(I need to mention, that one customer could have two entries in origins
at the same period of time (if he reservates a room for his wife for
example))
As a result I get the customer id's e.g. 1(5), 1(9), 3(7) and 4(14)
where the valus in () refer to the customerOriginID.
The Application server now takes the customerID's and creates an Array
of Beans to allow me, select the data, etc.
But at this point I have no reference to the g_a_customers_origins table.
If I process the first entity (ID 1) i don't know if i need to take the
origin entity with ID 5 or if I should take the entity with ID 9.
I don't see a way of storing the originID somewhere...
If I could, I could create a getOrigin() ethod which returns the
corresponding origin entity.
So, do you see a way of solving this?
thx in Advance
Nomike aka Michael Postmann
PS: I was thinking of creating a SessionBean for that instead of
building an entity bean, but as this represents data and not business
logic it seems to be a bit not ejb-'isch.
PPS: This is only the beginning, as there are similar references to
other tables as well, but I need to get an Idea on how to do this first.
I have 2 Tables g_m_customers and g_a_customers_origins in a sybase
adaptive serer anywhere (ASA) database.
CREATE TABLE "DBA"."G_M_CUSTOMERS"
(
"CUSTOMERID" integer NOT NULL DEFAULT autoincrement ,
"MANAGERID" integer NULL ,
"SALUTATION" varchar(50) NULL ,
"TITLE" varchar(50) NULL ,
"FIRST_NAME" varchar(100) NULL ,
"LAST_NAME" varchar(100) NULL ,
"PROVISORY" bit NOT NULL DEFAULT 0 ,
"COUNTRY" varchar(50) NULL ,
"BIRTHDAY" date NULL ,
"STREET" varchar(50) NULL ,
"ZIP_CODE" varchar(8) NULL ,
"CITY" varchar(50) NULL ,
"PHONE" varchar(25) NULL ,
"MOBILPHONE" varchar(25) NULL ,
"FAX" varchar(25) NULL ,
"EMAIL" varchar(50) NULL ,
"GENDER" integer NOT NULL DEFAULT 0 ,
"DELETED" bit NOT NULL ,
"DEBITID" integer NULL ,
"CONTACTPERSON" varchar(100) NULL ,
"INTEREST" varchar(50) NULL ,
"HOME_CLUB" char(25) NULL ,
"TRAINER" char(25) NULL ,
"GOLFHANDICAP" integer NULL ,
"HEALTHINSURANCEID" integer NULL ,
"RESSORTGROUPID" integer NULL ,
"CUSTOMERLOGIN" varchar(25) NULL ,
"PASSWORD" varchar(25) NULL ,
"languageid" integer NULL ,
"phone2" varchar(25) NULL ,
"ISCONTACT" bit NULL ,
"INSURANCEID" varchar(25) NULL ,
"COINSURED" integer NULL ,
"HI_STATE" varchar(10) NULL ,
"HI_STATE_ADD" varchar(10) NULL ,
"STREET2" varchar(50) NULL ,
"COUNTY" varchar(50) NULL ,
"GROUPTYPE" integer NULL ,
"GENDER_PREF" integer NULL DEFAULT 2 ,
"COINSURED_TYPEID" integer NULL ,
"HI_VPNUMMER" integer NULL ,
"HI_FACHGEBIET" integer NULL ,
PRIMARY KEY ("CUSTOMERID"),
)
CREATE TABLE "DBA"."G_A_CUSTOMERS_ORIGINS"
(
"CUSTOMERORIGINID" integer NOT NULL DEFAULT autoincrement ,
"CUSTOMERID" integer NOT NULL ,
"ORIGINID" varchar(5) NOT NULL ,
"HOTEL_CUSTOMERID" integer NULL ,
"ARRIVAL" timestamp NULL ,
"DEPARTURE" timestamp NULL ,
"HOTEL_RESERVATION_NUMBER" integer NULL ,
"RESERVATION_LINE_NUMBER" integer NULL ,
"ROOM_NUMBER" varchar(10) NULL ,
"TYPE" integer NULL ,
"CHECKIN" bit NOT NULL ,
"NUMBER_PAX" integer NULL ,
"PROPERTYID" integer NULL ,
"GUESTPROPERTYID" integer NULL ,
"LAST_MODIFIED" timestamp NULL ,
"CONTACTNUMBER" integer NULL ,
"IF_STATUS" integer NULL DEFAULT 0 ,
"FIDELIO_RESERVATION_NUMBER" integer NULL ,
"LAST_STATE" tinyint NULL DEFAULT 0 ,
PRIMARY KEY ("CUSTOMERORIGINID"),
)
I'm creating J2EE-Beans for that and I'm currently trying to develop an
Entity-Bean for g_m_customers.
Now I need a finder which should be able to limit the resultset by the
following criteria:
customerid, firstname, lastname, street, zip, city (up until here it's easy)
But I need to be able to filter by
hotel_customerID, hotel_reservation_number, and room_number too.
I'll give you an example:
I want all customers which firstname is 'Michael' which have been in
room number 4711 on the 2nd December 2005.
This is:
SELECT c.customerid
FROM g_m_customers c
LEFT JOIN g_a_customers_origins o
ON (c.customerid = o.customerid)
WHERE c.first_name LIKE 'Michael'
AND o.room_number = 4711
AND o.arrival <= '2005-12-02'
AND o.departure >= '2005-12-02';
(I need to mention, that one customer could have two entries in origins
at the same period of time (if he reservates a room for his wife for
example))
As a result I get the customer id's e.g. 1(5), 1(9), 3(7) and 4(14)
where the valus in () refer to the customerOriginID.
The Application server now takes the customerID's and creates an Array
of Beans to allow me, select the data, etc.
But at this point I have no reference to the g_a_customers_origins table.
If I process the first entity (ID 1) i don't know if i need to take the
origin entity with ID 5 or if I should take the entity with ID 9.
I don't see a way of storing the originID somewhere...
If I could, I could create a getOrigin() ethod which returns the
corresponding origin entity.
So, do you see a way of solving this?
thx in Advance
Nomike aka Michael Postmann
PS: I was thinking of creating a SessionBean for that instead of
building an entity bean, but as this represents data and not business
logic it seems to be a bit not ejb-'isch.
PPS: This is only the beginning, as there are similar references to
other tables as well, but I need to get an Idea on how to do this first.