UNION problem
-
Dear People ,
can anybody helpme out in this case.
i need to selectively combine two recordsets. if the first one does not return any rows it should not appear in the result set. if it returns then it should be combined with another recordset which is always returns rows.
say like this ,
if 1=1
select 1 'hi'
union
if 1=2
select 2 'hi'
as is said sometimes the second recordset will return values.
Guru -
select * from one where where_cond
union
select * from two where exists (select * from one where where_cond) [AND where_cond]
-
thanks mice for the reply.
your solution is fine . but in my case
i have some views in the from clause that may or may not be there. so i do not want to process the statement at all. Is that possible ??
Guru -
Is there anything common between the two recordsets? Can you post the queries?
Far away is close at hand in the images of elsewhere.
Anon. -
You could insert your result sets into a temp table.
-
thanks david and jxflagg..
This is a SCM database. The development model is COM. I am developing the purchase as well as subcontracting cycle.
So my procedures have to deal with both the cycles.
some components like receiveing goods are common to both the cycles. Now, in this particular case i have to select all documents from purchase components as well as subcontracting components. The components have their views exposed.
now if a company wants only purchase i should not process the subcontract views.
How to write a union ?
my query..
if @refdocenu1hdr_tmp = 'PO' -- purchase order
begin
select distinct
rtrim(o.ouinstname) 'CREATED_ATMLT',
rtrim(convert(char(10),
h.docdate,120)) 'DATEML',
rtrim(h.folder) 'FOLDERML',
case h.referencedoc
when 'SO' then rtrim(clo_cust_name)
else rtrim(supplier_name)
end 'FROMSUPPLIERNAME',
rtrim(h.docno) 'REFDOC_MLT',
rtrim(a.paramdesc) 'REFERENCEDOCML',
h.referreddoclineno 'REFERREDDOCLINENOML',
rtrim(b.paramdesc) 'REFERREDDOCML',
rtrim(h.referreddocno) 'REFERREDDOCNOML',
rtrim(h.suppliercode) 'SUPPCUSTML'
from gr_po_hdr_vw h(nolock),
(select referencedoc , ouinstid , docno , max(amendno) as maxamendno
from gr_po_hdr_vw (nolock)
group
by referencedoc , ouinstid , docno ) m ,
gr_po_line_vw d (nolock),
depdb..fw_admin_view_ouinstance o (nolock),
supp_supdtls_vw (nolock),
cust_lo_info_vw (nolock),
component_metadata_table a (nolock),
component_metadata_table b(nolock)
where h.referencedoc = m.referencedoc
and h.ouinstid = m.ouinstid
and h.docno = m.docno
and h.amendno = m.maxamendno
and h.referencedoc like case @refdocenu1hdr_tmp
when 'PG' then '%'
else @refdocenu1hdr_tmp
end
and h.referreddoc like case @refdocenu1hdr_tmp
when 'PG' then @refdocenu1hdr_tmp
else '%'
end
and h.ouinstid = @filterouid_tmp
and d.linestatus in ( 'OPEN' , 'NT CLOSED' )
and h.status = 'OPEN'
and isnull(h.groption,'') = 'YES'
and isnull(h.invbeforegr,'') = case h.referreddoc
when 'PG' then 'YES'
else 'NO'
end
and h.doctype like @refdoctypehdr_tmp
and h.doctype <> 'DROPSHIP'
and case @refdocenu1hdr_tmp
when 'PG' then h.referreddocno
else h.docno
end between @refdocnumberfrom_tmp and @refdocnumberto_tmp
and h.docdate between @refdocdate_from_tmp and @refdocdate_to_tmp
and (isnull(h.folder,'%')) like @folder_tmp
and h.ouinstid = d.ouinstid
and h.docno = d.docno
and h.referencedoc = d.referencedoc
and h.amendno = d.amendno
and d.receiptou = @ctxt_ouinstance_tmp
and d.linestatus in ( 'OPEN' , 'NT CLOSED' )
and d.balanceqty > 0
and isnull(d.adhocitemcls,'') like @adhocitemclasshdr_tmp
and isnull(d.itemcode,'') like @item_code_search_tmp
and isnull(d.variant,'') like @variantno_tmp
and isnull(d.itemdesc,'') like @itemdescriptionhdr_tmp
and h.suppliercode like @suppcusthdr_tmp
and o.ouinstid = h.ouinstid
and loid = @lo_id
and supplier_code =* h.suppliercode
and supplier_name like @ename_tmp
and clo_lo = @lo_id
and clo_cust_code =* h.suppliercode
and clo_cust_name like @ename_tmp
and a.componentname = 'GR'
and a.paramcategory = 'META'
and a.paramtype = 'REF_DOC'
and a.paramcode = h.referencedoc
and a.langid = @ctxt_language_tmp
and b.componentname = 'GR'
and b.paramcategory = 'META'
and b.paramtype = 'REFD_DOC'
and b.paramcode =* h.referreddoc
and b.langid = @ctxt_language_tmp
order
by DATEML desc ,
REFDOC_MLT desc
if @@rowcount = 0
begin
-- There are no documents as per Serach Criteria
select @m_errorid = 1400018
return
end
end
if @refdocenu1hdr_tmp = 'RS' -- release slip
begin
select distinct
rtrim(o.ouinstname) 'CREATED_ATMLT',
rtrim(convert(char(10),
h.docdate,120)) 'DATEML',
rtrim(h.folder) 'FOLDERML',
case h.referencedoc
when 'SO' then rtrim(clo_cust_name)
else rtrim(supplier_name)
end 'FROMSUPPLIERNAME',
rtrim(h.docno) 'REFDOC_MLT',
rtrim(a.paramdesc) 'REFERENCEDOCML',
h.referreddoclineno 'REFERREDDOCLINENOML',
rtrim(b.paramdesc) 'REFERREDDOCML',
rtrim(h.referreddocno) 'REFERREDDOCNOML',
rtrim(h.suppliercode) 'SUPPCUSTML'
from gr_prs_hdr_vw h (nolock),
(select referencedoc , ouinstid , docno , max(amendno) as maxamendno
from gr_prs_hdr_vw(nolock)
group
by referencedoc , ouinstid , docno ) m ,
gr_prs_line_vw d (nolock),
depdb..fw_admin_view_ouinstance o (nolock),
supp_supdtls_vw (nolock),
cust_lo_info_vw (nolock),
component_metadata_table a (nolock),
component_metadata_table b(nolock)
where h.referencedoc = m.referencedoc
and h.ouinstid = m.ouinstid
and h.docno = m.docno
and h.amendno = m.maxamendno
and h.referencedoc like case @refdocenu1hdr_tmp
when 'PG' then '%'
else @refdocenu1hdr_tmp
end
and h.referreddoc like case @refdocenu1hdr_tmp
when 'PG' then @refdocenu1hdr_tmp
else '%'
end
and h.ouinstid = @filterouid_tmp
and d.linestatus in ( 'OPEN' , 'NT CLOSED' )
and h.status = 'OPEN'
and isnull(h.groption,'') = 'YES'
and isnull(h.invbeforegr,'') = case h.referreddoc
when 'PG' then 'YES'
else 'NO'
end
and h.doctype like @refdoctypehdr_tmp
and h.doctype <> 'DROPSHIP'
and case @refdocenu1hdr_tmp
when 'PG' then h.referreddocno
else h.docno
end between @refdocnumberfrom_tmp and @refdocnumberto_tmp
and h.docdate between @refdocdate_from_tmp and @refdocdate_to_tmp
and (isnull(h.folder,'%')) like @folder_tmp
and h.ouinstid = d.ouinstid
and h.docno = d.docno
and h.referencedoc = d.referencedoc
and h.amendno = d.amendno
and d.receiptou = @ctxt_ouinstance_tmp
and d.linestatus in ( 'OPEN' , 'NT CLOSED' )
and d.balanceqty > 0
and isnull(d.adhocitemcls,'') like @adhocitemclasshdr_tmp
and isnull(d.itemcode,'') like @item_code_search_tmp
and isnull(d.variant,'') like @variantno_tmp
and isnull(d.itemdesc,'') like @itemdescriptionhdr_tmp
and h.suppliercode like @suppcusthdr_tmp
and o.ouinstid = h.ouinstid
and loid = @lo_id
and supplier_code =* h.suppliercode
and supplier_name like @ename_tmp
and clo_lo = @lo_id
and clo_cust_code =* h.suppliercode
and clo_cust_name like @ename_tmp
and a.componentname = 'GR'
and a.paramcategory = 'META'
and a.paramtype = 'REF_DOC'
and a.paramcode = h.referencedoc
and a.langid = @ctxt_language_tmp
and b.componentname = 'GR'
and b.paramcategory = 'META'
and b.paramtype = 'REFD_DOC'
and b.paramcode =* h.referreddoc
and b.langid = @ctxt_language_tmp
order
by DATEML desc ,
REFDOC_MLT desc
if @@rowcount = 0
begin
-- There are no documents as per Serach Criteria
select @m_errorid = 1400018
return
end
end
if @refdocenu1hdr_tmp = 'SO' -- sale order
begin
select distinct
rtrim(o.ouinstname) 'CREATED_ATMLT',
rtrim(convert(char(10),
h.docdate,120)) 'DATEML',
rtrim(h.folder) 'FOLDERML',
case h.referencedoc
when 'SO' then rtrim(clo_cust_name)
else rtrim(supplier_name)
end 'FROMSUPPLIERNAME',
rtrim(h.docno) 'REFDOC_MLT',
rtrim(a.paramdesc) 'REFERENCEDOCML',
h.referreddoclineno 'REFERREDDOCLINENOML',
rtrim(b.paramdesc) 'REFERREDDOCML',
rtrim(h.referreddocno) 'REFERREDDOCNOML',
rtrim(h.suppliercode) 'SUPPCUSTML'
from gr_so_hdr_vw h(nolock),
(select referencedoc , ouinstid , docno , max(amendno) as maxamendno
from gr_so_hdr_vw(nolock)
group
by referencedoc , ouinstid , docno ) m ,
gr_so_line_vw d (nolock),
depdb..fw_admin_view_ouinstance o (nolock),
supp_supdtls_vw (nolock),
cust_lo_info_vw (nolock),
component_metadata_table a (nolock),
component_metadata_table b(nolock)
where h.referencedoc = m.referencedoc
and h.ouinstid = m.ouinstid
and h.docno = m.docno
and h.amendno = m.maxamendno
and h.referencedoc like case @refdocenu1hdr_tmp
when 'PG' then '%'
else @refdocenu1hdr_tmp
end
and h.referreddoc like case @refdocenu1hdr_tmp
when 'PG' then @refdocenu1hdr_tmp
else '%'
end
and h.ouinstid = @filterouid_tmp
and d.linestatus in ( 'OPEN' , 'NT CLOSED' )
and h.status = 'OPEN'
and isnull(h.groption,'') = 'YES'
and isnull(h.invbeforegr,'') = case h.referreddoc
when 'PG' then 'YES'
else 'NO'
end
and h.doctype like @refdoctypehdr_tmp
and h.doctype <> 'DROPSHIP'
and case @refdocenu1hdr_tmp
when 'PG' then h.referreddocno
else h.docno
end between @refdocnumberfrom_tmp and @refdocnumberto_tmp
and h.docdate between @refdocdate_from_tmp and @refdocdate_to_tmp
and (isnull(h.folder,'%')) like @folder_tmp
and h.ouinstid = d.ouinstid
and h.docno = d.docno
and h.referencedoc = d.referencedoc
and h.amendno = d.amendno
and d.receiptou = @ctxt_ouinstance_tmp
and d.linestatus in ( 'OPEN' , 'NT CLOSED' )
and d.balanceqty > 0
and isnull(d.adhocitemcls,'') like @adhocitemclasshdr_tmp
and isnull(d.itemcode,'') like @item_code_search_tmp
and isnull(d.variant,'') like @variantno_tmp
and isnull(d.itemdesc,'') like @itemdescriptionhdr_tmp
and h.suppliercode like @suppcusthdr_tmp
and o.ouinstid = h.ouinstid
and loid = @lo_id
and supplier_code =* h.suppliercode
and supplier_name like @ename_tmp
and clo_lo = @lo_id
and clo_cust_code =* h.suppliercode
and clo_cust_name like @ename_tmp
and a.componentname = 'GR'
and a.paramcategory = 'META'
and a.paramtype = 'REF_DOC'
and a.paramcode = h.referencedoc
and a.langid = @ctxt_language_tmp
and b.componentname = 'GR'
and b.paramcategory = 'META'
and b.paramtype = 'REFD_DOC'
and b.paramcode =* h.referreddoc
and b.langid = @ctxt_language_tmp
order
by DATEML desc ,
REFDOC_MLT desc
if @@rowcount = 0
begin
-- There are no documents as per Serach Criteria
select @m_errorid = 1400018
return
end
end
if @refdocenu1hdr_tmp = 'SC' -- Subcontract order
begin
select distinct
rtrim(o.ouinstname) 'CREATED_ATMLT',
rtrim(convert(char(10),
h.docdate,120)) 'DATEML',
rtrim(h.folder) 'FOLDERML',
case h.referencedoc
when 'SO' then rtrim(clo_cust_name)
else rtrim(supplier_name)
end 'FROMSUPPLIERNAME',
rtrim(h.docno) 'REFDOC_MLT',
rtrim(a.paramdesc) 'REFERENCEDOCML',
h.referreddoclineno 'REFERREDDOCLINENOML',
rtrim(b.paramdesc) 'REFERREDDOCML',
rtrim(h.referreddocno) 'REFERREDDOCNOML',
rtrim(h.suppliercode) 'SUPPCUSTML'
from gr_sco_hdr_vw h(nolock),
(select referencedoc , ouinstid , docno , max(amendno) as maxamendno
from gr_sco_hdr_vw(nolock)
group
by referencedoc , ouinstid , docno ) m ,
gr_sco_line_vw d (nolock),
depdb..fw_admin_view_ouinstance o (nolock),
supp_supdtls_vw (nolock),
cust_lo_info_vw (nolock),
component_metadata_table a (nolock),
component_metadata_table b(nolock)
where h.referencedoc = m.referencedoc
and h.ouinstid = m.ouinstid
and h.docno = m.docno
and h.amendno = m.maxamendno
and h.referencedoc like case @refdocenu1hdr_tmp
when 'PG' then '%'
else @refdocenu1hdr_tmp
end
and h.referreddoc like case @refdocenu1hdr_tmp
when 'PG' then @refdocenu1hdr_tmp
else '%'
end
and h.ouinstid = @filterouid_tmp
and d.linestatus in ( 'OPEN' , 'NT CLOSED' )
and h.status = 'OPEN'
and isnull(h.groption,'') = 'YES'
and isnull(h.invbeforegr,'') = case h.referreddoc
when 'PG' then 'YES'
else 'NO'
end
and h.doctype like @refdoctypehdr_tmp
and h.doctype <> 'DROPSHIP'
and case @refdocenu1hdr_tmp
when 'PG' then h.referreddocno
else h.docno
end between @refdocnumberfrom_tmp and @refdocnumberto_tmp
and h.docdate between @refdocdate_from_tmp and @refdocdate_to_tmp
and (isnull(h.folder,'%')) like @folder_tmp
and h.ouinstid = d.ouinstid
and h.docno = d.docno
and h.referencedoc = d.referencedoc
and h.amendno = d.amendno
and d.receiptou = @ctxt_ouinstance_tmp
and h.ouinstid = @filterouid_tmp
and d.linestatus in ( 'OPEN' , 'NT CLOSED' )
and d.balanceqty > 0
and isnull(d.adhocitemcls,'') like @adhocitemclasshdr_tmp
and isnull(d.itemcode,'') like @item_code_search_tmp
and isnull(d.variant,'') like @variantno_tmp
and isnull(d.itemdesc,'') like @itemdescriptionhdr_tmp
and h.suppliercode like @suppcusthdr_tmp
and o.ouinstid = h.ouinstid
and loid = @lo_id
and supplier_code =* h.suppliercode
and supplier_name like @ename_tmp
and clo_lo = @lo_id
and clo_cust_code =* h.suppliercode
and clo_cust_name like @ename_tmp
and a.componentname = 'GR'
and a.paramcategory = 'META'
and a.paramtype = 'REF_DOC'
and a.paramcode = h.referencedoc
and a.langid = @ctxt_language_tmp
and b.componentname = 'GR'
and b.paramcategory = 'META'
and b.paramtype = 'REFD_DOC'
and b.paramcode =* h.referreddoc
and b.langid = @ctxt_language_tmp
order
by DATEML desc ,
REFDOC_MLT desc
if @@rowcount = 0
begin
-- There are no documents as per Serach Criteria
select @m_errorid = 1400018
return
end
end
if @refdocenu1hdr_tmp = 'SR'
subcontract release slip
begin
select distinct
rtrim(o.ouinstname) 'CREATED_ATMLT',
rtrim(convert(char(10),
h.docdate,120)) 'DATEML',
rtrim(h.folder) 'FOLDERML',
case h.referencedoc
when 'SO' then rtrim(clo_cust_name)
else rtrim(supplier_name)
end 'FROMSUPPLIERNAME',
rtrim(h.docno) 'REFDOC_MLT',
rtrim(a.paramdesc) 'REFERENCEDOCML',
h.referreddoclineno 'REFERREDDOCLINENOML',
rtrim(b.paramdesc) 'REFERREDDOCML',
rtrim(h.referreddocno) 'REFERREDDOCNOML',
rtrim(h.suppliercode) 'SUPPCUSTML'
from gr_scrs_hdr_vw h(nolock),
(select referencedoc , ouinstid , docno , max(amendno) as maxamendno
from gr_scrs_hdr_vw(nolock)
group
by referencedoc , ouinstid , docno ) m ,
gr_scrs_line_vw d (nolock),
depdb..fw_admin_view_ouinstance o (nolock),
supp_supdtls_vw (nolock),
cust_lo_info_vw (nolock),
component_metadata_table a (nolock),
component_metadata_table b(nolock)
where h.referencedoc = m.referencedoc
and h.ouinstid = m.ouinstid
and h.docno = m.docno
and h.amendno = m.maxamendno
and h.referencedoc like case @refdocenu1hdr_tmp
when 'PG' then '%'
else @refdocenu1hdr_tmp
end
and h.referreddoc like case @refdocenu1hdr_tmp
when 'PG' then @refdocenu1hdr_tmp
else '%'
end
and h.ouinstid = @filterouid_tmp
and d.linestatus in ( 'OPEN' , 'NT CLOSED' )
and h.status = 'OPEN'
and isnull(h.groption,'') = 'YES'
and isnull(h.invbeforegr,'') = case h.referreddoc
when 'PG' then 'YES'
else 'NO'
end
and h.doctype like @refdoctypehdr_tmp
and h.doctype <> 'DROPSHIP'
and case @refdocenu1hdr_tmp
when 'PG' then h.referreddocno
else h.docno
end between @refdocnumberfrom_tmp and @refdocnumberto_tmp
and h.docdate between @refdocdate_from_tmp and @refdocdate_to_tmp
and (isnull(h.folder,'%')) like @folder_tmp
and h.ouinstid = d.ouinstid
and h.docno = d.docno
and h.referencedoc = d.referencedoc
and h.amendno = d.amendno
and d.receiptou = @ctxt_ouinstance_tmp
and h.ouinstid = @filterouid_tmp
and d.linestatus in ( 'OPEN' , 'NT CLOSED' )
and d.balanceqty > 0
and isnull(d.adhocitemcls,'') like @adhocitemclasshdr_tmp
and isnull(d.itemcode,'') like @item_code_search_tmp
and isnull(d.variant,'') like @variantno_tmp
and isnull(d.itemdesc,'') like @itemdescriptionhdr_tmp
and h.suppliercode like @suppcusthdr_tmp
and o.ouinstid = h.ouinstid
and loid = @lo_id
and supplier_code =* h.suppliercode
and supplier_name like @ename_tmp
and clo_lo = @lo_id
and clo_cust_code =* h.suppliercode
and clo_cust_name like @ename_tmp
and a.componentname = 'GR'
and a.paramcategory = 'META'
and a.paramtype = 'REF_DOC'
and a.paramcode = h.referencedoc
and a.langid = @ctxt_language_tmp
and b.componentname = 'GR'
and b.paramcategory = 'META'
and b.paramtype = 'REFD_DOC'
and b.paramcode =* h.referreddoc
and b.langid = @ctxt_language_tmp
order
by DATEML desc ,
REFDOC_MLT desc
if @@rowcount = 0
begin
-- There are no documents as per Serach Criteria
select @m_errorid = 1400018
return
end
end
Are temp tables faster than just plain select ??
Guru -
thanks david and jxflagg..
This is a SCM database. The development model is COM. I am developing the purchase as well as subcontracting cycle.
So my procedures have to deal with both the cycles.
some components like receiveing goods are common to both the cycles. Now, in this particular case i have to select all documents from purchase components as well as subcontracting components. The components have their views exposed.
now if a company wants only purchase i should not process the subcontract views.
How to write a union ?
my query..
if @refdocenu1hdr_tmp = 'PO' -- purchase order
begin
select distinct
rtrim(o.ouinstname) 'CREATED_ATMLT',
rtrim(convert(char(10),
h.docdate,120)) 'DATEML',
rtrim(h.folder) 'FOLDERML',
case h.referencedoc
when 'SO' then rtrim(clo_cust_name)
else rtrim(supplier_name)
end 'FROMSUPPLIERNAME',
rtrim(h.docno) 'REFDOC_MLT',
rtrim(a.paramdesc) 'REFERENCEDOCML',
h.referreddoclineno 'REFERREDDOCLINENOML',
rtrim(b.paramdesc) 'REFERREDDOCML',
rtrim(h.referreddocno) 'REFERREDDOCNOML',
rtrim(h.suppliercode) 'SUPPCUSTML'
from gr_po_hdr_vw h(nolock),
(select referencedoc , ouinstid , docno , max(amendno) as maxamendno
from gr_po_hdr_vw (nolock)
group
by referencedoc , ouinstid , docno ) m ,
gr_po_line_vw d (nolock),
depdb..fw_admin_view_ouinstance o (nolock),
supp_supdtls_vw (nolock),
cust_lo_info_vw (nolock),
component_metadata_table a (nolock),
component_metadata_table b(nolock)
where h.referencedoc = m.referencedoc
and h.ouinstid = m.ouinstid
and h.docno = m.docno
and h.amendno = m.maxamendno
and h.referencedoc like case @refdocenu1hdr_tmp
when 'PG' then '%'
else @refdocenu1hdr_tmp
end
and h.referreddoc like case @refdocenu1hdr_tmp
when 'PG' then @refdocenu1hdr_tmp
else '%'
end
and h.ouinstid = @filterouid_tmp
and d.linestatus in ( 'OPEN' , 'NT CLOSED' )
and h.status = 'OPEN'
and isnull(h.groption,'') = 'YES'
and isnull(h.invbeforegr,'') = case h.referreddoc
when 'PG' then 'YES'
else 'NO'
end
and h.doctype like @refdoctypehdr_tmp
and h.doctype <> 'DROPSHIP'
and case @refdocenu1hdr_tmp
when 'PG' then h.referreddocno
else h.docno
end between @refdocnumberfrom_tmp and @refdocnumberto_tmp
and h.docdate between @refdocdate_from_tmp and @refdocdate_to_tmp
and (isnull(h.folder,'%')) like @folder_tmp
and h.ouinstid = d.ouinstid
and h.docno = d.docno
and h.referencedoc = d.referencedoc
and h.amendno = d.amendno
and d.receiptou = @ctxt_ouinstance_tmp
and d.linestatus in ( 'OPEN' , 'NT CLOSED' )
and d.balanceqty > 0
and isnull(d.adhocitemcls,'') like @adhocitemclasshdr_tmp
and isnull(d.itemcode,'') like @item_code_search_tmp
and isnull(d.variant,'') like @variantno_tmp
and isnull(d.itemdesc,'') like @itemdescriptionhdr_tmp
and h.suppliercode like @suppcusthdr_tmp
and o.ouinstid = h.ouinstid
and loid = @lo_id
and supplier_code =* h.suppliercode
and supplier_name like @ename_tmp
and clo_lo = @lo_id
and clo_cust_code =* h.suppliercode
and clo_cust_name like @ename_tmp
and a.componentname = 'GR'
and a.paramcategory = 'META'
and a.paramtype = 'REF_DOC'
and a.paramcode = h.referencedoc
and a.langid = @ctxt_language_tmp
and b.componentname = 'GR'
and b.paramcategory = 'META'
and b.paramtype = 'REFD_DOC'
and b.paramcode =* h.referreddoc
and b.langid = @ctxt_language_tmp
order
by DATEML desc ,
REFDOC_MLT desc
if @@rowcount = 0
begin
-- There are no documents as per Serach Criteria
select @m_errorid = 1400018
return
end
end
if @refdocenu1hdr_tmp = 'SC' -- Subcontract order
begin
select distinct
rtrim(o.ouinstname) 'CREATED_ATMLT',
rtrim(convert(char(10),
h.docdate,120)) 'DATEML',
rtrim(h.folder) 'FOLDERML',
case h.referencedoc
when 'SO' then rtrim(clo_cust_name)
else rtrim(supplier_name)
end 'FROMSUPPLIERNAME',
rtrim(h.docno) 'REFDOC_MLT',
rtrim(a.paramdesc) 'REFERENCEDOCML',
h.referreddoclineno 'REFERREDDOCLINENOML',
rtrim(b.paramdesc) 'REFERREDDOCML',
rtrim(h.referreddocno) 'REFERREDDOCNOML',
rtrim(h.suppliercode) 'SUPPCUSTML'
from gr_sco_hdr_vw h(nolock),
(select referencedoc , ouinstid , docno , max(amendno) as maxamendno
from gr_sco_hdr_vw(nolock)
group
by referencedoc , ouinstid , docno ) m ,
gr_sco_line_vw d (nolock),
depdb..fw_admin_view_ouinstance o (nolock),
supp_supdtls_vw (nolock),
cust_lo_info_vw (nolock),
component_metadata_table a (nolock),
component_metadata_table b(nolock)
where h.referencedoc = m.referencedoc
and h.ouinstid = m.ouinstid
and h.docno = m.docno
and h.amendno = m.maxamendno
and h.referencedoc like case @refdocenu1hdr_tmp
when 'PG' then '%'
else @refdocenu1hdr_tmp
end
and h.referreddoc like case @refdocenu1hdr_tmp
when 'PG' then @refdocenu1hdr_tmp
else '%'
end
and h.ouinstid = @filterouid_tmp
and d.linestatus in ( 'OPEN' , 'NT CLOSED' )
and h.status = 'OPEN'
and isnull(h.groption,'') = 'YES'
and isnull(h.invbeforegr,'') = case h.referreddoc
when 'PG' then 'YES'
else 'NO'
end
and h.doctype like @refdoctypehdr_tmp
and h.doctype <> 'DROPSHIP'
and case @refdocenu1hdr_tmp
when 'PG' then h.referreddocno
else h.docno
end between @refdocnumberfrom_tmp and @refdocnumberto_tmp
and h.docdate between @refdocdate_from_tmp and @refdocdate_to_tmp
and (isnull(h.folder,'%')) like @folder_tmp
and h.ouinstid = d.ouinstid
and h.docno = d.docno
and h.referencedoc = d.referencedoc
and h.amendno = d.amendno
and d.receiptou = @ctxt_ouinstance_tmp
and h.ouinstid = @filterouid_tmp
and d.linestatus in ( 'OPEN' , 'NT CLOSED' )
and d.balanceqty > 0
and isnull(d.adhocitemcls,'') like @adhocitemclasshdr_tmp
and isnull(d.itemcode,'') like @item_code_search_tmp
and isnull(d.variant,'') like @variantno_tmp
and isnull(d.itemdesc,'') like @itemdescriptionhdr_tmp
and h.suppliercode like @suppcusthdr_tmp
and o.ouinstid = h.ouinstid
and loid = @lo_id
and supplier_code =* h.suppliercode
and supplier_name like @ename_tmp
and clo_lo = @lo_id
and clo_cust_code =* h.suppliercode
and clo_cust_name like @ename_tmp
and a.componentname = 'GR'
and a.paramcategory = 'META'
and a.paramtype = 'REF_DOC'
and a.paramcode = h.referencedoc
and a.langid = @ctxt_language_tmp
and b.componentname = 'GR'
and b.paramcategory = 'META'
and b.paramtype = 'REFD_DOC'
and b.paramcode =* h.referreddoc
and b.langid = @ctxt_language_tmp
order
by DATEML desc ,
REFDOC_MLT desc
if @@rowcount = 0
begin
-- There are no documents as per Serach Criteria
select @m_errorid = 1400018
return
end
end
Are temp tables faster than just plain select ??
Guru -
quote:
Three much
some very Serious change in DB design required I Think
-
Hi Gurumoorthy
Are you anyway connected or working with Ramco India ??. I am working with Ramco USA. By looking at the query, it looks like Ramco solution (product), backend code. I am just checking. Please email me at dsubramani@rsc.ramco.com, if required. Thanks
Dharma
-
Wow what a query, still trying to get my head round it. Suggest in this case to put results in temp tables and then check / union from there.
quote:
Are temp tables faster than just plain select
Depends on what is required, volume, indexes and the like. Comparing a select with a select into temp table/select from temp table would result in poorer performance for temp table due to creation and insertion. I usually only use temp tables where no other solution presents itself or there is an improvement in performance after comparison.
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks people i will try to use temp tables and get back with the results.
Guru
Guru -
This example does NOT deal with all of your criteria, but just provided to show a point. If a "Constant" evaluation in a WHERE clause evaluates to FALSE, SQL Server will essentially drop that portion from the query plan, and only provide the columns in the result (this is very fast). Therefore, if you can arrange to UNION ALL the various "main source" tables, maybe as one "derived table", and use WHERE condition to have really only one of the SELECT in the UNION actually having a performance effect.
Sample below for ilustration only. Change the value of @refdocenu1hdr_tmp, and do a "display estimated execution plan". You should see the percentage of the execution shift from UNION to UNION as the value of @refdocenu1hdr_tmp changes.
Select * from gr_po_hdr_vw h(nolock)
Join gr_po_line_vw d (nolock)
On and h.ouinstid = d.ouinstid and h.docno = d.docno
Where @refdocenu1hdr_tmp = 'PO' -- purchase order
UNION ALL
Select * from gr_prs_hdr_vw h(nolock)
Join gr_prs_line_vw d (nolock)
On and h.ouinstid = d.ouinstid and h.docno = d.docno
Where @refdocenu1hdr_tmp = 'RS' -- release slip
UNION ALL
Select * from gr_prs_hdr_vw h(nolock)
Join gr_prs_line_vw d (nolock)
On and h.ouinstid = d.ouinstid and h.docno = d.docno
Where @refdocenu1hdr_tmp = 'SO' -- sale order
UNION ALL
Select * from gr_sco_hdr_vw h(nolock)
Join gr_sco_line_vw d (nolock)
On and h.ouinstid = d.ouinstid and h.docno = d.docno
Where @refdocenu1hdr_tmp = 'SC' -- Subcontract order
UNION ALL
Select * from gr_scrs_hdr_vw h(nolock)
Join gr_scrs_line_vw d (nolock)
On and h.ouinstid = d.ouinstid and h.docno = d.docno
Where @refdocenu1hdr_tmp = 'SR' -- subcontract release slip
Once you understand the BITs, all the pieces come together
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply