-1
SELECT ica.CORP_ID, ica.CORP_IDB, ica.ITEM_ID, ica.ITEM_IDB, 
ica.EXP_ACCT_NO, ica.SUB_ACCT_NO, ica.PAT_CHRG_NO, ica.PAT_CHRG_PRICE, 
ica.TAX_JUR_ID, ica.TAX_JUR_IDB, ITEM_PROFILE.COMDTY_NAME
FROM ITEM_CORP_ACCT ica
,ITEM_PROFILE
WHERE (ica.CORP_ID = 1000) 
AND (ica.CORP_IDB = 4051) 
AND (ica.ITEM_ID = 1000) 
AND (ica.ITEM_IDB = 4051)
AND ica.EXP_ACCT_NO = ITEM_PROFILE.EXP_ACCT_NO

I'm trying basically say since the exp account code is '801500' then the Name should return "Miscellaneous Medic...".

enter image description here

Cœur
  • 37,241
  • 25
  • 195
  • 267
Belal Kamara
  • 7
  • 5
  • Can you give a minimal example and formulate a question? – Dschoni Aug 23 '17 at 14:56
  • Post the tables and data as text [READ THIS](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) – Juan Carlos Oropeza Aug 23 '17 at 14:56
  • Promote the use of explict `JOIN` sintaxis, Aaron Bertrand wrote a nice article [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) about it. – Juan Carlos Oropeza Aug 23 '17 at 14:56
  • 1
    @Dschoni _Can_ they or _will_ they? Not the same thing unfortunately for us. – Tim Biegeleisen Aug 23 '17 at 14:56
  • 1
    `SELECT .. CASE ica.EXP_ACCT_NO WHEN '801500' THEN 'Miscellaneous Medic...' ELSE ITEM_PROFILE.COMDTY_NAME END AS COMDTY_NAME FROM ..` I believe – Serg Aug 23 '17 at 15:12
  • I dont see any question. Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. [**How to create a Minimal, Complete, and Verifiable example**](http://stackoverflow.com/help/mcve) – Juan Carlos Oropeza Aug 23 '17 at 15:12
  • @Belal Kamara How is it going in order to find an answer? Did you find what the problem was? – Menelaos Sep 18 '17 at 08:09

1 Answers1

0

It seems as if what you are showing is not possible. Have you edited the data in the editor??? You are joining using ica.EXP_ACCT_NO = ITEM_PROFILE.EXP_ACCT_NO . Therefore, every entry with EXP_ACCT_NO = 801500, should also have the same COMDTY_NAME.

However, it could be the case that your IDs are not actually numbers and that they are strings with whitespace (801500__ vs 801500 ). But since you are not performing a left-outer join, it would also mean you have an entry in ITEM_PROFILE with the same whitespace.

You also need to properly normalize your table data (unless this is a view) but it still means you have erroneous data.

Try to perform the same query, but using the TRIM function to remove whitespace: https://stackoverflow.com/a/6858168/1688441 .

Example:

SELECT ica.CORP_ID, ica.CORP_IDB, ica.ITEM_ID, ica.ITEM_IDB, 
ica.EXP_ACCT_NO, ica.SUB_ACCT_NO, ica.PAT_CHRG_NO, ica.PAT_CHRG_PRICE, 
ica.TAX_JUR_ID, ica.TAX_JUR_IDB, ITEM_PROFILE.COMDTY_NAME
FROM ITEM_CORP_ACCT ica
,ITEM_PROFILE
WHERE (ica.CORP_ID = 1000) 
AND (ica.CORP_IDB = 4051) 
AND (ica.ITEM_ID = 1000) 
AND (ica.ITEM_IDB = 4051)

AND trim(ica.EXP_ACCT_NO) = trim(ITEM_PROFILE.EXP_ACCT_NO);
Menelaos
  • 23,508
  • 18
  • 90
  • 155