What are the challenges you face when working across database platforms? Take the survey
Options

Qualified fields not included in suggestions for temp tables

MikeyCMikeyC Posts: 249 Bronze 3
edited May 14, 2008 6:41AM in SQL Prompt Previous Versions
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.)

Comments

  • Options
    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.
    Chris Buckingham
    Red-Gate support
  • Options
    MikeyCMikeyC Posts: 249 Bronze 3
    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.)
  • Options
    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
    Chris Buckingham
    Red-Gate support
  • Options
    MikeyCMikeyC Posts: 249 Bronze 3
    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.)
  • Options
    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
Sign In or Register to comment.