Subquery issues in 3.5

PDinCA

I had a working query:

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

but need to get the SiteID for each Order, so I started to type:

     , ( SELECT ind.SiteID FROM dbo.Individual

expecting my predefined alias of "ind" to appear after SP3.5 auto-filled the "Individual" table name - no alias. That's issue #1.

I carried on typing the alias myself, followed by the start of my WHERE clause that uses an "ind" column. After the space following the "WHERE", as in:

, ( SELECT ind.SiteID FROM dbo.Individual ind WHERE 

the candidate list includes all the aliases from the main query, but ignores the "ind" alias I just typed - that's issue #2.

As the alias is ignored, the candidate list after typing "ind." doesn't contain any columns from the Individual table - issue #2 "consequence"?

As my line of code ends with another subquery, it's no surprise that the alias is also missing after I typed:

, ( SELECT ind.SiteID FROM dbo.Individual ind WHERE ind.IndividualID = ( SELECT o.IndividualID FROM dbo.Orders

Wishing to name the resulting column, I completed the ( SELECT.. ) and typed my " AS " whereupon a candidate list of table names is presented - issue #3. I wanted:

, ( SELECT ind.SiteID FROM dbo.Individual ind WHERE ind.IndividualID = ( SELECT o.IndividualID FROM dbo.Orders o WHERE o.OrderID = sd.OrderID ) ) AS SiteID

"AS" should be sensitive to its context - SELECT or FROM and show column names for SELECT "AS"s. It's debateable whether there's any meaningful candidate for assigning an alias to a subquery when using "AS" in the FROM...

Aside from this, there are MANY improvements, especially in dealing with large scripts, that work great - THANKS!

Bart Read

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

PDinCA

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

Bart Read

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

PDinCA

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?

Bart Read

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

PDinCA

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

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