Red Gate forums :: View topic - No intellisense when alias is in front of column + CASE
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Prompt 6
SQL Prompt 6 forum

No intellisense when alias is in front of column + CASE

Search in SQL Prompt 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
buckley



Joined: 26 Jan 2006
Posts: 25

PostPosted: Mon Jan 13, 2014 4:03 pm    Post subject: No intellisense when alias is in front of column + CASE Reply with quote

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
Back to top
View user's profile Send private message
buckley



Joined: 26 Jan 2006
Posts: 25

PostPosted: Mon Jan 13, 2014 4:05 pm    Post subject: Reply with quote

Note that this happens when you add a column at the top when you didn't have the chance to type the comma yet. At that moment in time its illegal syntax but that's how one does it.
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 139
Location: Red Gate Software

PostPosted: Mon Jan 13, 2014 5:23 pm    Post subject: Reply with quote

Hi Buckley,
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.
Back to top
View user's profile Send private message
buckley



Joined: 26 Jan 2006
Posts: 25

PostPosted: Tue Jan 14, 2014 8:34 am    Post subject: Reply with quote

Alright. With this response rate it is motivating for us to file bug reports. We'll take the time to report any issues in detail so you can reproduce and make the product better/awesome.

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.
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 139
Location: Red Gate Software

PostPosted: Tue Jan 14, 2014 11:27 am    Post subject: Reply with quote

Keep em' coming Smile Although that said, this one is going to take a little longer to find a fix for than the last one.

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!)
Back to top
View user's profile Send private message
buckley



Joined: 26 Jan 2006
Posts: 25

PostPosted: Wed Feb 12, 2014 2:57 pm    Post subject: Reply with quote

Any update on this case? I'm a willing tester for all your beta versions you can throw at me Smile
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 139
Location: Red Gate Software

PostPosted: Thu Feb 13, 2014 10:34 am    Post subject: Reply with quote

Hi Buckley,
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 Smile
Back to top
View user's profile Send private message
buckley



Joined: 26 Jan 2006
Posts: 25

PostPosted: Thu Feb 13, 2014 2:05 pm    Post subject: Reply with quote

Hi Aaron,

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.
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 139
Location: Red Gate Software

PostPosted: Wed Mar 05, 2014 6:48 pm    Post subject: Reply with quote

Hi Buckley,
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!
Back to top
View user's profile Send private message
buckley



Joined: 26 Jan 2006
Posts: 25

PostPosted: Thu Mar 06, 2014 3:57 pm    Post subject: Reply with quote

Better with build 6.3.0.235.
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
Back to top
View user's profile Send private message
Aaron Law



Joined: 28 Jun 2013
Posts: 139
Location: Red Gate Software

PostPosted: Thu Mar 06, 2014 5:36 pm    Post subject: Reply with quote

Hmmm odd, I get suggestions on 6.3.0.235 even with the comma in there:
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?
Back to top
View user's profile Send private message
buckley



Joined: 26 Jan 2006
Posts: 25

PostPosted: Thu Mar 06, 2014 11:50 pm    Post subject: Reply with quote

Indeed, after trying it a second time I get intellisense with or without the comma. Very well done!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group