Rekursive SQL Query

[galaxy0815]

Hello,

I have an issue with a query I need to create. The following in my Basic table with the name ClientAddress:


Clientnr----------AddressLine---------Country---------Stopdate
47-----------------Mainstrett3---------GERMANY---------30.Nov.2010
47-----------------Obamastreet3--------USA-------------01.Feb.2011
47-----------------Boulevard3----------USA-------------01.Oct.2011
47-----------------Rue7----------------FRANCE----------31.Dec.9999
588----------------Neustrasse3---------GERMANY---------31.Dec.9999
899----------------Uferstrasse4--------GERMANY---------25.Mar.2012
899----------------Boulevard78---------UK--------------31.Dec.9999
.....

So it stores historical addresses (the ones that have a Stopdate<>31.Dec.9999) and current valid addresses (Stopdate=31.Dec.9999) for quite a number of clients. In total there are around 20,000 lines in the table.


I need to write now an SQL statement (No stored procedure etc as my user does not have the rights for this) that reports me any country change in the following way (Based on the above example) (in case there is a new address record, but within the same country, this can be ignored):

Clientnr---------------Old_Country-------------NEW_Country---------Changedate
47---------------------GERMANY-----------------USA-----------------30.Nov.2010
47---------------------USA---------------------FRANCE--------------01.Oct.2011
899--------------------GERMANY-----------------UK------------------25.Mar.2012
...

Does anyone know who to do this? I tried to solve it via recursive SQL(seems for me to be the only way), read now 2 hours about it, but to be honest I do not know how to use this powerful functionality as I never worked with it till now.


Can anyone help me with this issue here, please? Thanks in advance

 

[MarcLodge]

Hi Galaxy,
I've no idea if this will work as I've obviously not got your tables so am unable to test, but something like this might lead you to the answer:

SELECT t1.Clientnr, t1.Country as Old_Country, t2.Country as NEW_Country, t1.Stopdate as Changedate
from ClientAddress t1,
     ClientAddress t2
where t1.Clientnr = T2.Clientnr
and   t1.Stopdate <> '9999-12-31'
and   t2.Stopdate = (SELECT MIN(t3.Stopdate)
                     from ClientAddress t3
                     where t3.Clientnr = t1.Clientnr
                     and   t3.Stopdate > t1.Stopdate)

Hope this helps.
Marc

 

[galaxy0815]

This works perfectly! Thanks very much!

 

[galaxy0815]

Hello,

Sorry to come back on this again, but I have an additional issue:

I tried to write the above mentioned query here:

SELECT t1.Clientnr, t1.Country as Old_Country, t2.Country as NEW_Country, t1.Stopdate as Changedate
from ClientAddress t1,
     ClientAddress t2
where t1.Clientnr = T2.Clientnr
and   t1.Stopdate <> '9999-12-31'
and   t2.Stopdate = (SELECT MIN(t3.Stopdate)
                     from ClientAddress t3
                     where t3.Clientnr = t1.Clientnr
                     and   t3.Stopdate > t1.Stopdate)

into the "new SQL writing Standard" as I have to join some additional other tables via left join, right join etc, and I loose the overview if I use the above coding. Therefore I changed it to the following:

SELECT t1.Clientnr
	,t1.Country AS Old_Country
	,t2.Country AS NEW_Country
	,t1.Stopdate AS Changedate
FROM (
	(
		ClientAddress t1 INNER JOIN ClientAddress t2 ON t1.Clientnr = T2.Clientnr t1.Stopdate <> '9999-12-31'
		) INNER JOIN (
		SELECT MIN(t3.STOPDATE) AS STOPDATETEMP
		FROM ClientAddress t3
		WHERE t3.Clientnr = t1.Clientnr
			AND t3.Stopdate > t1.Stopdate
		) AS ADDRESS_MAX ON t2.STOPDATE = ADDRESS_MAX.STOPDATETEMP
	)

But when I try to execute it, I get the following error message "t1.Stopdate is an undefined name". This seems to be related to the t1.Stopdate field I used in the Subquery of the joins.

Does anyone know how to solve this? Thanks in advance!

 

[MarcLodge]

The line

ClientAddress t1 INNER JOIN ClientAddress t2 ON t1.Clientnr = T2.Clientnr t1.Stopdate <> '9999-12-31'

seems wrong to me. You're missing and AND between T2.Clientnr and t1.Stopdate. I think it should read:

ClientAddress t1 INNER JOIN ClientAddress t2 ON t1.Clientnr = T2.Clientnr AND t1.Stopdate <> '9999-12-31'

 

[galaxy0815]

@MarcLodge,
Your point is correct, but this issue just occured when I copied the statement here. Seems that it was missed when I did copy and paste. But the statement I try to execute (which contains the AND) still fails with above error message which I do not understand.

Can anyone please help?

Thanks,
Martin

 

[fredericofonseca]

outer select list fields reference alias that are not part of the current select
SELECT [highlight #EF2929]t1.Clientnr
      ,t1.Country AS Old_Country
      ,t2.Country AS NEW_Country
      ,t1.Stopdate AS Changedate[/highlight]
FROM 
[highlight #FCE94F]------- sub-table 1 start[/highlight]
 ( [highlight #EF2929]select list missing[/highlight]

[highlight #8AE234]
------- sub-table 2 start
        ( [highlight #EF2929]select list missing[/highlight]
          [highlight #EF2929]from clause missing[/highlight]
                ClientAddress t1 
           INNER JOIN ClientAddress t2 
              ON t1.Clientnr = T2.Clientnr 
             AND t1.Stopdate <> '9999-12-31'[highlight #EF2929][/highlight]
        )  [highlight #EF2929]table alias missing[/highlight]
------- sub-table 2 end[/highlight]

        INNER JOIN 

[highlight #C4A000]------- sub-table 3 start
                   ( SELECT MIN(t3.STOPDATE) AS STOPDATETEMP
                       FROM ClientAddress t3
                      WHERE t3.Clientnr = t1.Clientnr
                        AND t3.Stopdate > t1.Stopdate
                   ) AS ADDRESS_MAX
------- sub-table 3 end[/highlight]

           ON [highlight #EF2929]t2.STOPDATE[/highlight] = ADDRESS_MAX.STOPDATETEMP

        ) 
[highlight #FCE94F]------- sub-table 1 end[/highlight]

field references on one select (and associated where conditions) can not reference a field by its alias.field denomination that is on a sub-select




Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886