Subquery issues in 3.5

• 
  No problem Steven, glad you like it overall. Can you do me a favour and post up what your query looked like in full after you started to enter the subquery please? I'm not really sure where you're entering it at the moment. I should then be able to come up with something similar here and figure out what's happening.
  Thanks,
  Bart

  Bart Read July 24, 2007 12:30

  - 0 +
• 
  Sorry - should've thought of posting that...

  SELECT sd.*
       , ( SELECT ind.SiteID FROM dbo.Individual ind WHERE ind.IndividualID = ( SELECT o.IndividualID FROM dbo.Orders o WHERE o.OrderID = sd.OrderID ) ) AS SiteID
       , ios.*
    FROM dbo.ShipDetail sd
         INNER JOIN dbo.OrderDetail od
                 ON od.OrderDetailID = sd.OrderDetailID
         INNER JOIN dbo.ImportOrderShipment ios
                 ON ios.OrderID = od.OrderID
                AND sd.ParcelCode = ios.ParcelCode
                    INNER JOIN dbo.Product p
                            ON p.ProductID = od.ItemID
                           AND p.SKU = ios.ProductID
   WHERE sd.ShipMethodID = dbo.ShipMethod_Unknown()
   ORDER BY 1
  

  PDinCA July 24, 2007 12:32

  - 0 +
• 
  OK thanks for posting that. I've managed to replicate the problem with the AdventureWorks database (my query is at the bottom and has pretty much identical structure to yours).
  
  So, what you ended up with when you started typing your subquery was something like (note that I'm using ^ to indicate the caret position):
  

  SELECT sd.*
       , ( SELECT * FROM dbo.Indivi^
       , ios.*
    FROM dbo.ShipDetail sd
         INNER JOIN dbo.OrderDetail od
                 ON od.OrderDetailID = sd.OrderDetailID
         INNER JOIN dbo.ImportOrderShipment ios
                 ON ios.OrderID = od.OrderID
                AND sd.ParcelCode = ios.ParcelCode
                    INNER JOIN dbo.Product p
                            ON p.ProductID = od.ItemID
                           AND p.SKU = ios.ProductID
   WHERE sd.ShipMethodID = dbo.ShipMethod_Unknown()
   ORDER BY 1
  

  
  The issue is that you've got an unterminated opening parenthesis in the middle of your query. If you're adding SQL at the end of your query this is usually fine, and often works better than adding a closing parenthesis, but in this situation it completely foxes Prompt. It ends up looking like your inner SELECT statement has two FROM clauses which screws it up.
  
  The way to fix it is to do the following:
  

  SELECT sd.*
       , (^)
       , ios.*
    FROM dbo.ShipDetail sd
         INNER JOIN dbo.OrderDetail od
                 ON od.OrderDetailID = sd.OrderDetailID
         INNER JOIN dbo.ImportOrderShipment ios
                 ON ios.OrderID = od.OrderID
                AND sd.ParcelCode = ios.ParcelCode
                    INNER JOIN dbo.Product p
                            ON p.ProductID = od.ItemID
                           AND p.SKU = ios.ProductID
   WHERE sd.ShipMethodID = dbo.ShipMethod_Unknown()
   ORDER BY 1
  

  
  Now you can start entering your subquery:
  

  SELECT sd.*
       , ( SELECT * FROM dbo.Individual ind^ )
       , ios.*
    FROM dbo.ShipDetail sd
         INNER JOIN dbo.OrderDetail od
                 ON od.OrderDetailID = sd.OrderDetailID
         INNER JOIN dbo.ImportOrderShipment ios
                 ON ios.OrderID = od.OrderID
                AND sd.ParcelCode = ios.ParcelCode
                    INNER JOIN dbo.Product p
                            ON p.ProductID = od.ItemID
                           AND p.SKU = ios.ProductID
   WHERE sd.ShipMethodID = dbo.ShipMethod_Unknown()
   ORDER BY 1
  

  
  You should find that this aliases your table and you can use all the aliases defined in your outer query. I managed to replicate this using the following query on the AdventureWorks 2005 database:
  

  USE [AdventureWorks]
  GO
  
  SELECT	p.*
  		, ( SELECT * FROM [Sales].[Customer] AS c )
  		, pd.*
  FROM [Production].[Product] AS p
  	INNER JOIN [Production].[ProductCostHistory] AS pch
  		ON p.[ProductID] = pch.[ProductID]
  	INNER JOIN [Production].[ProductDocument] AS pd
  		ON pch.[ProductID] = pd.[ProductID]
  	INNER JOIN [Production].[ProductInventory] AS pin
  		ON pd.[ProductID] = pin.[ProductID]
  WHERE p.[ProductID] = 10
  ORDER BY 1
  

  
  Hope that helps.
  
  
  Thanks,
  Bart

  Bart Read July 24, 2007 12:49

  - 0 +
• 
  While constructing as you suggested, I managed to get one of those "Authentication Required" connection requests for <<DB>>.ind (my alias) as soon as I hit the dot after the "ind".
  
  I noticed your response to the post at http://www.red-gate.com/messageboard/viewtopic.php?t=5277 was that the problem is fixed. Is it fixed in this scenario too?
  
  If I "Do Not Connect", I get another "Authentication Required" dialog after typing the " FROM dbo." - is that fixed too?
  
  There's still a bug after this - the "ind" alias doesn't appear after the WHERE, but all the other aliases in the query do show up... Did you get the same bugs or are you using 3.5 "Friday" edition?

  PDinCA July 24, 2007 13:00

  - 0 +
• 
  Hmm, no that's not fixed because we've never been able to reproduce it. I didn't want to make any changes without knowing for sure what's going on because I'd really just be guessing, so I might end up not fixing the problem and breaking something else.
  
  OK thanks, I'm not sure what else to suggest for now, but we'll keep looking into this.
  
  Thanks,
  Bart

  Bart Read July 24, 2007 13:02

  - 0 +
• 
  Another incidence...
  
  Coding an INSERT INTO ... SELECT FROM ... where the FROM is a Table Variable and the last column in the SELECT list is a COUNT(*) from a child table, as in:

  DECLARE @PRODUCT TABLE
        ( ProductID                int
        , IsReturnable             bit
        , IsSoldSeparately         bit
        , SKU                      nvarchar(20)
        , LanguageID               int
        , IdNum                    int           IDENTITY(1,1)
        , ProductID_NEW            int DEFAULT 0 NOT NULL
        , AudioFileCount           int
        , PRIMARY KEY ( SKU )
        )
  
  INSERT INTO @PRODUCT
       ( ProductID
       , IsReturnable
       , IsSoldSeparately
       , SKU
       , LanguageID
       , AudioFileCount
       )
  SELECT ProductID
       , IsReturnable
       , IsSoldSeparately
       , SKU
       , LanguageID
       , ISNULL( ( SELECT COUNT(*) FROM dbo.DownloadableItem di WHER) ),0) AS AudioFileCount
    FROM dbo.Product p
   WHERE LEFT(p.SKU,2) = 'DS'
   ORDER BY p.productID
  

  AS you can see the "WHER" is not "WHERE" followed by the criteria. The "WHERE" doesn't show up in the candidates list - in fact the list is empty...

  PDinCA July 24, 2007 14:16

  - 0 +
• 
  Hopefully the last quirks...
  
  Referring to the "Authentication Required" issue, I just copied 2 COMMENT lines into an existing comment bounded by /* .. */, then typed a few words, ending with a period (for end of sentence), and was aked to Authenticate!
  
  Original:

  /* -----------------------------------------------------------------------------
  ** Product
  ** -------------------------------------------------------------------------- */
  

  Final:

  /* -----------------------------------------------------------------------------
  ** Product
  **
  ** Copy to a temp table and assign ID's, massaging the SKU and counting MP3 files. 
  ** -------------------------------------------------------------------------- */
  

  where the additional typing started after the "ID's".
  
  Hope this helps isolate the issue...
  
  Candidate List doesn't pop up when the dot after the "pr" alias before the "ORDER BY" is typed, as in:

  SELECT * FROM dbo.Price pr WHERE ItemID IN ( SELECT ProductID_NEW FROM @PRODUCT ) AND pr. ORDER BY 1
  

  PDinCA July 24, 2007 14:28

  - 0 +

Add comment

Please sign in to leave a comment.