Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Los Angeles, CA
    Posts
    28

    Unanswered: Problem with SQL Headings

    I am having trouble using the concatenation operator (||) and substitute heading in my sql. Basically I want the output to look something like this:

    WAREHOUSE TOT_ALL B0 Pct B1 Pct
    ----------- -------- ---- ---- ---- -----
    Mich 12000 6000 (50.) 3000 (25.)
    Calif. 20000 5000 (25.) 2000 (10.)

    The problem is I can make the parenthesis using the operator, but can not get the heading I want.

    select distinct warehouse,
    count (*) tot_all,
    count (b0) || ‘ (‘ ||
    substr (count (b0) / count (*) * 100, 1, 3) || ‘)’ ||
    count (b1) || ‘ (‘ ||
    substr (count (b0) / count (*) * 100, 1, 3) || ‘)’
    from product_line
    group by warehouse;

    So I get something like this:

    WAREHOUSE TOT_ALL COUN SUBS COUN SUBS
    ----------- -------- ---- ---- ---- -----
    Mich 12000 6000 (50.) 3000 (25.)
    Calif. 20000 5000 (25.) 2000 (10.)

    I don’t seem to be able to use the substitute heading and concatenation operators together, I have tried numerous combinations and I can’t find any documentation on this.


    Thanks

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

    Cool


    Try this:
    Code:
    select distinct warehouse,
    count (*) tot_all,
    count (b0) || 
     ( ||substr (count (b0) / count (*) * 100, 1, 3) || )  "B0   Pct",
    count (b1) || 
     ( ||substr (count (b0) / count (*) * 100, 1, 3) || )  "B1   Pct"
    from product_line
    group by warehouse;



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

  3. #3
    Join Date
    Feb 2004
    Location
    Los Angeles, CA
    Posts
    28
    Thanks LKbrwn. That worked. However, the columns are rather large now, any way to keep then to a reasonable size?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How would define "rather large" and "reasonable size"? Do you have an example of what you currently have and what you'd like to have?

    SUBSTR is already applied and, viewing your first post, columns do not seem to be of a King Kong size to me ...

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

    Cool


    You can control the size of columns with the "COL" statement:
    Code:
    COL tot_all FOR 999,999
    COL B0 FOR 99,999
    COL B0_Pct FOR A(7)
    COL B1 FOR 99,999
    COL B1_Pct FOR A(7)
    select distinct warehouse,
             count (*) tot_all,
             count (b0) B0 
              ( ||substr (count (b0) / count (*) * 100, 1, 3) || )  B0_Pct,
             count (b1) B1 
              ( ||substr (count (b0) / count (*) * 100, 1, 3) || )  B1_Pct
      from product_line
    group by warehouse;



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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    As this thread deals with Oracle implementation details, and has almost nothing to do with SQL itself, I'm moving the thread to the Oracle forum. The discussion may help other Oracle users.

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    Los Angeles, CA
    Posts
    28

    That Worked

    That worked perfectly.. Thanks!

Posting Permissions

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