All Forums General
skchintha 13 posts Joined 12/13
18 Feb 2014
error: The format or data contains a bad character

Hi ,
 
When iam trying this sql its throwing an error ."The format or data contains a bad character"
 
I added =" substr(a13.FiscalWeekID,1,4) -1 || substr(a13.FiscalWeekID,5,2) "    function in this sql
 
select    a18.Prod_ClassID  Prod_ClassID,
        sum(a11.EXTENDEDPRICE)  WJXBFS1
    from    REPORTING_V.TransactionLine    a11
        join    REPORTING_V.CALENDAR    a12
          on     (a11.BUSINESSDAYID = a12.BUSINESSDAYID)
        join    REPORTING_V.CALENDAR    a13
          on     (a12.FiscalWeekID = substr(a13.FiscalWeekID,1,4) -1 || substr(a13.FiscalWeekID,5,2) )

        join    REPORTING_V.CALENDARWEEK    a14
          on     (a13.CalendarWeek = a14.CalendarWeek)
        join    REPORTING_V.CALENDARMONTH    a15
          on     (a14.CalendarMonth = a15.CalendarMonth)
        join    REPORTING_V.CALENDARQUARTER    a16
          on     (a15.CalendarQuarter = a16.CalendarQuarter)
        join    REPORTING_V.PRODUCT    a17
          on     (a11.ITEMID = a17.ITEMID)
        join    REPORTING_V.PROD_SUBCLASS    a18
          on     (a17.Prod_ClassID = a18.Prod_ClassID and
        a17.Prod_SubClassID = a18.Prod_SubClassID)
    where    (a16.CalendarYear in (2013)
     and a11.BUSINESSUNITGROUPID in (1, 5, 6, 7, 9)
     and a13.FiscalWeekID in (201335))
    group by    a18.Prod_ClassID
 
 
Please can any body help me out.
 

  1. Tnxs
M.Saeed Khurram 544 posts Joined 09/12
18 Feb 2014

How do you think you can subtract integer from string?
substr(a13.FiscalWeekID,1,4) -1
You can cast it to integer then perform subtraction.
CAST(substr(a13.FiscalWeekID,1,4) AS INTEGER) -1
 

Khurram

Adeel Chaudhry 773 posts Joined 04/08
18 Feb 2014

This error usually occurs when you try to compare integer values with varchar [containing alpha-numeric values].
 
From what it appears to be .... FiscalWeekID must be INT and SUBSTR returns VARCHAR .... first verify if SUBSTR is resulting in only numeric values, and then cast it to INT to get a match without error.
 
HTH!

-- If you are stuck at something .... consider it an opportunity to think anew.

skchintha 13 posts Joined 12/13
19 Feb 2014

Thanks for reply,.
I am new to SQL.
 
I used cast function but its throwing error:
 
select    a18.Prod_ClassID  Prod_ClassID,
        sum(a11.EXTENDEDPRICE)  WJXBFS1
    from    REPORTING_V.TransactionLine    a11
        join    REPORTING_V.CALENDAR    a12
          on     (a11.BUSINESSDAYID = a12.BUSINESSDAYID)
        join    REPORTING_V.CALENDAR    a13
          on     (a12.FiscalWeekID =CAST(substr(a13.FiscalWeekID,1,4) AS INTEGER) -1) ||  cast(substr(a13.FiscalWeekID,5,2) as integer))
        join    REPORTING_V.CALENDARWEEK    a14
          on     (a13.CalendarWeek = a14.CalendarWeek)
        join    REPORTING_V.CALENDARMONTH    a15
          on     (a14.CalendarMonth = a15.CalendarMonth)
        join    REPORTING_V.CALENDARQUARTER    a16
          on     (a15.CalendarQuarter = a16.CalendarQuarter)
        join    REPORTING_V.PRODUCT    a17
          on     (a11.ITEMID = a17.ITEMID)
        join    REPORTING_V.PROD_SUBCLASS    a18
          on     (a17.Prod_ClassID = a18.Prod_ClassID and
        a17.Prod_SubClassID = a18.Prod_SubClassID)
    where    (a16.CalendarYear in (2013)
     and a11.BUSINESSUNITGROUPID in (1, 5, 6, 7, 9)
     and a13.FiscalWeekID in (201335))
    group by    a18.Prod_ClassID
 
 
error :expected some thing between ')' and '||'.
 
If i remove second cast function its working fine.
are  this joins are correct? Actually it should bring me last year week but its not returning any data.
 
Please let me know
Tnxs.
 

Adeel Chaudhry 773 posts Joined 04/08
19 Feb 2014

You have 1 extra ')' at the end of high-lighted text, following is correct:
 
(a12.FiscalWeekID =CAST(substr(a13.FiscalWeekID,1,4) AS INTEGER) -1) ||  cast(substr(a13.FiscalWeekID,5,2) as integer)
 
HTH!

-- If you are stuck at something .... consider it an opportunity to think anew.

skchintha 13 posts Joined 12/13
20 Feb 2014

Its thowing same error .
Any other solutions.
Is that syntax is correct . I tried all the ways.
Please suggest .

Adeel Chaudhry 773 posts Joined 04/08
22 Feb 2014

Can you share the values of the column: a13.FiscalWeekID

-- If you are stuck at something .... consider it an opportunity to think anew.

krishaneesh 140 posts Joined 04/13
27 Feb 2014

Please remove the bracket after -1 as bolded below and it should work fine
on (a12.FiscalWeekID= cast(substr(a13.FiscalWeekId,1,4) as integer)-1||cast(substr(a13.fiscalweekid,5,2) as integer)).
 
But i do not think the join condition suffices as the result of the concatenation gives a different answer than what ideally is expected. From the value for the a13.fiscalweekid=201335 in the where clause the result of the concatenation will be like '2012  35' which i do not think will match. You should add an additional trim condition for both the cast's likebelow.
on (a12.fiscalweekid=trim(cast(substr(a13.fiscalweekid,1,4)as integer)-1)||trim(cast(substr(a13.fiscalweekid,5,2) as integer))).
HTH
 
 

skchintha 13 posts Joined 12/13
19 Mar 2014

Thanks

You must sign in to leave a comment.