Linking Databases

[kcoleman26]

Crystal 2008
ODBC Connection

Not sure I can even explain this intelligently. This is for a Housekeeping Report for a Hotel.

I am linking 3 databases {RMP}-->{LMGEP}-->{GIP} in that order as Left Outer Joins. The report works like I want except it doesn't pull the room when 2 or more people share the same room. This information only exist in the {GIP} file. How can I get this info to pull into my report? When I tried swithing the {GIP} file first and linking the files differently the report pulls the same room number and every guest still in the system in random order.

KC

 

[dgillz]

please post sample data abd desired results.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[IanWaterman]

Also post your select statement, it is likely that you are putting a condition on LMGEP or GIP tables which is then overriding the Left Outer Joins.

Ian

 

[kcoleman26]

Select Statement
not ({RMP.OTYPRM} in ["H", "O"]) and
{@f_Occup_Stat} = {?p_Occ_Status} and
{@f_Clean_Status} <> "Clean" and
{GIP.WINGGI} = {?p_Wing}

Results:
Wing Rm # Rm Type Occ Stat Clean Stat Depart Units VIP Grp # PPl Share
DL D5112 T2 Occupied Dirty 4/24/2008 1.00 N-NHS10 2 0.00
DL D5113 T2 Occupied Dirty 4/26/2008 1.00 C-BMC8 1 0.00
DL D5116 T2 Occupied Dirty 4/25/2008 1.00 C-BMC8 1 0.00
DL D5117 T2 Occupied Dirty 4/23/2008 1.00 N-NHS10 1 0.00
DL D5122 T2 Occupied Dirty 4/24/2008 1.00 N-NHS10 3 0.00


What is happening is everything with with a share isn't showing up. D5118 is a Occupied room but is a share reservation.

What I managed to find on a different report was when a reservation is a share the {RMP.GSTARM}was blank unless I linked the files differently when I tried to link this report the same way. My data kept repeating the same room numbers over and over.

This is my formula using the {RMP.GSTARM} which I use in my select statement.
Occ Stat Formula
if {RMP.GSTARM} = "O" AND DATE ({RMP.DPDTRM})+1 = CurrentDate then "DUE OUT" else

if {RMP.GSTARM} = "O" AND DATE ({RMP.DPDTRM})+1 <> CurrentDate then "Occupied" else

if {RMP.GSTARM} <> "O" then "VACANT"




SQL
SELECT "LMGEP"."HKCDGE", "RMP"."HSKURM", "RMP"."CSTARM", "RMP"."DPDTRM",
"RMP"."GSTARM", "RMP"."WINGRM", "RMP"."OTYPRM", "RMP"."RMNORM",
"RMP"."RMTPRM", "GIP"."CONVGI", "GIP"."VIPGI", "GIP"."WINGGI",
"GIP"."NPERGI", "GIP"."NCT1GI", "GIP"."SEQSGI"
FROM {oj ("ATHENA"."LMDTA"."RMP" "RMP" LEFT OUTER JOIN
"ATHENA"."LMDTA"."LMGEP" "LMGEP" ON "RMP"."SEQRM"="LMGEP"."SEQGE") INNER
JOIN "ATHENA"."LMDTA"."GIP" "GIP" ON "LMGEP"."SEQGE"="GIP"."SEQGI"}
WHERE NOT ("RMP"."OTYPRM"='H' OR "RMP"."OTYPRM"='O') AND ("GIP"."WINGGI"='CA' OR "GIP"."WINGGI"='DL' OR "GIP"."WINGGI"='GA' OR
"GIP"."WINGGI"='MG')

 

[IanWaterman]

Try changing last line of select statement to

(isnull({GIP.WINGGI}) or GIP.WINGGI} = {?p_Wing})


Ian

 

[kcoleman26]

It was that easy...WOW
What did the isnull do?

 

[kcoleman26]

Wait sorry that didn't work. I looked at the wrong room number when I updated the report.

 

[IanWaterman]

YOu select statement is not wrapping on the viewr can you add line breaks so it fits screen. Looks like join from LMGEP to GIP is an equal join not a left outer.

Ian

 

[kcoleman26]

SELECT "LMGEP"."HKCDGE", "RMP"."HSKURM", "RMP"."CSTARM",
"RMP"."DPDTRM", "RMP"."GSTARM", "RMP"."WINGRM", "RMP"."OTYPRM", "RMP"."RMNORM", "RMP"."RMTPRM", "GIP"."CONVGI",
"GIP"."VIPGI", "GIP"."WINGGI", "GIP"."NPERGI",
"GIP"."NCT1GI", "GIP"."SEQSGI"
FROM
{oj ("ATHENA"."LMDTA"."RMP" "RMP" LEFT OUTER JOIN "ATHENA"."LMDTA"."LMGEP" "LMGEP"
ON "RMP"."SEQRM"="LMGEP"."SEQGE") INNER JOIN "ATHENA"."LMDTA"."GIP" "GIP" ON "LMGEP"."SEQGE"="GIP"."SEQGI"}
WHERE NOT ("RMP"."OTYPRM"='H' OR "RMP"."OTYPRM"='O') AND ("GIP"."WINGGI" IS NULL OR ("GIP"."WINGGI"='CA'
OR "GIP"."WINGGI"='DL' OR "GIP"."WINGGI"='GA' OR "GIP"."WINGGI"='MG'))

Currenlty the LMGEP to GIP is an inner join it didn't change the results if it was a Left Outer or Inner I have been playing with it both ways

 

[kcoleman26]

It is now pulling the Shares into the report. I changed the LMGEP to GIP to Left Outer/Inforce From/=. I had Join Not Enforced selected.

However the share is showing a blank field. Is their a way to show the value of the share which would be 1,2,3, or 4. Instead of blank?

 

[IanWaterman]

The only way it can be blank is if the field is null.

Where is this data stored?

Ian

 

[kcoleman26]

Would the isnull in my Select statement make it be doing that.

not ({RMP.OTYPRM} in ["H", "O"]) and
{@f_Occup_Stat} = {?p_Occ_Status} and
{@f_Clean_Status} <> "Clean" and
(isnull({GIP.WINGGI}) or {GIP.WINGGI} = {?p_Wing})

The Data that I need is actually in the {GIP} file here is the sample data C1091 & C1092 are shares so they are showing up now but I loose the # of PPl and the Grp Code both which are stored in the {GIP} file.

Wing Rm # Rm Type Occ Stat Hsk Stat Depart Units Grp Code # of PPl Share
CA C1090 T2 Occupied Dirty 4/24/2008 1.00 N-NH015 1 0.00
CA C1091 T2 Occupied Dirty 4/24/2008 1.00
CA C1092 T2 Occupied Dirty 4/24/2008 1.00
CA C1093 T2 Occupied Dirty 4/24/2008 1.00 N-NHS10 1 0.00
CA C1094 T2 Occupied Dirty 4/26/2008 1.00 C-BMC8 1 0.00


SQL AGAIN

SELECT "LMGEP"."HKCDGE", "RMP"."HSKURM", "RMP"."CSTARM",
"RMP"."DPDTRM", "RMP"."GSTARM", "RMP"."WINGRM",
"RMP"."OTYPRM", "RMP"."RMNORM", "RMP"."RMTPRM",
"GIP"."CONVGI", "GIP"."VIPGI", "GIP"."WINGGI",
"GIP"."NPERGI", "GIP"."NCT1GI", "GIP"."SEQSGI"
FROM {oj ("ATHENA"."LMDTA"."RMP" "RMP"
LEFT OUTER JOIN "ATHENA"."LMDTA"."LMGEP" "LMGEP" ON "RMP"."SEQRM"="LMGEP"."SEQGE")
LEFT OUTER JOIN "ATHENA"."LMDTA"."GIP" "GIP" ON "LMGEP"."SEQGE"="GIP"."SEQGI"}
WHERE NOT ("RMP"."OTYPRM"='H' OR "RMP"."OTYPRM"='O') AND ("GIP"."WINGGI" IS NULL OR ("GIP"."WINGGI"='CA' OR "GIP"."WINGGI"='DL' OR "GIP"."WINGGI"='GA' OR "GIP"."WINGGI"='MG'))

 

[lbass]

Adding "isnull" is not sufficient to correct for selections on a table to the right of a left join, since it still excludes records that are neither null or equal to your criteria.

If you can use "Add command", try changing your SQL to this, which puts the selection criteria in the From clause, to allow the selections on the right hand tables without affecting the joins:

SELECT "LMGEP"."HKCDGE", "RMP"."HSKURM", "RMP"."CSTARM",
"RMP"."DPDTRM", "RMP"."GSTARM", "RMP"."WINGRM",
"RMP"."OTYPRM", "RMP"."RMNORM", "RMP"."RMTPRM",
"GIP"."CONVGI", "GIP"."VIPGI", "GIP"."WINGGI",
"GIP"."NPERGI", "GIP"."NCT1GI", "GIP"."SEQSGI"
FROM {oj ("ATHENA"."LMDTA"."RMP" "RMP"
LEFT OUTER JOIN "ATHENA"."LMDTA"."LMGEP" "LMGEP" ON "RMP"."SEQRM"="LMGEP"."SEQGE" and
NOT ("RMP"."OTYPRM"='H' OR "RMP"."OTYPRM"='O'))
LEFT OUTER JOIN "ATHENA"."LMDTA"."GIP" "GIP" ON "LMGEP"."SEQGE"="GIP"."SEQGI" and
("GIP"."WINGGI" IS NULL OR ("GIP"."WINGGI"='CA' OR "GIP"."WINGGI"='DL' OR "GIP"."WINGGI"='GA' OR "GIP"."WINGGI"='MG')))

You might have to add additional parens to make this work--depends upon the database.

-LB

 

[kcoleman26]

I tried the Add Command and it is asking for a Command Parameter. I have no experiece with this. The database I am connecting to I beleive is ODBC (RDO).

Any other suggestions, I am game.

KC

 

[lbass]

What is the exact message you are getting?

-LB

 

[kcoleman26]

Failed to retrieve data from the database.
Details:4200:[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 -
Token {was not valid.Valid tokensTABLE LATERAL <IDENTIFIER>.
[Database Vendor Code: -104]

After I copy the SQL and hit create it wants me to Give a
Parameter Name
Prompting Text
Vaule Type
Default Value

I don't know what to enter here.

 

[lbass]

What do you mean "After I copy the SQL and hit create"?

You should be starting a new report and going into the Add Command area at the top of your table list and entering the query there.

Also try removing the "{oj" as in:

SELECT "LMGEP"."HKCDGE", "RMP"."HSKURM", "RMP"."CSTARM",
"RMP"."DPDTRM", "RMP"."GSTARM", "RMP"."WINGRM",
"RMP"."OTYPRM", "RMP"."RMNORM", "RMP"."RMTPRM",
"GIP"."CONVGI", "GIP"."VIPGI", "GIP"."WINGGI",
"GIP"."NPERGI", "GIP"."NCT1GI", "GIP"."SEQSGI"

FROM (("ATHENA"."LMDTA"."RMP" "RMP"
LEFT OUTER JOIN "ATHENA"."LMDTA"."LMGEP" "LMGEP" ON "RMP"."SEQRM"="LMGEP"."SEQGE" and
NOT ("RMP"."OTYPRM"='H' OR "RMP"."OTYPRM"='O'))

LEFT OUTER JOIN "ATHENA"."LMDTA"."GIP" "GIP" ON "LMGEP"."SEQGE"="GIP"."SEQGI" and
("GIP"."WINGGI" IS NULL OR ("GIP"."WINGGI"='CA' OR "GIP"."WINGGI"='DL' OR "GIP"."WINGGI"='GA' OR "GIP"."WINGGI"='MG')))

-LB