i selected different filed from table using cursor, hoe include union in cursor ,
my query is
declare
@is_wtBalDr_by_pty decimal(18,3)declare
@Rtn_wtBalCr_by_pty decimal(18,3)BEGIN
Declare @ClientID1 numeric Declare Client_Cursor1 CURSOR READ_ONLY FOR SELECT client_id from client where group_id=4 OPEN Client_Cursor1; FETCH NEXT FROM Client_Cursor1 into @ClientID1;
WHILE (@@FETCH_STATUS = 0) BEGIN set
@is_wtBalDr_by_pty=isnull
( (select (isnull(sum(cast(CONVERT(NVARCHAR(75),DECRYPTBYKEY(smithissue.grwt)) as decimal(18,3))),0)*isnull( smithissue.touch,0))/@Touch from smithissue inner join smithmaster on smithmaster.bill_id = smithissue.bill_id where CAST(FLOOR(CAST(smithissue.bill_date AS FLOAT)) AS DATETIME)<= CAST(FLOOR(CAST(@dateTo AS FLOAT)) AS DATETIME) and smithmaster.smith_id=@ClientID1 group by smithissue.touch),0)set
@Rtn_wtBalCr_by_pty=isnull
((select (isnull(sum(cast(CONVERT(NVARCHAR(75),DECRYPTBYKEY(smithreturn.grwt)) as decimal(18,3))),0)*isnull( smithreturn.touch,0))/@Touch from smithreturn inner join smithmaster on smithmaster.bill_id = smithreturn.bill_idwhere CAST(FLOOR(CAST(smithreturn.bill_date AS FLOAT)) AS DATETIME)<= CAST(FLOOR(CAST(@dateTo AS FLOAT)) AS DATETIME)
and smithmaster.smith_id=@ClientID1 group by smithreturn.touch),0)
select convert(decimal(18,3),(@is_wtBalDr_by_pty-@Rtn_wtBalCr_by_pty)+cast(convert(nvarchar(75),DECRYPTBYKEY(client.opening_wt_balance))as decimal(18,3))) as NetWtDr ,PtyOnWtBalDr
=
case when convert(decimal(18,3),(@is_wtBalDr_by_pty-@Rtn_wtBalCr_by_pty)+cast(convert(nvarchar(75),DECRYPTBYKEY(client.opening_wt_balance))as decimal(18,3))) >=0then convert
(nvarchar(50),(@is_wtBalDr_by_pty-@Rtn_wtBalCr_by_pty)+cast(convert(nvarchar(75),DECRYPTBYKEY(client.opening_wt_balance))as decimal(18,3)))else
''
end
,PtyOnWtBalCr
=
case when convert(decimal(18,3),(@is_wtBalDr_by_pty-@Rtn_wtBalCr_by_pty)+cast(convert(nvarchar(75),DECRYPTBYKEY(client.opening_wt_balance))as decimal(18,3))) <0then convert
(nvarchar(50),(@is_wtBalDr_by_pty-@Rtn_wtBalCr_by_pty)+cast(convert(nvarchar(75),DECRYPTBYKEY(client.opening_wt_balance))as decimal(18,3)))else
''
end
,client
.client_id as clientId, client.client_code as clientCode,client
client
.client_name as clientName, client.address1 as address1, .address2 as address2, client.address3 as address3,client
client
.city as city, client.state as state, client.zippin as zippin, client.email as email, .phone as phone, client.mobile as mobile, tablegroup.group_name as groupName,client
.maintainWeightBalance as maintainWeightBalance,
client
convert(nvarchar(75),DECRYPTBYKEY(client.opening_cash_balance)) as openingCashBalance, client.remarks as remarks,client.group_id as groupId, .can_delete as canDelete,convert(nvarchar(75),DECRYPTBYKEY(client.opening_cash_balance)) as balance,
'0' as bal,convert(nvarchar(75),DECRYPTBYKEY(client.wt_balance)) as wtBalancefrom
client inner join tablegroup ON client.group_id = tablegroup.group_id where
client.client_id= @ClientID1 and client.group_id=@groupIdFETCH NEXT FROM Client_Cursor1 into @ClientID1;
END
Endend