is this query right? (urgent)

Hi

I have a report which has a table and that table has 4 columns

I want to represent the Data like this.

Company Match or Profit Sharing or Safe Harbor Company Match or ProfitSharing or Safeharbor

Years of Service Vesting Years of service Vesting

1 40 1 50

I have 3 text boxes saying Company Match, Safe harbor and Profit Sharing, and the User normally can click 2 checkboxes

Suppose if the user clicks only company Match, i want the company Match to display on the left hand side if the users clicks on 2 things say company match and safe harbor.

I want the Company match to come on the left and safe harbor to be on the right. and my Expression is as follows:

for the Left hand side its :

IIf(Fields!CompanyMatch.Value =true,"Company Match",IIf(Fields!SafeHarbor.Value =true,"Safe Harbor","Profit Sharing"))

so how can i display the details in the above fashion.

any help is appreciated.

Regards,

Karen

Is that you are passign values from an asp page to report, in that case pass two paramaters to reports 1) companymatching 2) safeharbour as boolean values

In the report desgin mode put all the 4 columns and based on the input parameters you can toggle the view to visible or hide.

|||

actually just showing the data from the Database

can u give me a example please.

Regards,

Karen

Is this Query right? (urgent)

Hi

I have a report which has a table and that table has 4 columns

I want to represent the Data like this.

Company Match or Profit Sharing or Safe Harbor Company Match or ProfitSharing or Safeharbor

Years of Service Vesting Years of service Vesting

1 40 1 50

I have 3 text boxes saying Company Match, Safe harbor and Profit Sharing, and the User normally can click 2 checkboxes

Suppose if the user clicks only company Match, i want the company Match to display on the left hand side if the users clicks on 2 things say company match and safe harbor.

I want the Company match to come on the left and safe harbor to be on the right. and my Expression is as follows:

for the Left hand side its :

IIf(Fields!CompanyMatch.Value = true,"Company Match",IIf(Fields!SafeHarbor.Value = true,"Safe Harbor","Profit Sharing"))

and the right hand side its:

IIf(Fields!ProfitSharing.Value = true," Profit Sharing","")

so how can i display the details in the above fashion.

any help is appreciated.

Regards,

Karen

Does your data need to be aggregated or is it a straight read? If so, are you looking to sum the data based on the selected criteria?

|||

My data is straight read from the db, and I am not looking to sum the data.

Hope this helps,

Regards,

Karen

|||

Replace your text values with field values (if this is what you are trying to do)

IIf(Fields!CompanyMatch.Value = true,CompanyMatchField.value,IIf(Fields!SafeHarbor.Value = true,SafeHarborField.value,ProfitSharingField.value))

and the right hand side its:

IIf(Fields!ProfitSharing.Value = true,ProfitSharingField.value,"")

Simone

sql

is this enough to copy user's permissions to another user's from sp_helpprotect

Hi I use exec helprotect to put a user's perimssion into a temp table called #permissions defined with these columns : (DBname,[Owner] ,[Object],[Grantee] , [Grantor] , [ProtectType] , [Action] , [Col] ) like this:

Now when I get those permissions I use this script inside a cursor to replicate each permission to the user: @.newLoginParam

My question: is there any big business scenario in which my script might fail or miss some permissions for any user?

here is the main part of my script:

declare @.objj as varchar(8000)
declare @.grantee as varchar(8000)
declare @.action as varchar(8000)
declare @.col as varchar(8000)
declare @.sqlGrant as varchar(8000)
declare @.ProtectType as varchar(8000)
declare @.count as int
set @.count=0
declare crsMyTblPermis cursor for
select Object,Grantee,ProtectType , Action, Col from #permissions
open crsMyTblPermis
fetch next from crsMyTblPermis into @.objj, @.grantee, @.ProtectType, @.action,@.col
while @.@.fetch_status=0
begin

if (@.objj!='.' and (@.col = '.' OR @.col = '(All+New)'))
begin
set @.sqlGrant = @.ProtectType +@.action + ' on ' + @.objj + ' to ' + @.newLoginParam
end

if (@.objj!='.' and @.col != '.' and @.col != '(All+New)')
begin
set @.sqlGrant = @.ProtectType +@.action + ' on ' + @.objj + '(' + @.col + ') to ' + @.newLoginParam
end

if (@.objj='.')
begin
set @.sqlGrant = @.ProtectType +@.action + ' to ' + @.newLoginParam
end

exec(@.sqlGrant)
fetch next from........

...etc

P.S: The main reason is that I can t test my script now on the production platform on which the script will be run

Thanks for your advice .

I think you nead a space between @.ProtectType and action.

If you need to grant the permission of an existing user to many new users consider using a database role. You can grant the permissions to the role and then just add the new users to the role.

|||

yeah i will also add the new user to the roles that the old user was part of as well. But i guess the old user might also have permissions on databse objects without necessarily belonging to a specific role?do you agree?

Thanks

Is this correct use of INSTEAD OF Triggers?

I am loading data from table A into table B. Certain columns in B have
check constraints. I'd like for any rows from A, which violate
constraints, to be inserted into a third table, C. When the process is
finished, I'll have only good rows in B, and exeption rows in C.

I am investigating INSTEAD OF triggers, however my question to the
group is, is there a better or best practice for this scenario? This
must be common. Any high-level tips or direction will be highly
appreciated.

DAP>> loading data from table A into table B. Certain columns in B have
check constraints. I'd like for any rows from A, which violate
constraints, to be inserted into a third table, C. <<

You might want to use a cursor that attempts to insert each A row into
B and throws the exceptions into C. This would give you better control
and perhaps a chance to fix the bad rows with (ugh!) procedural code.

A moire set-oriented approach woudl be to create a VIEW on A whch has
the B constraints:

CREATE VIEW GoodA
AS SELECT *
FROM A
WHERE << B's constraints as predicates>> ;

You are probably thinking that the next step is to use:

CREATE VIEW BadA
AS SELECT *
FROM A
WHERE NOT (<< B's constraints as predicates>>);

But this does not work. A CHECK() constraint will accept an UNKNOWN
result from its predicate; a WHERE clause will reject them. You will
have to write a little extra code in each predicate to handle NULLs.

example:

CREATE TABLE B
( ..
foo INTEGER CHECK ( foo >= 0), -- works for null
..);

SELECT *
FROM A
WHERE ( foo >= 0 OR foo IS NULL);|||Hi

You can try something like:

INSERT INTO Table C
SELECT col1, col2, col3 FROM TABLE A
WHERE <CLAUSE TO TEST CONSTRAINT FAIL
INSERT INTO Table B
SELECT col1, col2, col3 FROM TABLE A
WHERE <CLAUSE TO TEST CONSTRAINTS PASS
OR
INSERT INTO Table B
SELECT col1, col2, col3 FROM TABLE A
WHERE NOT EXISTS ( SELECT * FROM TABLE C WHERE <CLAUSE TO CHECK NOT IN C>)

John

"Dan" <dpratte@.dpratte.com> wrote in message
news:1115474335.494377.167790@.o13g2000cwo.googlegr oups.com...
>I am loading data from table A into table B. Certain columns in B have
> check constraints. I'd like for any rows from A, which violate
> constraints, to be inserted into a third table, C. When the process is
> finished, I'll have only good rows in B, and exeption rows in C.
> I am investigating INSTEAD OF triggers, however my question to the
> group is, is there a better or best practice for this scenario? This
> must be common. Any high-level tips or direction will be highly
> appreciated.
> DAP|||Dan (dpratte@.dpratte.com) writes:
> I am loading data from table A into table B. Certain columns in B have
> check constraints. I'd like for any rows from A, which violate
> constraints, to be inserted into a third table, C. When the process is
> finished, I'll have only good rows in B, and exeption rows in C.
> I am investigating INSTEAD OF triggers, however my question to the
> group is, is there a better or best practice for this scenario? This
> must be common.

Not really.

I think the only way to do this without duplicating the constraints is
run a cursor one-by-one as suggested by Celko. An improvement could be
to first attempt to insert all, and if there is an error, use the
cursor as a fallback. But you could not do this in an INSTEAD OF
trigger, because an error in a trigger aborts the batch. You see,
the whole idea is that the INSERT statement should be atomic, either
all rows make it, or others not.

An alternative would be move the constraints to the trigger and check
for them there. An INSTEAD OF trigger would then redo the original
INSERT statement for the good rows. An AFTER trigger would delete
the bad rows.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

I missed the title to this! Rather than use a trigger I would put the logic
into a stored procedure.

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:427d0150$0$1878$da0feed9@.news.zen.co.uk...
> Hi
> You can try something like:
> INSERT INTO Table C
> SELECT col1, col2, col3 FROM TABLE A
> WHERE <CLAUSE TO TEST CONSTRAINT FAIL>
> INSERT INTO Table B
> SELECT col1, col2, col3 FROM TABLE A
> WHERE <CLAUSE TO TEST CONSTRAINTS PASS>
> OR
> INSERT INTO Table B
> SELECT col1, col2, col3 FROM TABLE A
> WHERE NOT EXISTS ( SELECT * FROM TABLE C WHERE <CLAUSE TO CHECK NOT IN C>)
> John
> "Dan" <dpratte@.dpratte.com> wrote in message
> news:1115474335.494377.167790@.o13g2000cwo.googlegr oups.com...
>>I am loading data from table A into table B. Certain columns in B have
>> check constraints. I'd like for any rows from A, which violate
>> constraints, to be inserted into a third table, C. When the process is
>> finished, I'll have only good rows in B, and exeption rows in C.
>>
>> I am investigating INSTEAD OF triggers, however my question to the
>> group is, is there a better or best practice for this scenario? This
>> must be common. Any high-level tips or direction will be highly
>> appreciated.
>>
>> DAP
>>

Is there WHERE for columns

Hi everybody,
Is there a way to filter columns at runtime based on their name?

I create SELECT statement which includes all columns, then, if I get some parameter I leave some columns out and don't select data from them.

Is it possible?

I'm working with MS SQL 2000.You can't omit columns. The closest thing you can do is use CASE expressions to hide the data you don't want to show, such as:

SELECT Col1, Col2, CASE WHEN Col1 = 'someValue' THEN Col3 ELSE NULL END AS Col3
FROM YourTable|||Is this possible:

SELECT Col1, Col2, CASE WHEN SomethingOutsideThisSelectCommand = 'someValue' THEN Col3 ELSE NULL END AS Col3
FROM YourTable|||As long as it's a valid SQL expression, it shouldn't be a problem.

Is there any way to 'undo' an update query?

I guess I am in big trouble now.

I have updated a table by querying in SQL Query Analyzer and one of table columns was all set to wrong data (of course it was because of my mistake in query sentence). I am wondering if there is any way to recover this table?

Unfortunately my database was not backuped after this table was created. I am wondering what the log file is for. Maybe can there any way to retrieve a table's old status by using the log table?
Any help?

without a database backup there's no way to restore data from a previous point in time. A log file can be used to store incremental changes from the last database/log backup.

in the future you may want to start your TSQL statement with a BEGIN TRAN, and allow yourself the ability to roll it back should you find yourself in a similar situation.

|||OK. I guess I gotta do all annoying import job again to make the table.
Anyway, thanks a lot, Greg. I appreciate your advice.

Is there any way to blank out certain columns in a single sele

The users doesn't want the data for these 4 columns to print out unless the
formID is one of the ones selected. I can use "" instead. The dataset is
sent to Crystal Report to print out so it's better using "". I think Crysta
l
prints out the word "NULL" if they're set to NULL.
Thanks.
"Raymond D'Anjou" wrote:

> Add a Case for each of these columns.
> Example:
> ...CASE when b.formID in ('2', '16', '11', '12', '1', '13', '10') then NU
LL
> else b.form end as form,...
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:9BD3F043-072D-4598-B2BB-BA63529EA142@.microsoft.com...
>
>I don't know anything about Crystal report.
You can use '' for Text datatypes but the numerics and dates may not give
you what you want.
You may have to do a bit of CASTing for these types.
If the users can only choose 1 formID, just build your query differently in
C#.
if formid in ('2', '16', '11', '12', '1', '13', '10') then
select t.tid, t.tdate, t.trip_ticket,t.source,'' AS form, '' AS
formdate, '' AS printed,...
else
select t.tid,t.tdate,t.trip_ticket,t.source,b.form, b.formdate,
b.printed,
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D27DCE8F-4B22-41C1-BDDF-A344467FCD63@.microsoft.com...
> The users doesn't want the data for these 4 columns to print out unless
> the
> formID is one of the ones selected. I can use "" instead. The dataset is
> sent to Crystal Report to print out so it's better using "". I think
> Crystal
> prints out the word "NULL" if they're set to NULL.
> Thanks.
> "Raymond D'Anjou" wrote:
>|||Hi,Thank you for the reply. I think the system got messed up here. The
"NULL" answer was address to Mesa's question.
My question for you is: Do I just append the "Case..." to the end of my
select
statement?
"Alpha" wrote:
> The users doesn't want the data for these 4 columns to print out unless th
e
> formID is one of the ones selected. I can use "" instead. The dataset is
> sent to Crystal Report to print out so it's better using "". I think Crys
tal
> prints out the word "NULL" if they're set to NULL.
> Thanks.
> "Raymond D'Anjou" wrote:
>|||No, the Case replaces the column name in your Select statement.
So, instead of:
select b.form,...
select CASE when b.formID in ('2', '16', '11', '12', '1', '13', '10') then
NULL else b.form end as form,
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:ED00B94C-E471-4508-9F1C-D8E2803E47EC@.microsoft.com...
> Hi,Thank you for the reply. I think the system got messed up here. The
> "NULL" answer was address to Mesa's question.
> My question for you is: Do I just append the "Case..." to the end of my
> select
> statement?
>
> "Alpha" wrote:
>