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
selectdistinct
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)
elsertrim(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'
fromgr_po_hdr_vw h(nolock),
(select referencedoc , ouinstid , docno , max(amendno) as maxamendno
fromgr_po_hdr_vw (nolock)
group
byreferencedoc , 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)
whereh.referencedoc=m.referencedoc
andh.ouinstid=m.ouinstid
andh.docno=m.docno
and h.amendno =m.maxamendno
andh.referencedoclikecase @refdocenu1hdr_tmp
when 'PG' then '%'
else @refdocenu1hdr_tmp
end
andh.referreddoclikecase @refdocenu1hdr_tmp
when 'PG' then @refdocenu1hdr_tmp
else '%'
end
andh.ouinstid=@filterouid_tmp
andd.linestatusin( 'OPEN' , 'NT CLOSED' )
andh.status='OPEN'
andisnull(h.groption,'')='YES'
andisnull(h.invbeforegr,'')=case h.referreddoc
when 'PG' then 'YES'
else 'NO'
end
andh.doctypelike@refdoctypehdr_tmp
andh.doctype<>'DROPSHIP'
andcase @refdocenu1hdr_tmp
when 'PG' then h.referreddocno
else h.docno
endbetween@refdocnumberfrom_tmpand@refdocnumberto_tmp
andh.docdatebetween@refdocdate_from_tmpand@refdocdate_to_tmp
and(isnull(h.folder,'%'))like@folder_tmp
andh.ouinstid=d.ouinstid
andh.docno=d.docno
andh.referencedoc=d.referencedoc
and h.amendno = d.amendno
andd.receiptou=@ctxt_ouinstance_tmp
andd.linestatusin( 'OPEN' , 'NT CLOSED' )
andd.balanceqty>0
andisnull(d.adhocitemcls,'')like@adhocitemclasshdr_tmp
andisnull(d.itemcode,'')like@item_code_search_tmp
andisnull(d.variant,'')like@variantno_tmp
andisnull(d.itemdesc,'')like@itemdescriptionhdr_tmp
andh.suppliercodelike@suppcusthdr_tmp
ando.ouinstid=h.ouinstid
andloid=@lo_id
andsupplier_code=*h.suppliercode
andsupplier_namelike@ename_tmp
andclo_lo=@lo_id
andclo_cust_code=*h.suppliercode
andclo_cust_namelike @ename_tmp
anda.componentname='GR'
anda.paramcategory='META'
anda.paramtype='REF_DOC'
anda.paramcode=h.referencedoc
anda.langid=@ctxt_language_tmp
andb.componentname='GR'
andb.paramcategory='META'
andb.paramtype='REFD_DOC'
andb.paramcode=*h.referreddoc
andb.langid=@ctxt_language_tmp
order
byDATEMLdesc ,
REFDOC_MLTdesc
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
selectdistinct
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)
elsertrim(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'
fromgr_prs_hdr_vw h (nolock),
(select referencedoc , ouinstid , docno , max(amendno) as maxamendno
fromgr_prs_hdr_vw(nolock)
group
byreferencedoc , 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)
whereh.referencedoc=m.referencedoc
andh.ouinstid=m.ouinstid
andh.docno=m.docno
and h.amendno =m.maxamendno
andh.referencedoclikecase @refdocenu1hdr_tmp
when 'PG' then '%'
else @refdocenu1hdr_tmp
end
andh.referreddoclikecase @refdocenu1hdr_tmp
when 'PG' then @refdocenu1hdr_tmp
else '%'
end
andh.ouinstid=@filterouid_tmp
andd.linestatusin( 'OPEN' , 'NT CLOSED' )
andh.status='OPEN'
andisnull(h.groption,'')='YES'
andisnull(h.invbeforegr,'')=case h.referreddoc
when 'PG' then 'YES'
else 'NO'
end
andh.doctypelike@refdoctypehdr_tmp
andh.doctype<>'DROPSHIP'
andcase @refdocenu1hdr_tmp
when 'PG' then h.referreddocno
else h.docno
endbetween@refdocnumberfrom_tmpand@refdocnumberto_tmp
andh.docdatebetween@refdocdate_from_tmpand@refdocdate_to_tmp
and(isnull(h.folder,'%'))like@folder_tmp
andh.ouinstid=d.ouinstid
andh.docno=d.docno
andh.referencedoc=d.referencedoc
and h.amendno = d.amendno
andd.receiptou=@ctxt_ouinstance_tmp
andd.linestatusin( 'OPEN' , 'NT CLOSED' )
andd.balanceqty>0
andisnull(d.adhocitemcls,'')like@adhocitemclasshdr_tmp
andisnull(d.itemcode,'')like@item_code_search_tmp
andisnull(d.variant,'')like@variantno_tmp
andisnull(d.itemdesc,'')like@itemdescriptionhdr_tmp
andh.suppliercodelike@suppcusthdr_tmp
ando.ouinstid=h.ouinstid
andloid=@lo_id
andsupplier_code=*h.suppliercode
andsupplier_namelike@ename_tmp
andclo_lo=@lo_id
andclo_cust_code=*h.suppliercode
andclo_cust_namelike @ename_tmp
anda.componentname='GR'
anda.paramcategory='META'
anda.paramtype='REF_DOC'
anda.paramcode=h.referencedoc
anda.langid=@ctxt_language_tmp
andb.componentname='GR'
andb.paramcategory='META'
andb.paramtype='REFD_DOC'
andb.paramcode=*h.referreddoc
andb.langid=@ctxt_language_tmp
order
byDATEMLdesc ,
REFDOC_MLTdesc
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
selectdistinct
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)
elsertrim(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'
fromgr_so_hdr_vw h(nolock),
(select referencedoc , ouinstid , docno , max(amendno) as maxamendno
fromgr_so_hdr_vw(nolock)
group
byreferencedoc , 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)
whereh.referencedoc=m.referencedoc
andh.ouinstid=m.ouinstid
andh.docno=m.docno
and h.amendno =m.maxamendno
andh.referencedoclikecase @refdocenu1hdr_tmp
when 'PG' then '%'
else @refdocenu1hdr_tmp
end
andh.referreddoclikecase @refdocenu1hdr_tmp
when 'PG' then @refdocenu1hdr_tmp
else '%'
end
andh.ouinstid=@filterouid_tmp
andd.linestatusin( 'OPEN' , 'NT CLOSED' )
andh.status='OPEN'
andisnull(h.groption,'')='YES'
andisnull(h.invbeforegr,'')=case h.referreddoc
when 'PG' then 'YES'
else 'NO'
end
andh.doctypelike@refdoctypehdr_tmp
andh.doctype<>'DROPSHIP'
andcase @refdocenu1hdr_tmp
when 'PG' then h.referreddocno
else h.docno
endbetween@refdocnumberfrom_tmpand@refdocnumberto_tmp
andh.docdatebetween@refdocdate_from_tmpand@refdocdate_to_tmp
and(isnull(h.folder,'%'))like@folder_tmp
andh.ouinstid=d.ouinstid
andh.docno=d.docno
andh.referencedoc=d.referencedoc
and h.amendno = d.amendno
andd.receiptou=@ctxt_ouinstance_tmp
andd.linestatusin( 'OPEN' , 'NT CLOSED' )
andd.balanceqty>0
andisnull(d.adhocitemcls,'')like@adhocitemclasshdr_tmp
andisnull(d.itemcode,'')like@item_code_search_tmp
andisnull(d.variant,'')like@variantno_tmp
andisnull(d.itemdesc,'')like@itemdescriptionhdr_tmp
andh.suppliercodelike@suppcusthdr_tmp
ando.ouinstid=h.ouinstid
andloid=@lo_id
andsupplier_code=*h.suppliercode
andsupplier_namelike@ename_tmp
andclo_lo=@lo_id
andclo_cust_code=*h.suppliercode
andclo_cust_namelike @ename_tmp
anda.componentname='GR'
anda.paramcategory='META'
anda.paramtype='REF_DOC'
anda.paramcode=h.referencedoc
anda.langid=@ctxt_language_tmp
andb.componentname='GR'
andb.paramcategory='META'
andb.paramtype='REFD_DOC'
andb.paramcode=*h.referreddoc
andb.langid=@ctxt_language_tmp
order
byDATEMLdesc ,
REFDOC_MLTdesc
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
selectdistinct
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)
elsertrim(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'
fromgr_sco_hdr_vw h(nolock),
(select referencedoc , ouinstid , docno , max(amendno) as maxamendno
fromgr_sco_hdr_vw(nolock)
group
byreferencedoc , 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)
whereh.referencedoc=m.referencedoc
andh.ouinstid=m.ouinstid
andh.docno=m.docno
and h.amendno =m.maxamendno
andh.referencedoclikecase @refdocenu1hdr_tmp
when 'PG' then '%'
else @refdocenu1hdr_tmp
end
andh.referreddoclikecase @refdocenu1hdr_tmp
when 'PG' then @refdocenu1hdr_tmp
else '%'
end
andh.ouinstid=@filterouid_tmp
andd.linestatusin( 'OPEN' , 'NT CLOSED' )
andh.status='OPEN'
andisnull(h.groption,'')='YES'
andisnull(h.invbeforegr,'')=case h.referreddoc
when 'PG' then 'YES'
else 'NO'
end
andh.doctypelike@refdoctypehdr_tmp
andh.doctype<>'DROPSHIP'
andcase @refdocenu1hdr_tmp
when 'PG' then h.referreddocno
else h.docno
endbetween@refdocnumberfrom_tmpand@refdocnumberto_tmp
andh.docdatebetween@refdocdate_from_tmpand@refdocdate_to_tmp
and(isnull(h.folder,'%'))like@folder_tmp
andh.ouinstid=d.ouinstid
andh.docno=d.docno
andh.referencedoc=d.referencedoc
and h.amendno = d.amendno
andd.receiptou=@ctxt_ouinstance_tmp
andh.ouinstid=@filterouid_tmp
andd.linestatusin( 'OPEN' , 'NT CLOSED' )
andd.balanceqty>0
andisnull(d.adhocitemcls,'')like@adhocitemclasshdr_tmp
andisnull(d.itemcode,'')like@item_code_search_tmp
andisnull(d.variant,'')like@variantno_tmp
andisnull(d.itemdesc,'')like@itemdescriptionhdr_tmp
andh.suppliercodelike@suppcusthdr_tmp
ando.ouinstid=h.ouinstid
andloid=@lo_id
andsupplier_code=*h.suppliercode
andsupplier_namelike@ename_tmp
andclo_lo=@lo_id
andclo_cust_code=*h.suppliercode
andclo_cust_namelike @ename_tmp
anda.componentname='GR'
anda.paramcategory='META'
anda.paramtype='REF_DOC'
anda.paramcode=h.referencedoc
anda.langid=@ctxt_language_tmp
andb.componentname='GR'
andb.paramcategory='META'
andb.paramtype='REFD_DOC'
andb.paramcode=*h.referreddoc
andb.langid=@ctxt_language_tmp
order
byDATEMLdesc ,
REFDOC_MLTdesc
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
selectdistinct
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)
elsertrim(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'
fromgr_scrs_hdr_vw h(nolock),
(select referencedoc , ouinstid , docno , max(amendno) as maxamendno
fromgr_scrs_hdr_vw(nolock)
group
byreferencedoc , 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)
whereh.referencedoc=m.referencedoc
andh.ouinstid=m.ouinstid
andh.docno=m.docno
and h.amendno =m.maxamendno
andh.referencedoclikecase @refdocenu1hdr_tmp
when 'PG' then '%'
else @refdocenu1hdr_tmp
end
andh.referreddoclikecase @refdocenu1hdr_tmp
when 'PG' then @refdocenu1hdr_tmp
else '%'
end
andh.ouinstid=@filterouid_tmp
andd.linestatusin( 'OPEN' , 'NT CLOSED' )
andh.status='OPEN'
andisnull(h.groption,'')='YES'
andisnull(h.invbeforegr,'')=case h.referreddoc
when 'PG' then 'YES'
else 'NO'
end
andh.doctypelike@refdoctypehdr_tmp
andh.doctype<>'DROPSHIP'
andcase @refdocenu1hdr_tmp
when 'PG' then h.referreddocno
else h.docno
endbetween@refdocnumberfrom_tmpand@refdocnumberto_tmp
andh.docdatebetween@refdocdate_from_tmpand@refdocdate_to_tmp
and(isnull(h.folder,'%'))like@folder_tmp
andh.ouinstid=d.ouinstid
andh.docno=d.docno
andh.referencedoc=d.referencedoc
and h.amendno = d.amendno
andd.receiptou=@ctxt_ouinstance_tmp
andh.ouinstid=@filterouid_tmp
andd.linestatusin( 'OPEN' , 'NT CLOSED' )
andd.balanceqty>0
andisnull(d.adhocitemcls,'')like@adhocitemclasshdr_tmp
andisnull(d.itemcode,'')like@item_code_search_tmp
andisnull(d.variant,'')like@variantno_tmp
andisnull(d.itemdesc,'')like@itemdescriptionhdr_tmp
andh.suppliercodelike@suppcusthdr_tmp
ando.ouinstid=h.ouinstid
andloid=@lo_id
andsupplier_code=*h.suppliercode
andsupplier_namelike@ename_tmp
andclo_lo=@lo_id
andclo_cust_code=*h.suppliercode
andclo_cust_namelike @ename_tmp
anda.componentname='GR'
anda.paramcategory='META'
anda.paramtype='REF_DOC'
anda.paramcode=h.referencedoc
anda.langid=@ctxt_language_tmp
andb.componentname='GR'
andb.paramcategory='META'
andb.paramtype='REFD_DOC'
andb.paramcode=*h.referreddoc
andb.langid=@ctxt_language_tmp
order
byDATEMLdesc ,
REFDOC_MLTdesc
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
selectdistinct
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)
elsertrim(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'
fromgr_po_hdr_vw h(nolock),
(select referencedoc , ouinstid , docno , max(amendno) as maxamendno
fromgr_po_hdr_vw (nolock)
group
byreferencedoc , 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)
whereh.referencedoc=m.referencedoc
andh.ouinstid=m.ouinstid
andh.docno=m.docno
and h.amendno =m.maxamendno
andh.referencedoclikecase @refdocenu1hdr_tmp
when 'PG' then '%'
else @refdocenu1hdr_tmp
end
andh.referreddoclikecase @refdocenu1hdr_tmp
when 'PG' then @refdocenu1hdr_tmp
else '%'
end
andh.ouinstid=@filterouid_tmp
andd.linestatusin( 'OPEN' , 'NT CLOSED' )
andh.status='OPEN'
andisnull(h.groption,'')='YES'
andisnull(h.invbeforegr,'')=case h.referreddoc
when 'PG' then 'YES'
else 'NO'
end
andh.doctypelike@refdoctypehdr_tmp
andh.doctype<>'DROPSHIP'
andcase @refdocenu1hdr_tmp
when 'PG' then h.referreddocno
else h.docno
endbetween@refdocnumberfrom_tmpand@refdocnumberto_tmp
andh.docdatebetween@refdocdate_from_tmpand@refdocdate_to_tmp
and(isnull(h.folder,'%'))like@folder_tmp
andh.ouinstid=d.ouinstid
andh.docno=d.docno
andh.referencedoc=d.referencedoc
and h.amendno = d.amendno
andd.receiptou=@ctxt_ouinstance_tmp
andd.linestatusin( 'OPEN' , 'NT CLOSED' )
andd.balanceqty>0
andisnull(d.adhocitemcls,'')like@adhocitemclasshdr_tmp
andisnull(d.itemcode,'')like@item_code_search_tmp
andisnull(d.variant,'')like@variantno_tmp
andisnull(d.itemdesc,'')like@itemdescriptionhdr_tmp
andh.suppliercodelike@suppcusthdr_tmp
ando.ouinstid=h.ouinstid
andloid=@lo_id
andsupplier_code=*h.suppliercode
andsupplier_namelike@ename_tmp
andclo_lo=@lo_id
andclo_cust_code=*h.suppliercode
andclo_cust_namelike @ename_tmp
anda.componentname='GR'
anda.paramcategory='META'
anda.paramtype='REF_DOC'
anda.paramcode=h.referencedoc
anda.langid=@ctxt_language_tmp
andb.componentname='GR'
andb.paramcategory='META'
andb.paramtype='REFD_DOC'
andb.paramcode=*h.referreddoc
andb.langid=@ctxt_language_tmp
order
byDATEMLdesc ,
REFDOC_MLTdesc
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
selectdistinct
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)
elsertrim(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'
fromgr_sco_hdr_vw h(nolock),
(select referencedoc , ouinstid , docno , max(amendno) as maxamendno
fromgr_sco_hdr_vw(nolock)
group
byreferencedoc , 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)
whereh.referencedoc=m.referencedoc
andh.ouinstid=m.ouinstid
andh.docno=m.docno
and h.amendno =m.maxamendno
andh.referencedoclikecase @refdocenu1hdr_tmp
when 'PG' then '%'
else @refdocenu1hdr_tmp
end
andh.referreddoclikecase @refdocenu1hdr_tmp
when 'PG' then @refdocenu1hdr_tmp
else '%'
end
andh.ouinstid=@filterouid_tmp
andd.linestatusin( 'OPEN' , 'NT CLOSED' )
andh.status='OPEN'
andisnull(h.groption,'')='YES'
andisnull(h.invbeforegr,'')=case h.referreddoc
when 'PG' then 'YES'
else 'NO'
end
andh.doctypelike@refdoctypehdr_tmp
andh.doctype<>'DROPSHIP'
andcase @refdocenu1hdr_tmp
when 'PG' then h.referreddocno
else h.docno
endbetween@refdocnumberfrom_tmpand@refdocnumberto_tmp
andh.docdatebetween@refdocdate_from_tmpand@refdocdate_to_tmp
and(isnull(h.folder,'%'))like@folder_tmp
andh.ouinstid=d.ouinstid
andh.docno=d.docno
andh.referencedoc=d.referencedoc
and h.amendno = d.amendno
andd.receiptou=@ctxt_ouinstance_tmp
andh.ouinstid=@filterouid_tmp
andd.linestatusin( 'OPEN' , 'NT CLOSED' )
andd.balanceqty>0
andisnull(d.adhocitemcls,'')like@adhocitemclasshdr_tmp
andisnull(d.itemcode,'')like@item_code_search_tmp
andisnull(d.variant,'')like@variantno_tmp
andisnull(d.itemdesc,'')like@itemdescriptionhdr_tmp
andh.suppliercodelike@suppcusthdr_tmp
ando.ouinstid=h.ouinstid
andloid=@lo_id
andsupplier_code=*h.suppliercode
andsupplier_namelike@ename_tmp
andclo_lo=@lo_id
andclo_cust_code=*h.suppliercode
andclo_cust_namelike @ename_tmp
anda.componentname='GR'
anda.paramcategory='META'
anda.paramtype='REF_DOC'
anda.paramcode=h.referencedoc
anda.langid=@ctxt_language_tmp
andb.componentname='GR'
andb.paramcategory='META'
andb.paramtype='REFD_DOC'
andb.paramcode=*h.referreddoc
andb.langid=@ctxt_language_tmp
order
byDATEMLdesc ,
REFDOC_MLTdesc
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 muchsome 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 selectDepends 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