Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2014
    Posts
    2

    Unanswered: Need a help in substr and Locate function.

    Guya, need a help in locate and substr function.

    I have a query like,

    select *
    from
    (
    selecT count(*) cnt, customer_cd
    ,SMALLINT(RANK() OVER(ORDER BY count(*) DESC)) as rnk

    from table.customer
    and substr(customer_cd, 1,1) <> '0'
    group by customer_cd) p
    fetch first 10 rows only

    It is giving result like,

    CNT Customer_cd

    200 1- Marketing customer
    190 2- Retail
    180 3- Financial
    170 Not available

    Any help how to get the only the number instead of full description.Like

    200 1
    190 2
    180 3
    170 Not available

    Thanks.

  2. #2
    Join Date
    Apr 2012
    Posts
    156
    From your message I think you are just needing to add the substr in the column list as well, try this:
    select *
    from
    (
    selecT count(*) cnt, substr(customer_cd,1,1)
    ,SMALLINT(RANK() OVER(ORDER BY count(*) DESC)) as rnk

    from table.customer
    and substr(customer_cd, 1,1) <> '0'
    group by customer_cd) p
    fetch first 10 rows only

  3. #3
    Join Date
    Nov 2014
    Posts
    2
    Thanks a lot for your reply. The issue is that, I just found, there can be different code that 1,2,3.
    It can be

    1- Marketing client
    2- Financial
    3- HR Client
    HP201- Client
    1111-9090 client.

    So, only substring won't work right. we need to find the DASH and then subst it.

    Not sure how to put in the query

  4. #4
    Join Date
    Apr 2012
    Posts
    156
    If you know all the values you could add a case statement so the values you want are displayed.

Tags for this Thread

Posting Permissions

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