topic Re: SQL Abap brain teaser in Application Development and Automation Discussions

SQL Abap brain teaser

Former Member — Thu, 21 Jun 2012 12:58:26 GMT

Hi all

I have to make a query on following Ztables:

ZTRAVEL - travel data (holding route identifier)

ZROUTE - route data (holding from_resort identifier and to_resort identifier)

ZRESORT - resort data (holding resort name)

What I would like to do is a generic query where constraints are travel data and resort name(in ZRESORT).

The problem is that a TRAVEL can exist without a ROUTE (start resort + arrive resort) ... for example, a user can create a travel saving only arrive and departure date.

If I make a big query binding ZTRAVEL to ZROUTE and ZROUTE twice to ZRESORT (one for start resort and the other for arrive resort name) eventual travel without route assigned will be cut out. If I use an OUTER options in join I cannot set constraints on joined fields table.

I tried also to create a maintainance view mixing all I need ( I read that for this kind of view are closer to what I need) but the problem is that I cannot put tables in join more times (as I need for resort).

Any idea or hint to get out of this trap ?

regards

Gabriele

Re: SQL Abap brain teaser

ThomasZloch — Thu, 21 Jun 2012 13:11:36 GMT

Not elegant, but you could maybe do the LEFT JOIN without the selection condition on resort name and apply it afterwards by deleting from the returned data.

Thomas

Re: SQL Abap brain teaser

Former Member — Thu, 21 Jun 2012 13:59:53 GMT

thanks for your reply.

I was curious to see if there is a solution but I don't think there is a direct way without 'tricks'.

I had the idea you proposed with another one with similar elegance ... put a dummy record in ZRESORT and ZROUTE so that a travel without route, will contain dummy route, which in turn, will holds dummy resort..and so join between table will no cut out rows..

Thank you for your reply anyway

Regards

Gabriele

Re: SQL Abap brain teaser

Jelena_Perfiljeva — Thu, 21 Jun 2012 16:49:57 GMT

It's a really strange case of the database design... If TRAVEL must be linked to RESORT through ROUTE then how could there be records with just RESORT and TRAVEL? Either we're not seeing the whole picture or it shouldn't be possible to create a record with just TRAVEL and RESORT. When designing tables, it should also be taken into consideration what kind of reports will need to be provided.

Anyway, just wanted to point out that in SELECT and in ABAP queries the same table can be added more than once using an alias (JOIN AS...). Although I'd be concerned how this whole query is going to perform...

Re: SQL Abap brain teaser

Former Member — Thu, 21 Jun 2012 18:48:14 GMT

Perhaps I misunderstand the question, but would it be worthwhile to just split the query? Have your join that you know will eliminate any TRAVEL that doesn't have a ROUTE. Then come in behind it and select all TRAVEL where ROUTE is blank? You can select appending corresponding fields of and everyone is happy.

Though this seems like the simple solution and you might be able to give a more clever single-query answer.

Re: SQL Abap brain teaser

Former Member — Fri, 22 Jun 2012 08:24:55 GMT

If TRAVEL must be linked to RESORT through ROUTE then how could there be records with just RESORT and TRAVEL?

Maybe I've not been clear ... you're right : it's impossible to have resort without route.

If I make a big query binding ZTRAVEL to ZROUTE and ZROUTE twice to ZRESORT

I use twice ZRESORT in join ... the problem is not that..