Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Unhappy Unanswered: Oracle query in SQL Server

    Hi,

    I want to write this query in sql server. Please help me, it is very urgent

    Any help will be appreciated.

    SELECT VALID_ITEM.PC_CODE,PC_TITLE,VALID_ITEM.DB_CONTRACT ,
    valid_item.ITEM_NO,DESCPT,UNITS,CONTQ,
    CONTR_PRICE ,nz(QTD,0) as qtd,ind_ovr_und,
    nz(QTD,0) - NVL(QUANTITY,0) as QUANT_PREV,
    Nz(QUANTITY,0) as quant_rev,
    round(NVL(quantity,0)*nvl(CONTR_PRICE,0),2) as QTD_VAL,
    AMT_PAID_ITEM,AMT_RET_ITEM
    FROM VALID_ITEM, cqe_item,CONTRACT_PC
    WHERE valid_item.db_contract = contract_pc.db_contract
    and valid_item.pc_code = contract_pc.pc_code
    and valid_item.db_contract = cqe_item.db_contract (+)
    and cqe_item.cqe_numb (+) = :EST_NO
    and valid_item.item_no = cqe_item.item_no (+)
    and valid_item.pc_code = cqe_item.pc_code (+)
    order by valid_item.item_no
    mr_roomi

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about

    Code:
    SELECT    i.PC_CODE
    	, PC_TITLE
    	, i.DB_CONTRACT
    	, i.ITEM_NO
    	, DESCPT
    	, UNITS
    	, CONTQ
    	, CONTR_PRICE 
    	, ISNULL(QTD,0) as qtd
    	, ind_ovr_und
    	, ISNULL(QTD,0) - ISNULL(QUANTITY,0) as QUANT_PREV
    	, Nz(QUANTITY,0) as quant_rev
    	, ROUNDISNULL(quantity,0)*ISNULL(CONTR_PRICE,0),2) as QTD_VAL
    	, AMT_PAID_ITEM
    	, AMT_RET_ITEM
          FROM VALID_ITEM v
     LEFT JOIN cqe_item i
    	ON v.db_contract = i.db_contract
           AND v.item_no     = i.item_no
           AND v.pc_code     = i.pc_code
     LEFT JOIN CONTRACT_PC c
    	ON v.db_contract = contract_pc.db_contract
           AND v.pc_code = contract_pc.pc_code
         WHERE  i.cqe_numb = @EST_NO
      ORDER BY i.item_no
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Worked

    Hi Brett,

    It worked fine but it is not extracting the same rows as Oracle code.? Data is same....what is possible reason?

    SELECT i.PC_CODE
    , PC_TITLE
    , i.DB_CONTRACT
    , i.ITEM_NO
    , DESCPT
    , UNITS
    , CONTQ
    , CONTR_PRICE
    , ISNULL(QTD,0) as qtd
    , ind_ovr_und
    , ISNULL(QTD,0) - ISNULL(QUANTITY,0) as QUANT_PREV
    , ISNULL(QUANTITY,0) as quant_rev
    , ROUND (ISNULL(quantity,0)*ISNULL(CONTR_PRICE,0),2) as QTD_VAL
    , AMT_PAID_ITEM
    , AMT_RET_ITEM
    FROM VALID_ITEM as v
    LEFT JOIN cqe_item as i
    ON v.db_contract = i.db_contract
    AND v.item_no = i.item_no
    AND v.pc_code = i.pc_code
    LEFT JOIN CONTRACT_PC as c
    ON v.db_contract = c.db_contract
    AND v.pc_code = c.pc_code
    WHERE i.cqe_numb = 58 ----gave the value
    and i.db_contract =26999 ----add one line
    ORDER BY i.item_no
    mr_roomi

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well you added another predicate....db_Contract_num...

    But I don't know how you can have an outter join to a variable...

    Like this

    and cqe_item.cqe_numb (+) = :EST_NO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Exclamation Problem

    Brett,

    I got the problem...problem is with this line of code that you probably missed to translate i.e.

    cqe_item.cqe_numb (+) = :EST_NO

    I think you did not give solution of right join here? Am I right?

    Thanks for you time..
    mr_roomi

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I guess you missed me pointing that out...

    How do you have a right join to a variable?

    What does that even mean?

    An outter join is between tables

    Is that meant to accomodate something like...

    ISNULL(cqe_item.cqe_numb,@EST_NO) = @EST_NO

    ??????????????????????????????????????????
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    query

    Hi Brett,

    in table cqe_numb can be 1,2,3...scenerio is join on values ...am I right?
    But this query is working perfect with oracle... ... ....

    cqe_item.cqe_numb (+) = :EST_NO

    SQL> desc cqe_item
    Name Null? Type
    ------------------------------- -------- ----
    DB_CONTRACT NOT NULL NUMBER(6)
    =======================================
    CQE_NUMB NOT NULL NUMBER(3) ====right here
    =======================================
    PC_CODE NOT NULL NUMBER(2)
    ITEM_NO NOT NULL VARCHAR2(7)
    QUANTITY NUMBER(11,3)
    AMT_PAID_ITEM NUMBER(11,2)
    AMT_RET_ITEM NUMBER(10,2)
    QTD_ITEM NUMBER(11,3)
    FY_ITEM NUMBER(4)
    mr_roomi

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I have no idea....

    Anyone heard of a right join to a variable?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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