1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

DECODE Function

Discussion in 'SQL PL/SQL' started by llkhoutx, Jun 5, 2015.

  1. llkhoutx

    llkhoutx Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I'm new to PL/SQL, but have a generally good understanding of SQL, but don't understand the following portion of a select statement:

    procedure XXX_ftp (p_from_date in date := sysdate - 5.2/1440,
    p_to_date in date := sysdate,
    p_filename in varchar2 := 'XXX.csv')
    as
    l_subModule VARCHAR2(30) := 'XXX_ftp';
    l_file_handle UTL_FILE.FILE_TYPE;
    l_dir varchar2(100) := 'REPORT_DIR';
    l_msg long;
    l_header varchar2(200);
    l_lf char(2) := chr(13) || chr(10);

    begin
    for rec in (select to_char(pu.created,'mm/dd/yyyy hh24:mi') || ',' || 'DI_PICKUP' || ',' ||
    decode(category,'E','EXPORT','I','IMPORT','T','TRANSHIP','R','RESTOW',category) || ',' || ...

    from ...

    loop
    end loop...

    A typical Query Result row is as follows:

    08/13/2013 14:06,DI_PICKUP,IMPORT,LOAD,HSD,SUDU1072391,20,RF,251486,-20C,,700 CTN FROZEN BONEL,,CAP STEWART,098N,NZMPI00498519,,,CTG,HOU,V

    Specifically, "category" is not a column in any table in the from clause and is not an alias column in the SQL, what does "category" in this instance mean? Apparently, "IMPORT" is returned. I'm clueless.:confused:

    Thank you in advance for your help.
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    You have provided insufficient data. Frankly, my first inclination would be to assume you have missed something and that 'category' is a column (or a column alias) in the table(s) accessed by the FROM clause.

    Theoretically there could be a PL/SQL function called CATEGORY that accepted no parameters and returned a value. However, that doesn't really make any sense in the context. I could make arguments that such a function could return meaningful data based on a factor other than parameters (i.e. the calling SCHEMA, or the DOW it was called, or the HOUR it was called). However, I seriously doubt it.

    In similar fashion, if this is in a package, there could be a global variable in the package called CATEGORY that is being used. However, since the function shown prefixes parameters with p_ and local variables with l_, global variables would probably be prefixed with g_. Also, a global variable would always generate the same DECODE value for all rows, so there would be no reason to include it in the SELECT list.

    Frankly, the only reason to include the DECODE in the SELECT list is if there is a value that will change based on rows... which brings me back to the start of this post.
     
  4. llkhoutx

    llkhoutx Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    I found "category" in one of the aliased tables in the SQL from clause. It's used in the SQL without an alas prefix. Silly me for not considering bad/lazy coding practices.:D

    Thank you for your help.:hurray