Post reply

What is the Query..?

  • October 20, 2011 at 2:09 am

    #261756

    Hi,

    I have Columns..

    IncidentId IsVoid TransNo

    10001 0 200501

    10002 1 200501

    10003 0 200501

    10004 0 200501

    10025 1 200510

    10026 1 200510

    10034 0 200525

    i will pass 'transno' in the where condition of my select query...

    i wanna check whether if any one of the incidentid for that 'TransNo' column has 'isvoid' column as 0....then my select query must be having the isvoid column as '0' for all the incidentid for that particular TransNo...

    how to check this in a select query...?

    pls someone help me...

    Thanks,
    Charmer

  • October 20, 2011 at 2:15 am

    #1397103

    so you want the get the IncidentID for a particual TransNo where IsVoid is 0

  • October 20, 2011 at 3:42 am

    #1397165

    no..no.....i mean that there can be different Incidentid's for a transno....if anyone of the isvoid value is 0 then isvoid must be 0....if all the values are 1 then isvoid must be 1 for the transno that im going to pass....

    Thanks,
    Charmer

  • October 20, 2011 at 3:46 am

    #1397168

    please can you display how you want the results to look like

    should the query return 4 rows for 200501, 0 for 200510 and 1 for 200525 etc

  • October 20, 2011 at 3:50 am

    #1397171

    if i pass transno 200501....then i need this kinda result..

    incidentid isvoid transno

    10001 0 200501

    10002 0 200501

    10003 0 200501

    10004 0 200501

    Thanks,
    Charmer

  • October 20, 2011 at 3:58 am

    #1397174

    you just want to get everything where isvoid is 0 and the transid = transid

    simple select statement

    select col1, col2, col3 from tab1 where col1 = VALUE and col2 = VALUE and col3 = VALUE etc

    replacing col#, tab#, VALUE with whatever you need it to be

  • October 20, 2011 at 4:00 am

    #1397175

    Something like this?

    WITH MinVals AS (

    SELECT

    TransNo,

    MIN(IsVoid) AS IsVoid

    FROM

    MyTable

    )

    SELECT

    t.IncidentId

    ,v.IsVoid

    ,t.TransNo

    FROM

    MyTable t

    JOIN

    MinVals v ON m.TransNo = v.TransNo

    Please post table DDL (CREATE TABLE), sample data (INSERT) and expected results next time. You'll get help a lot quicker and it'll be tested.

    John

  • October 20, 2011 at 4:01 am

    #1397178

    noticed that you want a 0 for the second IsVoid, in that case put a "case" statement around isvoid in the select and say if it is 1 then 0, else 0

  • October 20, 2011 at 4:22 am

    #1397195

    Thanks Guys....I got Some Idea...

    Thanks,
    Charmer

Viewing 9 posts - 1 through 8 (of 8 total)

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