The EntitySpaces Community

Share and learn about the EntitySpaces Architecture.
Welcome to The EntitySpaces Community Sign in | Join | Help
in
Home Forums Photos
The EntitySpaces Community » EntitySpaces API » Dynamic Query API » Re: SubQuery error - correlation name error

SubQuery error - correlation name error

Last post 08-15-2008, 8:00 AM by Mike.Griffin. 5 replies.
Sort Posts: Previous Next

  •  08-14-2008, 5:51 PM 10751

    SubQuery error - correlation name error

    Hi,

    I'm trying to get a grasp on the new sub query syntax.  When I attempt to run the query I get the error that the correlation name is specified multiple times in the from clause.  What am I doing wrong?

     Thanks

    Lee

    Code:
     Dim thisdate As Date
            thisdate = "8/1/2008"
            Dim currentMonth As New BusinessObjects.ViewWoodInventoryQuery("wi")
            Dim ytd As New BusinessObjects.ViewYTDWoodInventoryQuery("ytdwi")

            ytd.Select(ytd.WoodDesc, ytd.BoardFeet, currentMonth.Select(currentMonth.BoardFeet.Sum).Where(currentMonth.TransactionDate.Equal(thisdate)).As("BF"))
            ytd.Where(ytd.MonthName.Equal("June"))

            ytd.InnerJoin(ytd).On(ytd.GradeDescription.Equal(currentMonth.GradeDescription))

            Dim ytdColl As New BusinessObjects.ViewYTDWoodInventoryCollection
            ytdColl.Load(ytd)


    


									
    
									
    
								
    
								
    
							    		
						
    
						
    
							
								
    
									
								
    								
								
							    		
						
    
					
    
				
    
				
    • 
			
				
  • 
				
    
					
    
						
    
							
    
								 08-14-2008, 8:42 PM
								
									10752 in reply to 10751
										
								
							
    
						
    
					
    
					
    
						
    
							
								
    
								
								
    
							    		
							
								
    
									
    
										
										Re: SubQuery error - correlation name error
									
    
									
									
    
										
									
    

									
    
										 
										
									
    

									
    
										
										
										
										
										
										
										
									
    
								
    

								
    
								
    
									
    
									
    
										
    I think this:
    

    

    Code:
    ytd.InnerJoin(ytd)
    

    should be:
    

    

    Code:
    ytd.InnerJoin(currentMonth)
    


    David Neal Parsons
    www.entityspaces.net
									
    
									
    
								
    
								
    
							    		
						
    
						
    
							
								
    
									
								
    								
								
							    		
						
    
					
    
				
    
				
    • 
			
				
  • 
				
    
					
    
						
    
							
    
								 08-14-2008, 9:52 PM
								
									10753 in reply to 10752
										
								
							
    
						
    
					
    
					
    
						
    
							
								
    
								
								
    
							    		
							
								
    
									
    
										
										Re: SubQuery error - correlation name error
									
    
									
									
    
										
									
    

									
    
										 
										
									
    

									
    
										
										
										
										
										
										
										
									
    
								
    

								
    
								
    
									
    
									
    
										
    David,
    

    I tried what you said and it worked.  However in the meantime I changed from using views to using the tables directly (did not know if the view was causing the problem).  So my code now looks like the following (I'm streamlining it to see if I can get to understand what's going on)
    

     
    

    Code:
    

    Dim woodInv As New BusinessObjects.TblWoodInventoryQuery("wi")
            Dim ytdInv As New BusinessObjects.TblWoodInventoryHistoryQuery("ytd")
           
            woodInv.Select(woodInv.Specie, woodInv.InventoryType, ytdInv.Select(ytdInv.Bdft.As("BF")))

            woodInv.InnerJoin(ytdInv).On(woodInv.Specie.Equal(ytdInv.Specie))

            Dim woodInvColl As New BusinessObjects.TblWoodInventoryCollection
            woodInvColl.Load(woodInv)
    

     When I run the above code snippet, I get the following SQL statement. 
    

    Code:
    

    SELECT wi.[specie] AS 'specie',wi.[inventoryType] AS 'inventoryType',ytd.*  FROM [tblWoodInventory] wi 
    INNER JOIN [tblWoodInventoryHistory] ytd ON wi.[specie] = ytd.[specie]
    

     


    It seems that the SubQuery is selecting all of my columns rather than just the one I am asking for.
    

    Any thoughts?
    

    Thanks again
    

    Lee  
    


									
    
									
    
								
    
								
    
							    		
						
    
						
    
							
								
    
									
								
    								
								
							    		
						
    
					
    
				
    
				
    • 
			
				
  • 
				
    
					
    
						
    
							
    
								 08-15-2008, 4:45 AM
								
									10755 in reply to 10753
										
								
							
    
						
    
					
    
					
    
						
    
							
								
    
								
								
    
							    		
							
								
    
									
    
										
										Re: SubQuery error - correlation name error
									
    
									
									
    
										
									
    

									
    
										 
										
									
    

									
    
										
										
										
										
										
										
										
									
    
								
    

								
    
								
    
									
    
									
    
										
    Try this:
     
    Code:
    Dim woodInv As New BusinessObjects.TblWoodInventoryQuery("wi")
    Dim ytdInv As New BusinessObjects.TblWoodInventoryHistoryQuery("ytd")
           
    woodInv.Select(woodInv.Specie, woodInv.InventoryType, ytdInv.Bdft.As("BF"))
    woodInv.InnerJoin(ytdInv).On(woodInv.Specie = ytdInv.Specie)

    Dim woodInvColl As New BusinessObjects.TblWoodInventoryCollection
    woodInvColl.Load(woodInv)
     
    Notice how your Bdft is on the select, you do not call ytdInv.Select. Also, notice that I use the "=" in the On() statement. You can use the natural language operators like = > < and so on ...
    


    EntitySpaces | Twitter | BLOG | Please honor our Software License
    
									
    
									
    
								
    
								
    
							    		
						
    
						
    
							
								
    
									
								
    								
								
							    		
						
    
					
    
				
    
				
    • 
			
				
  • 
				
    
					
    
						
    
							
    
								 08-15-2008, 7:24 AM
								
									10757 in reply to 10755
										
								
							
    
						
    
					
    
					
    
						
    
							
								
    
								
								
    
							    		
							
								
    
									
    
										
										Re: SubQuery error - correlation name error
									
    
									
									
    
										
									
    

									
    
										 
										
									
    

									
    
										
										
										
										
										
										
										
									
    
								
    

								
    
								
    
									
    
									
    
										
    Mike's code is going to be more efficient. Once you have the inner join, you can directly select columns from either table without an additional sub-select. As to why your code did not produce the results you expected, the alias needs to be on the entire sub-select, not just on the column. I think it is just a mis-placed closing paren.
    

    This:
    

    

    Code:
    woodInv.Select(woodInv.Specie, woodInv.InventoryType, ytdInv.Select(ytdInv.Bdft.As("BF")))
    

    should be:
    

    

    Code:
    woodInv.Select(woodInv.Specie, woodInv.InventoryType, ytdInv.Select(ytdInv.Bdft).As("BF"))
    


    David Neal Parsons
    www.entityspaces.net
									
    
									
    
								
    
								
    
							    		
						
    
						
    
							
								
    
									
								
    								
								
							    		
						
    
					
    
				
    
				
    • 
			
				
  • 
				
    
					
    
						
    
							
    
								 08-15-2008, 8:00 AM
								
									10760 in reply to 10757
										
								
							
    
						
    
					
    
					
    
						
    
							
								
    
								
								
    
							    		
							
								
    
									
    
										
										Re: SubQuery error - correlation name error
									
    
									
									
    
										
									
    

									
    
										 
										
									
    

									
    
										
										
										
										
										
										
										
									
    
								
    

								
    
								
    
									
    
									
    
										David, that will not work, he cannot call Select() there, it returns the "Query" which means "*".  I could be wrong, even so I do not like that syntax, it was never designed to work that way.


    EntitySpaces | Twitter | BLOG | Please honor our Software License
    
									
    
									
    
								
    
								
    
							    		
						
    
						
    
							
								
    
									
								
    								
								
							    		
						
    
					
    
				
    
				
    • 
			
				

			

		

		

			

				
					

						View as RSS news feed in XML
					

						
			

		

	
		
	




								

								
								
									
								
							

						

					

				
					

						
Copyright © 2005 - 2008, EntitySpaces, LLC