SQL: if value exists then show else hide-CodePudding

I have an SQL query that filters down purchase credit and invoices and removes any zero tax amounts, it has just come to light that there is some i need to include for me to get the correct figure. Luckily these few invoices and credit notes can be identified on there reference number, unfortunately i don not know how to write such a query. I am guessing it should be an if statement. Any help would be much appreciated, query is below.

SELECT 
 --T_PURCHASEINVOICE.C_ID, 
  T_PURCHASEINVOICE.C_DATE, 
  T_PURCHASEINVOICE.C_NUMBER, 
  T_PURCHASEINVOICE.C_NETAMOUNT  , 
  T_PURCHASEINVOICE.C_GROSSAMOUNT, 
  T_PURCHASEINVOICE.C_TAXAMOUNT , 
  T_PURCHASEINVOICE.C_PERIOD,
  T_PURCHASEINVOICE.C_ALTERNATEREFERENCE ,
  T_TAXRATE.C_CODE
FROM 
  T_PURCHASEINVOICE (NOLOCK) 
  JOIN T_PURCHASEINVOICE_TAXLINE (NOLOCK) ON T_PURCHASEINVOICE.C_ID = T_PURCHASEINVOICE_TAXLINE.C__OWNER_ 
  JOIN T_TAXRATE (NOLOCK) ON T_PURCHASEINVOICE_TAXLINE.C_TAXRATE = T_TAXRATE.C_ID 
WHERE 
  T_TAXRATE.C_CODE = '01' 
  AND T_PURCHASEINVOICE.C_ALTERNATEREFERENCE LIKE '%MRINV%'
  --AND T_PURCHASEINVOICE.C_TAXAMOUNT <> 0 
UNION ALL 
SELECT 
  --T_PURCHASECREDITNOTE.C_ID, 
  T_PURCHASECREDITNOTE.C_DATE, 
  T_PURCHASECREDITNOTE.C_NUMBER, 
  case when T_PURCHASECREDITNOTE.C_NUMBER  = 'PCN' THEN 1 ELSE -1 END * T_PURCHASECREDITNOTE.C_NETAMOUNT , 
  case when T_PURCHASECREDITNOTE.C_NUMBER  = 'PCN' THEN 1 ELSE -1 END * T_PURCHASECREDITNOTE.C_GROSSAMOUNT ,
  case when T_PURCHASECREDITNOTE.C_NUMBER  = 'PCN' THEN 1 ELSE -1 END * T_PURCHASECREDITNOTE.C_TAXAMOUNT ,
  T_PURCHASECREDITNOTE.C_PERIOD,
  T_PURCHASECREDITNOTE.C_ALTERNATEREFERENCE,
  T_TAXRATE.C_CODE 
FROM 
  T_PURCHASECREDITNOTE (NOLOCK) 
  JOIN T_PURCHASECREDITNOTE_TAXLINE (NOLOCK) ON T_PURCHASECREDITNOTE.C_ID = T_PURCHASECREDITNOTE_TAXLINE.C__OWNER_ 
  JOIN T_TAXRATE (NOLOCK) ON T_PURCHASECREDITNOTE_TAXLINE.C_TAXRATE = T_TAXRATE.C_ID 
WHERE 
  T_TAXRATE.C_CODE = '01'
  AND T_PURCHASECREDITNOTE.C_ALTERNATEREFERENCE LIKE '%MRCR%'
  --AND T_PURCHASECREDITNOTE.C_TAXAMOUNT <> 0

As you can see i have commented out the AND T_PURCHASECREDITNOTE.C_TAXAMOUNT <> 0 and AND T_PURCHASEINVOICE.C_TAXAMOUNT <> 0 as that will remove every entry that has a zero tax amount.

The references i need to show are as follows purchase credit: MRCR and purchase invoice: MCINV. Below is what needs to be added to the other query, if C_TAXAMOUNT is zero don't show unless C_ALTERNATIVEREFERENCE LIKE '%MRCR%', that's how i see it unfortunately haven't got the skills to implement.

CodePudding user response：

SQL works by filtering data, so the concept of if statements doesn't really apply here. Rather you're using a condition to select what should be returned, which is what SQL's WHERE clause does; any rows matching the WHERE condition are returned, any which don't aren't.

You want those with zero tax OR those with a specific reference (including if both are true; but OR covers that too).

So you can replace:

AND T_PURCHASEINVOICE.C_ALTERNATEREFERENCE LIKE '%MRINV%'
--AND T_PURCHASEINVOICE.C_TAXAMOUNT <> 0

with

AND (
    T_PURCHASEINVOICE.C_ALTERNATEREFERENCE LIKE '%MRINV%'
    OR T_PURCHASEINVOICE.C_TAXAMOUNT <> 0 
)

Thus only if one or both of the conditions within the brackets returns true will that row be included in the results.