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

Expand Wildcard Suggestion

hafnerahafnera Posts: 16 New member
edited April 21, 2016 6:11AM in SQL Prompt
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 );

Comments

Sign In or Register to comment.