Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: data inconsistency

    Hi everyone please help me!

    I have a query like this:
    Select /*+choose */
    P.Agent_Id,
    P.Product_Type_Grp_Id,
    P.Product_Subtype_Grp_Id,
    P.Denom,
    p.Created_By,
    --(SELECT LOCALTIMESTAMP FROM DUAL),
    p.Updated_By,
    --(SELECT LOCALTIMESTAMP FROM DUAL),
    NULL,
    Null,
    --SEQ_AGENT_PRODUCT_ID.NEXTVAL,
    p.Subs_Brand
    From Tbl_Agent A, Tbl_Agent_Product P
    Where A.Agent_Id != 258
    And A.Agent_Role In (2,3)

    Start With A.Agent_Id = 258
    Connect By Nocycle A.Status_Cd = 'A'
    And A.Parent_Agent_Id = Prior A.Agent_Id;

    this is in a stored proc being called by a job. This runs according to my seniors, 5 hours. My question is on this query, can you help me tell if there could be a possibility of data inconsistency? or a possibility that a row of data have been skipped?

    because this should be be getting and updating all the children of 258, but as a result it didn't.


    will the updates on a table be committed if an error occured like this below?

    ORA-30928: Connect by filtering phase runs out of temp tablespace
    30928. 00000 - "Connect by filtering phase runs out of temp tablespace"
    *Cause: It is probably caused by the fact that there is a loop in the data.
    *Action: Please retry the query with the NO_FILTERING hint. If the same error
    still occurs, then increase temp tablespace.


    because upon testing the query, i get this. please help me.

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

    Cool

    It appears you have no conditions that connect Tbl_Agent A with Tbl_Agent_Product P.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jan 2012
    Posts
    2
    prior to the query i posted there's a connection between the two tables but it also results to an error. for the mean time i substituted the agent_id and equated it to 258 which data is what im looking for.

    but like i said, some of the children of 258 wasnt updated when it was called. so im not sure if some rows are randomly skipped during the update of the table

Posting Permissions

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