Post reply

Need to change type of result column.

  • January 15, 2009 at 12:22 pm

    #75397

    While running this query:

    SELECT DISTINCT Tools.UUCBIM, Tools.JOBNOF, Tools.FITEMF, Tools.ITDSC, Tools.ENGNO, Tools.ITDSC06, Tools.ESTHRS01, Tools.TOTHRS01, Prog.PROCDE, Tools.OPSTCF03

    FROM

    MAPDBA.ERNI.MAPDBA.PROGRM as Prog INNER JOIN

    (QuoteHrs INNER JOIN PRDQRYXA.ERNI.PRDQRYXA.TOOLQRYOUT as Tools ON QuoteHrs.ITEM=Tools.JOBNOF)

    ON Prog.PROCDE=Tools.UUCBIM

    ORDER BY Tools.UUCBIM, Tools.JOBNOF, Tools.FITEMF

    My one column, Tools.JOBNOF is most ints but there are a few varchars in there. The Tools table is being read from an AS400. SO the error I get is:

    Syntax error converting the varchar value to a column of data type int.

    And if I cast it to varchar like:

    SELECT DISTINCT Tools.UUCBIM, cast(Tools.JOBNOF as varchar(12)), Tools.FITEMF, Tools.ITDSC, Tools.ENGNO, Tools.ITDSC06, Tools.ESTHRS01, Tools.TOTHRS01, Prog.PROCDE, Tools.OPSTCF03

    FROM

    MAPDBA.ERNI.MAPDBA.PROGRM as Prog INNER JOIN

    (QuoteHrs INNER JOIN PRDQRYXA.ERNI.PRDQRYXA.TOOLQRYOUT as Tools ON QuoteHrs.ITEM=Tools.JOBNOF)

    ON Prog.PROCDE=Tools.UUCBIM

    ORDER BY Tools.UUCBIM, Tools.JOBNOF, Tools.FITEMF

    I get an error:

    ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

    What am I doing wrong and how do I fix it?

  • January 15, 2009 at 12:49 pm

    #927637

    your order by has to be the same...simply order by the same casting you did to get teh field itself:

    ORDER BY Tools.UUCBIM, cast(Tools.JOBNOF as varchar(12)), Tools.FITEMF

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 15, 2009 at 1:06 pm

    #927658

    SELECT Distinct Tools.UUCBIM, cast(Tools.JOBNOF as varchar(12)), Tools.FITEMF, Tools.ITDSC, Tools.ENGNO, Tools.ITDSC06, Tools.ESTHRS01, Tools.TOTHRS01, Prog.PROCDE, Tools.OPSTCF03

    FROM

    PRDQRYXA.ERNI.PRDQRYXA.TOOLQRYOUT as Tools inner join

    MAPDBA.ERNI.MAPDBA.PROGRM as Prog on Tools.UUCBIM=Prog.PROCDE INNER JOIN

    QuoteHrs on QuoteHrs.Item=Tools.JOBNOF

    ORDER BY Tools.UUCBIM, cast(Tools.JOBNOF as varchar(12)), Tools.FITEMF

    Still gives me the error about converting varchar to int.

  • January 15, 2009 at 1:25 pm

    #927677

    What is the datatype of QuoteHrs.Item?

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • January 15, 2009 at 1:32 pm

    #927686

    Garadin, you're a genius! That was my whole problem. QuoteHrs.Item was all ints so when I imported it from Access it should have been a varchar, but was turned into ints.

    Thanks for the help, I really appreciate it!

  • January 15, 2009 at 2:19 pm

    #927715

    No problem Andy, glad we could help.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply