SQL SERVER CAL FUNCTION IN IN CLAUSE -


i have function returns set of itemcodes. result correct when call in stored procedure doesn't show anything:

declare   @itemgroupcode int set       @itemgroupcode=118 declare   @todate datetime set       @todate='15.april.15'  declare   @group nvarchar(max) set       @group=(select [dbo].[currproduction] (118,'15.april.2015')) select @group  select * ( select  o.itemcode [item],tm.itemname dscription,o.warehouse wr,obtn.lotnumber,  itl1.quantity qty ,tm.u_reusb,@group grp  oinm o inner join oitl on oitl.doctype = o .transtype , oitl.docnum = o.base_ref  , oitl.docline= o .doclinenum , oitl .loccode = o.warehouse , o .itemcode = oitl.itemcode inner join itl1 on itl1.logentry = oitl.logentry , itl1.itemcode=oitl.itemcode inner join oitm  tm on tm.itemcode = itl1.itemcode  inner join oitb on tm .itmsgrpcod = oitb .itmsgrpcod inner join owhs oh   on o.warehouse=oh.whscode inner join obtn on obtn.sysnumber = itl1.sysnumber , obtn.itemcode = itl1.itemcode , tm.manbtchnum = 'y'   --tm.itmsgrpcod=108 ,   tm.itemcode in (@group)   , o.docdate<=@todate ) s   pivot  (  sum(qty)  wr in (  [dis],[g&i],[gd-001],[gd-002],[gd-003],[gd-004],  [gd-005],[gd-006],[gd-007],[gd-008],[gd-009],[gd-010],  [gd-011],[gd-012],[gd-013],[gd-014],[gd-015],[gd-016],[gd-017],[gd-018],  [gd-019],[gd-020],[gd-021],[gd-022],[gd-023],[gd-024],[gd-025],[gd-026],  [gd-027],[gd-028],[gd-029],[gd-030],[gd-031],[gd-032],[gd-033],[gd-034],[gd-035], [khi ab],[khi mm],[loan],[lub],[main],[pcg - m1],[pcg - m2], [phs],[pmg],[pro],[rej],[rep],[rep-v],[selling],[skp] ) )as pvt

the function returns following when paste in in clause gives me correct result, when pass in result of function call, doesn't show anything

function returns:

'fyct-00063','fycm-00016','fycm-00064','fycf-00018','fycm-00021','fyog-00016','fycm-00004','fycm-00031','fycm-00042' function code following.  **  alter function [dbo].[currproduction] ( @unit varchar(10), @date datetime ) returns varchar(max)  begin     -- declare return variable here     declare @resultvar varchar(max)   select distinct @resultvar='''' + replace(stuff((    select    ',',+cast(owor.itemcode varchar(10)) [text()]    owor    owor.postdate=@date , owor.u_unit=case                                  when @unit=108 'unit no1'                                  when @unit=118 'unit no 2'                                  when @unit=119 'unit no 3' end     , owor.status!='c'     xml path('') , type)    .value('.','nvarchar(max)'),1,1,' '),',',''',''')+''''                          -- return result of function     return @resultvar end

**

in order want function should inline table valued function. can call in in. example:

create table ttinline(id int) go  insert dbo.ttinline values  (1),(2),(3)  create function fninline() returns table return select * dbo.ttinline go  select * dbo.ttinline id in(select * dbo.fninline())

output:

id 1 2 3

edit:

alter function [dbo].[currproduction] ( @unit varchar(10), @date datetime ) returns table   return     select distinct cast(owor.itemcode varchar(10)) code    owor    owor.postdate=@date , owor.u_unit=case                                  when @unit=108 'unit no1'                                  when @unit=118 'unit no 2'                                  when @unit=119 'unit no 3' end     , owor.status!='c' go   declare @itemgroupcode int set @itemgroupcode = 118 declare @todate datetime set @todate = '15.april.15'  select  *    ( select    o.itemcode [item] ,                     tm.itemname dscription ,                     o.warehouse wr ,                     obtn.lotnumber ,                     itl1.quantity qty ,                     tm.u_reusb                 oinm o                     inner join oitl on oitl.doctype = o.transtype                                        , oitl.docnum = o.base_ref                                        , oitl.docline = o.doclinenum                                        , oitl.loccode = o.warehouse                                        , o.itemcode = oitl.itemcode                     inner join itl1 on itl1.logentry = oitl.logentry                                        , itl1.itemcode = oitl.itemcode                     inner join oitm tm on tm.itemcode = itl1.itemcode                     inner join oitb on tm.itmsgrpcod = oitb.itmsgrpcod                     inner join owhs oh on o.warehouse = oh.whscode                     inner join obtn on obtn.sysnumber = itl1.sysnumber                                        , obtn.itemcode = itl1.itemcode                                        , tm.manbtchnum = 'y'               --tm.itmsgrpcod=108 ,                     tm.itemcode in ( select code [dbo].[currproduction](118, '15.april.2015') )                     , o.docdate <= @todate         ) s pivot  ( sum(qty) wr in ( [dis], [g&i], [gd-001], [gd-002], [gd-003], [gd-004],                        [gd-005], [gd-006], [gd-007], [gd-008], [gd-009],                        [gd-010], [gd-011], [gd-012], [gd-013], [gd-014],                        [gd-015], [gd-016], [gd-017], [gd-018], [gd-019],                        [gd-020], [gd-021], [gd-022], [gd-023], [gd-024],                        [gd-025], [gd-026], [gd-027], [gd-028], [gd-029],                        [gd-030], [gd-031], [gd-032], [gd-033], [gd-034],                        [gd-035], [khi ab], [khi mm], [loan], [lub], [main],                        [pcg - m1], [pcg - m2], [phs], [pmg], [pro], [rej],                        [rep], [rep-v], [selling], [skp] ) )as pvt

-

Popular posts from this blog

c# - ODP.NET Oracle.ManagedDataAccess causes ORA-12537 network session end of file -

overview i want replace oracle.dataaccess orcale. managed dataaccess, opening connection latter throws ora-12537 network session end of file exception. exception message / stack trace {oracleinternal.network.networkexception (0x000030f9): ora-12537 : netzwerksession: dateiende @ oracleinternal.network.readerstream.read(orabuf ob) @ oracleinternal.ttc.orabufreader.getdatafromnetwork() @ oracleinternal.ttc.orabufreader.read(boolean bignoredata) @ oracleinternal.ttc.marshallingengine.unmarshalub1(boolean bignoredata) @ oracleinternal.ttc.ttcprotocolnegotiation.readresponse()} i trying connect oracle 11g database , not have client installed on local machine. working test application (unmanaged) using oracle.dataaccess works fine . using system; using oracle.dataaccess.client; namespace app.odp.unmanaged { internal class program { private static void main(string[] args) { //dummy connection string...
Read more

matlab - Compression and Decompression of ECG Signal using HUFFMAN ALGORITHM -

i want compress , decompress ecg signal stored text message text file , find out file size, cr,prd ratio,and qs. here code compression , decompression of ecg signal using huffman algorithm. i'm trying find out file size, compression ratio,prd ratio,qs. but error i'm getting the ecg file in text format. %clearing variables , screen enter code here clear all; close all; clc; [file1]=uigetfile('*.txt'); %fprintf('enter ecg file name :%s',file1); p=file1; t=sprintf('%s',p); b1=dlmread([file1]); len=length(b1); lead=input('enter lead number :'); %lead=str2double(lead); col2=b1(1:end,lead); e=fix(sqrt(len)); m=1; i=1:e j=1:e g2(i,j)=fix(col2(m)*1000); %amplifying 1000 m=m+1; end end g3=g2; i=1:e j=1:e ...
Read more

utf 8 - split utf-8 string into bytes in python -

i trying split utf-8 string bytes in python 3. problem is, when use bytearray, byte, encode etc functions array size of element 14 bytes, not 1 byte expected. need split text file sequence of bytes , send them byte after byte using sockets. tried this: infile = open (file, "r") str = infile.read() byte_str = bytes(str, 'utf-8') print("size of byte_str",sys.getsizeof(byte_str[0])) print gives me 14, need 1... suggestion? quoting official documentation : sys.getsizeof(object[, default]) return size of object in bytes. object can type of object. built-in objects return correct results, not have hold true third-party extensions implementation specific. only memory consumption directly attributed object accounted for, not memory consumption of objects refers to. if given, default returned if object not provide means retrieve size. otherwise typeerror raised. getsizeof() calls object’s __sizeof__ method , adds ...
Read more