DataStage not identifying Varchar

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
sagar deshmukh
Participant
Posts: 103
Joined: Fri Jan 18, 2008 12:55 am
Location: chennai

DataStage not identifying Varchar

Post by sagar deshmukh »

Hi i have a rounine as given below




DEFFUN ConvDateCYYDDDtoYYYYMMDD(A) Calling "DSU.ConvDateCYYDDDtoYYYYMMDD"
DEFFUN ConvDateToYYYYMMDD(A) Calling "DSU.ConvDateToYYYYMMDD"
DEFFUN ConvtSignOverPunch(A) Calling "DSU.ConvtSignOverPunch"

******
*Init*
******

RECORD_LENGTH = 100
REJECT = "REJECT-"
CODE = 0
Ans = ""
IC4X = Arg1

*********
*Process*
*********
If Len(IC4X) < RECORD_LENGTH Or Len(IC4X) > RECORD_LENGTH Then
GoTo Reject
End Else
GoTo ProcessIC4X
End

ProcessIC4X:
*** Get Country_Code - 1 ***
Country_Code = Trim(IC4X[1,3])
If (IsNull(Country_Code) Or (Country_Code <> "US" And Country_Code <> "GU" And Country_Code <> "CA")) Then
CODE = 1
GoTo Reject
End
Ans = Ans:Country_Code:"|"

*** Get Item_Nbr - 2 ***
Item_Nbr = Trim(IC4X[26,6])
If Num(Item_Nbr) = @FALSE Then
CODE = 2
GoTo Reject
End
Ans = Ans:Item_Nbr:"|"

*** Get Loc_Nbr - 3 ***
Loc_Nbr = Trim(IC4X[5,5])
If Num(Loc_Nbr) = @FALSE Then
CODE = 3
GoTo Reject
End
Ans = Ans:Loc_Nbr:"|"



Reject:
Return ("<":REJECT:CODE:">")


here for Loc_Nbr - 3 if we give value as 'e4545' it identifies as varchar and aborts....but if we give 45e45 then it doesnt identify it as varchar....
can you tell me whats the problem......



bt same piece of code for Get Item_Nbr - 2 is working fine,....
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Aborts how?
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
sagar deshmukh
Participant
Posts: 103
Joined: Fri Jan 18, 2008 12:55 am
Location: chennai

Post by sagar deshmukh »

My datastage job will read the REJECT:CODE generated then it will abort if REJECT:CODE is available.
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Re: DataStage not identifying Varchar

Post by chulett »

FYI - always use the "Code" tags when posting code or anything else that requires the preservation of whitespace, otherwise the forum software will automatically collapse it down. Helps make it actually readable, assuming there was proper indentation done in the first place, that is.
sagar deshmukh wrote:Hi i have a rounine as given below

Code: Select all

      DEFFUN ConvDateCYYDDDtoYYYYMMDD(A) Calling "DSU.ConvDateCYYDDDtoYYYYMMDD"
      DEFFUN ConvDateToYYYYMMDD(A) Calling "DSU.ConvDateToYYYYMMDD"
      DEFFUN ConvtSignOverPunch(A) Calling "DSU.ConvtSignOverPunch"

******
*Init*
******

      RECORD_LENGTH = 100
      REJECT = "REJECT-"
      CODE = 0
      Ans = ""
      IC4X = Arg1

*********
*Process*
*********
      If Len(IC4X) < RECORD_LENGTH Or Len(IC4X) > RECORD_LENGTH Then
         GoTo Reject
      End Else
         GoTo ProcessIC4X
      End

ProcessIC4X:
      *** Get Country_Code - 1 ***
      Country_Code = Trim(IC4X[1,3])
      If (IsNull(Country_Code) Or (Country_Code <> "US" And Country_Code <> "GU" And Country_Code <> "CA")) Then
         CODE = 1
         GoTo Reject
      End
      Ans = Ans:Country_Code:"|"

      *** Get Item_Nbr - 2 ***
      Item_Nbr = Trim(IC4X[26,6])
      If Num(Item_Nbr) = @FALSE Then
         CODE = 2
         GoTo Reject
      End
      Ans = Ans:Item_Nbr:"|"

      *** Get Loc_Nbr - 3 ***
      Loc_Nbr = Trim(IC4X[5,5])
      If Num(Loc_Nbr) = @FALSE Then
         CODE = 3
         GoTo Reject
      End
      Ans = Ans:Loc_Nbr:"|"

  

Reject:
      Return ("<":REJECT:CODE:">")

here for Loc_Nbr - 3 if we give value as 'e4545' it identifies as varchar and aborts....but if we give 45e45 then it doesnt identify it as varchar....
can you tell me whats the problem......



bt same piece of code for Get Item_Nbr - 2 is working fine,....
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Can you please post some examples of the full values being passed as Arg1 here, ideally ones that work and ones that don't.

Edited to add: Never mind - "but if we give 45e45 then it doesnt identify it as varchar". That's because 45e45 is a number - in scientific notation.
Last edited by chulett on Wed Dec 31, 2008 12:16 pm, edited 1 time in total.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
sagar deshmukh
Participant
Posts: 103
Joined: Fri Jan 18, 2008 12:55 am
Location: chennai

Post by sagar deshmukh »

Thanks i will keep in mind...but any solution for this?
Top
kandyshandy
Participant
Posts: 597
Joined: Fri Apr 29, 2005 6:19 am
Location: Singapore

Post by kandyshandy »

Is it only happening to cases where 'e' is present in between numbers?
Kandy
_________________
Try and Try again…You will succeed atlast!!
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Yup, edited my previous post to include the "why".
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
sagar deshmukh
Participant
Posts: 103
Joined: Fri Jan 18, 2008 12:55 am
Location: chennai

Post by sagar deshmukh »

here is my fullcode......
FunctionName = "ParseFE1X"

********************
* Define Functions *
********************

DEFFUN ConvDateCYYDDDtoYYYYMMDD(A) Calling "DSU.ConvDateCYYDDDtoYYYYMMDD"
DEFFUN ConvDateToYYYYMMDD(A) Calling "DSU.ConvDateToYYYYMMDD"
DEFFUN ConvtSignOverPunch(A) Calling "DSU.ConvtSignOverPunch"

******
*Init*
******

RECORD_LENGTH = 100
REJECT = "REJECT-"
CODE = 0
Ans = ""
IC4X = Arg1

*********
*Process*
*********
If Len(IC4X) < RECORD_LENGTH Or Len(IC4X) > RECORD_LENGTH Then
GoTo Reject
End Else
GoTo ProcessIC4X
End

ProcessIC4X:
*** Get Country_Code - 1 ***
Country_Code = Trim(IC4X[1,3])
If (IsNull(Country_Code) Or (Country_Code <> "US" And Country_Code <> "GU" And Country_Code <> "CA")) Then
CODE = 1
GoTo Reject
End
Ans = Ans:Country_Code:"|"

*** Get Item_Nbr - 2 ***
Item_Nbr = Trim(IC4X[26,6])
If Num(Item_Nbr) = @FALSE Then
CODE = 2
GoTo Reject
End
Ans = Ans:Item_Nbr:"|"

*** Get Loc_Nbr - 3 ***
Loc_Nbr = Trim(IC4X[5,5])
If Num(Loc_Nbr) = @FALSE Then
CODE = 3
GoTo Reject
End
Ans = Ans:Loc_Nbr:"|"

*** Get Owner_Flag - 4 ***
Owner_Flag = Trim(IC4X[4,1])
If (IsNull(Owner_Flag) Or Owner_Flag = "") Then
CODE = 4
GoTo Reject
End
Ans = Ans:Owner_Flag:"|"

*** Get Txn_Date, Get Txn_Time, and concatenate them - 5 ***
CYYDDD = Trim(IC4X[10,6])
HHMMSS = Trim(IC4X[37,6])
HH = Trim(IC4X[37,2])
MM = Trim(IC4X[39,2])
SS = Trim(IC4X[41,2])
COLON = ":"
If ((Num(CYYDDD) = @FALSE) Or (IsNull(CYYDDD)) Or (CYYDDD="") Or (Num(HHMMSS) = @FALSE) Or (IsNull(HHMMSS)) Or (HHMMSS="") Or (CYYDDD = 000000) Or (CYYDDD = 100000)) Then
CODE = 5
GoTo Reject
End
Txn_Date = ConvDateCYYDDDtoYYYYMMDD(CYYDDD)
Txn_Time = OConv(IConv(HH:COLON:MM:COLON:SS,"MTS"),"MTS")
Txn_Date = Txn_Date:" ":Txn_Time
If Txn_Date = @NULL Then
CODE = 5.5
GoTo Reject
End
Ans = Ans:Txn_Date:"|"

*** Get Txn_Code - 6 ***
Txn_Code = Trim(IC4X[16,4])
If (IsNull(Txn_Code) Or Txn_Code = "" Or (Txn_Code <> "FE12" And Txn_Code <> "FE13")) Then
CODE = 6
GoTo Reject
End
Ans = Ans:Txn_Code:"|"

*** Get Txn_Qty - 7 ***
Txn_Qty = Trim(IC4X[20,6])
If (IsNull(Txn_Qty) Or Txn_Qty = "") Then
CODE = 7
GoTo Reject
End
Txn_Qty = ConvtSignOverPunch(Txn_Qty)
If Num(Txn_Qty) = @FALSE Then
CODE = 7.5
GoTo Reject
End
Ans = Ans:Txn_Qty * 1:"|"

*** Get Txn_Amt - 8 ***
Txn_Amt = Trim(IC4X[54,9])
If (IsNull(Txn_Amt) Or Txn_Amt = "") Then
CODE = 8
GoTo Reject
End

Txn_Amt = ConvtSignOverPunch(Txn_Amt)
If Num(Txn_Amt) = @FALSE Then
CODE = 8.5
GoTo Reject
End
Ans = Ans:Txn_Amt * 0.01:"|"

*** Get Revenue_Group - 9 ***
Revenue_Group = Trim(IC4X[32,2])
If ((IsNull(Revenue_Group)) Or (Revenue_Group = "") Or (Revenue_Group = 00)) Then
CODE = 9
GoTo Reject
End
Ans = Ans:Revenue_Group:"|"

*** Get Usage - 10 ***
Usage = Trim(IC4X[35,1])
If (IsNull(Usage) Or Usage = "") Then
CODE = 10
GoTo Reject
End
Ans = Ans:Usage:"|"


*** Get Credit_Type - 11 ***
**Credit_Type = Trim(IC4X[93,3])
**If (IsNull(Credit_Type) Or Credit_Type= "" And Txn_Code = "IC41") Then
**CODE = 11
** GoTo Reject
**End
**Ans = Ans:Credit_Type:"|"



*** Get Cust_id - 11 ***
Cust_id = Trim(IC4X[43,11])
If (IsNull(Cust_id) Or Cust_id = "") Then
CODE = 11
GoTo Reject
End
Ans = Ans:Cust_id:"|"


*** Get Sales_flag - 12 ***
Sales_flag = Trim(IC4X[79,1])
If (IsNull(Sales_flag) Or Sales_flag = "") Then
CODE = 12
GoTo Reject
End
Ans = Ans:Sales_flag:"|"

*** Get Sales_flag - 13 ***
Sub_flag = Trim(IC4X[81,1])
If (IsNull(Sub_flag) Or Sub_flag = "") Then
CODE = 14
GoTo Reject
End
Ans = Ans:Sub_flag:"|"



*** Get Extract_Dts - 14 ***
Extract_Dts = Trim(RIGHT(IC4X,19))
Ans = Ans:Extract_Dts
Return (Ans)



Reject:
Return ("<":REJECT:CODE:">")





the input value you can use is....
US C01e02107365FE1200001 14071330WP216581729283432854000000125 082910829908309S102008-12-12 00:00:00
and
US Ce1102107365FE1200001 14071330WP216581729283432854000000125 082910829908309S102008-12-12 00:00:00

Code: Select all

[list][img][/img][/list]

Code: Select all

[list][/list]

Code: Select all

Last edited by sagar deshmukh on Wed Dec 31, 2008 12:41 pm, edited 2 times in total.
Top
sagar deshmukh
Participant
Posts: 103
Joined: Fri Jan 18, 2008 12:55 am
Location: chennai

Post by sagar deshmukh »

chullet...thanks alot i got ur point.
Top
kandyshandy
Participant
Posts: 597
Joined: Fri Apr 29, 2005 6:19 am
Location: Singapore

Post by kandyshandy »

If you are sure that you won't get a $ in your data, then replace all 'e' s with '$' and then replace them back when/where you need.
Kandy
_________________
Try and Try again…You will succeed atlast!!
Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

So much for keeping the use of code tags in mind. Go back, edit the post and add them, please. For grins if nothing else.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
sagar deshmukh
Participant
Posts: 103
Joined: Fri Jan 18, 2008 12:55 am
Location: chennai

Post by sagar deshmukh »

sagar deshmukh wrote:here is my fullcode......
FunctionName = "ParseFE1X"

********************
* Define Functions *
********************

DEFFUN ConvDateCYYDDDtoYYYYMMDD(A) Calling "DSU.ConvDateCYYDDDtoYYYYMMDD"
DEFFUN ConvDateToYYYYMMDD(A) Calling "DSU.ConvDateToYYYYMMDD"
DEFFUN ConvtSignOverPunch(A) Calling "DSU.ConvtSignOverPunch"

******
*Init*
******

RECORD_LENGTH = 100
REJECT = "REJECT-"
CODE = 0
Ans = ""
IC4X = Arg1

*********
*Process*
*********
If Len(IC4X) < RECORD_LENGTH Or Len(IC4X) > RECORD_LENGTH Then
GoTo Reject
End Else
GoTo ProcessIC4X
End

ProcessIC4X:
*** Get Country_Code - 1 ***
Country_Code = Trim(IC4X[1,3])
If (IsNull(Country_Code) Or (Country_Code <> "US" And Country_Code <> "GU" And Country_Code <> "CA")) Then
CODE = 1
GoTo Reject
End
Ans = Ans:Country_Code:"|"

*** Get Item_Nbr - 2 ***
Item_Nbr = Trim(IC4X[26,6])
If Num(Item_Nbr) = @FALSE Then
CODE = 2
GoTo Reject
End
Ans = Ans:Item_Nbr:"|"

*** Get Loc_Nbr - 3 ***
Loc_Nbr = Trim(IC4X[5,5])
If Num(Loc_Nbr) = @FALSE Then
CODE = 3
GoTo Reject
End
Ans = Ans:Loc_Nbr:"|"

*** Get Owner_Flag - 4 ***
Owner_Flag = Trim(IC4X[4,1])
If (IsNull(Owner_Flag) Or Owner_Flag = "") Then
CODE = 4
GoTo Reject
End
Ans = Ans:Owner_Flag:"|"

*** Get Txn_Date, Get Txn_Time, and concatenate them - 5 ***
CYYDDD = Trim(IC4X[10,6])
HHMMSS = Trim(IC4X[37,6])
HH = Trim(IC4X[37,2])
MM = Trim(IC4X[39,2])
SS = Trim(IC4X[41,2])
COLON = ":"
If ((Num(CYYDDD) = @FALSE) Or (IsNull(CYYDDD)) Or (CYYDDD="") Or (Num(HHMMSS) = @FALSE) Or (IsNull(HHMMSS)) Or (HHMMSS="") Or (CYYDDD = 000000) Or (CYYDDD = 100000)) Then
CODE = 5
GoTo Reject
End
Txn_Date = ConvDateCYYDDDtoYYYYMMDD(CYYDDD)
Txn_Time = OConv(IConv(HH:COLON:MM:COLON:SS,"MTS"),"MTS")
Txn_Date = Txn_Date:" ":Txn_Time
If Txn_Date = @NULL Then
CODE = 5.5
GoTo Reject
End
Ans = Ans:Txn_Date:"|"

*** Get Txn_Code - 6 ***
Txn_Code = Trim(IC4X[16,4])
If (IsNull(Txn_Code) Or Txn_Code = "" Or (Txn_Code <> "FE12" And Txn_Code <> "FE13")) Then
CODE = 6
GoTo Reject
End
Ans = Ans:Txn_Code:"|"

*** Get Txn_Qty - 7 ***
Txn_Qty = Trim(IC4X[20,6])
If (IsNull(Txn_Qty) Or Txn_Qty = "") Then
CODE = 7
GoTo Reject
End
Txn_Qty = ConvtSignOverPunch(Txn_Qty)
If Num(Txn_Qty) = @FALSE Then
CODE = 7.5
GoTo Reject
End
Ans = Ans:Txn_Qty * 1:"|"

*** Get Txn_Amt - 8 ***
Txn_Amt = Trim(IC4X[54,9])
If (IsNull(Txn_Amt) Or Txn_Amt = "") Then
CODE = 8
GoTo Reject
End

Txn_Amt = ConvtSignOverPunch(Txn_Amt)
If Num(Txn_Amt) = @FALSE Then
CODE = 8.5
GoTo Reject
End
Ans = Ans:Txn_Amt * 0.01:"|"

*** Get Revenue_Group - 9 ***
Revenue_Group = Trim(IC4X[32,2])
If ((IsNull(Revenue_Group)) Or (Revenue_Group = "") Or (Revenue_Group = 00)) Then
CODE = 9
GoTo Reject
End
Ans = Ans:Revenue_Group:"|"

*** Get Usage - 10 ***
Usage = Trim(IC4X[35,1])
If (IsNull(Usage) Or Usage = "") Then
CODE = 10
GoTo Reject
End
Ans = Ans:Usage:"|"


*** Get Credit_Type - 11 ***
**Credit_Type = Trim(IC4X[93,3])
**If (IsNull(Credit_Type) Or Credit_Type= "" And Txn_Code = "IC41") Then
**CODE = 11
** GoTo Reject
**End
**Ans = Ans:Credit_Type:"|"



*** Get Cust_id - 11 ***
Cust_id = Trim(IC4X[43,11])
If (IsNull(Cust_id) Or Cust_id = "") Then
CODE = 11
GoTo Reject
End
Ans = Ans:Cust_id:"|"


*** Get Sales_flag - 12 ***
Sales_flag = Trim(IC4X[79,1])
If (IsNull(Sales_flag) Or Sales_flag = "") Then
CODE = 12
GoTo Reject
End
Ans = Ans:Sales_flag:"|"

*** Get Sales_flag - 13 ***
Sub_flag = Trim(IC4X[81,1])
If (IsNull(Sub_flag) Or Sub_flag = "") Then
CODE = 14
GoTo Reject
End
Ans = Ans:Sub_flag:"|"



*** Get Extract_Dts - 14 ***
Extract_Dts = Trim(RIGHT(IC4X,19))
Ans = Ans:Extract_Dts
Return (Ans)



Reject:
Return ("<":REJECT:CODE:">")





the input value you can use is....
US C01e02107365FE1200001 14071330WP216581729283432854000000125 082910829908309S102008-12-12 00:00:00
and
US Ce1102107365FE1200001 14071330WP216581729283432854000000125 082910829908309S102008-12-12 00:00:00

Code: Select all

[list][img][/img][/list]

Code: Select all

[list][/list]

Code: Select all

Top
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Missed it by *that* much.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Top
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:
Contact ray.wurlod

Post by ray.wurlod »

Code tags, not Quote tags!
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Top
Post Reply

Return to “IBM<sup>®</sup> Infosphere DataStage Server Edition”