Help need in Query--urgent - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

Help need in Query--urgent

Author

Topic

ani12342004
Starting Member

2 Posts

Posted - 2012-02-16 : 12:13:50

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

Animesh Saikia

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 12:48:39

WHAT Does

SELECT SITE_CSANAME , COUNT(*)
FROM ARADMIN.CSA_CI_MVSS
GROUP BY SITE_CSANAME

Give you

And, btw, this is a SQL Server site. Not Oracle. I know both and will help where I can, but you should try

http://www.dbforums.com/oracle/

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ani12342004
Starting Member

2 Posts

Posted - 2012-02-16 : 23:02:28

Thanks Brett..

The query gives the results of all sites like
LivoniaMI
AguascalMX
PamplonaES
amd so on.

when in field '$536871158$' we place only one value in format as AguascalMX, then it is working,but as it is a multiple selection field...like LivoniaMI,AguascalMX,PamplonaES

Thanks
Animesh

Animesh Saikia

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-17 : 09:54:02

Sorry, this being SQL Server I did not know that was a Variable

However, SQL is SQL

You can't do what you are doing

You need to break up your string in a function and return a table

Or use dynamic SQL...which I don't remember how to do that in Oracle

Again, your best bet is to go to the link I provided

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources