No intellisense when alias is in front of column + CASE
buckley
Posts: 69 New member
We bumped into an issue where there were no column suggestions and took the time to write an isolated example.
SELECT
ca. -- no intellisense
BalancePreviousBusinessDay = CASE WHEN 1 = 1 THEN '1'
ELSE '2'
END,
FROM
vwCashAccountSpaarrekening ca
Note that if there is a comma before BalancePreviousBusinessDay the column suggestions appear.
If we can be of further assistance let me know
SELECT
ca. -- no intellisense
BalancePreviousBusinessDay = CASE WHEN 1 = 1 THEN '1'
ELSE '2'
END,
FROM
vwCashAccountSpaarrekening ca
Note that if there is a comma before BalancePreviousBusinessDay the column suggestions appear.
If we can be of further assistance let me know
Comments
Thanks again for the repro steps!
I think your guess is right and since it's illegal syntax the Prompt parser is failing half way through the script and doesn't get to resolve "ca" to "vwCashAccountSpaarrekening". I'll look into seeing if I can make it a little bit more robust in resolving aliases.
Off topic. I am interested in how you develop this product. What kind of testing do you perform. Before or After. Do you in this case first write a failing test and then make it pass.
Do you have a large test harness by this time to detect regressions? I'm primarily a inhouse business developer (lots of CRUD/sql/..) and haven't worked on a product like prompt. I think it must be interesting and poses lots of challenges.
It varies from bug to bug if we write the tests before or after. In the bugs you've reported it was quicker to put the scripts into small tests and run them there than it was to start SSMS every time I made a change.
We have a large set of integration tests (~50,000) that run against test databases every time a build on our build server is completed and then another set of slow running ones overnight. I'm currently trying to move more of our tests into small, quick running unit tests that just mock a database where needed - these are really cool with NCrunch since they can be run every keypress making the feedback if a test fails/passes almost immediate.
It is really interesting to work on, especially as the parser needs to handle incomplete syntax and figure out all the things user might enter at each point (without being too slow!)
This turned out to be a tricky one to solve with how our parser currently works. We've got some work planned over the next few months on rewriting a part of the parser to allow some of the more advanced prompting feature so I was hoping to include this as part of that work.
I'll have another look at it today to see if there's any quicker fixes that could be included in the beta
Thanks for the update. I'll leave it up to you. I don't need a quick fix and prefer the best possible solution even though it will take some time. If you need a tester I'm here. I hope you use my (3) cases which will result in 3 failing tests. Once everything is green again we have a test harness for a robust sql parser than can handle statements that are a working in progress/temporarily invalid.
I've just put in some changes that have made the tests for your 3 issues turn green. This private build should have more robust error recovery if you'd like to give it a go?
Thanks again for all your help!
The previous build had trouble with an alias that comes first. Thanks Aaron
To pick up the original report there is still the case of the invalid comma which breaks intellisense (bold below)
SELECT
ca. -- no intellisense
BalancePreviousBusinessDay = CASE WHEN 1 = 1 THEN '1'
ELSE '2'
END, -- comma here gives no intellesense
FROM
vwCashAccountSpaarrekening ca
At the "no intellisense" comment:
At the "comma here gives no intellisense":
I'm wondering if I'm missing something that's causing it to break for you?