locked
select statement empty record doubt RRS feed

  • Question

  • i have a following sql statement ,

    SELECT

    ((ISNULL(B.QTY * B.UNITPRICE,0)) + ISNULL((L.QTY * L.UNITPRICE),0)) AS AMOUNT FROM BILLOFMATERIALDETAILS B,LINEITEMVARIANCEDETAIL L WHERE B.PROJECTID = 17 AND B.LINEID = 'SH10/1' AND L.PROJECTID = B.PROJECTID AND L.LINEID = B.LINEID

     

    first table has no data for this condition . Second table has data . Since there is no data for required condition . My total output is coming as empty , How to solve this issue .

    first table has no data for this condition . Second table has data . Since there is no data for required condition . My total output is coming as empty , How to solve this issue .




    raghu
    Monday, March 30, 2009 12:30 PM

Answers

  • THANKS TILLAI . IS THIS MY BELOW IS CORRECT .

    SELECT

    ((ISNULL(B.QTY * B.UNITPRICE,0)) + ISNULL((L.QTY * L.UNITPRICE),0)) AS AMOUNT

    FROM

    BILLOFMATERIALDETAILS B FULL JOIN LINEITEMVARIANCEDETAIL L

    ON

    L.PROJECTID = B.PROJECTID AND L.LINEID = B.LINEID

    WHERE

    ((B.PROJECTID = 17 OR B.PROJECTID IS NULL) OR (B.PROJECTID IS NULL OR B.PROJECTID = 17 )) AND L.LINEID = 'SH10/1'

    SOMETIMES CONDTION MAY BE LIKE LINEITEMVARIANCEDETAIL TABLE THERE MAY NOT BE ANY DATA BUT IN BILLOFMATERIALDETAILS TABLE WE WILL HAVE DATA . SO IN THIS CONDITION . MY ABOVE QUERY IS CORRECT ?


    raghu
    • Marked as answer by raghu_grdr Tuesday, March 31, 2009 4:03 AM
    Monday, March 30, 2009 1:44 PM

All replies

  • Have you tried OUTER JOIN like this,

    SELECT

    ((ISNULL(B.QTY * B.UNITPRICE,0)) + ISNULL((L.QTY * L.UNITPRICE),0)) AS AMOUNT

    FROM

    BILLOFMATERIALDETAILS B RIGHT JOIN LINEITEMVARIANCEDETAIL L ON L.PROJECTID = B.PROJECTID AND L.LINEID = B.LINEID

    WHERE

    B.PROJECTID = 17 AND B.LINEID = 'SH10/1'

    Monday, March 30, 2009 12:33 PM
  • I'm not able to understand your question, please provide some sample data.

    If one table doesn't meet condtion, you can do LEFT/RIGHT JOIN, so for no data you will get NULLS.
    Mangal Pardeshi
    SQL With Mangal
    Monday, March 30, 2009 12:33 PM
  • same problem
    raghu
    Monday, March 30, 2009 12:41 PM
  • As Mangal said, you will get quick guidance or solution only if you post your queries properly.

    Clearly mention ur Problem, table structure and sample data.

    Monday, March 30, 2009 12:44 PM
  • i have two tables

    first table return data 10 for the condition above

    second table return data ""  for the condition above .

    i am adding 10 + isnull("",0) in the above query . So that i will get 10 as output .

    But i am getting only "" . how to handle this ? .
    raghu
    Monday, March 30, 2009 12:48 PM
  • It is not clear to us what you are working with nor what results you are trying to achieve.

    Please provide the schema of your tables and some sample data and we will help you resolve your issue faster.


    every day is a school day
    Monday, March 30, 2009 12:57 PM


  • BILLOFMATERIALDETAILS


    QTY UNITPRICE LINEID PROJECTID
    10 10 SH12/1 17
    10 10 SH12/2 17
    10 10 SH6/1 17


    LINEITEMVARIANCEDETAIL

    QTY UNITPRICE LINEID PROJECTID
    100 100 SH12/1 17
    9 11 SH6/1 17
    10 10 SH12/2 17
    10 10 SH10/1 17
    0 0 SH10/2 17
    12 136 SH12/3 17


    THIS IS MY TABLE  STRUCTURE

    NOW I AM WRITING THE FOLLWINIG QUERY

    SELECT

    ((ISNULL(B.QTY * B.UNITPRICE,0)) + ISNULL((L.QTY * L.UNITPRICE),0)) AS AMOUNT FROM BILLOFMATERIALDETAILS B,LINEITEMVARIANCEDETAIL L WHERE B.PROJECTID = 17 AND B.LINEID = 'SH10/1' AND L.PROJECTID = B.PROJECTID AND L.LINEID = B.LINEID .


    MY OUTPUT SHOULD BE 100 FOR THIS . bUT I AM NOT GETTING ANY DATA . tHAT IS MY PROBLEM


    raghu
    Monday, March 30, 2009 1:05 PM
  • Hi raghu,

    The problem is that the condition B.LINEID = 'SH10/1' will not be returning anything as the table BILLOFMATERIALDETAILS does not have any lineid that matches 'SH10/1'. Please check if this is what you need.

    Cheers,
    Padmanabhan
    Monday, March 30, 2009 1:23 PM
  • ya u r right . But this is one of the condition .B.LINEID = 'SH10/1'  . For other condition like B.LINEID = 'SH11/1'   there will be data . So  i want to handle this .
    raghu
    Monday, March 30, 2009 1:26 PM
  • Ok, now we're getting somewhere. The first thing I notice is that your WHERE clause contains B.LINEID = 'SH10/1', with B being the alias for the BILL table. No record exists in that table with that value so thats why you are getting no results.

    create table BillOfMaterialDetails
    (qty int, unitprice int, lineid varchar(20), projectid int)
    create table lineitemvariancedetail
    (qty int, unitprice int, lineid varchar(20), projectid int)
    
    insert into BillOfMaterialDetails
    select 10, 10, 'sh12/1', 17
    union all
    select 10, 10, 'sh12/2', 17
    union all
    select 10, 10, 'sh6/1', 17
    
    
    insert into lineitemvariancedetail
    select 100, 100, 'sh12/1', 17
    union all
    select 9, 11, 'sh6/1', 17
    union all
    select 10, 10,'sh12/2', 17
    union all
    select 10, 10, 'sh10/1', 17
    union all
    select 0, 0, 'sh10/2', 17
    union all
    select 12, 13, 'sh12/3', 17
    
    SELECT
    ((ISNULL(B.QTY * B.UNITPRICE,0)) + ISNULL((L.QTY * L.UNITPRICE),0)) AS AMOUNT 
    FROM BILLOFMATERIALDETAILS B
    	RIGHT OUTER JOIN LINEITEMVARIANCEDETAIL L
    		on L.PROJECTID = B.PROJECTID AND L.LINEID = B.LINEID	 
    WHERE L.PROJECTID = 17 
    AND L.LINEID = 'SH10/1' 
    
    

    every day is a school day
    Monday, March 30, 2009 1:29 PM
  • I am not sure what is your purpose of your query,

    But anyhow i had managed to write an query for your requirement.

    Is this you need?

    SELECT

    ((ISNULL(B.QTY * B.UNITPRICE,0)) + ISNULL((L.QTY * L.UNITPRICE),0)) AS AMOUNT
    FROM BILLOFMATERIALDETAILS B RIGHT JOIN LINEITEMVARIANCEDETAIL L ON L.PROJECTID = B.PROJECTID AND L.LINEID = B.LINEID WHERE (B.PROJECTID = 17 OR B.PROJECTID IS NULL) AND L.LINEID = 'SH10/1'

    Monday, March 30, 2009 1:29 PM
  • THANKS TILLAI . IS THIS MY BELOW IS CORRECT .

    SELECT

    ((ISNULL(B.QTY * B.UNITPRICE,0)) + ISNULL((L.QTY * L.UNITPRICE),0)) AS AMOUNT

    FROM

    BILLOFMATERIALDETAILS B FULL JOIN LINEITEMVARIANCEDETAIL L

    ON

    L.PROJECTID = B.PROJECTID AND L.LINEID = B.LINEID

    WHERE

    ((B.PROJECTID = 17 OR B.PROJECTID IS NULL) OR (B.PROJECTID IS NULL OR B.PROJECTID = 17 )) AND L.LINEID = 'SH10/1'

    SOMETIMES CONDTION MAY BE LIKE LINEITEMVARIANCEDETAIL TABLE THERE MAY NOT BE ANY DATA BUT IN BILLOFMATERIALDETAILS TABLE WE WILL HAVE DATA . SO IN THIS CONDITION . MY ABOVE QUERY IS CORRECT ?


    raghu
    • Marked as answer by raghu_grdr Tuesday, March 31, 2009 4:03 AM
    Monday, March 30, 2009 1:44 PM
  • It seems to be correct.

    For reliable output, test for wide range of values.


    Be the change you want.
    Monday, March 30, 2009 1:48 PM
  • OK BUT

     AND L.LINEID = 'SH10/1'

     IS CORRECT ? . Or some changes shuld be done here ?

    raghu
    Monday, March 30, 2009 1:50 PM
  • You dindt mention your requirement correctly, my analysis and reply is based on the table and the query you have posted.


    Be the change you want.
    Monday, March 30, 2009 1:53 PM
  • Hi raghu,

    You are checking the same condition twice!!! Check your query.

    Cheers
    Padmanabhan
    Monday, March 30, 2009 1:54 PM
  • sorry tilla . my requirment is like . in both the tables there is possibility . So can you please write the query to handle both the condition and let me know
    raghu
    Monday, March 30, 2009 2:00 PM
  • Raghu - what results would you expect for the following test values?

    LINEID = 'SH12/2'
    LINEID = 'SH6/1'
    LINEID = 'SH12/3'


    every day is a school day
    Monday, March 30, 2009 2:07 PM