Qualified fields not included in suggestions for temp tables

MikeyC

I reported this before in another thread, but it appears to have gotten ignored since the thread was started for a different issue:

http://www.red-gate.com/messageboard/vi ... 0505#20505

Given this code:

SELECT
S.status,
S.[high],
S.Low,
S.name
INTO #Reason1
FROM MASTER.dbo.spt_values S

SELECT *
FROM #Reason1 R
WHERE R.<cursor>

Status, High, and Name are the only suggestions, low isn't suggested unless the S. is taken off the front of it. (I thought it was simply just a matter of if it was qualified it wasn't included but this example shows there is more going on than that.)

chris.buckingham

I find that alias selection seems to work OK. Set Auto Insert / Enable alias assigment ON. If you type a statement from Northwind:

SELECT * FROM [Categories] AS C,[Employees] AS E

then place your cursor back into the statement to the *, type c. and you will have

SELECT c.* from [Categories] AS C,[Employees] AS E

you will find only fields referring to Categories are displayed as choices.

MikeyC

I don't want SQL Prompt to generate an alias for tables itself, I supply them myself.

Did you bother to try the code I supplied? Alias selection does normally work, but in the case I supplied it doesn't, at least not for me. The problem is specifically with temp tables that have qualified fields in the creation of the temp table. (Your example doesn't include temp tables, so I would expect it to work.)

chris.buckingham

Apologies but I had already found previously that your temp tables example also worked OK. Could you please refresh the cache by deleting the contents of the folder
C:\Profiles\<user name>\Local Settings\Application Data\Red Gate\SQL Prompt 3
and try again please

MikeyC

Do I really have to delete everything in there, my settings and all, or just the cache files. (I deleted just the cache files and tried again.)

Try this version:

SELECT
S.status,
S.[high],
S2.LOW,
S.NAME,
S.number,
s2.[type]
INTO #Reason1
FROM MASTER.dbo.spt_values S,
MASTER.dbo.spt_values S2

SELECT *
FROM #Reason1 R
WHERE R.<cursor>

At <cursor> I only get high, number, status, and type as suggestions. (Name and Low aren't suggested.)

tanya

Hi ,

This looks like a case sensitivity issue.
NAME and LOW are written in uppercase while in the spt_values table they are written in lowercase. By changing both the columns (NAME and LOW) to lowercase you will see that they do get listed in the candidate list.
Thank you for reporting this issue.

Regards,
Tanya