Expand Wildcard Suggestion

hafnera

I run into a few instances where I do the 'Expand Wildcards' but none of the columns are listed. When I hover over the table alias it says 'Unresolved Table Alias'. I have tried doing a 'Refresh Suggestions' but it still doesn't work. The query isn't that long (65 lines) and it is only selecting from a single table. There are some calculated columns and case statements after the '*', but other than that it is a pretty straight forward SELECT. I am not sure what is causing the table alias to not resolve correctly. Here is the query I have and the 'adj.*' is what I am trying to expand. I am running SQL Prompt 7.2.0.260 but the same problem existed in 7.1.0.315.

SELECT  'AD-HOC  ' AS Adj_Type ,
        'AD HOC ADJUSTMENT    ' AS Adj_Desc ,
        adj.* ,
        ( CASE WHEN adj.comment_line LIKE '%FQHC%'
               THEN '01 - Reconcile Adj'
               ELSE ( CASE WHEN adj.comment_line LIKE '%claim%'
                           THEN '02 - Ad Hoc Adjustment'
                           ELSE '12 - Other Ad Hoc Adjustment'
                      END )
          END ) AS Service_Category ,
        ( CASE WHEN CHARINDEX('1', adj.comment_line,
                              CHARINDEX('claim ', adj.comment_line)) = 0
               THEN NULL
               ELSE SUBSTRING(adj.comment_line,
                              CHARINDEX('1', adj.comment_line,
                                        CHARINDEX('claim ', adj.comment_line)),
                              ( CASE WHEN CHARINDEX(' ', adj.comment_line,
                                                    CHARINDEX('1',
                                                              adj.comment_line,
                                                              CHARINDEX('claim ',
                                                              adj.comment_line)))
                                          - CHARINDEX('1', adj.comment_line,
                                                      CHARINDEX('claim ',
                                                              adj.comment_line)) >= 6
                                     THEN CHARINDEX(' ', adj.comment_line,
                                                    CHARINDEX('1',
                                                              adj.comment_line,
                                                              CHARINDEX('claim ',
                                                              adj.comment_line)))
                                          - CHARINDEX('1', adj.comment_line,
                                                      CHARINDEX('claim ',
                                                              adj.comment_line))
                                          + 1
                                     ELSE ( CASE WHEN CHARINDEX('.',
                                                              adj.comment_line,
                                                              CHARINDEX('1',
                                                              adj.comment_line,
                                                              CHARINDEX('claim ',
                                                              adj.comment_line)))
                                                      - CHARINDEX('1',
                                                              adj.comment_line,
                                                              CHARINDEX('claim ',
                                                              adj.comment_line)) >= 6
                                                 THEN CHARINDEX('.',
                                                              adj.comment_line,
                                                              CHARINDEX('1',
                                                              adj.comment_line,
                                                              CHARINDEX('claim ',
                                                              adj.comment_line)))
                                                      - CHARINDEX('1',
                                                              adj.comment_line,
                                                              CHARINDEX('claim ',
                                                              adj.comment_line))
                                                      + 1
                                                 ELSE LEN(adj.comment_line)
                                                      - CHARINDEX('1',
                                                              adj.comment_line,
                                                              CHARINDEX('claim ',
                                                              adj.comment_line))
                                                      + 1
                                            END )
                                END ))
          END ) AS ParsedClaim_ID
FROM    MyTableAdjustments AS adj
WHERE   adj.special_tx_type_c IN ( 3, 4 );

Aaron L

Hi hafnera,

Thanks for your post - I can reproduce the issue using your script and will look into a fix now.

Thanks,
Aaron.

Aaron L

Hi Hafnera,

I think we've got a fix for you in this private build (EDIT: The fix is now included in the stable release of SQL Prompt) . Could you give it a try and let me know if it works as you'd expect?

Thanks!
Aaron.

alex80

I had the same issue and it worked for me, at least.

hafnera

That worked perfectly! Thanks!

Aaron L

Thanks for giving it a try Hafnera and Alex!

We'll do some more testing on the fix here and will include it in the stable release of SQL Prompt (hopefully next week).

Thanks,
Aaron.