Select col record which are not null

Hello there,

How can I only git the record in the select which are not emplty or null. I am doing a select on union and in one of the union query I have mentioned NULL to make both the col similar to each other as a condition for UNION. now in the select when I try MAX(name) it is getting the NULL and the record which has string in it. please help. below is my query

SELECT 
a.Branch,
a.ID,
MAX(a.soldQty) AS soldQty,
MAX(a.AvailQty) as AvailQty,
a.productname,
MAX(a.BrandCode) as BrandCode,
a.BrandName,
a.Companyname,
MAX(a.GenericName) as GenericName

FROM 
(SELECT
    dbo.Branch.NAME                              AS Branch,
    tafsil.id                                    AS ID,
    dbo.Tafsil.description                  AS ProductName,
    SUM(dbo.SaleDetail.quantity)      AS soldQty,
    dbo.FinishProduct.brandname            AS BrandName,
    dbo.FinishProduct.genericname      AS GenericName,
    dbo.FinishProduct.ean           AS BrandCode,
    dbo.FinishProduct.universalcode AS GenericCode,
    0                                                AS AvailQty,
    dbo.Supplier.companyName            AS CompanyName
FROM dbo.Batch INNER JOIN dbo.Tafsil
ON      (dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN dbo.SaleDetail
ON      (dbo.Batch.id = dbo.SaleDetail.batchId)
INNER JOIN dbo.Sale
ON  (dbo.SaleDetail.saleId = dbo.Sale.id)
INNER JOIN dbo.Receipt
ON      (dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN dbo.Warehouse
ON      (dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN dbo.Branch
ON      (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.TafsilLink
ON      (dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN      dbo.FinishProduct
ON      (dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN dbo.Supplier
ON      (dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE
    dbo.Receipt.receiptDate >= '2013-03-21' AND dbo.Receipt.receiptDate < CONVERT(DATE, GETDATE())
GROUP BY
    dbo.Branch.NAME,
    tafsil.id ,
    dbo.Tafsil.description ,
    dbo.FinishProduct.brandname ,
    dbo.FinishProduct.genericname ,
    dbo.Supplier.companyName ,
    dbo.FinishProduct.ean ,
    dbo.FinishProduct.universalcode

UNION

SELECT
    dbo.Branch.name                                          AS Branch,
    dbo.Tafsil.id                                          AS ID,
    dbo.Tafsil.description                              AS ProductName,
    0                                                            AS soldQty,
    dbo.FinishProduct.brandName                        AS BrandName,
    NULL                                                      AS GenericName,
    ''                                                     AS BrandCode,
    0                                                      AS GenericCode,
    SUM(dbo.WarehouseProductQuantity.actualQuantity -
    dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
    dbo.Supplier.companyName            AS CompanyName

FROM dbo.WarehouseSubType INNER JOIN dbo.WarehouseType
ON (dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN dbo.WarehouseSubType1
ON (dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN dbo.Warehouse
ON (dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN dbo.Branch
ON (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.WarehouseProductQuantity
ON (dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN dbo.Tafsil
ON (dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN dbo.TafsilLink
ON (dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN dbo.FinishProduct
ON (dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN dbo.Supplier
ON (dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
GROUP BY
    dbo.Branch.name,
    dbo.Tafsil.description,
    dbo.FinishProduct.brandName,
    dbo.Supplier.companyName,
    dbo.Tafsil.id 
    
    ) a
    
    GROUP BY
a.Branch,
a.ID,
a.productname,
a.BrandName ,
a.Companyname,
a.BrandCode,
GenericName

Open in new window

zolfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Louis01Commented:
Just add
WHERE IsNull(ColName, '') = ''
0
zolfAuthor Commented:
can you please elaborate on your comments. I don't understand. I don't want the select to contain empty or null values.if you see my query I have this

MAX(a.GenericName) as GenericName

but it is returning 2 records. I only want it to return the col which has value in it
0
Tony303Commented:
Hi zolf again.

How about changing the NULL to ' '  in the second Union SELECT statement for generic code.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Tony303Commented:
If the generic code is being "MAXED" (ie aggregated), then it needs to be removed from the Group By clause in the bottom line zolf.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
zolfAuthor Commented:
Hi Tony, thanks for your comment can you please post your query. As the query is complicated and don't want to mess up
0
Tony303Commented:
Try this for the removal of Group BY

SELECT
a.Branch,
a.ID,
MAX(a.soldQty) AS soldQty,
MAX(a.AvailQty) as AvailQty,
a.productname,
MAX(a.BrandCode) as BrandCode,
a.BrandName,
a.Companyname,
MAX(a.GenericName) as GenericName

FROM
(SELECT
    dbo.Branch.NAME                              AS Branch,
    tafsil.id                                    AS ID,
    dbo.Tafsil.description                  AS ProductName,
    SUM(dbo.SaleDetail.quantity)      AS soldQty,
    dbo.FinishProduct.brandname            AS BrandName,
    dbo.FinishProduct.genericname      AS GenericName,
    dbo.FinishProduct.ean           AS BrandCode,
    dbo.FinishProduct.universalcode AS GenericCode,
    0                                                AS AvailQty,
    dbo.Supplier.companyName            AS CompanyName
FROM dbo.Batch INNER JOIN dbo.Tafsil
ON      (dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN dbo.SaleDetail
ON      (dbo.Batch.id = dbo.SaleDetail.batchId)
INNER JOIN dbo.Sale
ON  (dbo.SaleDetail.saleId = dbo.Sale.id)
INNER JOIN dbo.Receipt
ON      (dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN dbo.Warehouse
ON      (dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN dbo.Branch
ON      (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.TafsilLink
ON      (dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN      dbo.FinishProduct
ON      (dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN dbo.Supplier
ON      (dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE
    dbo.Receipt.receiptDate >= '2013-03-21' AND dbo.Receipt.receiptDate < CONVERT(DATE, GETDATE())
GROUP BY
    dbo.Branch.NAME,
    tafsil.id ,
    dbo.Tafsil.description ,
    dbo.FinishProduct.brandname ,
    dbo.FinishProduct.genericname ,
    dbo.Supplier.companyName ,
    dbo.FinishProduct.ean ,
    dbo.FinishProduct.universalcode

UNION

SELECT
    dbo.Branch.name                                          AS Branch,
    dbo.Tafsil.id                                          AS ID,
    dbo.Tafsil.description                              AS ProductName,
    0                                                            AS soldQty,
    dbo.FinishProduct.brandName                        AS BrandName,
    NULL                                                      AS GenericName,
    ''                                                     AS BrandCode,
    0                                                      AS GenericCode,
    SUM(dbo.WarehouseProductQuantity.actualQuantity -
    dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
    dbo.Supplier.companyName            AS CompanyName

FROM dbo.WarehouseSubType INNER JOIN dbo.WarehouseType
ON (dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN dbo.WarehouseSubType1
ON (dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN dbo.Warehouse
ON (dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN dbo.Branch
ON (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.WarehouseProductQuantity
ON (dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN dbo.Tafsil
ON (dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN dbo.TafsilLink
ON (dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN dbo.FinishProduct
ON (dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN dbo.Supplier
ON (dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
GROUP BY
    dbo.Branch.name,
    dbo.Tafsil.description,
    dbo.FinishProduct.brandName,
    dbo.Supplier.companyName,
    dbo.Tafsil.id
   
    ) a
 
 WHERE a.GenericName  IS NOT NULL
   
    GROUP BY
a.Branch,
a.ID,
a.productname,
a.BrandName ,
a.Companyname,
a.BrandCode
0
zolfAuthor Commented:

If the generic code is being "MAXED" (ie aggregated), then it needs to be removed from the Group By clause in the bottom line

this solved the issue,my brain got locked since morning I am trying to get this work and I missed out that aggregated col need not be in group
0
Tony303Commented:
If not that.

Let's see if my first comment is a workable solution

SELECT
a.Branch,
a.ID,
MAX(a.soldQty) AS soldQty,
MAX(a.AvailQty) as AvailQty,
a.productname,
MAX(a.BrandCode) as BrandCode,
a.BrandName,
a.Companyname,
MAX(a.GenericName) as GenericName

FROM
(SELECT
    dbo.Branch.NAME                              AS Branch,
    tafsil.id                                    AS ID,
    dbo.Tafsil.description                  AS ProductName,
    SUM(dbo.SaleDetail.quantity)      AS soldQty,
    dbo.FinishProduct.brandname            AS BrandName,
    dbo.FinishProduct.genericname      AS GenericName,
    dbo.FinishProduct.ean           AS BrandCode,
    dbo.FinishProduct.universalcode AS GenericCode,
    0                                                AS AvailQty,
    dbo.Supplier.companyName            AS CompanyName
FROM dbo.Batch INNER JOIN dbo.Tafsil
ON      (dbo.Batch.productId = dbo.Tafsil.id)
INNER JOIN dbo.SaleDetail
ON      (dbo.Batch.id = dbo.SaleDetail.batchId)
INNER JOIN dbo.Sale
ON  (dbo.SaleDetail.saleId = dbo.Sale.id)
INNER JOIN dbo.Receipt
ON      (dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN dbo.Warehouse
ON      (dbo.Batch.warehouseId = dbo.Warehouse.id)
INNER JOIN dbo.Branch
ON      (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.TafsilLink
ON      (dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN      dbo.FinishProduct
ON      (dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN dbo.Supplier
ON      (dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE
    dbo.Receipt.receiptDate >= '2013-03-21' AND dbo.Receipt.receiptDate < CONVERT(DATE, GETDATE())
GROUP BY
    dbo.Branch.NAME,
    tafsil.id ,
    dbo.Tafsil.description ,
    dbo.FinishProduct.brandname ,
    dbo.FinishProduct.genericname ,
    dbo.Supplier.companyName ,
    dbo.FinishProduct.ean ,
    dbo.FinishProduct.universalcode

UNION

SELECT
    dbo.Branch.name                                          AS Branch,
    dbo.Tafsil.id                                          AS ID,
    dbo.Tafsil.description                              AS ProductName,
    0                                                            AS soldQty,
    dbo.FinishProduct.brandName                        AS BrandName,
    ''                                                      AS GenericName,
    ''                                                     AS BrandCode,
    0                                                      AS GenericCode,
    SUM(dbo.WarehouseProductQuantity.actualQuantity -
    dbo.WarehouseProductQuantity.reservedQuantity) AS AvailQty,
    dbo.Supplier.companyName            AS CompanyName

FROM dbo.WarehouseSubType INNER JOIN dbo.WarehouseType
ON (dbo.WarehouseSubType.warehouseTypeId = dbo.WarehouseType.id)
INNER JOIN dbo.WarehouseSubType1
ON (dbo.WarehouseSubType.id = dbo.WarehouseSubType1.warehouseSubTypeId)
INNER JOIN dbo.Warehouse
ON (dbo.WarehouseSubType1.id = dbo.Warehouse.warehouseSubType1Id)
INNER JOIN dbo.Branch
ON (dbo.Warehouse.branchId = dbo.Branch.id)
INNER JOIN dbo.WarehouseProductQuantity
ON (dbo.Warehouse.id = dbo.WarehouseProductQuantity.warehouseId)
INNER JOIN dbo.Tafsil
ON (dbo.WarehouseProductQuantity.tafsilId = dbo.Tafsil.id)
INNER JOIN dbo.TafsilLink
ON (dbo.Tafsil.id = dbo.TafsilLink.sourceId)
INNER JOIN dbo.FinishProduct
ON (dbo.TafsilLink.targetId = dbo.FinishProduct.tafsilId)
INNER JOIN dbo.Supplier
ON (dbo.FinishProduct.supplierId = dbo.Supplier.tafsilId)
WHERE dbo.WarehouseSubType1.warehouseSubTypeId IN (1, 4)
GROUP BY
    dbo.Branch.name,
    dbo.Tafsil.description,
    dbo.FinishProduct.brandName,
    dbo.Supplier.companyName,
    dbo.Tafsil.id
   
    ) a
 
 WHERE a.GenericName  IS NOT NULL
   
    GROUP BY
a.Branch,
a.ID,
a.productname,
a.BrandName ,
a.Companyname,
a.BrandCode
0
zolfAuthor Commented:
thanks once again for your help
0
Tony303Commented:
No problem, I have to go to bed now.... I have had a big day too with the SQL!!!
Take care, mate.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.