June 21, 2022 at 11:44 am
Hi everyone, I hope this is the right community.
In my new position I’m pulling lots of sql reports. Other than BASIC sql, I’m use excel, powershell and python.
Right now I’m having trouble understanding when I should export my sql queries to one of those other tools, vs. is it possible for me to just do it all in sql?
For instance, I’m having a lot of trouble using variables because it seems my query ends up 8 miles long. Is it possible to derive a column based off another derived column without encasing another select extremely lengthy select statement?
Sometimes I just feel like pulling the base info and exporting to another tool makes more sense, but I’d like to of course pull as much as possible directly from sql.
Additionally just basic tips/tricks for finding relationships would be awesome. Again, I’m new to ssms and using tsql.
Any direction you could point me in would extremely helpful. Further, I’d be willing to pay $100 for an hour of anyones time if they can help with some of my (I think, basic) questions.
Thanks all!
June 21, 2022 at 12:20 pm
Your post is quite open-ended and there are many sub-questions. At this point, I will mainly address this point:
Is it possible to derive a column based off another derived column without encasing another select extremely lengthy select statement?
The principle in SQL is all-at-once. This means that the entire SELECT list is defined as a single operation. This means that, you cannot say:
SELECT A, A+9 AS B, B+C AS D FROM tbl
With B referring back to A+9. B refers to the column tbl.B, and if there isn't one. you get an error about a missing column. You need to write this as one of:
SELECT A, A+9 AS B, A+9+C AS D FROM tbl
; WITH CTE AS (
SELECT A, A+9 AS B, C FROM tbl
)
SELECT A, B, B + C AS D FROM tbl
There are SQL products that violate the SQL standard in this regard, but SQL Server is not one of them.
Furthermore, you cannot use a column alias in FROM-JOIN, WHERE, GROUP BY or HAVING, but you can use it in ORDER BY. This may seem inconsistent, but this is due to that logically a query is evaluated in the order FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY.
In most cases, you want to work with your data in SQL, because that is the most efficient. Keep in mind that when you write a query, you don't have to think about in which order to process the tables; the optimizer figures that out. If you would join four tables in Python, you would have to code the join yourself. The exception to this rule is when you need to do advanced string processing, like regular expressions, as T-SQL is not well equipped for this.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 21, 2022 at 7:49 pm
Can you post a sample query? Have you read the Stairway series of articles on this site?
https://www.sqlservercentral.com/stairways
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 22, 2022 at 4:34 am
Hi hakiyu. Glad to "meet" you and welcome aboard.
Since you have asked such a open-ended question, I can only respond with an open-ended answer. It IS possible that you might take it as rudeness because it's short and to the point but it's certainly NOT meant as any form of personal attack, as some people might take my directness on this hotpoint subject for me.
I've found that a whole lot of people resort to other tools like PowerShell, Python, VB, SSIS, SSRS, and a world of other things because of just one thing... they don' know how to write code in T-SQL. That's not always the case but it's the usual case.
I've also found that if they spent the same amount of time learning T-SQL as they spent learning other tools, they wouldn't need those other tools about 99% of the time, especially for things like reporting... even what seems to be complicated reporting.
While I'd personally love to earn $100 an hour for teaching you stuff, that would be a ripoff to you, IMHO. There's so much good FREE information, especially on this site even outside the "Stairways" that Michael mentioned in the form of some very well written articles. Some of the articles are relatively "ancient" but they cover things that are still seriously applicable and important today.
To make sure that you DO have a great grasp on "the Basics", take the test at the end of the course at the following link. If you can't, pass the first time with a 100% from memory, then you should "take the course" and make sure you "study" and not just go through the motions. Even if you DO pass with a 100%, look at the subjects... you might just find an item or ten that you don't actually know and the test does NOT cover all of those. Here's the link to the course... and, yes... it's 100% free... you don't even need to create a use name.
https://www.w3schools.com/sql/
For instance, I’m having a lot of trouble using variables because it seems my query ends up 8 miles long. Is it possible to derive a column based off another derived column without encasing another select extremely lengthy select statement?
Once you're done with "the Basics" I told you about, take a look at the article at the first link in my signature line below for how to post a question AND some "readily consumable data" as an example and let's fix one of the queries you're talking about. If you follow that example, people will flock to assist you in your quest for more knowledge.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 19, 2022 at 11:00 pm
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply