Solved

SSIS Syntax

Posted on 2010-09-10
20
250 Views
Last Modified: 2012-05-10
I am trying to write the expression for the following.

([Est Delivery Date]) == 0 || ([Est Delivery Date]) == 0 && ([In DC Based On Allc#]) == 0 ? 123104 : ([In DC Based On Allc#])

I actually need this to happen in my expression(VB Version)
IF EstDeliveryDate <> 0 and  ([In DC Based On Allc#]) = 0 THEN
  EstDeliveryDate
ELSEIF EstDeliveryDate = 0 THEN
  '1234'
ELSE
  ([In DC Based On Allc#])
END IF
0
Comment
Question by:johnnyaction
  • 10
  • 10
20 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 33646306
Try the below exp
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33646310
Try the below exp
[EstDeliveryDate] != 0 && [In DC Based On Allc#] = 0 ? [EstDeliveryDate] : [EstDeliveryDate] = 0 ? '1234' : [In DC Based On Allc#]

Open in new window

0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33646636
It did not work. There must be a syntax issue?

[EstDeliveryDate] != 0 && [In DC Based On Allc#] = 0 ? [EstDeliveryDate] : [EstDeliveryDate] = 0 ? '1234' : [In DC Based On Allc#]

Im not real familiar with the code just yet. Is the elseif portion in this code the same as the code I gave?
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33646877
i should have tested it before posting it..my Bad..try the below SQL

The expression was missing = operator, and single quotes instead of double and there was a conflict of data types around the operator

(DT_STR,20,1252)[EstDeliveryDate] != "0" && (DT_STR,20,1252)[In DC Based On Allc#] == "0" ? (DT_STR,20,1252)[EstDeliveryDate] : (DT_STR,20,1252)[EstDeliveryDate] == "0" ? "1234" : (DT_STR,20,1252)[In DC Based On Allc#]
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33647039
Well I made a mistake too. I just put in any value for "1234" It really needs to be a double precision float[DT_R8]. This is what I should have wrote in my first post

IF EstDeliveryDate <> 0 and  ([In DC Based On Allc#]) = 0 THEN
  EstDeliveryDate
ELSEIF EstDeliveryDate = 0 THEN
  123104
ELSE
  ([In DC Based On Allc#])
END IF

Sorry for the confusion
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33647079
In such a Case try the below Exp without any conversions

EstDeliveryDate != 0 && [In DC Based On Allc#] == 0 ? EstDeliveryDate :EstDeliveryDate == 0 ? 123104 : [In DC Based On Allc#]
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33647295
Unable to parse statment??
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33647392
It works fine for me...
Make sure all the datatypes are the same... That's the reason i Converted all of them to string in previous post's --- ID: 33646877
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33647446
Check it out
EE.docx
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33647684
can you tell me the datatypes of the columns being used in the Exp.. It might just be the that the datatypes differ
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:johnnyaction
ID: 33647767
1 column using precision-float [DT_R8]. That is what it was from the beginning
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33647949
In such a case you will need to convert the other column to Float or this column to match the data type of other column  or both to match datatypes Such as the exp below...

(DT_R8)EstDeliveryDate != 0 && (DT_R8)[In DC Based On Allc#] ==0 ?(DT_R8)EstDeliveryDate :(DT_R8)EstDeliveryDate == 0 ? 1234 : (DT_R8)[In DC Based On Allc#]


Just remove (DT_R8) beside the float column
Derived.jpg
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33647988
I am still red(error). I think it may be some sort of syntax error still. Have you tried moving off that line your on to see if it turns red? Just trying all avenues???
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33648055
It works fine for me..
Did you try changing all the Column Datatypes to string.. I am sure that its because of data type mismatch..
You will need to post your sample data for those two columns or capture the Error msg ..just hover over the  error expression and post the screen shot ..so that i can see the error msg to help you out
Derived.jpg
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33648103
This is what I had originally
([Est Delivery Date]) == 0 || ([Est Delivery Date]) == 0 && ([In DC Based On Allc#]) == 0 ? 123104 : ([In DC Based On Allc#])    With the precision_float.

The datatype never needed to be changed and thats what it needs to be. I just needed the logic to change a little in my original post
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33648173
here ya go...
EE1.docx
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33648219
I posted the logic according to the SQL you posted and i have tested it..which works fine.. The OLD expression you posted above also works fine for me without any syntax errors.. Unless and until you post the exact error msg .. i will not be able to help you further
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 33648283
My bad..dint see your post..ignore my previous post.. What is your column Name

Please check your column Name..I see that you have missed Square brackets for Column --- Est Delivery Date..Add square brackets in case the Column Name have spaces ..

. Try the expression below

[Est Delivery Date] != 0 && [In DC Based On Allc#] == 0 ? [Est Delivery Date] :[Est Delivery Date] == 0 ? 123104 : [In DC Based On Allc#]
0
 
LVL 1

Author Comment

by:johnnyaction
ID: 33649023
Didnt even realize the spelling of the column. That was it. Thanks for your help
0
 
LVL 1

Author Closing Comment

by:johnnyaction
ID: 33649027
Nice work
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now