Results 1 to 10 of 10

Thread: Sql Plz Help!!!

  1. #1
    Join Date
    Jun 2003
    Posts
    34

    Unhappy Unanswered: Sql Plz Help!!!

    We have a table which looks like the following...

    Rep_det
    =========

    Job_num Rep_num Dim1 Dim2 Dim3 Met1 Met2 Met3
    ------- -------- ---- ---- ---- ---- ---- ----
    100 1 AK 200 300
    100 1 CA 500 200
    100 1 NY 1000 350
    100 1 MA 750 250
    200 1 CA 250 350
    200 1 NY 1000 700
    200 1 MA 500 100
    200 1 NJ 750 1100

    WE WANT A REPORT AS SHOWN BELOW...

    STATE CID SID PRIOR_CID PRIOR_SID
    ----- --- --- --------- ---------
    AK 200 300
    cA 250 350 500 200
    NY 1000 700 1000 350
    MA 500 100 750 250
    NJ 750 1000

    If you would notice that AK is not present in current "Job" so current "CID" / "SID" is NULL but it was present in the previous "Job" so there are entries in PRIOR_CID / PRIOR_SID.

    Its opposite for NJ.

    Could anyone help me out with a query for the same. Would really appreciate a help. I have attched the above so that you have a look.

    Thanks.
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Seems nobody does any research anymore.

    This question about 'pivot' tables has been answered exhaustively in many forums including this one.

    Try: http://asktom.oracle.com/

    http://asktom.oracle.com/pls/ask/f?p...:766825833740,
    http://asktom.oracle.com/pls/ask/f?p...:156812348067,
    http://asktom.oracle.com/pls/ask/f?p...:124812348063,
    http://asktom.oracle.com/pls/ask/f?p...:419593546543,
    ... and 118 more ...

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2003
    Posts
    34
    I tried onto doing Pivot already, but I guess am not getting what I should could you please see my code and let me know where exactly I am going wrong ??

    select dim1 STATE,
    max(decode(rn,1,dim2)) CID,
    max(decode(rn,2,dim2)) PRIOR_CID
    from ( select dim1, dim2,
    row_number() over (partition by dim1 order by dim2 ) rn
    from rep_det )
    group by dim1

    Originally posted by LKBrwn_DBA
    Seems nobody does any research anymore.

    This question about 'pivot' tables has been answered exhaustively in many forums including this one.

    Try: http://asktom.oracle.com/

    http://asktom.oracle.com/pls/ask/f?p...:766825833740,
    http://asktom.oracle.com/pls/ask/f?p...:156812348067,
    http://asktom.oracle.com/pls/ask/f?p...:124812348063,
    http://asktom.oracle.com/pls/ask/f?p...:419593546543,
    ... and 118 more ...


  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    Ok, try this:

    Code:
    
    Select dim1 STATE
    ,max(decode(rn,2,dim2,null)) CID
    ,max(decode(rn,2,dim3,null)) SID
    ,max(decode(rn,1,dim2,null)) PRIOR_CID
    ,max(decode(rn,1,dim3,null)) PRIOR_SID
    from (
    Select a.dim1, dim2, dim3
    ,row_number() OVER (partition by a.dim1 ORDER BY rep_num desc) rn
    from rep_det b, (select distinct dim1 from rep_det) a
    where b.dim1(+) = a.dim1)
    group by dim1
    /
    
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Jun 2003
    Posts
    34
    I think still we are not getting what we want. If you see the result closely u'll still see that NJ which has JOB 200 and is the latest record with no prior record still we have it in the PRIOR_CID column, which obviously is incorrect. The rule of thumb is IF A RECORD DOES HAVE A PRIOR RUN JOB KEEP THE VALUE NULL IN PRIOR ELSE POPULATE, AND VICE VERSA.
    In the query that you have suggested the inner query results RN to be 1 for both NJ and AK, and I guess thats the problem.
    I appreciate all the help.

    Thanks

    Originally posted by LKBrwn_DBA
    Ok, try this:

    Code:
    
    Select dim1 STATE
    ,max(decode(rn,2,dim2,null)) CID
    ,max(decode(rn,2,dim3,null)) SID
    ,max(decode(rn,1,dim2,null)) PRIOR_CID
    ,max(decode(rn,1,dim3,null)) PRIOR_SID
    from (
    Select a.dim1, dim2, dim3
    ,row_number() OVER (partition by a.dim1 ORDER BY rep_num desc) rn
    from rep_det b, (select distinct dim1 from rep_det) a
    where b.dim1(+) = a.dim1)
    group by dim1
    /
    

  6. #6
    Join Date
    Jun 2003
    Posts
    34
    I think still we are not getting what we want. If you see the result closely u'll still see that NJ which has JOB 200 and is the latest record with no prior record still we have it in the PRIOR_CID column, which obviously is incorrect. The rule of thumb is IF A RECORD DOES HAVE A PRIOR RUN JOB KEEP THE VALUE NULL IN PRIOR ELSE POPULATE, AND VICE VERSA.
    In the query that you have suggested the inner query results RN to be 1 for both NJ and AK, and I guess thats the problem.
    I appreciate all the help.

    Thanks

    Originally posted by LKBrwn_DBA
    Ok, try this:

    Code:
    
    Select dim1 STATE
    ,max(decode(rn,2,dim2,null)) CID
    ,max(decode(rn,2,dim3,null)) SID
    ,max(decode(rn,1,dim2,null)) PRIOR_CID
    ,max(decode(rn,1,dim3,null)) PRIOR_SID
    from (
    Select a.dim1, dim2, dim3
    ,row_number() OVER (partition by a.dim1 ORDER BY rep_num desc) rn
    from rep_det b, (select distinct dim1 from rep_det) a
    where b.dim1(+) = a.dim1)
    group by dim1
    /
    

  7. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    Ok, how about this:

    Code:
    
    SQL>Select dim1 STATE
      2  ,max(decode(job_num,200,dim2,null)) CID
      3  ,max(decode(job_num,200,dim3,null)) SID
      4  ,max(decode(job_num,100,dim2,null)) PRIOR_CID
      5  ,max(decode(job_num,100,dim3,null)) PRIOR_SID
      6  from rep_det
      7  group by dim1
      8  /
    
    ST        CID        SID  PRIOR_CID  PRIOR_SID
    -- ---------- ---------- ---------- ----------
    AK                              200        300
    CA        250        350        500        200
    MA        500        100        750        250
    NJ        750       1100
    NY       1000        700       1000        350
    
    5 rows selected.
    
    SQL>
    
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    Join Date
    Jun 2003
    Posts
    34

    Thumbs up

    This is great/fantastic... but what if I dunno whats my current/prior job number is or I have to find out along with this query ?
    Originally posted by LKBrwn_DBA
    Ok, how about this:

    Code:
    
    SQL>Select dim1 STATE
      2  ,max(decode(job_num,200,dim2,null)) CID
      3  ,max(decode(job_num,200,dim3,null)) SID
      4  ,max(decode(job_num,100,dim2,null)) PRIOR_CID
      5  ,max(decode(job_num,100,dim3,null)) PRIOR_SID
      6  from rep_det
      7  group by dim1
      8  /
    
    ST        CID        SID  PRIOR_CID  PRIOR_SID
    -- ---------- ---------- ---------- ----------
    AK                              200        300
    CA        250        350        500        200
    MA        500        100        750        250
    NJ        750       1100
    NY       1000        700       1000        350
    
    5 rows selected.
    
    SQL>
    

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    OK look at this:

    Code:
    
    Select dim1 STATE
    ,max(decode(job_num,maxj,dim2,null)) CID
    ,max(decode(job_num,maxj,dim3,null)) SID
    ,max(decode(job_num,minj,dim2,null)) PRIOR_CID
    ,max(decode(job_num,minj,dim3,null)) PRIOR_SID
    from rep_det a,
       ( select min(job_num) minj, max(job_num) maxj from rep_det) b
    group by dim1
    /
    
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  10. #10
    Join Date
    Jun 2003
    Posts
    34
    This excellant. Bulls Eye. Thanks a lot. I really appreciate it.

    Originally posted by LKBrwn_DBA
    OK look at this:

    Code:
    
    Select dim1 STATE
    ,max(decode(job_num,maxj,dim2,null)) CID
    ,max(decode(job_num,maxj,dim3,null)) SID
    ,max(decode(job_num,minj,dim2,null)) PRIOR_CID
    ,max(decode(job_num,minj,dim3,null)) PRIOR_SID
    from rep_det a,
       ( select min(job_num) minj, max(job_num) maxj from rep_det) b
    group by dim1
    /
    

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •