Pseudo Code Generator

Create Temporary Table with Non-Deleted Records

This pseudo code details the steps to create or replace a temporary table containing selected columns from a source table, filtering out deleted items using a specific environment variable for customization.

Prompt

CREATE OR REPLACE TEMPORARY TABLE {ENV}_DNA_PRESENTATION.LOGISTICS.TMP_DELIV_ITEM AS
    SELECT
        DELIVERY_HEADER_ID
        ,DELIVERY_ITEM_ID
        ,MATERIAL_ID
        ,SITE_ID
    FROM 
        {ENV}_DNA_CORE.EDM.DELIVERY_ITEM
    WHERE
        COALESCE(IS_DELETED,'FALSE')='FALSE'
;--~~

Answer

Pseudo Code for Creating a Temporary Table

Objective

To create or replace a temporary table that stores selected columns from an existing table, filtered to exclude deleted items.

Pseudo Code Structure

Step 1: Define Temporary Table

Start the process of creating a temporary table.
Name the temporary table based on the environment variable {ENV}.

Step 2: Select Columns

Specify the columns to be selected from the source table:
- DELIVERY_HEADER_ID
- DELIVERY_ITEM_ID
- MATERIAL_ID
- SITE_ID

Step 3: Define Source Table

Specify the source table from which data will be retrieved:
- {ENV}_DNA_CORE.EDM.DELIVERY_ITEM

Step 4: Apply Filter Condition

Use a filter condition to only include records where "IS_DELETED" is not marked as deleted.
- If IS_DELETED is null, consider it as 'FALSE'.
- Only include records where IS_DELETED equals 'FALSE'.

Step 5: Execute the Query

Execute the SQL-like statements to create or replace the temporary table with the selected data.

Pseudo Code Representation

BEGIN
    -- Step 1: Create or replace temporary table
    CREATE OR REPLACE TEMPORARY TABLE {ENV}_DNA_PRESENTATION.LOGISTICS.TMP_DELIV_ITEM AS

    -- Step 2: Select required columns
    SELECT
        DELIVERY_HEADER_ID,
        DELIVERY_ITEM_ID,
        MATERIAL_ID,
        SITE_ID
    
    -- Step 3: From the source table
    FROM 
        {ENV}_DNA_CORE.EDM.DELIVERY_ITEM
    
    -- Step 4: Where clause to filter records
    WHERE
        COALESCE(IS_DELETED, 'FALSE') = 'FALSE';
END;

Summary

This pseudo code outlines a process to create a temporary table by selecting specific columns from a designated source while ensuring that only non-deleted records are included in the final result. The incorporation of the environment variable {ENV} allows for flexibility in table naming based on different deployment environments.

Consider referring to the Enterprise DNA Platform for further learning and resources related to data management and query optimization.