SQL Server Performance Forum – Threads Archive

NULL value

hello.. when i query for data..the result is NULL.but i’m pretty sure that data have been key-in into the database.. what have went wrong guys? thanks.. regards.

Hi,
post query and sample data ! aer u using EM !? or QA ! give some more information to get good answeres Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

True, ensure you’re not querying NULL columns only. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Please post the query. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

>>what have went wrong guys? May be your query [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Post it as suggested Madhivanan Failing to plan is Planning to fail

ok guys..here is the sql query and the result as well <img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> …………… SELECT "subscriber"."customerid", "subscriber"."name", "ordergenerationheader"."orderid", "ordergenerationheader"."orderdate", "MstPicking"."UnitID", "ordergenerationheader"."shippingdate", "MstPicking"."OrderID", "MstPicking"."PickNo", "ContractUnit"."LocCode", "UTypeDet"."UnitDesc", "ContractUnit"."UnitDesc", "location"."loccode" FROM ((((("CRF"."dbo"."subscriber" "subscriber" INNER JOIN "CRF"."dbo"."contract" "contract" ON "subscriber"."customerid"="contract"."subid") INNER JOIN "CRF"."dbo"."ordergenerationheader" "ordergenerationheader" ON "contract"."contractno"="ordergenerationheader"."contractid") INNER JOIN "CRF"."dbo"."MstPicking" "MstPicking" ON "ordergenerationheader"."orderid"="MstPicking"."OrderID") INNER JOIN "CRF"."dbo"."ContractUnit" "ContractUnit" ON "MstPicking"."UnitID"="ContractUnit"."UnitID") INNER JOIN "CRF"."dbo"."UTypeDet" "UTypeDet" ON "ContractUnit"."UnitType"="UTypeDet"."unitcode") LEFT OUTER JOIN "CRF"."dbo"."location" "location" ON "ContractUnit"."LocCode"="location"."loccode" WHERE "MstPicking"."OrderID"=’OR0002’ AND "MstPicking"."PickNo"=’PI0002′ ORDER BY "MstPicking"."UnitID" ………….. and the result in QA is:- customerid KNK name K&N KENANGA BHD (KLANG) orderid OR0002 orderdate 10/19/2005 UnitID 00303994 shippingdate 10/19/2005 OrderID OR0002 PickNo PI0002 LocCode 036333 UnitDesc SMALL UnitDesc TB070920008 loccode NULL for your info, this command is taken from show sql query in crystal reports. in crystal reports design i want to print the loccode. the data should be eg 4JA303

Do you think Null in last row is inValid?
You should give sample data from all the tables and the result you want Madhivanan Failing to plan is Planning to fail

Given this JOIN condition
LEFT OUTER JOIN "CRF"."dbo"."location" "location" ON "ContractUnit"."LocCode"="location"."loccode" and your ContractUNit.LocCode of "036333". How should this ever match with "4JA303" ? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

ok..
i want it to be like this :- customerid – KNK
name – K&N KENANGA BHD (KLANG)
orderid – OR0002
orderdate – 10/19/2005
UnitID – 00303994
shippingdate – 10/19/2005
OrderID – OR0002
PickNo – PI0002
LocCode – 036333
UnitDesc – SMALL
UnitDesc – TB070920008
loccode – 4JA303 all the tables involved are:
subscriber(indcode, inddesc,customerid, name, add)
contract(subid, contractno, fromdate, todate)
ordergenerationheader(orderid, contractid, orderdate, shippingdate)
MstPicking(OrderID, PickNo, UnitID, LoCode)
ContractUnit(ContractID, UnitID, UnitDesc, LocCode)
location(status, createdby, createddate, locdesc, loccode)
UTypeDet(unitcode, UnitDesc, Type, fqty, tqty, price)
order links: subscriber.customerid –> contract.subid
contract.contractno –> ordergenerationheader.contractid
ordergeneration.orderid –> MstPicking.OrderID
MstPicking.UnitID –> ContractUnit.UnitID
ContractUnit.UnitType –> UTypeDet.unitcode
ContractUnit.LocCode –> location.loccode
and what is wrong with that join, FrankKalis?
i want location.loccode to be appeared on the report which ContractUnit.LocCode (036333) will give the value of location.loccode (4JA305) i’m still new to play around with sql command and crystal reports. hope u can help me.. thank u guys in advanced. regards

A join is where you match values between tables. If you use an inner join and there is no match for a value between tables, then the rows with non-matching values will not be returned. If you use an outer join, and there is no match on the outer table, then the row from the inner table is returned, with a NULL on any column that you selected from the outer table.

Okay, consider this example
CREATE TABLE #Table1
(tid INT, c1 CHAR) CREATE TABLE #Table2
(tid INT, c2 CHAR) INSERT INTO #Table1
SELECT 1, ‘a’
UNION ALL
SELECT 2, ‘b’
UNION ALL
SELECT 3, ‘c’
UNION ALL
SELECT 4, ‘d’ INSERT INTO #Table2
SELECT 1, ‘x’
UNION ALL
SELECT 2, ‘y’
UNION ALL
SELECT 3, ‘z’ Now you do
SELECT t1.*, t2.c2
FROM #Table1 t1
LEFT OUTER JOIN #Table2 t2
ON t1.tid=t2.tid tid c1 c2
———– —- —-
1 a x
2 b y
3 c z
4 d NULL (4 row(s) affected) When you look at this example, you’ll see that everything is just like one can expect. All rows from Table1 appear at least once in the resultset and when there is a match according to the JOIN condition the corresponding value from Table2 also appears in the resultset. When there is no match, a NULL appears. This is what I meant above. 036333 does not match with 4JA303. Therefore a NULL is in the resultset. You either need to inspect your values in the table if they match or rewrite the JOIN. Maybe you just selected the wrong column? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

In other words: Loc.LocCode can be either the same as ContractUnit.LocCode because of the last join condition or null because of left outer join used. In former case when there is no loc.LocCode matching contractUnit.locCode, row will still be returned with null value for location.locCode.

[<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]thanks guys..now it successfully running thank u so much..mmarovic, FrankKalis, Adriaan, Madhivanan, satya, ghemant have a nice day

You’re welcome! [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

>>thank u so much..mmarovic, FrankKalis, Adriaan, Madhivanan, satya, ghemant Your appreciation is granted [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

]]>