Options

Very minor bug in "expand wildcard"

eklein@ahd.comeklein@ahd.com Posts: 17 Bronze 2
in SQL Prompt
I know this is a minor issue but it's so odd I thought I would pass it along.   Looking at the following code 

SELECT *
FROM
(
    SELECT rpt_rec_num,
           LEFT(cell_row, 3) AS cell_row,
           COUNT(*) AS occurs
  --  FROM [CRD_2010_FMT_$(HCycleN)].dbo.cell_data
    FROM [CRD_2010_FMT_210630].dbo.cell_data
    WHERE sheet = 's201'
          AND cell_row
          BETWEEN '01500' AND '01699'
          AND cell_col = '00100'
    GROUP BY rpt_rec_num,
             LEFT(cell_row, 3)
) dt;


this code will execute correctly with either of the two from statements.   However if I use the "expand wildcard" option on the above version I get what I expect  
"SELECT dt.RPT_REC_NUM,
       dt.cell_row,
       dt.occurs "   ....


However I use the equivalent commented out version of the from statement I get the same result table, but the "expand wildcard" options results in the following 

"SELECT dt.cell_row,
       dt.occurs "    ....


I noticed long ago that if I use a environmental variable in the fully qualified name, then SQL prompt doesn't know what the column names are for suggestions and I have accepted that as a limitation of the product.   But in this case loosing one of the columns makes no sense at all.  

I use the environmental variable as this db is released by the US government every 3 months  and I have 11 years of history.  Nine time out of 10 I want the most current which the environmental variable conveniently selects. All my code gets updated each time I get a new data base as I update the environmental variable at that time.   During development I usually just use a specific reference to the current one and SQL prompt has no problems.   

If fixing this is a big deal, don't worry about it, I love your product, but I thought I might be an indicator of a more important issue that you would want to know about.   

Thanks 


Tagged:
· Share on Twitter

Best Answer

Answers

  • Options
    eklein@ahd.comeklein@ahd.com Posts: 17 Bronze 2
    It was in SSMS only, but that is the only environment that I normally work in 
    · Share on Twitter
  • Options
    eklein@ahd.comeklein@ahd.com Posts: 17 Bronze 2
    It was in SSMS only, but that is the only environment that I normally work in 
    If it makes a differance here is the current release levels 

    SQL Server Management Studio 15.0.18338.0
    SQL Server Management Objects (SMO) 16.100.41011.9
    Microsoft Analysis Services Client Tools 15.0.19205.0
    Microsoft Data Access Components (MDAC) 10.0.19041.1
    Microsoft MSXML 3.0 5.0 6.0 
    Microsoft .NET Framework 4.0.30319.42000
    Operating System 10.0.19042
    · Share on Twitter
  • Options
    ATurnerATurner Posts: 190 Silver 2
    Hi eklein

    Apologies for the late response, but i cannot replicate this on the latest version are you still getting this issue? 
    · Share on Twitter
  • Options
    eklein@ahd.comeklein@ahd.com Posts: 17 Bronze 2
    It continues to fail.   My curent configuration is 

    SQL Server Management Studio 15.0.18338.0
    SQL Server Management Objects (SMO) 16.100.41011.9
    Microsoft Analysis Services Client Tools 15.0.19205.0
    Microsoft Data Access Components (MDAC) 10.0.19041.1
    Microsoft MSXML 3.0 5.0 6.0 
    Microsoft .NET Framework 4.0.30319.42000
    Operating System 10.0.19044

    Sql prompt is at 10.12.0.27933  (most current as of 10 minutes ago) 

    Table def is 

    USE [CRD_2010_FMT_220331]
    GO

    /****** Object:  Table [dbo].[cell_data]    Script Date: 6/6/2022 12:35:27 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[cell_data](
    [fld_name] [char](15) NOT NULL,
    [cell_row] [char](5) NOT NULL,
    [cell_col] [char](5) NOT NULL,
    [RPT_REC_NUM] [int] NOT NULL,
    [ITM_VAL_NUM] [bigint] NOT NULL,
    [ITM_VAL_NUMF] [float] NOT NULL,
    [ITM_VAL_ALPHA] [varchar](128) NOT NULL,
    [sheet] [char](4) NOT NULL,
    [suppress] [char](1) NOT NULL,
    [component] [char](4) NOT NULL,
    [TYPE] [varchar](30) NOT NULL,
    [Data_id] [int] NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[cell_data] ADD  DEFAULT ((0)) FOR [ITM_VAL_NUM]
    GO

    ALTER TABLE [dbo].[cell_data] ADD  DEFAULT ((0)) FOR [ITM_VAL_NUMF]
    GO

    ALTER TABLE [dbo].[cell_data] ADD  DEFAULT ('') FOR [ITM_VAL_ALPHA]
    GO

    ALTER TABLE [dbo].[cell_data] ADD  DEFAULT (' ') FOR [suppress]
    GO

    ALTER TABLE [dbo].[cell_data] ADD  DEFAULT (' ') FOR [component]
    GO

    with a cluster index and 3 2ndary indexes 

    Is there anything else I can do to help.  The base table is quite large(244M rows), but I could create a subset for debugging purposes.  The data is publicly availiable, so there are no security issues

    Or if you want send me a zoom or webex meeting invite and I could share my screen and show you what I am seeing.  I am in US in Eastern Time zone.   I won't be in the offices tomorrow until PM but we can work out a schedule.  If I recall correctly you are +5 hours from me so mornings would probably be best.   

    Again this is not a prioriity problem for me, but as a developer myself, I know strange errors sometimes show you things you didn't know and can be educational 




    · Share on Twitter
  • Options
    ATurnerATurner Posts: 190 Silver 2
    edited June 13, 2022 7:44PM
    Hi eklein

    If you could I am unable to replicate the issue here currently. 
    · Share on Twitter
Sign In or Register to comment.