Issue while using the WHERE clause in LOAD Statement!

[#1]

Hi All,

I am facing problem in using WHERE clause in a LOAD statement. I am getting the below error while using this LOAD statement. Hope some one can help me out.

SecurityTable:
LOAD DISTINCT
 'USER' as [ACCESS],
 upper([userid]) as [USERID],
 upper([password]) as [PASSWORD],
 upper(mfaccode) as SecurityMfa,
 'Some' as SecurityPage
resident TempTab where userid not in('001001admin','001001vadmin');

CONCATENATE (SecurityTable)
LOAD DISTINCT
 'USER' as [ACCESS],
 upper([userid]) as [USERID],
 upper([password]) as [PASSWORD],
 upper(mfaccode) as SecurityAccount,
 '*' as SecurityMfa,
 'Some' as SecurityPage
resident TempTab where userid in('001001admin','001001vadmin');

Error:

Garbage after statement
SecurityTable:
LOAD DISTINCT
 'USER' as [ACCESS],
 upper([userid]) as [USERID],
 upper([password]) as [PASSWORD],
 upper(mfaccode) as SecurityMfa,
 'Some' as SecurityPage
resident TempTab where userid not in('001001admin','001001vadmin')

[#2]

I have had problems with in before. Multiple values do not seem to be allowed.

A quick workaround is

where userid not ('001001admin') and userid not ('001001vadmin')

or where userid <>('001001admin') and userid <> ('001001vadmin')

I am sure there is a smarter solution though!

[#3]

Thanks for very quick reply!

rickl wrote:
I have had problems with in before. Multiple values do not seem to be allowed.
A quick workaround is
where userid not ('001001admin') and userid not ('001001vadmin')
or where userid <>('001001admin') and userid <> ('001001vadmin')
I am sure there is a smarter solution though!<div></div>

For this

[code[where userid not in('001001admin','001001vadmin')

This I will put like this

where userid not ('001001admin') and userid not ('001001vadmin')

or where userid <>('001001admin') and userid <> ('001001vadmin')

How about for below one:

where userid in('001001admin','001001vadmin')

Thanks and Regards,

Rikab

[#4]

Do the same, but with an "OR" statement:

[CODE]

where userid in "a" or userid in "b"

[CODE\]

[skaredovs]

Hi,

I suggest to use function MATCH

where match(userid,'001001admin','001001vadmin')=0

[#6]

rickl wrote:
Do the same, but with an "OR" statement:
<blockquote><pre>
where userid in "a" or userid in "b"
[CODE\]<div></div>

It has accepted <> instead of not in. But it is not accepting in(or). Throwing the below error. Let me know what to do.

Garbage after statement
CONCATENATE (SecurityTable)
LOAD DISTINCT
 'USER' as [ACCESS],
 upper([userid]) as [USERID],
 upper([password]) as [PASSWORD],
 upper(mfaccode) as SecurityAccount,
 '*' as SecurityMfa,
 'Some' as SecurityPage
resident TempTab where userid in ('001001admin') or userid in ('001001vadmin')

Script that has been used:

SecurityTable:
LOAD DISTINCT
 'USER' as [ACCESS],
 upper([userid]) as [USERID],
 upper([password]) as [PASSWORD],
 upper(mfaccode) as SecurityMfa,
 'Some' as SecurityPage
resident TempTab where userid <> ('001001admin') and userid <> ('001001vadmin');

CONCATENATE (SecurityTable)
LOAD DISTINCT
 'USER' as [ACCESS],
 upper([userid]) as [USERID],
 upper([password]) as [PASSWORD],
 upper(mfaccode) as SecurityAccount,
 '*' as SecurityMfa,
 'Some' as SecurityPage
resident TempTab where userid in ('001001admin') or userid in ('001001vadmin');

Thanks and Regards,

Rikab

[#7]

It has accepted LIKE instead of IN. But this login script is not working you have any idea why? By the way thanks a lot for your effort.

LOAD DISTINCT
 'USER' as [ACCESS],
 upper([userid]) as [USERID],
 upper([password]) as [PASSWORD],
 upper(mfaccode) as SecurityAccount,
 '*' as SecurityMfa,
 'Some' as SecurityPage
resident TempTab where [userid] like ('001001admin') or userid like ('001001vadmin');

Thanks and Regards,

Rikab

[#8]

Try "=" instead of "in"

[#9]

rickl wrote:
Try "=" instead of "in"<div></div>

Yes, both "=" and LIKE works. Data has been loaded now. But I think there is some thing wrong with the section access script.

Thanks and Regards,

Rikab

[boorgura]

I might be wrong, but just noticed that in the initial code, there in no space after IN.

Please check if that is carusing the error.

Would be great if you let me know, whether that was the issue.