Please Help Beginners

The followong statement is working

iif ([Measures].[Docsum]>500, "1","0")

But there is a need to count these Docsums
so
iif ([Measures].[Docsum]>500, Count[Docsum],"0")

but not this way
Can somebody help and explains, how to Count these sums
Thank'scan the count or sum wrap around the iif-statement?

Please help

Hello everyone, I received this program for use with my college class's. Now it is important that I get this working, after about 4 hours of messing around with Visual Studio Pro to get it installed, I ran into yet another problem. The SQL Server will not install here is the error log I get.

Microsoft SQL Server 2005 Setup beginning at Sat Feb 17 12:21:14 2007
Process ID : 3932
c:\f3669493315ed3747ac0bc50\setup.exe Version: 2005.90.2047.0
Running: LoadResourcesAction at: 2007/1/17 12:21:14
Complete: LoadResourcesAction at: 2007/1/17 12:21:14, returned true
Running: ParseBootstrapOptionsAction at: 2007/1/17 12:21:14
Loaded DLL:c:\f3669493315ed3747ac0bc50\xmlrw.dll Version:2.0.3609.0
Complete: ParseBootstrapOptionsAction at: 2007/1/17 12:21:14, returned false
Error: Action "ParseBootstrapOptionsAction" failed during execution. Error information reported during run:
Could not parse command line due to datastore exception.
Source File Name: utillib\persisthelpers.cpp
Compiler Timestamp: Wed Oct 26 16:38:20 2005
Function Name: writeEncryptedString
Source Line Number: 124
-
writeEncryptedString() failed
Source File Name: utillib\persisthelpers.cpp
Compiler Timestamp: Wed Oct 26 16:38:20 2005
Function Name: writeEncryptedString
Source Line Number: 123
-
Error Code: 0x80070002 (2)
Windows Error Text: The system cannot find the file specified.

Source File Name: cryptohelper\cryptsameusersamemachine.cpp
Compiler Timestamp: Wed Oct 26 16:37:25 2005
Function Name: sqls::CryptSameUserSameMachine::ProtectData
Source Line Number: 50

2
Could not skip Component update due to datastore exception.
Source File Name: datastore\cachedpropertycollection.cpp
Compiler Timestamp: Wed Oct 26 16:37:20 2005
Function Name: CachedPropertyCollection::findProperty
Source Line Number: 130
-
Failed to find property "InstallMediaPath" {"SetupBootstrapOptionsScope", "", "3932"} in cache
Source File Name: datastore\propertycollection.cpp
Compiler Timestamp: Wed Oct 26 16:37:21 2005
Function Name: SetupBootstrapOptionsScope.InstallMediaPath
Source Line Number: 44
-
No collector registered for scope: "SetupBootstrapOptionsScope"
Running: ValidateWinNTAction at: 2007/1/17 12:21:14
Complete: ValidateWinNTAction at: 2007/1/17 12:21:14, returned true
Running: ValidateMinOSAction at: 2007/1/17 12:21:14
Complete: ValidateMinOSAction at: 2007/1/17 12:21:14, returned true
Running: PerformSCCAction at: 2007/1/17 12:21:14
Complete: PerformSCCAction at: 2007/1/17 12:21:14, returned true
Running: ActivateLoggingAction at: 2007/1/17 12:21:14
Error: Action "ActivateLoggingAction" threw an exception during execution. Error information reported during run:
Datastore exception while trying to write logging properties.
Source File Name: datastore\cachedpropertycollection.cpp
Compiler Timestamp: Wed Oct 26 16:37:20 2005
Function Name: CachedPropertyCollection::findProperty
Source Line Number: 130
-
Failed to find property "primaryLogFiles" {"SetupStateScope", "", ""} in cache
Source File Name: datastore\propertycollection.cpp
Compiler Timestamp: Wed Oct 26 16:37:21 2005
Function Name: SetupStateScope.primaryLogFiles
Source Line Number: 44
-
No collector registered for scope: "SetupStateScope"
00EDCFC0Unable to proceed with setup, there was a command line parsing error. : 2
Error Code: 0x80070002 (2)
Windows Error Text: The system cannot find the file specified.

Source File Name: datastore\propertycollection.cpp
Compiler Timestamp: Wed Oct 26 16:37:21 2005
Function Name: SetupBootstrapOptionsScope.InstallMediaPath
Source Line Number: 44

Class not registered.
Failed to create CAB file due to datastore exception
Source File Name: datastore\cachedpropertycollection.cpp
Compiler Timestamp: Wed Oct 26 16:37:20 2005
Function Name: CachedPropertyCollection::findProperty
Source Line Number: 130
-
Failed to find property "HostSetup" {"SetupBootstrapOptionsScope", "", "3932"} in cache
Source File Name: datastore\propertycollection.cpp
Compiler Timestamp: Wed Oct 26 16:37:21 2005
Function Name: SetupBootstrapOptionsScope.HostSetup
Source Line Number: 44
-
No collector registered for scope: "SetupBootstrapOptionsScope"
Message pump returning: 2

I have a core 2 duo 64 bit chipset, 1 gig of ram, all updates, and im running XP sp2. I have repaired my .net framework to ensure it's stable. I'm running out of options and I only have a few days left till my programing class starts and it's very important that I get this working.

It looks like you have a corrupted installation set. Its not even able to launch the set up bootstrap. When you say you "received this program", what program di you receive and where did it come from?|||Also, are you certain that you have the correct 64 bit version of the installer?

please check this trigger

Aperently I could not insert a text field to another table from INSERTED in a trigger.
It seems th follwoing is working, do you see any problem joining INSERTED to the mySrcTable
which is the table that has this trigger

INSERT INTO myRemoteDatabase.dbo.myDestTable (myTrID,myFirstName,myBigText)
SELECT i.myTrID, i.myFirstName, p.myBigText
FROM INSERTED i INNER JOIN mySrcTable p ON i.myTrID = p.myTrID
WHERE (i.myTrType = 'In')

Thanks,

You can set the compatibility level to 80 or higher Or you can choose varchar(max) which has limitation of 8000 characters.

For details seeCREATE TRIGGER.

Hope it helps.

sql

Please Advise: Big Control Flow or Big Data Flow

Hi all! I recently started working with SSIS and one of the things that is puzzling me the most is what's the best way to go:

A small control flow, with large data flow tasks A control flow with more, but smaller, data flow tasksAny help will be greatly appreciated.
Thanks,
Ricardo
Multiple smaller data flows are generally better than a single big one. Breaking a large process into smaller data flows allows you to manage memory better and offers more opportunities for restartability. Large data flows can also suffer from threading problems in the current version.

|||

Dear Friend,

Depends on your system requirements. But there is things that you only can do inside dataflow...

Regards!

|||

Ricardo,

I'd generally agree with what the other guys have said. However, I think it would be rare that you would get the chance to make this decision. The nature of your dataflow is determined by...well...whatever the reason you're building it is. You don't generally get the chance to say "Hmmm, I think i'll put less components in this dataflow". You can of course put more, but you would be foolish to do so.

Similarly, when you have a requirement its pretty clear whether you need a task or a component to achieve it so, again, you don't (usually) have a choice.

-Jamie

|||Thanks everyone for the quick replies. I'm finding it easier as I go along. Some mistakes in the beginning, but after a while, things start being a bit more logical.

Thanks,
Ricardo

Please Advise: Big Control Flow or Big Data Flow

Hi all! I recently started working with SSIS and one of the things that is puzzling me the most is what's the best way to go:

A small control flow, with large data flow tasks A control flow with more, but smaller, data flow tasksAny help will be greatly appreciated.
Thanks,
Ricardo
Multiple smaller data flows are generally better than a single big one. Breaking a large process into smaller data flows allows you to manage memory better and offers more opportunities for restartability. Large data flows can also suffer from threading problems in the current version.

|||

Dear Friend,

Depends on your system requirements. But there is things that you only can do inside dataflow...

Regards!

|||

Ricardo,

I'd generally agree with what the other guys have said. However, I think it would be rare that you would get the chance to make this decision. The nature of your dataflow is determined by...well...whatever the reason you're building it is. You don't generally get the chance to say "Hmmm, I think i'll put less components in this dataflow". You can of course put more, but you would be foolish to do so.

Similarly, when you have a requirement its pretty clear whether you need a task or a component to achieve it so, again, you don't (usually) have a choice.

-Jamie

|||Thanks everyone for the quick replies. I'm finding it easier as I go along. Some mistakes in the beginning, but after a while, things start being a bit more logical.

Thanks,
Ricardo

Placing sp_executesql data into a var

I have a stored procedure that takes various parameters and performs simple selects using these. A quick summary of the db structure this is working on would be :

lookup_table1
table1_id
description

lookup_table2
table2_id
description

This stored procedure attempts to select the given description from the given table and return the id, if the id isnt present it then inserts the new value and returns the new id.

Now I have this working, ish, but the problem that I have is that in the case of a new insert the procedure returns two datasets, an empty one (as the initial select returned no results) and another with the id of the newly inserted value.

This procedure as it stands right now

CREATE PROCEDURE aida_lookup
@.lookup_table varchar(255),
@.lookup_id_name varchar(255),
@.lookup_description_col varchar(255),
@.lookup_value varchar(255)
AS

declare @.sql nvarchar(2048)

--
-- Check if the given lookup value exists in the give table / column
--
set @.sql ='SELECT ' + @.lookup_id_name + ' FROM ' + @.lookup_table + ' WHERE ' + @.lookup_description_col + ' = ''' + @.lookup_value + ''''
EXECUTE sp_executesql @.sql
if (@.@.rowcount = 0) goto new_value
if (@.@.error <> 0) goto on_error
return(1)

--
-- Insert new lookup value into the given table / column
--
new_value:
-- NEED TO CLEAR PREVIOUS NULL SELECT
begin transaction
set @.sql = 'INSERT INTO ' + @.lookup_table + ' (' + @.lookup_description_col + ') VALUES (''' + @.lookup_value + '''); SELECT SCOPE_IDENTITY()'
execute sp_executesql @.sql
if (@.@.error <> 0) goto on_error
commit transaction
return(1)

--
-- Error handler
--
on_error:
rollback transaction
return (0)
GO

The problem being is that if the value is inserted as a new value I need to remove the empty dataset so that regardless of how the procedure is run, it will always return the id at position row 0 column 0.

My attempted solution was to declare a @.temp var and place the return value of sp_executesql into this, and if it wasnt null then return it, and if it was then proceed to insert, e.g.

declare @.temp int
...
SET @.temp = EXECUTE sp_executesql @.SQL
if (@.@.temp = null) goto new_value
SELECT @.temp
return(1)

Obviously this doesnt work, so I am open to suggestions. To be completly honest I have run out of hair to rip out and I am sure this can probably be done in a much more elegant fashion, so any help will be greatly appreciatedHow about

CREATE TABLE #temp(Result vachar(8000))

INSERT INTO #temp(Result) EXEC(@.SQL)|||If it is at all possible I would like to avoid using temporary tables, as if I can do this with SQL memory objects the performance hit would (I assume be much lower)|||Your worried about performance, yet your using dynamic sql...hmmmm|||Exactly, using dynamic sql is a necessary evil in this case (long story), so i dont want to hit the system even more.|||I'm not sure exactly what your looking for...but here are three versions...

DECLARE @.sql nvarchar(4000), @.x int, @.y int, @.z int

PRINT 'Something that will work'
SELECT @.sql = N'SELECT * FROM INFORMATION_SCHEMA.TABLES'
EXECUTE @.y = sp_EXECuteSQL @.sql
SELECT @.z = @.@.ERROR, @.x = @.@.ROWCOUNT
SELECT @.x, @.y, @.z

PRINT 'Something that will return 0 rows'
SELECT @.sql = N'SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ' + '''' + 'Brett' + ''''
EXECUTE @.y = sp_EXECuteSQL @.sql
SELECT @.z = @.@.ERROR, @.x = @.@.ROWCOUNT
SELECT @.x, @.y, @.z

PRINT 'Something that will explode'
SELECT @.sql = N'SELECT * FROM INFORMATION_SCHEMA.BRETT'
EXECUTE @.y = sp_EXECuteSQL @.sql
SELECT @.z = @.@.ERROR, @.x = @.@.ROWCOUNT
SELECT @.x, @.y, @.z|||Originally posted by Brett Kaiser
PRINT 'Something that will explode'
SELECT @.sql = N'SELECT * FROM INFORMATION_SCHEMA.BRETT'
EXECUTE @.y = sp_EXECuteSQL @.sql
SELECT @.z = @.@.ERROR, @.x = @.@.ROWCOUNT
SELECT @.x, @.y, @.z
[/code]

Well .. brett... it did explode ...and it ruined my laptop ... i m gonna sue you for this one :)|||Originally posted by Enigma
Well .. brett... it did explode ...and it ruined my laptop ... i m gonna sue you for this one :)

I'll see if I can dig up an 8086 for you...I'm sure it'll be an improvement...

Did you want 1 or 2 5 1/4 drives?

And I think there's a huge 250k chip in there...

Black and green are you ok, right?|||Brett .. no need to ... i ve already got a 8086 with 1 MB RAM ...
dont bother yourself with the 256 K .. right now I am trying to get Windows 3.11 loaded on it .. help me out there if you can :)|||Is that vanilla or windows for workgroups?

and a meg...wow...

HUGE|||Just incase anybody is searching the forums for the same answer I was looking for the following stored procedure works :

CREATE PROCEDURE aida_lookup
@.lookup_table varchar(255),
@.lookup_id_name varchar(255),
@.lookup_description_col varchar(255),
@.lookup_value varchar(255)
AS

declare @.procedure_sql nvarchar(2048)

set @.procedure_sql = '

declare @.sql nvarchar(2048)

set @.sql = (SELECT ' + @.lookup_id_name + ' FROM ' + @.lookup_table + ' WHERE ' + @.lookup_description_col + ' = ''' + @.lookup_value +''')

if (@.sql = null)
begin
begin transaction
INSERT INTO ' + @.lookup_table + ' (' + @.lookup_description_col + ') VALUES (''' + @.lookup_value + ''')
SELECT SCOPE_IDENTITY()
if (@.@.error <> 0) goto on_error
commit transaction
return

on_error:
rollback transaction
return
end
else
begin
SELECT @.sql
end
return'
print @.procedure_sql;

EXEC( @.procedure_sql )
return(1)
GO|||You can return values from executesql by OUTPUT semantics on fully declared parameters:

declare @.sql nvarchar(4000)
declare @.params nvarchar(4000)

set @.sql = N'select @.OUT=@.IN * 2'

set @.params = N'@.in int, @.out int OUTPUT'

declare @.in int
declare @.out int

set @.in = 4

exec sp_executesql @.sql, @.params, @.in, @.out OUTPUT

select @.out

=8

PL/SQL: couldnt create trigger

Hi,

i have a mass operation with some triggers to increment the id of a table ...

now, some of the copy&pasted statements working, some won't ... :(

part of the statement:

CREATE OR REPLACE TRIGGER SHEMA.TRG_INS_SEQ_LICENSE_ID BEFORE INSERT ON SHEMA.LICENSE REFERENCING OLD AS old NEW AS new FOR EACH ROW
(some pl/sql syntax)

^^ this doesn't print out any error.

CREATE OR REPLACE TRIGGER SHEMA.TRG_INS_SEQ_SOFTWARE_ID BEFORE INSERT ON SHEMA.SOFTWARE REFERENCING OLD AS old NEW AS new FOR EACH ROW
(some pl/sql syntax)

^^ there's an error like:
CREATE OR REPLACE TRIGGER SHEMA.TRG_INS_SEQ_WORKSTATION_ID BEFORE INSERT ON SHEMA.WORKST
*
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is
incomplete or malformed

whats going wrong?? both tables exists in the database with an id field defined as number(10) and flagged with a primary key.

maybe somebody can explain ...

thanksproblem solved|||Hi, I am stuck with this same problem. Please can someone help. It is quite urgent.

Thaks in advanced for your help...

Regards,

Paul|||This is what the error means:

Cause: In a declaration, the name of a variable or cursor is misspelled or the declaration makes a forward reference. Forward references are not allowed in PL/SQL. A variable or cursor must be declared before it is referenced it in other statements, including other declarative statements. For example, the following declaration of dept_rec raises this exception because it refers to a cursor not yet declared:

DECLARE
dept_rec dept_cur%ROWTYPE;
CURSOR dept_cur IS SELECT ...
...

Without seeing your code, it's hard to comment further.|||Hi,

This problem is getting strange.

I am using this code to product a primary key for a table. I have created a sequene and am using the next value from the sequence to act as the key.

The code I am using for the trigger is as follows

BEGIN
IF :new.ORDER_ID IS NULL THEN
SELECT AUTO_ORDER_ID.NEXTVAL ///SEQUENCE
INTO :new.ORDER_ID
FROM dual;
END IF;
END;

This has worked for about 8 tables with the exception of this one. I have tried to re-create the table using a different name and a different ID atttribute i.e table called sales and Sales_id and still no joy.

any ideas??

Oh.. I have also reinstalled the whole 9i database and I still get the same problem

Thanks for your help in advanced.

Regards,

Paul|||/// is not a valid comment syntax in PL/SQL, so that won't work! If that isn't the problem, I can't see anything obvious. You are getting:

ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed

yes? On what line?

If still struggling, please post the ENTIRE trigger text exactly as you are running it - including the CREATE..., and the description of the table...|||I am stuck with the exact same error as above, the code I am using is as follows:

CREATE OR REPLACE TRIGGER "FYP"."TEST" BEFORE
INSERT ON "FYP"."JOBS"
FOR EACH ROW begin
select seq_jobs.nextval into :new.id from dual;
end;

sequence named seq_jobs exists.

The table named exists too and the code is as follows:
CREATE TABLE "FYP"."JOBS" ("ID" NUMBER(10), "ALARM_TEXT"
VARCHAR2(255 byte), "POINT_ID" NUMBER(10), "DATE" DATE, "PRORITY"
NUMBER(10))
TABLESPACE "FYP" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
PARALLEL ( DEGREE DEFAULT )
LOGGING

This is really annoying: I cannot see the forward reference in the code: I don't see anything referenced apart from the table, the colum ID and the sequence...|||Hello all,

I'm pretty new to Oracle and I'm having the same problem. I got a DB with 14 tables, and on 5 of them I can't create a trigger and get the same errors as above:

ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed.

Code for the table:

CREATE TABLE "DCSMSG"."DCSMSG_TBL_UNKOWNMSG"("UNKNOWNMSG_ID"
NUMBER NOT NULL, "SOURCE_ID" NUMBER NOT NULL, "DATE" DATE NOT
NULL, "MESSAGE" NVARCHAR2(50) NOT NULL,
CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_FID" FOREIGN KEY(
"SOURCE_ID")
REFERENCES "DCSMSG"."DCSMSG_TBL_SOURCES"("SOURCE_ID"),
CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_ID" PRIMARY KEY(
"UNKNOWNMSG_ID") USING
INDEX
TABLESPACE "DCSMSG"
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10 INITRANS
2 MAXTRANS 255)
TABLESPACE "DCSMSG" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING

Code for the Sequence:

CREATE SEQUENCE "DCSMSG"."DCSMSG_SEQ_UNKMSG_AUTONR" INCREMENT BY
1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 ORDER

Code for the Trigger:

CREATE OR REPLACE TRIGGER "DCSMSG"."DCSMSG_TBL_TRG_UNKMSG_AUTONR"
BEFORE INSERT
ON "DCSMSG"."DCSMSG_TBL_UNKNOWNMSG"
FOR EACH ROW BEGIN
SELECT DCSMSG.DCSMSG_SEQ_UNKMSG_AUTONR.NEXTVAL
INTO :NEW.UNKNOWNMSG_ID
FROM DUAL;
END;

Anyone who can help me out? I did the same thing for the other 8 tables, and it worked fine. I re-created these 5 tables, but didn't work. removing the foreign keys didn't work either. I can't see the problem here.

Any help would be very much appreciated! Thanks!

With kind regards,

Gog|||Originally posted by Gog
Hello all,

I'm pretty new to Oracle and I'm having the same problem. I got a DB with 14 tables, and on 5 of them I can't create a trigger and get the same errors as above:

ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed.

Code for the table:

CREATE TABLE "DCSMSG"."DCSMSG_TBL_UNKOWNMSG"("UNKNOWNMSG_ID"
NUMBER NOT NULL, "SOURCE_ID" NUMBER NOT NULL, "DATE" DATE NOT
NULL, "MESSAGE" NVARCHAR2(50) NOT NULL,
CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_FID" FOREIGN KEY(
"SOURCE_ID")
REFERENCES "DCSMSG"."DCSMSG_TBL_SOURCES"("SOURCE_ID"),
CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_ID" PRIMARY KEY(
"UNKNOWNMSG_ID") USING
INDEX
TABLESPACE "DCSMSG"
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10 INITRANS
2 MAXTRANS 255)
TABLESPACE "DCSMSG" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)
LOGGING

Code for the Sequence:

CREATE SEQUENCE "DCSMSG"."DCSMSG_SEQ_UNKMSG_AUTONR" INCREMENT BY
1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 ORDER

Code for the Trigger:

CREATE OR REPLACE TRIGGER "DCSMSG"."DCSMSG_TBL_TRG_UNKMSG_AUTONR"
BEFORE INSERT
ON "DCSMSG"."DCSMSG_TBL_UNKNOWNMSG"
FOR EACH ROW BEGIN
SELECT DCSMSG.DCSMSG_SEQ_UNKMSG_AUTONR.NEXTVAL
INTO :NEW.UNKNOWNMSG_ID
FROM DUAL;
END;

Anyone who can help me out? I did the same thing for the other 8 tables, and it worked fine. I re-created these 5 tables, but didn't work. removing the foreign keys didn't work either. I can't see the problem here.

Any help would be very much appreciated! Thanks!

With kind regards,

Gog
Looking at your code and that of Rhs98 posted earlier, I notice that you both have table and column names in double quotes, and in fact both have a column called "DATE" which is of course a reserved word in Oracle.

Changing the column name from "DATE" to ADATE (i.e. anything that isn't a reserved word) makes the problem go away.

Moral: NEVER put double quotes round table and column names, and (as a consequence) NEVER use reserved words as table and column names!|||hey thanks for the fast reply .. It seems to have worked. But this raises another question.

Why did a few other tables with a field called 'DATE' in it work properly than? Sorry! Apparently I had another name then 'DATE' for my date fields in the other table :-$ So never mind about that!

And ps. I didn't put the quotes there, DBA Studio studio gave that code to me when I selected 'show object ddl' :-D

thx for the help!

pk constraint enforcement using SSIS to import data

Note: I'm running a bottom up design on this project as I won't know what data I'm really working with until I can get it imported and analyze it. Also, I'm not a DBA or developer, so please be gentle...

I am importing 30k+ rows using SSIS (OLEDB -DB2- source to OleDB -2k5- destination). The import works fine, but I just realized that I need to set up a pk on the row emp_ids. The problem is that in the DB2 source, the emp_ids were removed (set to whitespace, but not null). So, I can't just uncheck the 'keep nulls' option and import the data.

Any suggestions or links (using SSIS) on how to identify the rows where emp_id = "whitespaces" and 1) either keep them from being imported, or 2) remove them afterwards?

(I suppose this could be done using sql statement to identify the whitespace rows, but that would present difficulties of its own due to the random spacing nature of the updates. Also, I'm hoping for a checkbox wonder solution.)

Please advise. Thanks!

- Isaac

Why not use a conditional split to look for NULLS and NULLS resulting from a TRIM() operation.

So TRIM() your data in the conditional split, and then test that for NULL. If it matches, then you can use that tagged output stream to do with it whatever you wish... You can throw them away, you can push them to their own destination (flat file, SQL server, etc...)|||

That worked perfectly. Thanks for the advice Phil!

- Isaac

|||

While experimenting, I also found that the sort transform can accomplish this task. Not only are the rows with whitespaces removed, but this task also removes duplicate ids from the list... two birds with one stone (using a sort task (with delete dups) vs a trim split).

Awesome... once again thanks!

- Isaac

|||

isaacb wrote:

While experimenting, I also found that the sort transform can accomplish this task. Not only are the rows with whitespaces removed, but this task also removes duplicate ids from the list... two birds with one stone (using a sort task (with delete dups) vs a trim split).

Awesome... once again thanks!

- Isaac

Hmm... I don't like that... I don't like that the sort transformation removes rows with spaces in them. For that matter, I don't want it to remove NULLs either. Getting rid of duplicates, yes, but I would think your resultset would be reduced to just one row with spaces, as opposed to none. Are you sure it just discarded ALL rows that were "empty"?|||

In looking at it again, it's not perfect as it does leave one duplicate whitespace row (the first one that it finds). While that shouldn't be acceptable in a real world scenario, it works for the first rough pass on my project.

The sort/delete functionality actually works rather well when you select your pk as the row to "sort" on. It only checks against the rows that you specify, so all the verified data is still there. I checked the results against a report that I pulled off the server... I eyeballed it for a few minutes, but it seems to be accurate.

Maybe I'm mis-using the functionality (?), but it works...

|||

isaacb wrote:

In looking at it again, it's not perfect as it does leave one duplicate whitespace row (the first one that it finds). While that shouldn't be acceptable in a real world scenario, it works for the first rough pass on my project.

The sort/delete functionality actually works rather well when you select your pk as the row to "sort" on. It only checks against the rows that you specify, so all the verified data is still there. I checked the results against a report that I pulled off the server... I eyeballed it for a few minutes, but it seems to be accurate.

Maybe I'm mis-using the functionality (?), but it works...

No, using the sort transformation to remove duplicates is a very valid use. And you get sorted data which helps is most cases for downstream transformations...

I was just concerned when you said it removed all of the rows with spaces, and it did what it's supposed to do which was to remove duplicates and therefore leave one row behind.

pivoting query on t-sql

gud day.

please help me. im working right now on a case study that will
retrieve/produce a simple report on sql. my problem is I dont know how
to pivot queries like in access. please help me. thanksHere's an example of a simple crosstab in SQL. Monthly Sales by region:

CREATE TABLE DailySales (region CHAR(10), saledate DATETIME, saleamount
DECIMAL(10,2) NOT NULL, PRIMARY KEY (region,saledate))

SELECT region,
SUM(CASE MONTH(saledate) WHEN 1 THEN saleamount END) AS jan,
SUM(CASE MONTH(saledate) WHEN 2 THEN saleamount END) AS feb,
SUM(CASE MONTH(saledate) WHEN 3 THEN saleamount END) AS mar,
SUM(CASE MONTH(saledate) WHEN 4 THEN saleamount END) AS apr,
SUM(CASE MONTH(saledate) WHEN 5 THEN saleamount END) AS may,
SUM(CASE MONTH(saledate) WHEN 6 THEN saleamount END) AS jun,
SUM(CASE MONTH(saledate) WHEN 7 THEN saleamount END) AS jul,
SUM(CASE MONTH(saledate) WHEN 8 THEN saleamount END) AS aug,
SUM(CASE MONTH(saledate) WHEN 9 THEN saleamount END) AS sep,
SUM(CASE MONTH(saledate) WHEN 10 THEN saleamount END) AS oct,
SUM(CASE MONTH(saledate) WHEN 11 THEN saleamount END) AS nov,
SUM(CASE MONTH(saledate) WHEN 12 THEN saleamount END) AS [dec]
FROM DailySales
GROUP BY region

These articles give examples of more complex, dynamic crosstabs:

http://www.sqlteam.com/item.asp?ItemID=2955
http://www.sqlmag.com/Articles/Inde...ArticleID=15608

--
David Portas
----
Please reply only to the newsgroup
--|||Check out the RAC utility.It is similar to
Access crosstab and has many more features/options.
You will find it as easy to use as Access.

www.rac4sql.net

Pivot Query....

Working on roles for projects (roles are granted and denied based on
dates) and I need to display a summary (on the web) that lists the
roles down the left column, with the dates along the top, and the
numbers needed in the matrix that results from the rows and columns
created by roles/dates...
Hey, I totally need some help with this, I know its wrong..but I need
ALL of the dates, not just a few...
Basically I need to sum up the numberneeded by date and by
role...anyone out there please help me out on this?
--Code
select RolesForDateID, ProjectDateID, ProjectDate, ProjectRoleID,
NumberNeeded
from (Select rfd.RolesForDateID, pd.PrjDateID as ProjectDateID,
pd.PrjDate as ProjectDate,
rfd.ProjectRoleID, rfd.NumberNeeded from
ut_extras_Projects_RolesForDate rfd
right outer join ut_extras_projectdates pd
on rfd.ProjectDateID = pd.PrjDateID) tmp
PIVOT
(
SUM(NumberNeeded) For ProjectDate IN (ProjectDate)
)
--end code
Hi
Without seeing the sample data + an expected result it is really hard to
suggest something
<Tremmorkeep@.gmail.com> wrote in message
news:5b32f45d-9405-4844-b840-a32088a0c4a9@.d27g2000prf.googlegroups.com...
> Working on roles for projects (roles are granted and denied based on
> dates) and I need to display a summary (on the web) that lists the
> roles down the left column, with the dates along the top, and the
> numbers needed in the matrix that results from the rows and columns
> created by roles/dates...
>
> Hey, I totally need some help with this, I know its wrong..but I need
> ALL of the dates, not just a few...
> Basically I need to sum up the numberneeded by date and by
> role...anyone out there please help me out on this?
>
> --Code
> select RolesForDateID, ProjectDateID, ProjectDate, ProjectRoleID,
> NumberNeeded
> from (Select rfd.RolesForDateID, pd.PrjDateID as ProjectDateID,
> pd.PrjDate as ProjectDate,
> rfd.ProjectRoleID, rfd.NumberNeeded from
> ut_extras_Projects_RolesForDate rfd
> right outer join ut_extras_projectdates pd
> on rfd.ProjectDateID = pd.PrjDateID) tmp
> PIVOT
> (
> SUM(NumberNeeded) For ProjectDate IN (ProjectDate)
> )
> --end code

Pivot Query....

Working on roles for projects (roles are granted and denied based on
dates) and I need to display a summary (on the web) that lists the
roles down the left column, with the dates along the top, and the
numbers needed in the matrix that results from the rows and columns
created by roles/dates...
Hey, I totally need some help with this, I know its wrong..but I need
ALL of the dates, not just a few...
Basically I need to sum up the numberneeded by date and by
role...anyone out there please help me out on this?
--Code
select RolesForDateID, ProjectDateID, ProjectDate, ProjectRoleID,
NumberNeeded
from (Select rfd.RolesForDateID, pd.PrjDateID as ProjectDateID,
pd.PrjDate as ProjectDate,
rfd.ProjectRoleID, rfd.NumberNeeded from
ut_extras_Projects_RolesForDate rfd
right outer join ut_extras_projectdates pd
on rfd.ProjectDateID = pd.PrjDateID) tmp
PIVOT
(
SUM(NumberNeeded) For ProjectDate IN (ProjectDate)
)
--end codeHi
Without seeing the sample data + an expected result it is really hard to
suggest something
<Tremmorkeep@.gmail.com> wrote in message
news:5b32f45d-9405-4844-b840-a32088a0c4a9@.d27g2000prf.googlegroups.com...
> Working on roles for projects (roles are granted and denied based on
> dates) and I need to display a summary (on the web) that lists the
> roles down the left column, with the dates along the top, and the
> numbers needed in the matrix that results from the rows and columns
> created by roles/dates...
>
> Hey, I totally need some help with this, I know its wrong..but I need
> ALL of the dates, not just a few...
> Basically I need to sum up the numberneeded by date and by
> role...anyone out there please help me out on this?
>
> --Code
> select RolesForDateID, ProjectDateID, ProjectDate, ProjectRoleID,
> NumberNeeded
> from (Select rfd.RolesForDateID, pd.PrjDateID as ProjectDateID,
> pd.PrjDate as ProjectDate,
> rfd.ProjectRoleID, rfd.NumberNeeded from
> ut_extras_Projects_RolesForDate rfd
> right outer join ut_extras_projectdates pd
> on rfd.ProjectDateID = pd.PrjDateID) tmp
> PIVOT
> (
> SUM(NumberNeeded) For ProjectDate IN (ProjectDate)
> )
> --end code

Pivot Query....

Working on roles for projects (roles are granted and denied based on
dates) and I need to display a summary (on the web) that lists the
roles down the left column, with the dates along the top, and the
numbers needed in the matrix that results from the rows and columns
created by roles/dates...
Hey, I totally need some help with this, I know its wrong..but I need
ALL of the dates, not just a few...
Basically I need to sum up the numberneeded by date and by
role...anyone out there please help me out on this?
--Code
select RolesForDateID, ProjectDateID, ProjectDate, ProjectRoleID,
NumberNeeded
from (Select rfd.RolesForDateID, pd.PrjDateID as ProjectDateID,
pd.PrjDate as ProjectDate,
rfd.ProjectRoleID, rfd.NumberNeeded from
ut_extras_Projects_RolesForDate rfd
right outer join ut_extras_projectdates pd
on rfd.ProjectDateID = pd.PrjDateID) tmp
PIVOT
(
SUM(NumberNeeded) For ProjectDate IN (ProjectDate)
)
--end codeHi
Without seeing the sample data + an expected result it is really hard to
suggest something
<Tremmorkeep@.gmail.com> wrote in message
news:5b32f45d-9405-4844-b840-a32088a0c4a9@.d27g2000prf.googlegroups.com...
> Working on roles for projects (roles are granted and denied based on
> dates) and I need to display a summary (on the web) that lists the
> roles down the left column, with the dates along the top, and the
> numbers needed in the matrix that results from the rows and columns
> created by roles/dates...
>
> Hey, I totally need some help with this, I know its wrong..but I need
> ALL of the dates, not just a few...
> Basically I need to sum up the numberneeded by date and by
> role...anyone out there please help me out on this?
>
> --Code
> select RolesForDateID, ProjectDateID, ProjectDate, ProjectRoleID,
> NumberNeeded
> from (Select rfd.RolesForDateID, pd.PrjDateID as ProjectDateID,
> pd.PrjDate as ProjectDate,
> rfd.ProjectRoleID, rfd.NumberNeeded from
> ut_extras_Projects_RolesForDate rfd
> right outer join ut_extras_projectdates pd
> on rfd.ProjectDateID = pd.PrjDateID) tmp
> PIVOT
> (
> SUM(NumberNeeded) For ProjectDate IN (ProjectDate)
> )
> --end code

pivot query : access to msSql

Hi; i'm trying to get this pivot query working in msSql:

TRANSFORM Sum(tblAssortiment.Aantal) AS SumOfAantal

SELECT tblAssortiment.Assortiment

FROM tblAssortiment

WHERE (((tblAssortiment.Artikel)=?))

GROUP BY tblAssortiment.Assortiment

PIVOT tblAssortiment.Maat;

Any ideas?

Thank you!

It's difficult to determine what you are trying to do without the full schema of your database or more information, but you should start here to find out more about the PIVOT statement in SQL Server:

http://msdn2.microsoft.com/en-us/library/ms177410.aspx