AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of dotnetpro
dotnetpro
 asked on

Custom SQL Query

Experts i have 2 queries in an IF block written below

declare @count int, @sql nvarchar(1000)
declare @poNumber varchar(20)

if(select sum(quantity_size.quantity) from carton inner join (select po_detail_id from po_detail, po_header where po_header.po_header_id = po_detail.po_header_id and po_header.po_number=@poNumber)PO_INFO
on PO_INFO.po_detail_id=carton.po_detail_id inner join quantity_size on carton.carton_id = quantity_size.carton_id  
and carton.attribute_status_id not in (1,3,4)) >0

begin
select distinct
po_header.po_number as PONumber,
vendor.name as Vendor,
po_status.description as POStatus,
po_detail.style as Style,
dbo.get_inventory_units(po_detail.po_detail_id) as inventoryunits,
dbo.get_staged_units(po_detail.po_detail_id) as StagedUnits,
dbo.get_shipped_units(po_detail.po_detail_id) as ShippedUnits,
'' as OtherUnits,
po_detail.po_detail_id as Id,
po_detail.cost as Cost,
po_detail.description as Description,
vendor_quality.description as Quality,
gender.description as Gender,
label.description as Label,
category.description as Category from po_detail,
po_header,
po_status,
vendor,
vendor_quality,
label,
category,
gender
where po_detail.po_header_id = po_header.po_header_id
and po_header.po_status_id = po_status.po_status_id
and po_header.vendor_id = vendor.vendor_id
and po_detail.gender_id = gender.gender_id
and po_detail.category_id = category.category_id
and po_detail.vendor_quality_id = vendor_quality.vendor_quality_id
and po_detail.label_id = label.label_id and po_header.po_number = @poNumber

UNION ALL

SECOND QUERY

select distinct
po_header.po_number as PONumber,
vendor.name as Vendor,
po_status.description as POStatus,
po_detail.style as Style,
'' as inventoryunits,
'' as StagedUnits,
'' as ShippedUnits,
dbo.get_other_inventory_units(po_detail.po_detail_id) as OtherUnits,
po_detail.po_detail_id as Id,
'' as Cost,
po_detail.description as Description,
vendor_quality.description as Quality,
gender.description as Gender,
label.description as Label,
category.description as Category from po_detail,
po_header,
po_status,
vendor,
vendor_quality,
label,
category,
gender
 where po_detail.po_header_id = po_header.po_header_id
and po_header.po_status_id = po_status.po_status_id
and po_header.vendor_id = vendor.vendor_id
and po_detail.gender_id = gender.gender_id
and po_detail.category_id = category.category_id
and po_detail.vendor_quality_id = vendor_quality.vendor_quality_id
and po_detail.label_id = label.label_id and po_header.po_number = @poNumber
order by vendor_quality.description, label.description, category.description, po_detail.description
end
else

begin
select distinct
po_header.po_number as PONumber,
vendor.name as Vendor,
po_status.description as POStatus,
po_detail.style as Style,
dbo.get_inventory_units(po_detail.po_detail_id) as inventoryunits,
dbo.get_staged_units(po_detail.po_detail_id) as StagedUnits,
dbo.get_shipped_units(po_detail.po_detail_id) as ShippedUnits,
'' as OtherUnits,
po_detail.po_detail_id as Id,
po_detail.cost as Cost,
po_detail.description as Description,
vendor_quality.description as Quality,
gender.description as Gender,
label.description as Label,
category.description as Category from po_detail,
po_header,
po_status,
vendor,
vendor_quality,
label,
category,
gender
where po_detail.po_header_id = po_header.po_header_id
and po_header.po_status_id = po_status.po_status_id
and po_header.vendor_id = vendor.vendor_id
and po_detail.gender_id = gender.gender_id
and po_detail.category_id = category.category_id
and po_detail.vendor_quality_id = vendor_quality.vendor_quality_id
and po_detail.label_id = label.label_id and po_header.po_number = @poNumber
end

The way i want the output is:
If the IF block returns true then the results from 1st query should be joined with the results from 2nd query.

The results from 2nd query have a non-zero and non-null column named "OtherUnits". And for what ever PO_Detail_ID this column is non-zero only that row should be added as an extra row to the results of first query.

Please let me know if i can explain it in a better way. I tried union but it gives me 10 + 10 rows where as i need something like for example if first query return 10 rows and second one returns only 3 then i need 13 rows back in total.
Microsoft SQL Server
Avatar of undefined
Last Comment
chapmandew
8/22/2022 - Mon
MonkeyPushButton
My understanding is that you're trying to reduce the redundancy of having the same query repeated twice in the above code,

I'd use a temp table and two inserts. You'll need to fill in the types of the columns you're selecting after the DECLARE table statement.
declare @count int, @sql nvarchar(1000)
declare @poNumber varchar(20)
 
DECLARE @Temp table (
        -- Add types to column names below.
	PONumber,
	Vendor,
	POStatus,
	Style,
	inventoryunits,
	StagedUnits,
	ShippedUnits,
	OtherUnits,
	Id,
	Cost,
	Description,
	Quality,
	Gender,
	Label,
	po_detail,
	po_header,
	po_status,
	vendor,
	vendor_quality,
	label,
	category,
	gender
)
 
-- First Select
INSERT @Temp
select distinct
	po_header.po_number as PONumber,
	vendor.name as Vendor,
	po_status.description as POStatus,
	po_detail.style as Style,
	dbo.get_inventory_units(po_detail.po_detail_id) as inventoryunits,
	dbo.get_staged_units(po_detail.po_detail_id) as StagedUnits,
	dbo.get_shipped_units(po_detail.po_detail_id) as ShippedUnits,
	'' as OtherUnits,
	po_detail.po_detail_id as Id,
	po_detail.cost as Cost,
	po_detail.description as Description,
	vendor_quality.description as Quality,
	gender.description as Gender,
	label.description as Label,
	category.description as Category from po_detail,
	po_header,
	po_status,
	vendor,
	vendor_quality,
	label,
	category,
	gender
where	po_detail.po_header_id = po_header.po_header_id
and 	po_header.po_status_id = po_status.po_status_id
and	po_header.vendor_id = vendor.vendor_id
and	po_detail.gender_id = gender.gender_id
and	po_detail.category_id = category.category_id
and	po_detail.vendor_quality_id = vendor_quality.vendor_quality_id
and	po_detail.label_id = label.label_id and po_header.po_number = @poNumber
 
 
if(select sum(quantity_size.quantity) from carton inner join (select po_detail_id from po_detail, po_header where po_header.po_header_id = po_detail.po_header_id and po_header.po_number=@poNumber)PO_INFO
on PO_INFO.po_detail_id=carton.po_detail_id inner join quantity_size on carton.carton_id = quantity_size.carton_id  
and carton.attribute_status_id not in (1,3,4)) >0
	-- Second select
	INSERT @Temp
	select distinct
	po_header.po_number as PONumber,
	vendor.name as Vendor,
	po_status.description as POStatus,
	po_detail.style as Style,
	'' as inventoryunits,
	'' as StagedUnits,
	'' as ShippedUnits,
	dbo.get_other_inventory_units(po_detail.po_detail_id) as OtherUnits,
	po_detail.po_detail_id as Id,
	'' as Cost,
	po_detail.description as Description,
	vendor_quality.description as Quality,
	gender.description as Gender,
	label.description as Label,
	category.description as Category from po_detail,
	po_header,
	po_status,
	vendor,
	vendor_quality,
	label,
	category,
	gender
	 where po_detail.po_header_id = po_header.po_header_id
	and po_header.po_status_id = po_status.po_status_id
	and po_header.vendor_id = vendor.vendor_id
	and po_detail.gender_id = gender.gender_id
	and po_detail.category_id = category.category_id
	and po_detail.vendor_quality_id = vendor_quality.vendor_quality_id
	and po_detail.label_id = label.label_id and po_header.po_number = @poNumber
	order by vendor_quality.description, label.description, category.description, po_detail.description
 
SELECT * FROM @Temp

Open in new window

dotnetpro
ASKER
Ok, Let me try this.
dotnetpro
ASKER
I am getting error near the keyword INSERT
Your help has saved me hundreds of hours of internet surfing.
fblack61
MonkeyPushButton
Could I see the sql as you've modified it please?
dotnetpro
ASKER
declare @count int, @sql nvarchar(1000)
declare @poNumber varchar(20)
set @poNumber='01-9158'
DECLARE @Temp table (
        -- Add types to column names below.
        PONumber  varchar(10),
        Vendor varchar(100),
        POStatus varchar(10),
        Style nvarchar(200),
        inventoryunits varchar(20),
        StagedUnits varchar(20),
        ShippedUnits varchar(20),
        OtherUnits varchar(20),
        Id bigint,
        Cost varchar(10),
        Description nvarchar(200),
        Quality varchar(100),
        Gender varchar(100),
        Label nvarchar(200),              
        category varchar(100)
 
-- First Select
INSERT  INTO @Temp
select distinct
        po_header.po_number,
        vendor.name ,
        po_status.description ,
        po_detail.style,
        dbo.get_inventory_units(po_detail.po_detail_id) ,
        dbo.get_staged_units(po_detail.po_detail_id) ,
        dbo.get_shipped_units(po_detail.po_detail_id) ,
        '' ,
        po_detail.po_detail_id ,
        po_detail.cost,
        po_detail.description ,
        vendor_quality.description ,
        gender.description ,
        label.description ,
        category.description  
            from po_detail,
        po_header,
        po_status,
        vendor,
        vendor_quality,
        label,
        category,
        gender
where   po_detail.po_header_id = po_header.po_header_id
and     po_header.po_status_id = po_status.po_status_id
and     po_header.vendor_id = vendor.vendor_id
and     po_detail.gender_id = gender.gender_id
and     po_detail.category_id = category.category_id
and     po_detail.vendor_quality_id = vendor_quality.vendor_quality_id
and     po_detail.label_id = label.label_id and po_header.po_number = @poNumber

if(select sum(quantity_size.quantity) from carton inner join (select po_detail_id from po_detail, po_header where po_header.po_header_id = po_detail.po_header_id and po_header.po_number=@poNumber)PO_INFO
on PO_INFO.po_detail_id=carton.po_detail_id inner join quantity_size on carton.carton_id = quantity_size.carton_id  
and carton.attribute_status_id not in (1,3,4)) >0
        -- Second select
        INSERT @Temp
        select distinct
        po_header.po_number as PONumber,
        vendor.name as Vendor,
        po_status.description as POStatus,
        po_detail.style as Style,
        '' as inventoryunits,
        '' as StagedUnits,
        '' as ShippedUnits,
        dbo.get_other_inventory_units(po_detail.po_detail_id) as OtherUnits,
        po_detail.po_detail_id as Id,
        '' as Cost,
        po_detail.description as Description,
        vendor_quality.description as Quality,
        gender.description as Gender,
        label.description as Label,
        category.description as Category from po_detail,
        po_header,
        po_status,
        vendor,
        vendor_quality,
        label,
        category,
        gender
         where po_detail.po_header_id = po_header.po_header_id
        and po_header.po_status_id = po_status.po_status_id
        and po_header.vendor_id = vendor.vendor_id
        and po_detail.gender_id = gender.gender_id
        and po_detail.category_id = category.category_id
        and po_detail.vendor_quality_id = vendor_quality.vendor_quality_id
        and po_detail.label_id = label.label_id and po_header.po_number = @poNumber
        order by vendor_quality.description, label.description, category.description, po_detail.description
 
SELECT * FROM @Temp
dotnetpro
ASKER
Oops i got it. Did not close the parenthese
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
dotnetpro
ASKER
I see the result looks good. But then can it be customized to show the result from 2nd query right underneath the matching Id from the first query ??
ASKER CERTIFIED SOLUTION
MonkeyPushButton
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Scott Pletcher
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
chapmandew
MonkeyPushButton, that is one of the best names I've seen on here....nice work.  :)
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent