Crystal Reports extracting data from partial fields entries in a database

[Gary Hnat]

Being in the Travel Industry I am always dealing with fields and tables in a database associated with flight bookings. I have a issue that has stumped me for a long time now and was looking for some guidance.

In one table (BOOKING) there is a field that contains the flight routing in linear "code" fashion like ... SEADENATLMIA, which stands for Seattle - Denver - Atlanta - Miami
In another separate table (CITY) there is a listing of all worldwide City Codes in one field and the corresponding City Name in another.
SEA, Seattle
DEN, Denver
etc

For Crystal Reports ... I need to extract the linear SEADENATLMIA info from the BOOKING table and show "Seattle Denver Atlanta Miami" as one field on a Crystal report (space delimiter)

I can achieve my end goal by doing this in 2 steps in MS Access but I really would like to do this on the fly in one step in Crystal (if possible)

In MS Access
1. Create a query to extract the individual 3 letter city codes from SEADENATLMIA ... by using the LEFT and MID functions
field 1 CityCode1: LEFT([BOOKING].[ROUTING],3)
field 2 CityCode2: MID ([BOOKING].[ROUTING],4,3)
field 3 CityCode3: MID ([BOOKING].[ROUTING],7,3)
etc

2. I create a second query based on the first query from above ... link the above 3 letter [CityCode1-3] fields individually to the [CITY] table's 3 letter code to get the City Name in the form of ... CityName1+" "+[CityName2}+" "+[CityName3] etc

Is it possible in Crystal to do some sort of linkage for each 3 letter city code found in the linear field in SQL and link them to the City table containing the City Names and subsequently giving me ... Seattle Denver Atlanta Miami?

Hopefully I am explaining myself clearly.

Any assistance would be greatly appreciated

Gary

 

[lbass]

Can you please show a sample of your current SQL query and/or identify the type of database?

-LB

 

[Gary Hnat]

Thanks for the quick reply!

The database is "Interbase/Embarcadero" (not MS SQL)

All I can give you is SQL code from a similar type of report that utilizes the BOOKING and CITY tables

I guess what I am looking for is a way in Crystal SQL to link up characters 1-3 from B.ITINERARY and take that 3 letter city code to link up to the 3 letter city code found in the CITY table to get the city name and at the same time do it for characters 4-6, 7-9, 10-12 etc to get the full city name (not code) routing

Makes sense?

Gary

SELECT
B.BOOKINGNO,
B.DEPARTDATE,
B.FARECODE,
B.RETURNDATE,
B.TRAVELTYPE_LINKNO,
B.VALIDAL,
B.ORIGTICKETNO,
B.STARTINGTICKETNO,
B.FLOWNCARRIER,
B.ITINERARY,
B.INTER_LINKCODE,
B.DEPARTDATE-I.PNRCREATIONDATE as APUR,
B.CALCNUMOFDAYS,
B.COMMAMT/100 as TOTAL_COM,
B.CONFIRMNO,
B.HIGHFARE/100 as HIGH_FARE,
B.LOWFARE/100 as LOW_FARE,
B.PASSENGERNAMEUPPER,
B.TAX3AMT/100 as QST,
B.TOTALFARE/100 as TOTAL_FARE,
B.TOURCODE,
B.CLIENTGSTAMT/100 as GST,
B.CALCBASEFARE/100 as BASE_FARE,
B.CALCTOTALTAXAMT/100 as TOTAL_TAX,
I.INVOICENO,
I.ISSUEDATE,
I.RECORDLOCATOR,
I.INVOICENUMBER,
I.INVOICEGROUP,
I.PNRCREATIONDATE,
S.SEGMENTNO,
S.INDEXNO,
S.AIRLINE,
S.ARRIVECITY as ARR_CITY_CODE,
S.ARRIVEDATETIME,
S.DEPARTCITY as DEP_CITY_CODE,
S.DEPARTDATETIME,
S.FLIGHTNO,
S.FAREBASIS,
S.FARE/100 as LEGFARE,
S.CLASSOFSVC,
S.TICKETDESIG,
S.MILEAGE,
CD.CITYNAME as DEP_CITY_NAME,
CA.CITYNAME as ARR_CITY_NAME,
P_C.NAME as CLIENT_NAME,
P_C.INTERFACEID as CLIENT_DK,
P_V.NAME as VENDOR_NAME,
P_V.INTERFACEID as VENDOR_ID,
TT.TRAVELTYPE
FROM INVOICE I
INNER JOIN BOOKING B on I.INVOICENO = B.INVOICE_LINKNO
INNER JOIN SEGMENTS S ON B.BOOKINGNO = S.BOOKING_LINKNO
INNER JOIN PROFILE P_C ON I.CLIENT_LINKNO = P_C.PROFILENO
INNER JOIN PROFILE P_V ON B.VENDOR_LINKNO = P_V.PROFILENO
LEFT OUTER JOIN TRAVELTYPE TT ON B.TRAVELTYPE_LINKNO = TT.TRAVELTYPENO
LEFT OUTER JOIN CITY CD ON S.DEPARTCITY = CD.CITY And CD.CodeType = 'A'
LEFT OUTER JOIN CITY CA ON S.ARRIVECITY = CA.CITY And CA.CodeType = 'A'
WHERE
AND (I.ISSUEDATE >= cast (F_Month (CURRENT_TIMESTAMP) || '/01/' || F_Year (CURRENT_TIMESTAMP) as timestamp) - 1
- EXTRACT(DAY FROM cast (F_Month (CURRENT_TIMESTAMP) || '/01/' || F_Year (CURRENT_TIMESTAMP) as timestamp) - 1) + 1
AND I.ISSUEDATE < cast (F_Month (CURRENT_TIMESTAMP) || '/01/' || F_Year (CURRENT_TIMESTAMP) as timestamp))
AND P_C.INTERFACEID = '0170150211'

 

[lbass]

To create a formula that will decode your city codes, first open a separate report using the City table. The purpose of this is to easily generate text that can be copied into a formula. You are essentially recreating the code table in text form.

Insert a text box in the detail section and enter the following, typing words/punctuation and substituting your actual field table and field names):

case '{City.Code}' : '{City.CityName}'

Run the report and then export the results to Notepad. Use the replace function to find " (double quotes generated by NotePad) and replace with nothing (enter nothing).

Then copy the resulting text and paste it into a new formula in your master report. Then add the necessary elements so that the formula now looks like this:

stringvar x := {table.tripcode}; //e.g. 'DENLAXSEA'
numbervar i;
numbervar j := len(x)/3;
stringvar y;

for i := 0 to j step 3 do(
y := y +
(
select(mid(x,i + 3, 3))
case 'DEN' : 'Denver'
case 'LAX' : 'Los Angeles'
case 'SEA' : 'Seattle' //etc.
));
if len>1 then
left(y,len-1)

-LB

 

[Gary Hnat]

Although your solution appears to work on paper its a bit cumbersome .. and it's simply not feasible due to the fact that numerous users in numerous locations will be running this same Crystal report over the network ... that I am looking to have a single field appear as Seattle Denver Atlanta Miami instead of SEADENATLMIA.

Please note that there could be upwards of 20 city codes all strung together or the field could be blank in some instances and the city code table has thousands of entries for every city in the world.

The common sense/logical solution I was contemplating was to use the LEFT and MID function in the SQL SELECT clause and then link City1 field to the CITY table in the FROM clause to get the city name ... something like ...

SELECT
b.itinerary
left(b.itinerary),3) as City1,
mid (b.itinerary,4,3) as City2
mid (b.itinerary,7,3) as City3
---
C1.CityName,
C2,CityName,
C3.CityName

FROM
inner join CITY C1 on City1 = C1.CITYCODE
inner join CITY C2 on City2 = C2.CITYCODE
etc
...
WHERE
...

And then in a Crystal formula .... [CityName1]+" "+[[CityName2]+" "+[CityName3] etc

HOWEVER ... I can't get the left and mid function to work at all in the SQL SELECT clause in Interbase

Thoughts

 

[lbass]

The formula DOES return a single field "Seattle Denver Los Angeles", for example, per row, and it automates the breakout, so you don't have to link one field to multiple copies of your table or know the number of cities per trip. Once you have the formula, you don't need to use the text box approach. You would only need to update it when the codes change or a new airport is added. I'm not sure whether the formula would fail with thousands of entries, but I can't test that here.

If you want to continue with your approach, you can see what functions are available for your database by looking in the SQL expression editor--if there is not a Left or Mid function, you should try substring() or substr(). In some databases, if you select those functions, it will enter {fn ()}, and in some you can use them directly. For left(), you would use substring(field,1,3), i.e., substring(field,startposition, length). Remember if you are using a SQL expression, you need to enclose select statements in parens.

(
Select ...
)

Or are you using a command? The problem with your approach is you are hardcoding the number of fields(cities) per trip.

-LB