Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    1

    Unanswered: Help needed in a query

    Hi All,
    Greetings!!

    I have used in menu a query which will work when field CI_Affected is clicked ---


    SELECT '"( ' || SITE_CSANAME || ' ) ' || NAME || '"' FROM ARADMIN.CSA_CI_MVSS WHERE CATEGORY_NAME= '$536871152$' AND SITE_CSANAME IN ('$536871158$')


    Upto the below part the menu is working fine and populating correct result from CSA_CI_MVSS view---


    SELECT '"( ' || SITE_CSANAME || ' ) ' || NAME || '"' FROM ARADMIN.CSA_CI_MVSS WHERE CATEGORY_NAME= '$536871152$'


    but when we add


    AND SITE_CSANAME IN ('$536871158$'), in the above query it's not populating any results.


    $536871158$ is CI Affected details field in a table in database.


    I am thinking it may be due to format of the value in that field. Refer second screenshot for details. The CI Affected Details field can have one value or multiple.

    For eg, its has value like --- AguascalMX,PamplonaES, and so on....may be multiple sites.

    If we set the value onlu as AguascalMX, it works fine, but as it is a multiple selection field it can be many sites in the field like AguascalMX,PamplonaES, and many more

    Please guide me how can i change the query to compare the CI affected Details value with SITE_CSANAME in csa_ci_mvss view.



    SELECT '"( ' || SITE_CSANAME || ' ) ' || NAME || '"' FROM ARADMIN.CSA_CI_MVSS WHERE CATEGORY_NAME= '$536871152$' AND SITE_CSANAME IN ('$536871158$')

    I tried to convert it as ---


    SELECT '"( ' || SITE_CSANAME || ' ) ' || NAME || '"'
    FROM ARADMIN.CSA_CI_MVSS
    WHERE CATEGORY_NAME= '$536871152$'
    AND SITE_CSANAME IN (RTRIM(REPLACE(RTRIM('$536871158$',''''),',',''',' '')),',''')


    But still not working..

    PLease guide...Its very urgent

    Thanks in advance!!

    Kind Regards,
    Animesh

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Since we don't have your tables & we don't have your data, that makes it a challenge to write SQL

    SELECT COUNT(*) FROM ARADMIN.CSA_CI_MVSS
    WHERE SITE_CSANAME LIKE '%$536871158$%';

    post results from SQL above
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Try the following

    select SITE_CSANAME
    WHERE CATEGORY_NAME= '$536871152$';

    I suspect you will not find any records that have the SITE_CSANAME equal to $536871158$. AND implies both conditions must be true.
    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
  •