error: The format or data contains a bad character

General
Enthusiast

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
8 REPLIES
Enthusiast

Re: error: The format or data contains a bad character

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

Re: error: The format or data contains a bad character

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!

Enthusiast

Re: error: The format or data contains a bad character

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.

Teradata Employee

Re: error: The format or data contains a bad character

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!

Enthusiast

Re: error: The format or data contains a bad character

Its thowing same error .

Any other solutions.

Is that syntax is correct . I tried all the ways.

Please suggest .

Teradata Employee

Re: error: The format or data contains a bad character

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

Enthusiast

Re: error: The format or data contains a bad character

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

Enthusiast

Re: error: The format or data contains a bad character

Thanks