Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how can I use IF in my query?

Posted on 2010-01-13
4
Medium Priority
?
152 Views
Last Modified: 2012-05-08
Hello guys

I have a problem and I don't know how to solve this.

I want to get all rows when TFAT not in ('E','X','H','T') and (B.OutrasSaidas <> 'O' OR B.OutrasSaidas IS NULL)

but there is a case where I have some rows that TFAT is = '1' but OutrasSaidas is also = 'O'
when I have this, I need to discard this option (outrassaidas) to get the value .
Is there any way to do that?
thanks
SELECT convert(char(10),B.NFI_DEMI,103) AS EMISSAO, SUM(A.INF_TOTA) AS FAT_NF,
       0 AS QTD_NF,SUM(isnull(B.NFI_VICS,0))AS SUBTRIBUTARIA INTO #TMP_CONSVENDAS FROM SIAINFI A (NOLOCK), SIANFIS B (NOLOCK)
WHERE A.INF_FORN in ('JOAL','NORSCOT','FICUS','LAPLISA','LAPLISA2','ROYAL OPERA','PARNASSA','ZARD','ROS','DAIWA')
  AND A.EMPRESA  in ('CARAJAS','HIDRAU')
  AND B.NFI_CODI=A.INF_CODI
  AND B.EMPRESA=A.EMPRESA
  AND B.NFI_CLIE NOT IN ('1','2042','4611','8910','9286','27289')
  AND CONVERT(Char(10), B.NFI_DEMI, 112) >= '20100101'
  AND CONVERT(Char(10), B.NFI_DEMI, 112) <= '20100112'
  AND (B.NFI_CANC      = 'N')
  AND (B.NFI_TFAT NOT IN ('E','X','H','T'))
  AND (B.NFI_FATESPE  <> 'S')
  AND (B.OutrasSaidas <> 'O' OR B.OutrasSaidas IS NULL)
  AND (B.NFI_NFIS IS NOT NULL)
  GROUP BY convert(char(10),B.NFI_DEMI,103)
  ORDER BY EMISSAO

Open in new window

0
Comment
Question by:hidrau
  • 2
  • 2
4 Comments
 
LVL 11

Accepted Solution

by:
rajvja earned 2000 total points
ID: 26302749
use
case
   when condition
   then
  value
  when condition
  then value
else
   value
end as OutputFldName
0
 
LVL 1

Author Comment

by:hidrau
ID: 26302769
friend,
 could you give me an example how I could do that in this query?
0
 
LVL 11

Assisted Solution

by:rajvja
rajvja earned 2000 total points
ID: 26302852
case is helpful in select query field list

select case when fld=value then newvalue end from tablename

like that
0
 
LVL 1

Author Closing Comment

by:hidrau
ID: 31676607
THANKS
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Integration Management Part 2
Screencast - Getting to Know the Pipeline

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question