Issue with loading data from one table and dropping the same table from where data has been loaded

[#1]

Hi All,

I am retrieving the admin login from from different table(mfac_mst) which i want to insert into the below table and drop the table(mfac_mst) to avoid the synthetic key. Let me know how to do that. If I follow the below method though the table has been dropped synthetic key has been formed. Some one let me know how to over come this problem.

TempTab:
LOAD DISTINCT
 account,
 mfaccode,
 [userid],
 [password]
resident items;

// Loading admin login from mfac_mst

CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=mercury;Initial Catalog=VPDPL-1;Data
Source=GL28;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=GL28;Use Encryption for
Data=False;Tag with column collation when possible=False];

Tab1:

select * from mfac_mst;

select rtrim(c_account_code)+rtrim(c_loginid) as userid,rtrim(c_password) as password,c_account_code as SecurityAccount,
'*' as SecurityMfa from mfac_mst where c_loginid in('vadmin','admin');

drop table Tab1;

//////////////////////////////////////////////

Thanks and Regards,

Rikab

[#2]

concatenate() the tables together. Or create two tables, load the fields you need into a resident table and drop the two tables drop tables tablename;

[#3]

Hi Anthony!

Can you please do the required changes in the attachment. As I didn't get you. Please help me out!

Thanks and Regards,

Rikab

[#4]

Hi Anthoy!

I have concatenated both the table to get the admin login. But how can i differentiate the admin login in the TempTab. If all the field are same in the temptab both the user and admin are behaving in the same way. Let me know what can be done now.

items:

LOAD *,
account as %Key;
SQL select i.c_account_code as account/*as accountas %key*/,i.c_item_code as itemcode,c_pack as pack,upper(i.c_account_code)

as SecurityAccount,upper(i.c_mfac_code) as SecurityMfa,
i.c_mfac_code as mfaccode,rtrim(i.c_name)+' '+rtrim(i.c_pack) as itemname,
rtrim(i.c_account_code)+rtrim(i.c_mfac_code) as userid,rtrim(m.c_password) as password,
i.n_mrp as MRP,i.n_newflag as recentitem,i.c_Scheme as schemes,m.c_name as mfacname,
c.c_name as contentname,c.c_note as contentname1
from item_mst i inner join mfac_mst m on i.c_mfac_code=m.c_mfac_code
left outer join content_mst c on i.c_content_code=c.c_content_code;

TempTab:
LOAD DISTINCT
 account,
 mfaccode,
 [userid],
 [password]
resident items;

items:

concatenate select c_account_code as account,upper(c_account_code)as SecurityAccount,
upper(c_mfac_code) as SecurityMfa,c_mfac_code as mfaccode,rtrim(c_account_code)+rtrim(c_loginid) as userid,
rtrim(c_password) as password,c_name as mfacname from mfac_mst where c_loginid in('vadmin','admin');

Thanks and Regards,

Rikab

[Oleg_Troyansky]

If you prefer to have the two tables separated, - it's OK, you don't have to concatenate. Just name the tables separately and at the end, drop both tables.

[#6]

Hello Oleg!

After naming the table separately and the dropping the same there has been some issues as shown below. Please let me know why it is happening. Is there is any other solution.

I don't think we can drop the items table as it will be required for me to get the sales figure.

CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=mercury;Initial Catalog=VPDPL-1;Data
Source=GL28;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=GL28;Use Encryption for
Data=False;Tag with column collation when possible=False];
items:

LOAD *,
account as %Key;
SQL select i.c_account_code as account/*as accountas %key*/,i.c_item_code as itemcode,c_pack as pack,upper(i.c_account_code)

as SecurityAccount,upper(i.c_mfac_code) as SecurityMfa,
i.c_mfac_code as mfaccode,rtrim(i.c_name)+' '+rtrim(i.c_pack) as itemname,
rtrim(i.c_account_code)+rtrim(i.c_mfac_code) as userid,rtrim(m.c_password) as password,
//rtrim(i.c_account_code)+rtrim(m.c_loginid) as userid,rtrim(m.c_password) as password,
i.n_mrp as MRP,i.n_newflag as recentitem,i.c_Scheme as schemes,m.c_name as mfacname,
c.c_name as contentname,c.c_note as contentname1
from item_mst i inner join mfac_mst m on i.c_mfac_code=m.c_mfac_code
left outer join content_mst c on i.c_content_code=c.c_content_code;
//where left(i.c_name,1)<>'~' and left(m.c_name,1)<>'~';

CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=mercury;Initial Catalog=VPDPL-1;Data
Source=GL28;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=GL28;Use Encryption for
Data=False;Tag with column collation when possible=False];

items1:

select c_account_code as account,upper(c_account_code)as SecurityAccount,
upper(c_mfac_code) as SecurityMfa,c_mfac_code as mfaccode,rtrim(c_account_code)+rtrim(c_loginid) as userid,
rtrim(c_password) as password,c_name as mfacname from mfac_mst where c_loginid in('vadmin','admin');
drop table items;

drop table items1;

Thanks and Regards,

Rikab

[#7]

Hi All,

Let me tell you the purpose of doing this.

// From this table I am able to get only the user login.
items:

LOAD *,
account as %Key;
SQL select i.c_account_code as account/*as accountas %key*/,i.c_item_code as itemcode,c_pack as pack,upper(i.c_account_code)

as SecurityAccount,upper(i.c_mfac_code) as SecurityMfa,
i.c_mfac_code as mfaccode,rtrim(i.c_name)+' '+rtrim(i.c_pack) as itemname,
rtrim(i.c_account_code)+rtrim(i.c_mfac_code) as userid,rtrim(m.c_password) as password,
//rtrim(i.c_account_code)+rtrim(m.c_loginid) as userid,rtrim(m.c_password) as password,
i.n_mrp as MRP,i.n_newflag as recentitem,i.c_Scheme as schemes,m.c_name as mfacname,
c.c_name as contentname,c.c_note as contentname1
from item_mst i inner join mfac_mst m on i.c_mfac_code=m.c_mfac_code
left outer join content_mst c on i.c_content_code=c.c_content_code;
//where left(i.c_name,1)<>'~' and left(m.c_name,1)<>'~';
// From here i am concatenating this table to get the admin login. If iam loading as a separate table synthetic key is formed.
CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=mercury;Initial Catalog=VPDPL-1;Data
Source=GL28;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=GL28;Use Encryption for
Data=False;Tag with column collation when possible=False];

items:

concatenate select c_account_code as account,upper(c_account_code)as SecurityAccount,
upper(c_mfac_code) as SecurityMfa,c_mfac_code as mfaccode,rtrim(c_account_code)+rtrim(c_loginid) as userid,
rtrim(c_password) as password,c_name as mfacname from mfac_mst where c_loginid in('vadmin','admin');

Now after concateting I am able to get the admin login with the user login. But let me know how can i differentiate the admin and user in the section access.

For more details please see the attached script.

[Oleg_Troyansky]

I'm not sure I understand what you mean by "differentiating user and admin" - usually the difference is manifested in the value of the field "ACCESS"...

Looking at your code, I spotted a few problems:

1. Section Access has to be in the very beginning of the document, there should be no other code before it.
2. All the fields in section access, including your additional fields, need to be in upper case (if I remember correctly)

[#9]

Hello Oleg!

Happy Morning! Hope you are doing good.

I'm not sure I understand what you mean by "differentiating user and admin" - usually the difference is manifested in the value of the field "ACCESS"... -->

Section Access;
// Using this I am giving access to the 'USER'. But if it is in the same table and column how will i give access to user and admin. What i mean to say is i am getting the userid for both admin/user from the same column and not like the one which was given below upper(account). Hope you can understand my problem now.

SecurityTable:
LOAD DISTINCT
 'USER' as [ACCESS],
 upper([userid]) as [USERID],
 upper([password]) as [PASSWORD],
 upper(account) as SecurityAccount,
 upper(mfaccode) as SecurityMfa,
 'Some' as SecurityPage
resident TempTab;
// Here I am giving access to the 'Admin'. Which i can't use anymore as i need to consider upper(userid) not upper(account).
CONCATENATE (SecurityTable)
LOAD DISTINCT
 'ADMIN' as [ACCESS],
 upper(account) as [USERID],
 upper(account) as [PASSWORD],
 upper(account) as SecurityAccount,
 '*' as SecurityMfa,
 'Some' as SecurityPage
resident TempTab;

1. Section Access has to be in the very beginning of the document, there should be no other code before it. --> ? Surely I will keep the section access in the beginning.
2. All the fields in section access, including your additional fields, need to be in upper case (if I remember correctly) --> Ok I will check the same.

Thanks and Regards,

Rikab

[#10]

Hi Oleg,

Section Access has to be in the very beginning of the document, there should be no other code before it. --> I have placed it in the beginning but I didn't worked

Have attached the script. Can you please place it in order.

Thanks and Regards,

Rikab