Posts tagged with “tips”

Useful Oracle SQL syntax features for column updates

  1. Multiple Column Updates using IN clause:
UPDATE table_name
SET (col1, col2, col3) = 
    (SELECT val1, val2, val3 FROM source_table WHERE condition)
WHERE condition;
  1. MERGE statement for conditional updates/inserts:
MERGE INTO target_table t
USING source_table s
ON (t.key = s.key)
WHEN MATCHED THEN
    UPDATE SET t.col1 = s.col1, t.col2 = s.col2
WHEN NOT MATCHED THEN
    INSERT (col1, col2) VALUES (s.col1, s.col2);
  1. Using DEFAULT values in updates:
UPDATE employees
SET (salary, commission) = (DEFAULT, DEFAULT)
WHERE department_id = 90;
  1. Updating with RETURNING clause to get modified values:
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 20
RETURNING employee_id, salary INTO :emp_id, :new_salary;
  1. Using row constructor expression:
UPDATE table_name
SET (col1, col2) = (SELECT * FROM (VALUES (val1, val2)));
  1. Conditional updates using CASE:
UPDATE employees
SET salary = CASE 
    WHEN department_id = 10 THEN salary * 1.1
    WHEN department_id = 20 THEN salary * 1.2
    ELSE salary
END;
  1. Using WITH clause (Common Table Expression) in updates:
WITH avg_sal AS (
    SELECT dept_id, AVG(salary) avg_salary
    FROM employees
    GROUP BY dept_id
)
UPDATE employees e
SET salary = (
    SELECT a.avg_salary 
    FROM avg_sal a 
    WHERE e.dept_id = a.dept_id
)
WHERE condition;

These syntaxes are particularly useful when:

  • You need to update multiple columns at once
  • The update values come from another table or subquery
  • You want to perform conditional updates
  • You need to track what was updated
  • You're dealing with complex data transformations

Remember that while these features provide more elegant solutions, it's important to:

  1. Test performance with your specific data volumes
  2. Ensure the business logic is clear to other developers
  3. Consider maintainability of the code
  4. Check if indexes are being used effectively

[solution] mcr.microsoft.com/dotnet/aspnet:8.0-alpine couldn't recognize windows time zone ID

ENV DOTNET_SYSTEM_GLOBALIZATION_INVARIANT false
RUN apk add --no-cache icu-libs tzdata

Reference

By the way, my devops colleague told me if you can, changing the windows time zone id with its standard equivalent is the more preferred way. He said "its not good to set that environment variable to false"

What did I do yesterday? This Script Can Help

As a programmer, I often get so focused on solving problems that I forget to prepare for standups. The daily meeting doesn't allow extra time for preparation. If you struggle to remember what you did yesterday, like I did, this short worklog script can help.

The Worklog Script

Save the following script as worklog and put it into your ~/bin or any other bin directory in your PATH environment variable. Don't forget to make it executable with chmod +x worklog.

#!/usr/bin/bash

days=1
if [ "$1" != "" ]; then
  days=$1
fi

echo
echo -n "On project $(git remote -v | head -n1 | awk '{print $2}' | sed -e 's,.*:\(.*/\)\?,,' -e 's/\.git$//')"
echo
echo
git --no-pager log --no-merges --pretty=tformat:"- %ad | %s%C(auto)%d [%h]" --date=short --reverse --all --since=${days}.days.ago --author="$(git config user.name)" 2>&1
echo

How It Works

  1. Default Days Setting:

    days=1
    if [ "$1" != "" ]; then
      days=$1
    fi
    

    This part sets the default number of days to 1. If you provide a different number as an argument when running the script, it updates the days variable accordingly.

  2. Fetching Project Name:

    echo
    echo -n "On project $(git remote -v | head -n1 | awk '{print $2}' | sed -e 's,.*:\(.*/\)\?,,' -e 's/\.git$//')"
    echo
    

    This part retrieves the project name from the Git remote URL and displays it. It uses a combination of git remote -v, awk, and sed commands to extract and format the project name.

  3. Generating the Git Log:

    git --no-pager log --no-merges --pretty=tformat:"- %ad | %s%C(auto)%d [%h]" --date=short --reverse --all --since=${days}.days.ago --author="$(git config user.name)" 2>&1
    

    This part generates the Git log for the specified number of days. It includes:

    • --no-pager: To prevent paging the output.
    • --no-merges: To exclude merge commits.
    • --pretty=tformat: To format each commit log line.
    • --date=short: To display dates in short format.
    • --reverse: To show the oldest commits first.
    • --all: To include all branches.
    • --since=${days}.days.ago: To limit the log to commits from the specified number of days ago.
    • --author="$(git config user.name)": To filter commits by the current Git user.
  4. Outputting the Result:

    echo
    

    Finally, it prints a blank line for better readability of the output.

Running the Script

To see what you have done since 1 day ago, simply run:

worklog

If you want to see the work done in the last 7 days, run:

worklog 7

Handling Multiple Projects

If your work involves multiple projects, the following script can help:

Save the following script as wlog and put it into your ~/bin directory. Don't forget to make it executable with chmod +x wlog.

#!/bin/bash

# Define an array with the project directories
project_dirs=(
    /d/git/xemt-core/dfx
    /d/git/apollo/mock
)

# Loop through each project directory and run the commands
for dir in "${project_dirs[@]}"; do
    (cd $dir && worklog "$@" && cd - >/dev/null)
done

How It Works

  1. Project Directories:

    project_dirs=(
        /d/git/xemt-core/dfx
        /d/git/apollo/mock
    )
    

    Define an array containing the directories of your projects.

  2. Loop Through Projects:

    for dir in "${project_dirs[@]}"; do
        (cd $dir && worklog "$@" && cd - >/dev/null)
    done
    

    Loop through each directory, change to that directory, run the worklog script with any passed arguments, and then change back to the previous directory. The cd - >/dev/null part suppresses output from the directory change.

This script helps me quickly summarize my work for daily standup meetings. Customize the project directories in the wlog script to match your projects and streamline your standup preparation.

Using `git diff HEAD` to show All Changes

You probably know:

  • git diff shows unstaged changes.
  • git diff --cached shows staged changes.

But how do you see all changes in one go? Just use:

git diff HEAD

Here's the breakdown:

  • git diff compares the working directory to the staged index. If nothing is staged, it's the same as git diff HEAD.
  • git diff --cached compares the staged index to HEAD. It's a shortcut for git diff --cached HEAD.
  • git diff HEAD (or git diff <commit/branch/tag>) compares your current working directory with the specified commit.

Rider tips: Don't add bom when creating utf-8 files!

BOM is annoying. Especially when I am creating a SQL script file.