Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Unanswered: SQL query modification

    How can the following sql be modified to only select the items that actually contain data of 9 digit length? I am looking for columns that may contain social security numbers.

    Code:
    SELECT b.table_name, a.column_name , a.column_type
    FROM apps.fnd_columns a,
    apps.fnd_tables b
    WHERE
    (
    column_name LIKE 'SUPPLIER%'
    )
    AND
    (
    column_name NOT LIKE '%COST%' AND
    column_name NOT LIKE '%KEY%' AND
    column_name NOT LIKE '%CAPACITY%' AND  
    column_name NOT LIKE '%DOCUMENT%' AND  
    column_name NOT LIKE '%LOT_NUM%'  AND
    column_name NOT LIKE '%LINE_NUM%' AND
    column_name NOT LIKE '%SITE%'  AND
    column_name NOT LIKE '%ITEM_NAM%'  
    )
    AND a.TABLE_ID = b.TABLE_ID
    AND a.application_id =  b.application_id
    --AND a.column_type = 'N'
    /

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Would this do any good? Or did I not understand you?

    AND LENGTH(column_name) = 9

  3. #3
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    No, the column names with a length of 9 are returned , not the contents with a length of 9.
    Code:
    SELECT b.table_name, a.column_name , a.column_type
    FROM apps.fnd_columns a,
    apps.fnd_tables b
    WHERE
    (
    column_name LIKE 'SSN%' OR
    column_name LIKE 'SUPPL%' OR
    column_name LIKE 'EMPL%' OR
    column_name LIKE 'TAX_ID%' OR
    column_name LIKE 'BANK%'   OR
    column_name LIKE 'NATIONAL%'
    )
    AND
    (
    column_name NOT LIKE '%COST%' AND
    column_name NOT LIKE '%KEY%' AND
    column_name NOT LIKE '%CAPACITY%' AND
    column_name NOT LIKE '%DOCUMENT%' AND
    column_name NOT LIKE '%LOT_NUM%'  AND
    column_name NOT LIKE 'LINE_NUM%'
    )
    AND a.TABLE_ID = b.TABLE_ID
    AND a.application_id =  b.application_id
    AND LENGTH(column_name) = 9
    Code:
    CE_UPG_AP_BANKS_TMP            BANK_NAME                      V
    CE_UPG_PURGE_PAY_ACCTS         BANK_NAME                      V
    CE_UPG_PURGE_AP_BANKS          BANK_NAME                      V
    CE_UPG_PURGE_AP_ACCTS          BANK_NAME                      V
    CE_UPGA_BANK_REC               BANK_CODE                      V
    CE_CP_WORKSHEET_LINES          BANK_NAME                      V
    PAY_BANK_BRANCHES              BANK_CODE                      V
    Last edited by wrwelden; 12-11-06 at 16:17.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Two words for you: Dynamic SQL.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    when you describe the field, how is it defined. If it is a varchar2 field then

    where length(column_name) = 9

    will work. If it is varchar or char then you have to do the following.

    where length(rtrim(column_name)) = 9

    As a matter of good programming, the use of a fixed length column (char or varchar) should be discontinued. It will cause nothing but problems.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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