Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2013
    Posts
    2

    Unanswered: Inline Code Block with Variables

    The following query may seem nonsense but it is very simplified to provide a real query so my question makes sense.
    We have several queries that are done periodically and the only thing that might be different are a couple parameters. The following is a sample where I have dates as parameters. The larger queries may have multiple query segments in it and for speed, the dates may be used repeatedly to reduce dataset sizes. As such, it is easier to use parameters instead of replacing the same dates several times. Functional tables handle this quite well, but these queries are just "tools" we run in TOAD, not create a database Function Table.

    The query executes fine stand-alone by using actual dates. I wrap it in the BEGIN/END so I can use parameters and it executes successfully. The problem is there is no result set returned to TOAD. I just get the message saying the query executed successfully.

    Is there a key word or something I must use to get it to return the result set?

    Thanks.

    Code:
    BEGIN ATOMIC
    DECLARE dtStart DATE DEFAULT '1-Jan-2013';
    DECLARE dtEnd   DATE DEFAULT '31-Jan-2013';
    
    WITH
      ORDERS AS
      (
        SELECT 
          sli.id_no AS line_item_id
          , so.company_id
        FROM PARTS.sls_orders so
        JOIN PARTS.sls_line_items sli ON sli.order_id = so.id_no
        WHERE sli.po_date BETWEEN dtStart AND dtEnd
      )
    SELECT
     ord.line_item_id
     , ord.company_id
     , cc.company_name
    FROM ORDERS ord
    JOIN COMPANIES.cmp_companies cc ON cc.company_id = ord.company_id
    ;
    
    END;

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't believe SELECT without the INTO clause is allowed in compound SQL.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Dec 2013
    Posts
    2
    I had thought earlier about getting the data out of the routine and tried putting a RETURN in but it didn't like that. So your idea of the INTO seemed logical.

    I created the three variables for the INTO but it doesn't seem to like the INTO line.
    "unexpected token "pO_line_item_id" was found folloinwg 'c.company_name INTO"
    Comment out the INTO line and it executes successfully again.

    Code:
    BEGIN ATOMIC
    DECLARE dtStart      DATE DEFAULT '1-Jan-2013';
    DECLARE dtEnd        DATE DEFAULT '31-Jan-2013';
    DECLARE pO_line_item_id DECIMAL(15,0);
    DECLARE pO_company_id   DECIMAL(15,0);
    DECLARE pO_company_name VARCHAR(75);
    
    WITH
      ORDERS AS
      (
        SELECT 
          sli.id_no AS line_item_id
          , so.company_id
        FROM PARTS.sls_orders so
        JOIN PARTS.sls_line_items sli ON sli.order_id = so.id_no
        WHERE sli.po_date BETWEEN dtStart AND dtEnd
      )
    SELECT
     ord.line_item_id
     , ord.company_id
     , cc.company_name
    INTO pO_line_item_id, pO_company_id, pO_company_name
    FROM ORDERS ord
    JOIN COMPANIES.cmp_companies cc ON cc.company_id = ord.company_id
    ;
    
    --RETURN;
    
    END;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •