Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Unanswered: Query within a Transform

    I utilize a data transformation SQL statement that is similar to:
    INSERT INTO Newfile
    (
    Field1, Field2...
    )
    Select(
    Value1
    Value2...) From Oldfile
    where .....

    However, one of the new values is dependent upon another statement that sets the new value to true
    if oldfile.Value4="SUB" and oldfile.Value5 = oldfile.Value6.

    How would I place this into the SQL statement above?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    Use a CASE or DECODE statement:
    Code:
    INSERT INTO Newfile
    (
    Field1, Field2...,FieldX,...
    )
    Select(
    Value1,
    Value2...,
    CASE 
      WHEN Value4="SUB" and Value5 = Value6
       THEN 'True'
        ELSE 'False' 
     END,
    ...) From Oldfile 
    where .....


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    So I can utilize a CASE statement within the Insert:

    insert into NEWFILE(
    Field1
    Field2
    Field3
    ...
    )
    select(
    Value1
    case .....
    Value3
    ...)
    from ....
    where....



    ??

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yes, you can. Imagine that CASE is nothing more than DECODE, but written in another way. It means that those two queries will do the same:
    Code:
    INSERT INTO DEPT (deptno, dname, loc)
    SELECT o.deptno + 50, 
      CASE WHEN o.deptno = 40 THEN 'Old operations'
           ELSE o.dname
      END,
      o.loc
    FROM DEPT o;
    
    INSERT INTO DEPT (deptno, dname, loc)
    SELECT o.deptno + 50, 
      DECODE(o.deptno, 40, 'Old operations', 
                            o.dname
            )
      o.loc
    FROM DEPT o;

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Simple, right?

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    Unbelievable. Great stuff.

    Which brings to mind another question.
    How about calling a function? If I call a function to help with the transform, where do I literally "place" the function - within the schema?

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Yoi place the function wherever you placed the CASE or DECODE().


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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