“Must declare the scalar variable” error in Toad for SQL

Started learning how to work with variables in T-SQL. Doing first basic exercise to declare today’s date and then print it, failed mizerably.

I was using Toad for SQL because this is my day to day tool. Tried several other examples, searched online for this error… looks like “it works”, but it doesn’t for me?!

Opened Microsoft SQL Management Studio, pasted same example, hit F5. Boom, worked! Ok, then it’s something related to Toad for SQL.

Searched again with Toad word included. Of course, found the issue description quickly on Toad’s forum: https://forums.toadworld.com/t/data-point-error-must-declare-the-scalar-variable/27407/8

This is not a bug and that is why things are not changing. This is original behavior from Toad for Oracle.

There are different execution needs depending on what you are doing.

When you have a script you might want to only execute the SQL you are working on. For this you use F9 and we figure out what the current statement is based on your cursor and only execute that.

Or when you have a script you might want to execute all in the script from top down. For this you use F5.

There is a third option which is to execute from the current statement to the end. For this there is a menu action but no hot key.

In the case of a SQL that contains a script variable you can’t execute the current statement as the variable must be declared in block of code that is being sent to the server. So in this case F5 is the only option.

Power Query – Week start and end dates in same column

Someone asked me the other day below thing: “On the same column I want a Range of dates not Duration between two dates So I have a Creation date and fulfillment date I need to come up with a column of weekly Range like for example
10/21 – 10/27
10/28 – 11/01
11/02 – 11/09
Something like this.

The solution can be pretty simple and mostly done through Power Query’s GUI.
Add a date range first using either way you want.
Then under Add Column – Date, you can easy add “Start of Week” and “End of Week”.

Note that if your week starts on Sunday, you don’t need to provide the first day of the week. I need my week to start on Monday so I added 1, instead of 0 or nothing “Date.EndOfWeek([Date],1)“.

The only thing to do now is to merge these two columns and put a separator of your choice. This will give you a column with Week’s start and end dates in the long format.

ex1

If you want month and date (or only date) you need to extract month and then date separately from both your Start and End of Week columns. Then merge month and date from Start of the week and again from End of the week.

At this moment, you only need to merge these 2 last columns, remove all unneeded columns and that’s it.

short

Here’s the entire query I ended up with.


let
Query1 = let
Source = #date(2019,1,1),
Custom1 = List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}})
in
#"Converted to Table",
#"Changed Type" = Table.TransformColumnTypes(Query1,{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Date", Order.Descending}}),
#"Inserted Start of Week" = Table.AddColumn(#"Sorted Rows", "Start of Week", each Date.StartOfWeek([Date],1), type date),
#"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date],1), type date),
#"Inserted Merged Column" = Table.AddColumn(#"Inserted End of Week", "Long range", each Text.Combine({Text.From([Start of Week], "en-US"), Text.From([End of Week], "en-US")}, " - "), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Merged Column", "Month", each Date.Month([Start of Week]), Int64.Type),
#"Inserted Day" = Table.AddColumn(#"Inserted Month", "Day", each Date.Day([Start of Week]), Int64.Type),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Day", {{"Month", type text}, {"Day", type text}}, "en-US"),{"Month", "Day"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Start"),
#"Inserted Month1" = Table.AddColumn(#"Merged Columns", "Month", each Date.Month([End of Week]), Int64.Type),
#"Inserted Day1" = Table.AddColumn(#"Inserted Month1", "Day", each Date.Day([End of Week]), Int64.Type),
#"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Day1", {{"Month", type text}, {"Day", type text}}, "en-US"),{"Month", "Day"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"End"),
#"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Start", "End"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Short range"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns2",{"Start of Week", "End of Week"})
in
#"Removed Columns"

Examples of Count, Count IF, Count duplicates in SQL

Count:

SELECT [ProductModel], COUNT(1) CNT
FROM [AdventureWorks2017].[Production].[vProductAndDescription]
GROUP BY [ProductModel]
ORDER BY CNT DESC

Count “if”:

SELECT [ProductModel], [Name],COUNT(1) CNT
FROM [AdventureWorks2017].[Production].[vProductAndDescription]
WHERE [ProductModel] LIKE '%Frame' AND [Name] LIKE '%52' --here's the IF statement
GROUP BY [ProductModel], [Name]
ORDER BY CNT DESC

Count duplicates:

SELECT [ProductModel], COUNT(1) CNT -- select a field you want to search for duplicates in
FROM [AdventureWorks2017].[Production].[vProductAndDescription]
GROUP BY [ProductModel] HAVING COUNT(1) > 1 -- if count is greater than 1, then it is a duplicate by that field
ORDER BY CNT DESC

Group count fields a tabular form:

SELECT [ProductModel], [Name], COUNT(1) CNT
FROM [AdventureWorks2017].[Production].[vProductAndDescription]
GROUP BY [ProductModel], [Name]
ORDER BY CNT DESC

ITIL 4 coming soon

More info on https://www.sysaid.com/blog/entry/whats-coming-in-itil-4.

Quoting blog post’s summary:

  • ITIL 4 is coming soon – early 2019.
  • It is an evolution of ITIL, not a revolutionary replacement.
  • It builds on many of the ideas in ITIL Practitioner.
  • It focusses more on how things fit together to create value, and less on process steps.
  • It’s going to be released in stages, with Foundation material available early in 2019.

Power BI Data Profiling – distinct vs unique

“Another month, another Power BI Desktop update” as Guy(s) in a Cube use to say.
Power BI Desktop October 2018 update brings in quite a few updates, with one thing being my favorite as I always do/check it manually when working with new data: Data Profiling. For more info and how to enable it, visit Microsoft’s official Power BI blog post or the detailed post from Matt Allington on https://exceleratorbi.com.au.

Ok, I don’t want to make it a long post, so here’s the thing: one of the Data Profiling feature is “Column distribution”.

Column Distribution allows you to get a sense for the overall distribution of values within a column in your data previews, including the count of distinct values (total number of different values found in a given column) and unique values (total number of values that only appear once in a given column).

So, distinct and/or unique. For anyone else these 2 words may seem similar, but for Data Guys like us, these are 2 different things (just like empty and null 🙂 ). Still confused? Here’s a very simple example to clear things up:

Let’s say we have a small company with 10 employees using 10 laptops from different Manufacturers: HP, Dell, Apple and Lenovo.
Manufacturers in Power BI's Column distribution
Distinct: We have laptops from 4 different Manufacturers (HP, Dell, Apple and Lenovo) aka “total number of different values”, regardless of how many of each we have.
Unique: We have only 2 laptops that nobody else have in our company (one from Apple and another one from Lenovo) aka “total number of values that only appear once”.

Thinking legacy, here’s the same thing in Excel, using countif or countifs.
Excel CountIF formula

Install-WindowsFeature Web-Net-Ext failed. Source files could not be found.

Error:

Trying to install .NET Framework 3.5 Features on a Windows Server 2016, either through GUI or PowerShell, returns below error (truncated):

“The request to add or remove features on the specified server failed.
Installation of one or more roles, role services, or features failed.
The source files could not be found.”

Cause:

.Net Framework 3.5 sources file are not pre-installed so we need to explicitly specify where “installer” can get the source files from.

Solution:

As per Microsoft’s official docs, there are 2 main solutions to make it work.

  1. Through GUI (Server Manager), when asked during the wizard, you need to specify the path to “Media:\Sources\SxS” folder.
  2. Through PowerShell, Install-WindowsFeature -Name Web-Net-Ext -source D:\Sources\SxS

Still, Microsoft says that even if this defined source is not found, installer should go to Windows Update to download required files. Let’s exclude those many possibilities that Windows Update is not accessible due to various reasons (no internet connection, GPOs disabling something etc.).

Anyway, since neither one of the above methods worked for me, I kept reading posts from 2nd+ google pages. This led me to not-so-old post from Michael Niehaus: https://blogs.technet.microsoft.com/mniehaus/2015/08/31/adding-features-including-net-3-5-to-windows-10/

Not sure what’s different with calling DISM directly (since Server Manager and Powershell probably does the same), but below solution worked for me.

Run this from PowerShell (including tilde characters): DISM.exe /Online /Add-Capability /CapabilityName:NetFx3~~~~ /Source:D:\Sources\SxS where D was the drive with my mounted Server 2016 ISO.

When above command is done, run this: Add-WindowsCapability –Online -Name NetFx3~~~~ –Source D:\Sources\SxS

What and when: DateAdd vs ParallelPeriod vs SamePeriodLastYear

Reza Rad over at http://radacad.com/dateadd-vs-parallelperiod-vs-sameperiodlastyear-dax-time-intelligence-question explains the differences and provides easy usage scenarios for DateAdd, ParallelPeriod and SamePeriodLastYear DAX functions.

Read his blog post to see how he reached to below conclusion:

  • DateAdd and SamePeriodLastYear both work based on the DYNAMIC period in the filter context.
  • ParallelPeriod is working STATICALLY based on the interval selected in the parameter.
  • ParallelPeriod and DateAdd can go more than one interval back and forward, while SamePeriodLastYear only goes one year back.
  • DateAdd works on the interval of DAY, as well as month, quarter and year, but ParallelPeriod only works on month, quarter, and year.
  • Depends on the filter context you may get a different result from these functions. If you get the same result in a year level context, it doesn’t mean that all these functions are the same! Look more into the detailed context.