September 6, 2012 at 5:06 am
Hi people!
Situation:
I have 2 groups of data:
X - Global
Y - Specific
I want to organize Y into X.
Example: X is a Global Group Permission, like 'Admin, Resources, ...'
Y contains the specific permissions inside the Global Group Permission, like 'Add permission...'
Admin
Add permission
...
...
Resource
....
And I have this:
SELECT f.WSEC_FEA_ACT_UID, c.CONV_STRING AS WSEC_FEA_ACT_NAME
FROM MSP_WEB_SECURITY_FEATURES_ACTIONS AS f INNER JOIN
MSP_WEB_CONVERSIONS AS c ON f.WSEC_FEA_ACT_NAME_ID = c.CONV_VALUE
WHERE (c.LANG_ID = '1033') AND (c.STRING_TYPE_UID = '946E9CC0-ED95-4F6F-877F-9A9ADAB57929') AND (f.WSEC_IS_ACTION = 0) AND (f.WSEC_ON_OBJECT <> 0)
UNION
SELECT f.WSEC_FEA_ACT_UID, c.CONV_STRING AS WSEC_FEA_ACT_NAME, f.WSEC_FEA_ACT_PARENT
FROM MSP_WEB_SECURITY_FEATURES_ACTIONS AS f INNER JOIN
MSP_WEB_CONVERSIONS AS c ON f.WSEC_FEA_ACT_NAME_ID = c.CONV_VALUE
WHERE (c.LANG_ID = '1033') AND (c.STRING_TYPE_UID = '946E9CC0-ED95-4F6F-877F-9A9ADAB57929') AND (f.WSEC_IS_ACTION <> 0) AND (f.WSEC_ON_OBJECT <> 0)
ORDER BY WSEC_FEA_ACT_NAME
I am using this QUERY in SSRS - BIDS.
This is not working.
Anybody can see what I can't here?
Thanks people!
Regards
September 6, 2012 at 5:11 am
Unless I'm going mad, you have more columns in the second SELECT than you do in the first. Statements in a Union must have the same number of columns.
September 6, 2012 at 5:29 am
Allright I've deleted that column, but it doesn't work anyway. This is getting interesting now.
Solutions?
Thanks!
September 6, 2012 at 5:34 am
Do you have a description of the error?
September 6, 2012 at 5:41 am
for readability
SELECT f.WSEC_FEA_ACT_UID ,
c.CONV_STRING AS WSEC_FEA_ACT_NAME
FROM
MSP_WEB_SECURITY_FEATURES_ACTIONS AS f
INNER JOIN MSP_WEB_CONVERSIONS AS c ON f.WSEC_FEA_ACT_NAME_ID = c.CONV_VALUE
WHERE c.LANG_ID = '1033'
AND c.STRING_TYPE_UID = '946E9CC0-ED95-4F6F-877F-9A9ADAB57929'
AND f.WSEC_IS_ACTION = 0
AND f.WSEC_ON_OBJECT <> 0
UNION
SELECT f.WSEC_FEA_ACT_UID ,
c.CONV_STRING AS WSEC_FEA_ACT_NAME ,
f.WSEC_FEA_ACT_PARENT -- assume you have removed this?
FROM
MSP_WEB_SECURITY_FEATURES_ACTIONS AS f
INNER JOIN MSP_WEB_CONVERSIONS AS c ON f.WSEC_FEA_ACT_NAME_ID = c.CONV_VALUE
WHERE c.LANG_ID = '1033'
AND c.STRING_TYPE_UID = '946E9CC0-ED95-4F6F-877F-9A9ADAB57929'
AND f.WSEC_IS_ACTION <> 0
AND f.WSEC_ON_OBJECT <> 0
ORDER BY WSEC_FEA_ACT_NAME;
not sure if I am reading this correctly...but would initially appear to be only one difference
between the two sections
AND f.WSEC_IS_ACTION = 0
AND f.WSEC_IS_ACTION <> 0
can you explain why.....couldnt you just have one query such as
SELECT f.WSEC_FEA_ACT_UID ,
c.CONV_STRING AS WSEC_FEA_ACT_NAME
FROM
MSP_WEB_SECURITY_FEATURES_ACTIONS AS f
INNER JOIN MSP_WEB_CONVERSIONS AS c ON f.WSEC_FEA_ACT_NAME_ID = c.CONV_VALUE
WHERE c.LANG_ID = '1033'
AND c.STRING_TYPE_UID = '946E9CC0-ED95-4F6F-877F-9A9ADAB57929'
AND f.WSEC_ON_OBJECT <> 0
what error message do you get if you run in SSMS?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 6, 2012 at 5:42 am
No. Is just that the result is not correct.
September 6, 2012 at 5:55 am
OK J Livingston SQL. Thanks
WSEC_IS_ACTION <> 0 , shows the global group permission name - 'Admin, Resources, ...' + Specific permissions
WSEC_IS_ACTION = 0 , only shows the global group permission name - 'Admin, Resources, ...'
What I need is to group by global group permission name the specific permissions.
For example, I have 2 kind of permission - A and B.
WSEC_ON_OBJECT <> 0 , shows A
WSEC_ON_OBJECT = 0 , shows B
For permissions A and B, the global group permission name is different.
Solutions?
Regards
September 6, 2012 at 5:59 am
suggest you post some create table scripts and sample data insert scripts that clearly represent the problem you are having...we cant see over your shoulder and having some scripts to recreate your problem on our own systems will assist everyone in getting you a quick resolution.
kind regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply