Post reply

Query help urgent

  • October 3, 2008 at 12:35 am

    #880301

    Msg 206, Level 16, State 2, Line 1

    Operand type clash: int is incompatible with image

    while running the query.. which is provided my saby

  • October 3, 2008 at 1:55 am

    #880330

    samsql (9/30/2008)

    Actaul table contains diffrent datatype for image1 to image10 it is image datatype bcoz it contains images

    CREATE TABLE dbo.table2

    (

    col1 VARCHAR(10),

    image1 VARCHAR(10),

    image2 VARCHAR(10),

    image3 VARCHAR(10),

    image4 VARCHAR(10),

    image5 VARCHAR(10),

    image6 VARCHAR(10),

    image7 VARCHAR(10),

    image8 VARCHAR(10),

    image9 VARCHAR(10),

    image10 VARCHAR(10),

    col2 VARCHAR(10))

    ---- the test table with the data given

    INSERT INTO dbo.table2

    (col1,image1,image2,image3,image4,image5,image6,image7,image8,image9,image10,col2)

    SELECT 12345,null,null,null,null,null,null,null,'notnull','notnull','notnull','abc' UNION ALL

    SELECT 12346,'notnull',null,'notnull',null,null,null,null,null,null,'notnull','abc' UNION ALL

    SELECT 12347,null,null,null,'notnull','notnull',null,null,null,'notnull',null,'abc' UNION ALL

    SELECT 12348,null,null,'notnull',null,'notnull','notnull',null,'notnull',null,null,'abc' UNION ALL

    SELECT 12348,null,null,'notnull',null,'notnull','notnull',null,null,null,'notnull','abc'

    12345NULLNULLNULLNULLNULLNULLNULLnotnullnotnullnotnullabc

    12346notnullNULLnotnullNULLNULLNULLNULLNULLNULLnotnullabc

    12347NULLNULLNULLnotnullnotnullNULLNULLNULLnotnullNULLabc

    12348NULLNULLnotnullNULLnotnullnotnullNULLnotnullNULLNULLabc

    12348NULLNULLnotnullNULLnotnullnotnullNULLNULLNULLnotnullabc

    i want a query which gives me output like this ....

    select count(1) from table2 -- output shud be 3

    for 3 rows which contain 3 not null values and

    select count(1) from table2 -- output shud be 2

    for 2 rows which contain 4 not null values

    I think that I understand what you want. Assuming that I did this should do the work for you:

    select sum(case when image1 is null then 0 else 1 end) as CountImage1,

    sum(case when image2 is null then 0 else 1 end) as CountImage2,

    sum(case when image3 is null then 0 else 1 end) as CountImage3,

    sum(case when image4 is null then 0 else 1 end) as CountImage4,

    sum(case when image5 is null then 0 else 1 end) as CountImage5,

    sum(case when image6 is null then 0 else 1 end) as CountImage6,

    sum(case when image7 is null then 0 else 1 end) as CountImage7,

    sum(case when image8 is null then 0 else 1 end) as CountImage8,

    sum(case when image9 is null then 0 else 1 end) as CountImage9,

    sum(case when image10 is null then 0 else 1 end) as CountImage10

    from table2

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • October 3, 2008 at 3:02 am

    #880351

    Nope this not the Query saby and ken has given me the exact query which i want but the issue in actualy senario image col contains datatype image ... i think bcoz of that i m getting orphanned error..

  • October 3, 2008 at 3:35 am

    #880358

    samsql (10/3/2008)

    Nope this not the Query saby and ken has given me the exact query which i want but the issue in actualy senario image col contains datatype image ... i think bcoz of that i m getting orphanned error..

    I’m sorry but it does work with image column as well as with varchar columns. I changed your table so it will include image type instead of varchar and ran it. If you’ll copy and paste it, you’ll be able to see that it doesn’t produce any errors. Can you write more about what you are trying to do so we’ll know why you are getting an error?

    CREATE TABLE dbo.table2

    (

    col1 int,

    image1 image,

    image2 image,

    image3 image,

    image4 image,

    image5 image,

    image6 image,

    image7 image,

    image8 image,

    image9 image,

    image10 image,

    col2 image)

    ---- the test table with the data given

    INSERT INTO dbo.table2

    (col1,image1,image2,image3,image4,image5,image6,image7,image8,image9,image10,col2)

    SELECT 12345,null,null,null,null,null,null,null,convert(image,'0x6E6F746E756C6C'),convert(image,'0x6E6F746E756C6C'),convert(image,'0x6E6F746E756C6C'),convert(image,'0x6E6F746E756C6C') UNION ALL

    SELECT 12346,convert(image,'notnull'),null,convert(image,'notnull'),null,null,null,null,null,null,convert(image,'notnull'),convert(image,'abc') UNION ALL

    SELECT 12347,null,null,null,convert(image,'notnull'),convert(image,'notnull'),null,null,null,convert(image,'notnull'),null,convert(image,'abc') UNION ALL

    SELECT 12348,null,null,convert(image,'notnull'),null,convert(image,'notnull'),convert(image,'notnull'),null,convert(image,'notnull'),null,null,convert(image,'abc') UNION ALL

    SELECT 12348,null,null,convert(image,'notnull'),null,convert(image,'notnull'),convert(image,'notnull'),null,null,null,convert(image,'notnull'),convert(image,'abc')

    select sum(case when image1 is null then 0 else 1 end) as CountImage1,

    sum(case when image2 is null then 0 else 1 end) as CountImage2,

    sum(case when image3 is null then 0 else 1 end) as CountImage3,

    sum(case when image4 is null then 0 else 1 end) as CountImage4,

    sum(case when image5 is null then 0 else 1 end) as CountImage5,

    sum(case when image6 is null then 0 else 1 end) as CountImage6,

    sum(case when image7 is null then 0 else 1 end) as CountImage7,

    sum(case when image8 is null then 0 else 1 end) as CountImage8,

    sum(case when image9 is null then 0 else 1 end) as CountImage9,

    sum(case when image10 is null then 0 else 1 end) as CountImage10

    from table2

    go

    drop table table2

    go

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • October 3, 2008 at 4:53 am

    #880378

    this is not the output i want... u jus check the out put of the query given by ken and saby...

  • October 3, 2008 at 5:08 am

    #880382

    As you only want to count non-null values, use DATALENGTH to convert image to int.

    eg WHEN 1 THEN Image1 becomes WHEN 1 THEN DATALENGTH(Image1) etc

    [Edit]

    or you could alter saby's CASE from

    Case IsNull([image1], 1) When 1 Then 1 Else 0 End Img1

    to something like

    CASE WHEN image1 IS NULL THEN 0 ELSE 1 END AS Img1

    as Adi showed.

    [/Edit]

  • October 3, 2008 at 5:11 am

    #880383

    samsql, could you help us by showing exactly how your output should look, in tabular format?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • October 3, 2008 at 5:51 am

    #880405

    This is what happens when an OP doesn't post the correct requirements.

    Sam... please read and heed the article at the link in my signature. I'm pretty much all done with this thread until that happens. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 3, 2008 at 9:34 am

    #880541

    Jeff Moden (10/3/2008)

    This is what happens when an OP doesn't post the correct requirements.

    Sam... please read and heed the article at the link in my signature. I'm pretty much all done with this thread until that happens. 😉

    I also think that Sam should explain better what he wants and by that I mean that writing something like that:

    select count(1) from table2 -- output shud be 3

    for 3 rows which contain 3 not null values and

    select count(1) from table2 -- output shud be 2

    for 2 rows which contain 4 not null values

    Doesn’t help at all. In any case I think that I understood what Sam wants and I modified my previous select statement to match my new understanding. If this isn’t what Sam is after, then I give up (at least until I get a better explanation about the requirements).

    CREATE TABLE dbo.table2

    (

    col1 int,

    image1 image,

    image2 image,

    image3 image,

    image4 image,

    image5 image,

    image6 image,

    image7 image,

    image8 image,

    image9 image,

    image10 image,

    col2 image)

    ---- the test table with the data given

    INSERT INTO dbo.table2

    (col1,image1,image2,image3,image4,image5,image6,image7,image8,image9,image10,col2)

    SELECT 12345,null,null,null,null,null,null,null,convert(image,'0x6E6F746E756C6C'),convert(image,'0x6E6F746E756C6C'),convert(image,'0x6E6F746E756C6C'),convert(image,'0x6E6F746E756C6C') UNION ALL

    SELECT 12346,convert(image,'notnull'),null,convert(image,'notnull'),null,null,null,null,null,null,convert(image,'notnull'),convert(image,'abc') UNION ALL

    SELECT 12347,null,null,null,convert(image,'notnull'),convert(image,'notnull'),null,null,null,convert(image,'notnull'),null,convert(image,'abc') UNION ALL

    SELECT 12348,null,null,convert(image,'notnull'),null,convert(image,'notnull'),convert(image,'notnull'),null,convert(image,'notnull'),null,null,convert(image,'abc') UNION ALL

    SELECT 12348,null,null,convert(image,'notnull'),null,convert(image,'notnull'),convert(image,'notnull'),null,null,null,convert(image,'notnull'),convert(image,'abc')

    select count(*) as NumOfRows, NumOfColsWithValue

    from

    (select col1, case when image1 is null then 0 else 1 end +

    case when image2 is null then 0 else 1 end +

    case when image3 is null then 0 else 1 end +

    case when image4 is null then 0 else 1 end +

    case when image5 is null then 0 else 1 end +

    case when image6 is null then 0 else 1 end +

    case when image7 is null then 0 else 1 end +

    case when image8 is null then 0 else 1 end +

    case when image9 is null then 0 else 1 end +

    case when image10 is null then 0 else 1 end as NumOfColsWithValue

    from table2) dt

    group by NumOfColsWithValue

    go

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 9 posts - 16 through 23 (of 23 total)

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