SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Selection with flag Problem		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zero1de
Yak Posting Veteran

Germany
66 Posts
Posted - 07/09/2012 :  05:15:45  Show Profile  Reply with Quote
Hi,
the switch flag Archiviert=0 also get Row's with status=1. I would like to have only records, the only have Archived=0 status. What is wrong here ?

Field Archive is int.

My SP query to do this here.

CREATE PROCEDURE [dbo].[sp_update_Immo_Meta]
WITH EXEC AS CALLER
AS
UPDATE I
SET Nachname = P.NAME1,
Vorname = P.NAME2,
ENGAGEMENTNR = P.ENGAGEMENT,
FORDERGNR = P.FORDERGNR,
PARTNERID = P.PARTNERID,
GLAEUBIGERNR = P.GLAEUBIGERNR,
GEBURTSDATUM = P.GEBURTSDATUM,
SPARTE = P.BEZSPARTE,
ZUSTSACHB = P.AKTENEIGNER,
HAUPTSCHULDNER = P.HAUPTSCHULDNER,
Fordnr = P.Fordnr,
Dokdatum = coalesce(Dokdatum, getdate()),
ARCHIVIERT = 1
FROM immo I
INNER JOIN TBL_IMMO_PARTNER P
ON i.Fordnr = P.Fordnr or i.partnerid = p.partnerid
WHERE ISNULL(i.archiviert,0)=0
AND i.stapelnummer = 'Tiff_Printer' or i.stapelnummer='Fax/E-Mail'

Grifter
Posting Yak Master

214 Posts
Posted - 07/09/2012 :  07:25:46  Show Profile  Reply with Quote
Hi

Are all of your non 1 values actually null, they may be empty string. So you could put:


WHERE ISNULL(NULLIF(i.archiviert, ''),0)=0


To check just:


select i.archiviert
from table
where i.archiviert = ''
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 07/09/2012 :  09:41:47  Show Profile  Reply with Quote
One issue i see here is you have an open OR condition which will cause all other conditions to bypass in WHERE. i think you need this



....
WHERE ISNULL(i.archiviert,0)=0 
AND (i.stapelnummer = 'Tiff_Printer' or i.stapelnummer='Fax/E-Mail')


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000