Reputation: 4721

Column returning null values instead of values present

I have a query in which, I have merged a sub query for my requirement.

Here the query is.

select a.mkey, b.mkey RefMkey, a.doc_no, a.doc_date, 
            e.type_desc DocType, c.first_name + ' ' + c.last_name CurrentUser,
        d.Type_desc Department, b.remarks, 
                (select f.type_desc from type_mst_a where f.Add_IInfo1
                 = b.NStatus_flag and f.type_code = 'S2') currentStatus
           from inward_doc_tracking_hdr a
        inner join inward_doc_tracking_trl b
            on a.mkey = b.ref_mkey
        inner join user_mst c
            on c.mkey = b.CUser_Id  
        inner join type_mst_a d
            on d.master_mkey = b.CDept_Id   
        inner join type_mst_a e
            on e.master_mkey = a.doc_type
        inner join type_mst_a f 
            on f.master_mkey = b.NStatus_flag
where a.mkey = 227394

but what happening here is, there are values present but I am getting as NULL

Update

CREATE TABLE [dbo].[Inward_Doc_Tracking_Hdr](
[Mkey] [numeric](18, 0) NOT NULL,
[FModule_ID] [char](1) NOT NULL,
[Comp_Mkey] [numeric](4, 0) NOT NULL,
[Branch_Mkey] [numeric](4, 0) NOT NULL,
[Tran_Type] [varchar](6) NOT NULL,
[Tran_Code] [numeric](8, 0) NOT NULL,
[Tran_No] [numeric](4, 0) NOT NULL,
[Department_ID] [varchar](4) NOT NULL,
[User_Id] [numeric](10, 0) NULL,
[Doc_Type] [numeric](10, 0) NULL,
[Doc_No] [varchar](30) NULL,
[Doc_Date] [datetime] NOT NULL,
[Dispatch_By] [char](1) NOT NULL,
[Status_Flag] [numeric](8, 0) NOT NULL,
[FA_Year] [smallint] NOT NULL,
[Party_Mkey] [numeric](10, 0) NULL,
[To_Department] [numeric](10, 0) NULL,
[To_User] [numeric](10, 0) NULL,
[Inward_Amt] [numeric](18, 3) NULL,
[Ref_No] [varchar](40) NULL,
[Ref_date] [datetime] NULL,
[U_DateTime] [datetime] NOT NULL,
[LastAction_DateTime] [datetime] NULL,
[Remarks] [varchar](255) NULL,
[Delete_Flag] [char](1) NOT NULL,
[Outward_Type] [char](1) NULL,
[Doc_Department] [numeric](10, 0) NULL,
[Party_Name] [varchar](80) NULL,
[Delivered_By] [varchar](30) NULL,
[Doc_Description] [varchar](50) NULL,
[Last_Department] [numeric](10, 0) NULL,
[Last_User] [numeric](10, 0) NULL,
[Approved_Amount] [float] NULL,
[Chq_No] [varchar](50) NULL,
[Chq_dated] [datetime] NULL,
[Chq_Bank] [varchar](40) NULL,
[Chq_Amount] [float] NULL,
[Vendor_MKey] [int] NULL,
[Vendor_Comp_Mkey] [int] NULL,
[Project_Mkey] [numeric](10, 0) NULL,
[Program_mkey] [numeric](10, 0) NULL,
[Payment_MKey] [int] NULL,
[Due_Date] [datetime] NULL,
[Updated_Remarks] [varchar](500) NULL,
[Updated_Bill_no] [varchar](27) NULL,
[Updated_Bill_Date] [datetime] NULL,
[Updated_Bill_Amt] [float] NULL,
[TotalDeductions] [float] NULL,
[Broker_Mkey] [numeric](10, 0) NULL,
[Customer_Mkey] [numeric](10, 0) NULL,
[Payable_Amt] [float] NULL,
[Balance_Amt] [float] NULL,
[Req_Bill_Flag] [char](1) NULL,
[Po_No] [varchar](50) NULL,
[Receipt_No] [varchar](50) NULL,
[Bill_No] [varchar](50) NULL,
[Org_id] [varchar](10) NULL,
[Site_Id] [varchar](50) NULL,
[Site_Name] [varchar](150) NULL,
[Cumulative_Amt] [numeric](18, 2) NULL,
[Email_Id] [varchar](50) NULL,
[Emp_Id] [varchar](6) NULL,
[Disp_through] [varchar](50) NULL,
[Disp_Through_Name] [varchar](100) NULL,
[Last_To_User] [numeric](10, 0) NULL

) ON [PRIMARY]

The query which shows there are result is

select type_desc, * from type_mst_a where type_code = 'S2'

Upvotes: 0

Answers (6)

mvisser

Reputation: 670

SELECT a.mkey, 
    b.mkey AS RefMkey, 
    a.doc_no, 
    a.doc_date, 
    e.type_desc AS DocType, 
    c.first_name + ' ' + c.last_name AS CurrentUser,
    d.Type_desc AS Department, 
    b.remarks, 
    docType.type_desc AS  currentStatus
FROM inward_doc_tracking_hdr a
INNER JOIN inward_doc_tracking_trl b
    on a.mkey = b.ref_mkey
INNER JOIN user_mst c
    on c.mkey = b.CUser_Id  
INNER JOIN type_mst_a d
    on d.master_mkey = b.CDept_Id   
INNER JOIN type_mst_a e
    on e.master_mkey = a.doc_type
LEFT JOIN
    (
        SELECT type_desc,
            Add_IInfo1 
        FROM type_mst_a
        WHERE type_code = 'S2'
    ) AS docType
    ON docType.Add_IInfo1 = b.NStatus_flag
WHERE a.mkey = 227394

Upvotes: 1

Milan Tomeš

Reputation: 344

What about this:

select a.mkey, b.mkey RefMkey, a.doc_no, a.doc_date, 
   e.type_desc DocType, c.first_name + ' ' + c.last_name CurrentUser,
   d.Type_desc Department, b.remarks, 
   (select type_desc from type_mst_a where Add_IInfo1 = b.NStatus_flag and type_code = 'S2') currentStatus
from inward_doc_tracking_hdr a
inner join inward_doc_tracking_trl b
    on a.mkey = b.ref_mkey
inner join user_mst c
    on c.mkey = b.CUser_Id  
inner join type_mst_a d
    on d.master_mkey = b.CDept_Id   
inner join type_mst_a e
    on e.master_mkey = a.doc_type
inner join type_mst_a f 
    on f.master_mkey = b.NStatus_flag
where a.mkey = 227394

which can be rewritten as:

select a.mkey, b.mkey RefMkey, a.doc_no, a.doc_date, 
   e.type_desc DocType, c.first_name + ' ' + c.last_name CurrentUser,
   d.Type_desc Department, b.remarks, g.type_desc as currentStatus
from inward_doc_tracking_hdr a
inner join inward_doc_tracking_trl b
    on a.mkey = b.ref_mkey
inner join user_mst c
    on c.mkey = b.CUser_Id  
inner join type_mst_a d
    on d.master_mkey = b.CDept_Id   
inner join type_mst_a e
    on e.master_mkey = a.doc_type
inner join type_mst_a f 
    on f.master_mkey = b.NStatus_flag
inner join type_mst_a g 
    on g.Add_IInfo1 = b.NStatus_flag and 
       g.type_code = 'S2'
where a.mkey = 227394

Upvotes: 0

Walter Mitty

Reputation: 18940

Your inner SELECT is what is known as a "correlated subquery".

Correlated subqueries can yield NULLS in the results even when there are no NULLS stored in the database. If there are rows in the main query for which there are zero matching rows in the subquery, NULLS will appear in the results.

You'll have to do some detective work on your data to see if this is the case.

Upvotes: 0

Zaid Mirza

Reputation: 3699

Your one of columns may be null,try left or full outer joins like this

select a.mkey, b.mkey RefMkey, a.doc_no, a.doc_date, 
            e.type_desc DocType, c.first_name + ' ' + c.last_name CurrentUser,
        d.Type_desc Department, b.remarks, 
                (select f.type_desc from type_mst_a where f.Add_IInfo1
                 = b.NStatus_flag and f.type_code = 'S2') currentStatus
           from inward_doc_tracking_hdr a
        left join inward_doc_tracking_trl b
            on a.mkey = b.ref_mkey
        left join user_mst c
            on c.mkey = b.CUser_Id  
        left join type_mst_a d
            on d.master_mkey = b.CDept_Id   
        left join type_mst_a e
            on e.master_mkey = a.doc_type
        left join type_mst_a f 
            on f.master_mkey = b.NStatus_flag
where a.mkey = 227394

Upvotes: 0

Arulkumar

Reputation: 13237

Instead of the sub query, you can do the same with the join itself:

select  a.mkey, b.mkey RefMkey, a.doc_no, a.doc_date, 
        e.type_desc DocType, c.first_name + ' ' + c.last_name CurrentUser,
        d.Type_desc Department, b.remarks, f.type_desc AS currentStatus
from inward_doc_tracking_hdr a
inner join inward_doc_tracking_trl b
    on a.mkey = b.ref_mkey
inner join user_mst c
    on c.mkey = b.CUser_Id  
inner join type_mst_a d
    on d.master_mkey = b.CDept_Id   
inner join type_mst_a e
    on e.master_mkey = a.doc_type
inner join type_mst_a f 
    on f.master_mkey = b.NStatus_flag AND f.Add_IInfo1 = b.NStatus_flag AND f.type_code = 'S2'
where a.mkey = 227394

Upvotes: 0

Christian

Reputation: 827

You not defined "f"

select f.type_desc from type_mst_a where f.Add_IInfo1
                 = b.NStatus_flag and f.type_code = 'S2'

Upvotes: 0

Column returning null values instead of values present

Answers (6)

Related Questions