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 );
Hi hafnera,
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 );