Issue in Query logic. Please help me

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

General SQL Server Forums

New to SQL Server Programming

Issue in Query logic. Please help me

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2011-06-03 : 00:57:13

Main Query :
SELECT COALESCE(
CASE WHEN B.c_nm108_id_qual = '24' THEN B.i_nm109_billing_id END,
CASE WHEN rtrim(RB.c_ref01_id_qual) = 'TJ' THEN RB.c_ref02_id_number
WHEN rtrim(RB.c_ref01_id_qual) = 'EI' THEN RB.c_ref02_id_number
WHEN rtrim(RB.c_ref01_id_qual) = 'SY' THEN RB.c_ref02_id_number ELSE ' ' END) as RB_i_nm109_bill_id
FROM PPRNPI.HIPTBILLPRV B LEFT OUTER JOIN PPRNPI.HIPTREFBLPRV RB ON B.i_cms_track_key = RB.i_cms_track_key WHERE rb.i_cms_track_key = UCASE('123456') AND
B.c_nm101_entity_id = '85' AND (B.c_nm108_id_qual = '24' OR (rtrim(RB.c_ref01_id_qual) IN ('TJ','EI','SY'))) AND NOT(B.c_nm108_id_qual = '24' AND (RB.c_ref01_id_qual) IN ('TJ','EI ','SY'));

When i execute this i am getting null value. Follwoing are the splitted query and result.

Query1 :

select RTrim(c_ref01_id_qual),c_ref02_id_number from PPRNPI.HIPTREFBLPRV where i_cms_track_key = UCASE('123456')

Result :

c_ref01_id_qual c_ref02_id_number
EI 50000

Query2:

select c_nm108_id_qual,c_nm101_entity_id from PPRNPI.HIPTBILLPRV where i_cms_track_key = UCASE('123456')

Result :

c_nm108_id_qual c_nm101_entity_id

Null 85

So joining the query 1 and query2 will make the Main query. The issue is my main query getting falied. It should show the value 50000 as result. but is it not showing. I didn't write this query. i am workign in maintanance project and some one from other vendor written. Waht soulc be the issue? any idea ?

I found if i remove the last "NOT" condition it is working as expected. But i shoulf not remove that. it is an existing working query. any sugestion ?

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-06-03 : 01:27:07

Hey is it MSSQL. Which version you are using?

In Love... With Me!

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2011-06-03 : 01:29:44

Hi,

forget about all those stuffs. What is the meaning of below line and what it does

NOT(B.c_nm108_id_qual = '24' AND (rtrim(RB.c_ref01_id_qual) IN ('TJ','EI ','SY')))

what is the logic falls here

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-06-03 : 12:00:12

it trims the trailing spaces on RB.c_ref01_id_qual field and checks if value is one among three categories 'TJ','EI ','SY' and also checks if B.c_nm108_id_qual = '24' . all resultset which doesnt fall on either of category will be returned as a result of NOT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources