Solved

SSIS Syntax

Posted on 2010-09-10
20
271 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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
 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql query 12 72
Need help with a query 3 48
Record open by another user 6 57
Is it possible to replace huge Multiple Spaces with a single space in SQL Server 2 29
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

806 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