Results 1 to 2 of 2

Thread: Set Variable

  1. #1
    Join Date
    Jun 2012
    Posts
    1

    Unanswered: Set Variable

    I would like to set a variable at the top of my code somewhere but I'm not sure how to do it. See code in red below. I would like the variable to be the month # and this query to run based on that variable.


    Code:
    SELECT H.MTH,
           H.VENDOR#,
           H.VENDOR_NAME,
           H.CATEGORY,
           CASE WHEN P.ETA_HIT_YES IS NULL THEN 0 ELSE P.ETA_HIT_YES END ETA_YES,
           CASE WHEN P.TOTAL_ETA_OPPS IS NULL THEN 0 ELSE P.TOTAL_ETA_OPPS END TOTAL_ETA_OPS
    FROM COLBI.EDISP_TOWERS_WITH_CAT H
    LEFT OUTER JOIN SC_9_ETA_TARG_MTH P 
    ON H.VENDOR# = P.VENDOR#
    AND P.MON = CAST(H.MTH AS INT)
    AND P.CATEGORY = H.CATEGORY
    WHERE (CAST(H.MTH AS INT)<= 4)ORDER BY H.VENDOR#, H.MTH, H.CATEGORY;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    15:17:32 SQL> @mm
    15:17:35 SQL> ACCEPT	mm	PROMPT	'Month? '
    Month? 11
    15:17:36 SQL> SELECT	empno, hiredate from emp
    15:17:36   2  where to_char(hiredate,'MM') = &mm;
    old   2: where to_char(hiredate,'MM') = &mm
    new   2: where to_char(hiredate,'MM') = 11
    
         EMPNO HIREDATE
    ---------- -------------------
          7839 1981-11-17 00:00:00
    
    15:17:36 SQL> !cat mm.sql
    ACCEPT 	mm 	PROMPT	'Month? ' 
    SELECT	empno, hiredate from emp
    where to_char(hiredate,'MM') = &mm;
    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.

Posting Permissions

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