INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Rekursive SQL Query

Rekursive SQL Query

(OP)
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

RE: Rekursive SQL Query

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:

CODE

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

RE: Rekursive SQL Query

(OP)
This works perfectly! Thanks very much!

RE: Rekursive SQL Query

(OP)
Hello,

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

I tried to write the above mentioned query here:

CODE

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:

CODE

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!


RE: Rekursive SQL Query

The line

CODE

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:

CODE

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

RE: Rekursive SQL Query

(OP)
@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

RE: Rekursive SQL Query

CODE

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


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

        INNER JOIN 

------- 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

           ON t2.STOPDATE = ADDRESS_MAX.STOPDATETEMP

        ) 
------- sub-table 1 end 

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close