Comments by Pinank_CD

Pinank_CD 1-May-13 5:20am View

Below is a scenerio where i have to write 2 queries for desired result. how can i write that in single query?
---------------------------------------------------------------------------------------

CREATE TABLE #tblTestSearchDetails (
UploadDetailsId int IDENTITY,
Up_Amount decimal(18,2),
Up_CCY varchar(8),
Up_ExtRef varchar(100),
Up_CustRef varchar(100),
Up_Status varchar(100)
)

insert into #tblTestSearchDetails(Up_Amount,Up_CCY,Up_CustRef,Up_ExtRef,Up_Status)values(300.00,'GBP','0000','EREF0000REMIashish222222-025','Amount')
insert into #tblTestSearchDetails(Up_Amount,Up_CCY,Up_CustRef,Up_ExtRef,Up_Status)values(300.00,'GBP','0000','EREF0000REMIashish222222-026','Amount')
insert into #tblTestSearchDetails(Up_Amount,Up_CCY,Up_CustRef,Up_ExtRef,Up_Status)values(300.00,'GBP','0000','EREF0000REMIPinank222222-030','Amount')
insert into #tblTestSearchDetails(Up_Amount,Up_CCY,Up_CustRef,Up_ExtRef,Up_Status)values(300.00,'GBP','0000','EREF0000REMIpinank999999','Amount')
insert into #tblTestSearchDetails(Up_Amount,Up_CCY,Up_CustRef,Up_ExtRef,Up_Status)values(400.00,'GBP','0000','EREF0000REMIrobert785478-007','Amount')

CREATE TABLE #tblTestLookUpDetails (
T_FundInId int IDENTITY, T_ClientNo varchar(100), T_ContractNo varchar(100),
T_CCY varchar(100), T_Name varchar(100), T_Amount decimal(18,2)
)

insert into #tblTestLookUpDetails(T_Amount,T_CCY,T_ClientNo,T_ContractNo,T_Name)values(300.00,'GBP','222222','222222-025','ashish')
insert into #tblTestLookUpDetails(T_Amount,T_CCY,T_ClientNo,T_ContractNo,T_Name)values(300.00,'GBP','222222','222222-026','ashish')
insert into #tblTestLookUpDetails(T_Amount,T_CCY,T_ClientNo,T_ContractNo,T_Name)values(300.00,'GBP','222222','222222-028','ashish')
insert into #tblTestLookUpDetails(T_Amount,T_CCY,T_ClientNo,T_ContractNo,T_Name)values(300.00,'GBP','999999','999999-030','pinank')
insert into #tblTestLookUpDetails(T_Amount,T_CCY,T_ClientNo,T_ContractNo,T_Name)values(300.00,'GBP','999999','999999-031','pinank')

create table #tblTestPhubRelation
(
R_RelId int IDENTITY,
R_UploadDetailId int,
R_FundsIn int,
R_DealNo varchar(20),
R_Name varchar(20),
R_Match varchar(20),
)

--Query:1
insert into #tblTestPhubRelation(R_UploadDetailId,R_FundsIn,R_DealNo,R_Match)
select U1.UploadDetailsId,T1.T_FundInId,T1.T_ContractNo,'Contract Match' from #tblTestSearchDetails U1, #tblTestLookUpDetails T1
where (U1.Up_ExtRef LIKE '%'+ T_ContractNo +'%' --or U.Up_ExtRef LIKE '%'+ T_ClientNo +'%'
or U1.Up_CustRef LIKE '%'+ T_ContractNo +'%')

--Query:2
insert into #tblTestPhubRelation(R_UploadDetailId,R_FundsIn,R_DealNo,R_Match)
select U1.UploadDetailsId,T1.T_FundInId,T1.T_ContractNo,'Client Number Match' from #tblTestSearchDetails U1, #tblTestLookUpDetails T1
where (U1.Up_ExtRef LIKE '%'+ T_ClientNo +'%' OR --or U.Up_ExtRef LIKE '%'+ T_ClientNo +'%'
U1.Up_CustRef LIKE '%'+ T_ClientNo +'%')
and U1.UploadDetailsId not in( select R_UploadDetailId from #tblTestPhubRelation)

select * from #tblTestPhubRelation

drop table #tblTestPhubRelation
drop table #tblTestSearchDetails
drop table #tblTestLookUpDetails

Pinank_CD 25-Jan-13 5:24am View

Hi,

I am not able to find much help. can you please guide me on below task?

1. After authorizing UName and Pwd from DB how can i return token and set in the session variable in clients environment?
2. Role(My Own Table and not aspnet membership) based permission on operation contract

Pinank_CD 24-Jan-13 6:14am View

Exactly this is what i want, can we achieve this?

1. The client calls Login with the username/password
2. Encryption performed it in the client sink
3. Message is sent to the server
4. The server decrypts the message in the server sink
5. Server Login() method validates the username/password from it's user database
6. Server creates a token which can be used to validate the client on future requests
7. The Login() method return with response (i.e. token)
8. Server’s response is encrypted in server sink
9. Message is sent to client
10. Message is received at client, decrypted, client now has token for future requests

Pinank_CD 24-Jan-13 5:56am View

Yes. i will validate the username and password in database, but i want to send the credentials in encrypt format and will decrypt on DAL. besides this, what security features can be implemented for authorization on contract levels?

Pinank_CD 22-Jan-13 6:12am View

cant see your email, where should i send?

Pinank_CD 22-Jan-13 4:39am View

Hey Andi,

i will be eager to share my solution, in case if succeed with some new solution. how can i reach you?

Pinank_CD 22-Jan-13 4:21am View

i think what you are saying is logically correct. there can be lots of formats and each one of them can have different methods to access. Same comments were posted by OriginalGriff. thank you all of you. marking this as a solution.

Pinank_CD 22-Jan-13 3:23am View

Hi Andreas,

you are corret on your part, but in the market there are 3rd party parser available, even they must be using some logic build the data out of a stream. dont know but there should be some solution to other formats as well after all everything is bit of piece. :)

Pinank_CD 10-Oct-12 11:05am View

It worked. thanks a lot Mr.Betha

Pinank_CD 10-Oct-12 6:51am View

your explanation was really helpful. i am sorry but i dint found the modified query in your comments.

can you modify the below query and resend.

DECLARE @param VARCHAR(MAX) = '3,24,2,45';
DECLARE @Xparam XML;

SELECT @Xparam = CAST('' + REPLACE(@param,',','') + '' AS XML)
SELECT x.i.value('.','INT') FROM @Xparam.nodes('//i') x(i)

Pinank_CD 10-Oct-12 4:01am View

Hi Mr.Betha,

Thanks for your reply.

your code isn't seems working. even i tried with the below simple query but nothing resulted.

-------------EXECUTION START--------------
DECLARE @param VARCHAR(MAX) = '3,24,2,45';
DECLARE @Xparam XML;

SELECT @Xparam = CAST('' + REPLACE(@param,',','') + '' AS XML)
SELECT x.i.value('.','INT') FROM @Xparam.nodes('//i') x(i)
-------------EXECUTION ENDS--------------

Also can you please provide more comments on subquery (SELECT x.i.value.....)

Pinank_CD 9-Oct-12 12:01pm View

it would be beneficial if something could work with easy casting or convert or replace. because in my SP, i have to use that variable at different levels but anyways all of the solutions were worth using.

a million thanks to all of the repliers, specially Mr.Dylan Morley and Mr.Santhosh Kumar.

Pinank_CD 5-Oct-12 10:22am View

when i am installing the service, it does not extract the required files. so manually i have to created folders and add files to it. i want my exe to extract,create folders and add files to it.

Comments by Pinank_CD (Top 13 by date)