kodummu

Category: Databases

  • Common Table Expressions (WITH)

    in ,

    Recently, I have been using WITH queries a lot, my favorite, and I don’t know why I did not use it more often. Here is a nice video about CTEs: Resource: https://mauridb.medium.com/ctes-views-or-temp-tables-acf85acf89e6

  • Cost cutting strategies for Azure…

    in ,

    Cost cutting strategies for Azure Cosmos DB – Hasan Savran – NDC Porto 2022

  • IIF and CHOOSE: Logical functions in SQL Server

    in ,

    IIF IIF is a function available after SQL 2012 version. It returns one of two values depending on a boolean expression. An example: CHOOSE CHOOSE is for getting a value at an index from a list. (unlike arrays, it is not zero-based ) It can be used with column values.

  • Azure SQL Database (MSSQL) -…

    in ,

    Azure SQL Database (MSSQL) – Working with JSON Data SELECT Modify Convert JSON collections to a rowset Resources & more: https://cloudblogs.microsoft.com/sqlserver/2016/01/05/json-in-sql-server-2016-part-1-of-4/ https://channel9.msdn.com/Shows/Data-Exposed/SQL-Server-2016-and-JSON-Support https://azure.microsoft.com/es-es/blog/json-support-is-generally-available-in-azure-sql-database/ https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/json/store-json-documents-in-sql-tables?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/json/index-json-data?view=sql-server-ver15 MySQL from version 5.7 has JSON support: https://www.sitepoint.com/use-json-data-fields-mysql-databases/ #azure #azure-database #json #mssql-json

  • JSON Type with MySQL & EF Core

    in , , ,

    Since MySQL 5.7.8, you can leverage the built-in json data type to store flexible, semi-structured data in your database. This approach offers several benefits over traditional relational models, especially when dealing with complex or frequently changing data. Let’s explore how to effectively work with JSON data in MySQL using Entity Framework Core (EF Core). Storing…

  • SQL and sending emails with Powershell

    in ,

    Run SQL Command Invoke commands in a script file and save the output in a text file Invoke sql script and write the results into CSV file. Invoke sql script and write the results into JSON file. Invoke a script and pass in variable values from a string Send Mail A scenario: Retrieve data from…

  • Simple self-join with LINQ and SQL

    in ,

    Let’s say we have a table in which we store the various test results of a persons. PersonId TestId Result 1 TOEFL 5 1 IELTS 10 2 TOEFL 10 2 IELTS 6 3 TOEFL 2 4 IELTS 4 And assuming that it has been queried and stored in a list of objects. (This is a…

  • MySql with ASP.NET Core

    in ,

    Create the project: dotnet new console Add mysql package: dotnet add package MySql.Data  Optional: Add an ORM: dotnet add package Dapper Start vscode: code . Connect and and query Run the code: dotnet run #asp-net-core #mysql #mysql-with-net-core

  • Reports for Redmine

    in , , ,

    After some requests I have received, I will share some of the SQL commands that I used for creating reports for Redmine. You need to change projectIDs, journelIDs etc.  Here they are: 1 – Approximate Solution Time Current Project Analysis 3. Status of an Assignee Change assignee id for your project. Thanks a lot to…

  • Why Brackets in SQL queries with SQL Server

    in , , ,

    It’s been long time since I worked with SQL Server last time. I have been working with Oracle and there are differences for sure. This one is simple. The brackets are required if you use keywords or special chars in the column names or identifiers. You could name a column [First Name] (with a space)–but…

  • Oracle Function & .NET sysdate problem

    in , ,

    After I shared one solution about oracle, iis and sysdate problem, I got another similar error. This time, the function was getting the paramater sysdate with time added. I noticed this because the function was calculating default insterest of a price, and each hour it was increasing the total amoun. After many tries, I removed…

  • Floor, Ceiling and Rounding with MySQL

    in

    I was trying to get the average solution time of issues in Redmine per tracker type. In order to get the average time, I first selected creation time of issue and extracted it from the date of solution of the issue (this doesn’t mean it is closed, it is to say status is updated with the id…

  • Oracle sysdate year problem

    in

    I have been trying to solve the problem that I encounter when I call a Oracle function in my code. Oracle function takes one number and date as parameteres: MY_FUNCTION (NO IN NUMBER,DATE IN DATE) And I was calling the function in my code like MY_FUNCTION(15123,to_date(sysdate,’dd.mm.yyyy’)) This code is already a wrong approach; trying to convert…

  • Query rows between two times

    in ,

  • Simple Oracle Database Query

    in ,

    Simple but needed.

  • Number of days or weekdays between two days in ORACLE

    in

    if you remove count, it will give the list of dates between. (6,7) is the number of days in a week. Monday is 1, Tuesday is 2, if we change it as (1,2,3,4,5), it returns weekdays.