Conditional split error - SQL Server Forums

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

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Conditional split error

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-09 : 14:20:05

I am using SSIS and added a conditional split onto the page:

DATEDIFF("dd",Out_DryDte1,GETDATE()) > 60 - 24 ||
DATEDIFF("dd",Out_DryDte2,GETDATE()) > 60 - 24 ||
DATEDIFF("dd",Out_DryDte3,GETDATE()) > 60 - 24 ||
DATEDIFF("dd",Out_DryDte4,GETDATE()) > 60 - 24 ||
DATEDIFF("dd",Out_DryDte5,GETDATE()) > 60 - 24 ||
DATEDIFF("dd",Out_DryDte6,GETDATE()) > 60 - 24 ||
DATEDIFF("dd",Out_DryDte7,GETDATE()) > 60 - 24 ||
DATEDIFF("dd",Out_DryDte7,GETDATE()) > 60 - 24

I got this error message when trying to run it:

"output "ValidRows" (397)" evaluated to NULL, but the "component "Conditional Split" (289)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.

I have my output name called ValidRows and the Default output name DiscardRows in the conditional split. I have the above condition on the same row which is the ValidRows.

I do have some nulls in some of the fields so I think that's what's stopping it from running. How can I fix my conditional split?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-09 : 15:05:05

I have these fields in the database:

Out_DryDte1, Out_DryDte2...Out_DryDte8

I would like to get all of the records out of the database that has a date greater than 60 days if you are going by today's date.

So I added to the Data Flow

1. DB Source

2. Conditional Split
DATEDIFF("dd",Out_DryDte1,GETDATE()) > 60 ||
DATEDIFF("dd",Out_DryDte2,GETDATE()) > 60 ||
DATEDIFF("dd",Out_DryDte3,GETDATE()) > 60 ||
DATEDIFF("dd",Out_DryDte4,GETDATE()) > 60 ||
DATEDIFF("dd",Out_DryDte5,GETDATE()) > 60 ||
DATEDIFF("dd",Out_DryDte6,GETDATE()) > 60 ||
DATEDIFF("dd",Out_DryDte7,GETDATE()) > 60 ||
DATEDIFF("dd",Out_DryDte7,GETDATE()) > 60

3. Two Destinations one for ValidRows and the other for DiscardRows

I do have some nulls listed in some of the fields.

I took off the -24 (I can add that later just need to get the first part fixed).

How would I write an expression/condition for a conditional split that will give me all of the records that are in the fields I mentioned above, to grab all of the records that are over 60 days late (judging by today's date). I hope this makes sense

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-09 : 15:07:25

Can you change your conditional split as below and try

DATEDIFF("dd",(ISNULL(Out_DryDte1) ? 0 :Out_DryDte1),GETDATE()) > 60 - 24 ||
DATEDIFF("dd",(ISNULL(Out_DryDte2) ? 0 :Out_DryDte1),GETDATE()) > 60 - 24 ||
DATEDIFF("dd",(ISNULL(Out_DryDte3) ? 0 :Out_DryDte1),GETDATE()) > 60 - 24 ||
DATEDIFF("dd",(ISNULL(Out_DryDte4) ? 0 :Out_DryDte1),GETDATE()) > 60 - 24 ||
DATEDIFF("dd",(ISNULL(Out_DryDte5) ? 0 :Out_DryDte1),GETDATE()) > 60 - 24 ||
DATEDIFF("dd",(ISNULL(Out_DryDte6) ? 0 :Out_DryDte1),GETDATE()) > 60 - 24 ||
DATEDIFF("dd",(ISNULL(Out_DryDte7) ? 0 :Out_DryDte1),GETDATE()) > 60 - 24 ||
DATEDIFF("dd",(ISNULL(Out_DryDte1) ? 0 :Out_DryDte7),GETDATE()) > 60 - 24

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-09 : 15:13:12

Or...in your DB source, instead of choosing table/view...choose SQL statement and handle as below...

SELECT * from yourtable WHERE
DATEDIFF(dd,Out_DryDte1,GETDATE()) > 60 OR ....AND SO ON.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-09 : 15:21:38

Tried that and it's not working still red I'm getting:

Error at Conditional Split getting diaries over 60 days [Conditonal S;it [289]]: The data types "DT_14" and "DT-DBTIMESTAMP" are incompatible for the conditional operator. The operand types cannot be implicitly cast into compatible types for the conditional operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

This is the conditional split now:
DATEDIFF("dd",(ISNULL(Out_DryDte1) ? 0 :Out_DryDte1),GETDATE()) > 60 - 24 ||
DATEDIFF("dd",(ISNULL(Out_DryDte2) ? 0 :Out_DryDte2),GETDATE()) > 60 - 24 ||
DATEDIFF("dd",(ISNULL(Out_DryDte3) ? 0 :Out_DryDte3),GETDATE()) > 60 - 24 ||
DATEDIFF("dd",(ISNULL(Out_DryDte4) ? 0 :Out_DryDte4),GETDATE()) > 60 - 24 ||
DATEDIFF("dd",(ISNULL(Out_DryDte5) ? 0 :Out_DryDte5),GETDATE()) > 60 - 24 ||
DATEDIFF("dd",(ISNULL(Out_DryDte6) ? 0 :Out_DryDte6),GETDATE()) > 60 - 24 ||
DATEDIFF("dd",(ISNULL(Out_DryDte7) ? 0 :Out_DryDte7),GETDATE()) > 60 - 24 ||
DATEDIFF("dd",(ISNULL(Out_DryDte8) ? 0 :Out_DryDte8),GETDATE()) > 60 - 24

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-09 : 15:29:55

Well I ran this:

DATEDIFF("dd",Out_DryDte1,GETDATE()) > 60

Which worked out okay. This field (OUt_DryDte1) has no nulls in the db.

When I added a different field with nulls

DATEDIFF("dd",Out_DryDte3,GETDATE()) > 60 I got this:

[Conditional Split [289]] Error: The expression "DATEDIFF("dd",Out_DryDte3,GETDATE()) > 60" on "output "validRows" (3469)" evaluated to NULL, but the "component "Conditional Split" (289)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-09 : 15:31:00

Okay just saw your reply. I will just use a SQL Statement like you suggested it's easier. Thanks!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-09 : 15:33:13

One last question. How would I add this in the sql statement?

SELECT * from yourtable WHERE
DATEDIFF(dd,Out_DryDte1,GETDATE()) > 60 - 14?

I want the records that are greater than 60 days but - 14 days from today's date? Does that make sense?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-09 : 15:50:05

No..Actually I dont get it. What does -14 days from today's date mean? doesn't datediff already hanlde that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 04:34:45

quote:
Originally posted by JJ297

One last question. How would I add this in the sql statement?

SELECT * from yourtable WHERE
DATEDIFF(dd,Out_DryDte1,GETDATE()) > 60 - 14?

I want the records that are greater than 60 days but - 14 days from today's date? Does that make sense?

for that what you need is


SELECT * from yourtable WHERE 
Out_DryDte1 > DATEADD(dd,-60,GETDATE())
AND Out_DryDte1 < DATEADD(dd,-14,GETDATE())

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-10 : 09:03:58

Thanks for getting back to me I left for the day.

I hope this is more clear:

A report is run weekly on Friday's. The report I am working on was run Feb. 27th I want to go back 60 days from when the report was run which will take me to Dec. 27th. I want the query to give me all of those records that are in the database before Dec. 27th's date.

visakh16 what you have listed will that get me the results?

I also have to do this for 8 Diaries called Out_DryDte1, Out_DryDte2...Out_DryDte8

Thanks

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-10 : 09:12:33

Okay found out from the mainframe programmer I don't need GetDate.

This is to clarify more as to what I need:

How would I set up a query to get a specific date when the report was run such as 2/27/2009 and then go back 60 days from 2/27/2009 and grab all of those records that are before 12/27/2008?

I want to search all of these fields: Out_DryDte1, Out_DryDte2, Out_DryDte3...Out_DryDt4 for those records before 12/27/2008.

Sorry for the confusion got bad information.

visakh16

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-10 : 09:16:32

Ok...If you are running the report today...then you need this..

SELECT * from yourtable WHERE
DATEDIFF(dd,Out_DryDte1,GETDATE()) > 73 and so on...

because SELECT DATEDIFF (dd,'12/27/2008',getdate()) gave me 73. Since you need all records before Dec 27th, you need to use > 73.

or..you can simply use..

SELECT * from yourtable WHERE
DATEDIFF(dd,'12/27/2008',Out_DryDte1) > 0 and so on...

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-10 : 09:35:19

Perfect this is what I need. Much easier to understand now.

Would I add the next record with a comma, and, or, in between?

DATEDIFF(dd,Out_DryDte1,GETDATE()) > 73, DATEDIFF, and or(dd,Out_DryDte2,GETDATE()) > 73

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-10 : 09:37:48

If any one of the dates needs to be older than Dec 27th use "OR", if all the dates need to be older than Dec 27th use "AND".

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-03-10 : 09:51:55

Thanks so much. I'm all set. Until again...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources