Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    3

    Unanswered: Simple Query with Variables

    Hello All,

    Let me start by saying I'm not well-versed in PL/SQL or Oracle. I have been handed a project that involves 3 different database servers (MSSQL, Oracle, and Sybase). All of which I am able to query with exception to Oracle. I am familiar with MSSQL's TSQL where I can declare a variable and in my query, assign that variable a value as a result of the query. Thus far, I am unable to create this same situation in Oracle. Can anyone PLEASE help? Below is what I have so far and I cannot for the life of me figure it out:

    Code:
    DECLARE
      beginDate DATE := TO_DATE('2011-03-31');
      endDate DATE := TO_DATE('2011-04-30');
      liquidWeight DECIMAL(16,8);
      slabWeight DECIMAL(16,8);
    BEGIN
    SELECT
      liquidWeight = SUM(CASTS.LIQ_WT) AS LIQUID,
      slabWeight = SUM(CASTS.SLAB_WT) AS SLAB
    FROM
      (
      SELECT
    		CAST_STRAND.CAST_SEQUENCE_ID, 
    		(CASE WHEN CAST_STRAND.CROP_HEAD_WEIGHT IS NULL THEN 0 ELSE CAST_STRAND.CROP_HEAD_WEIGHT END 
    		 + CASE WHEN CAST_STRAND.CROP_TAIL_WEIGHT is null then 0 else CAST_STRAND.CROP_TAIL_WEIGHT END 
    		 + CASE WHEN CAST_TUNDISH_DATA.TUNDISH_FLY_NET_WEIGHT IS NULL THEN 0 ELSE CAST_TUNDISH_DATA.TUNDISH_FLY_NET_WEIGHT END 
    		 + 1400*(SELECT COUNT(CC_HEAT.HEAT_ID) FROM L2MELTSHOP.CC_HEAT CC_HEAT WHERE CC_HEAT.CAST_SEQ_NUM = CAST_STRAND.CAST_SEQUENCE_ID))/2000 AS LIQ_WT, 
    		 (SELECT SUM(CC_HEAT.SLAB_WT) FROM L2MELTSHOP.CC_HEAT CC_HEAT WHERE CC_HEAT.CAST_SEQ_NUM = CAST_STRAND.CAST_SEQUENCE_ID)/2000 AS SLAB_WT 	 
      FROM PRD.CAST_STRAND CAST_STRAND, PRD.CAST_TUNDISH_DATA CAST_TUNDISH_DATA WHERE CAST_STRAND.CAST_SEQUENCE_ID = CAST_TUNDISH_DATA.CAST_SEQUENCE_ID(+) AND (TO_CHAR(CAST_STRAND.CAST_START_TIME, 'yyyy-mm-dd') > '2011-03-31' AND TO_CHAR(CAST_STRAND.CAST_START_TIME, 'yyyy-mm-dd') < '2011-04-30')) CASTS;
      
    SELECT
      liquidWeight, slabWeight;
        
    END;
    I know my syntax is wrong but I am unable to find any example similar to it. I am also unable to use my dates. The query runs fine without the declare and the begin/end.

    Thanks,
    gomer

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    That would be something like SELECT something INTO variable FROM ...

    I wonder what is the purpose of the last SELECT statement of the block.

  3. #3
    Join Date
    May 2011
    Posts
    3

    SELECT statement purpose

    n_i,

    In TSQL for MSSQL, you assign a value of a query to a variable by using it in a SELECT statement. You can do it various ways but the easiest is to use it in a SELECT statement. The following two statements are the same. I'm looking to do the same in Oracle in PL/SQL:

    Code:
    SELECT myVariable = SUM(myvalues) FROM myTable
    Code:
    myVariable = (SELECT SUM(myvalues) FROM myTable)
    Is there an equivalent in PL/SQL?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT SUM(myvalues) INTO myvariable FROM myTable

    works when only single row returned
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2011
    Posts
    3

    SELECT Purpose

    The purpose in the last SELECT statement is to return the values of the variables that I set in the previous SELECT statement. Can you post an example of selecting into a variable that actually runs in SQL+ Worksheet? The table I'm trying to select from is PRD.CAST_STRAND and I would like to get a count of all of the records and then select that value.

Tags for this Thread

Posting Permissions

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