|
The technical forum of the NBN Trust for use by the Network. It includes discussions and announcements on the NBN Atlas, Recorder 6 and Indicia |
You are not logged in. Please login or register.
Forum → Reporting & XML Reports → Sql example in help file
My question is with regards to the help file on reporting. There is some example SQL which is for temporary tables. The specific example is creating a temp. table for locations. the part which is confusing me a bit is that the temp table #Locations is created so that the location_key is the primary key. So it cannot be null. Yet the SQL select after the left join is selecting location_key being equal to null. Any thoughts please?
Thanks
Gary
Hi Gary,
Can you clarify where you are looking - which help file on reporting - I don't think you are talking about the Help files in Recorder itself.....is this a file which helps you write SQL reports?
Cheers,
Lynn
Hello Lynn,
I saw the code in a download that is available on the NBN forum which gives details about XML reports and SQL. I did not understand the last part of the SQL from LEFT JOIN. I was just looking for an explanation as to how that works? The code below is what I was looking at from the file.
Cheers for now
Gary
CREATE TABLE #Locations (Location_Key CHAR(16) COLLATE SQL_Latin1_General_CP1_CI_AS PRIMARY KEY,
Parent_Key CHAR(16) COLLATE Database_Default)
INSERT INTO #Locations (LOcation_Key,Parent_Key)
SELECT L.LOCATION_KEY, L.LOCATION_KEY
FROM
LOCATION L
WHERE
<Condition field= "L.Location_key" operator="equal" type="Location" name="Select Locations" entrycount="-1" />
WHILE 1=1 BEGIN
INSERT INTO #Locations
SELECT L.Location_Key, Tinc.Parent_Key
FROM Location L
INNER JOIN #Locations Tinc on L.Parent_Key=Tinc.Location_Key
LEFT JOIN #Locations Texc ON Texc.Location_Key=L.Location_Key
WHERE Texc.Location_Key IS NULL
IF @@ROWCOUNT=0
BREAK
END
Hi Gary,
On consultation with others here is an explanation of what the code is doing:
It just expands out the Location to get all Location including those in the Hierarchy below it. It is working downwards through the hierarchy.
Location keys (from the XML parameter and can be many ) go into the temporary table in both the Location and Parent field.
Main Loop
INNER JOIN #Locations Tinc on L.Parent_Key=Tinc.Location_Key
This line finds the children of the Locations in the temporary table (ie it is finding the locations in the database which have as their parents Locations in the temporary table) The parent key is picked up from the parent of the temporary table, so in effect the original location key is put in the parent field for all its children.
LEFT JOIN #Locations Texc ON Texc.Location_Key=L.Location_Key WHERE Texc.Location_Key IS NULL
This Line ensure that we are only processing Location not already done by checking that they are not in the table
IF @@ROWCOUNT=0
BREAK
The Locations found are added to the table and the loop goes round until no new Locations are found
The final temporary table has the originally specified Location keys plus
all the children of these Locations. The parent key column contains the
originally 'seed' location key, not the true parent of the location. This
allows some flexibility in using the temporary table. For example you can
exclude the 'seed' locations by ignoring anything where the Location and the
parent are the same.
Hope this helps,
Lynn
Hello Lynn,
Thanks for your reply and yes, it has helped. As I understand it the temp table would get populated with the 'child' entries if a parent was one which was initially selected into it. Otherwise, if it was a child in the original temp table then no additional records would be added.
I can now see what the left join clause is doing. If that was not there then there would be an error as it would be possible that a duplicate entry was trying to be added.
I have been reading about 'self-joins' within tables and I guess this is an example of this?
Cheers for now
Gary
Forum → Reporting & XML Reports → Sql example in help file
Powered by PunBB 1.4.6, supported by Informer Technologies, Inc.