I have 12 colums named in source
ERR1 - ERR12. I do have a reference table for ERR1 - ERR12 and haing values 1-12
Now the nature of ERR columns is that each of them has either 1 or more than 1 TICK mark in a small BOX.
In the destinaltion table I have only 1 column to get the Values from 1-12.
How do i match and get the values.
I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups
Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:
How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
Thanks for all your help!
what is the best way to uses ssis to deal with a CSV file that contains a number of records that contain a different number of fields ?
I could just load as a single column delimited by <CRLF> but then I would have to write the code to parse the line, effectively detokenising the columns myself but if that is the case then why uses ssis at all ?
I have a requirement to Insert Column 1 and Column 2 based on below condition only. Looking for a Store procedure or query
Condition : Allow Insert when column 1 and Column 2 have same values on 2nd row insert. But should not allow insert when Column 2 value is different.
ALLOW INSERT:
Column1 Column2
A0007 12-Aug
A0007 12-Aug
A0007 12-Aug
DONOT ALLOW INSERT: (COLUMN1 ID should not allow different dates)
Column1 Column2
A0007 23-Mar
A0007 02-Feb
FINAL OUTPUT Should be
Column1Column2
A000712-Aug
A000712-Aug
A000712-Aug
B000220-Jun
B000220-Jun
C000330-Sep
Discard Insert when Column1 ID's comes with Different dates.
My requirement is below.enhancing the T- sql query as I was told not to use SSIS.
Insert data from Table A to Table B with conditions:
1. Truncate gender to one character if necessary.
2. Convert character fields to uppercase as necessary.
3. For systems that supply both residential and mailing addresses, use the residential address if available (both street_address and zip fields have value), mailing address otherwise.
In SSIS I took conditional split with 'ISNULL(res_street_address) == TRUE || ISNULL(res_zip) == TRUE '
default outputname :Consider Res Address; Outputname:Consider mail address.
and mapped as:
(Table A) mail_street_address---street address(Table B)
(Table A) mail_city----------------City(Table B)
(Table A) mail_Zip----------------Zip(Table B)
(Table A) mail_state-------------state(Table B)
(Table A) res_street_address--street address(Table B)
(Table A) res_city---------------City(Table B)
(Table A) res_Zip----------------Zip(Table B)
(Table A) res_state--------------state(Table B)
I want to do the same with T-sql code too:
I came up with below T-SQl but unable to pick(street,city,state,zip columns as I have take combination of street and zip from Table A not individual columns as I wrote in below query) based on above condition(3):
Insert into TABLE B
SELECT
Stats_ID
,UPPER(first_name) first_name
,UPPER(middle_name )middle_name
,UPPER(last_name) last_name
,UPPER(name_suffix) name_suffix
[code]....
I have a very simple query like the following…
SELECT table2.column_code2,
table2.column_description2,
table2.column_code1,
table1.column_description1
FROM database_001.table2 table1 LFET OUTER JOIN database_001.table2 table1 on (table2.column_code1 = table1.column_code1)
From this query, its returning me a result set of something like below:
--------------------------------------------------------------------------------------------------
column_code1 column_description1 column_code2 column_description2
--------------------------------------------------------------------------------------------------
RO1 BOOK RL1 PDF/ECOPY
RO2 PAPER RL2 CONFERENCE
RO5 JOURNAL RL11 OTHER
Now, on the above query I want to insert three extra columns with the name (status, location and contact) where the results in the extra three columns would be based on the conditions I want to define in the query based on the above results…
Something for example (I am not trying to write a condition: my question is how to write it),
if column_code1 = RO1 and column_description2 = PDF/ECOPY on status column it should return a value ‘ONLINE’ & on location column it should return ‘WEB’ and on contact column it should write ‘BOB’.
Also, if column_code1 = RO5 and column_description1 = JOURNAL on status column it should return a value ‘ON PRESS FOR PRINT’ & on location column it should return ‘S.R STREET, LONDON’ and on contact column it should write ‘SMITH’ like below result…so the final output should be the top four columns and the extra three columns…
See the attachment for better formatting...
---------------------------------------------------------------------------------------------
status location contact
---------------------------------------------------------------------------------------------
ONLINE WEB BOB
ON PRESS FOR PRINT S.R STREET, LONDON SMITH
How to achieve the below condition in ssis
if person_id is like '123%' or '124%'
then details='xyz'
else details='yxz'
HI i need to write the Condition for Insert and Update Reccord depending upon the Prod_ID. How to write the Follwing condtion in the Condition Split? pls Anyone give me the Solution?
" if Prod_ID Exist then UPDATE Records
if Prod_ID Not EXIST then INSERT Records "
how to write the above conditon in the Condional Split?
Thanks & Regards,
Jeyakumar.M
chennai
i am creating ssis packages with condition split . condition is SUBSTRING(EnglishProductName,1,1) == "A". pacakge is successfully executived but data is not move to condition split transformer to oldeb destinations. it not showing any error.
View 3 Replies View RelatedI have created a package that use SSIS check pointing for failure-retry mechanism. I knew that when this package fails, on restarting the package it starts from the task where it got failed.
Is it possible for me to override this on a custom condition and start the package at an earlier task where it executed successfully?
Ex:
Check pointing is enabled.
FTP task è Write to Staging Table è Write to Target table.
Assuming I am downloading XML file through FTP and writing to a table.
FTP Download is successful.
Read from XML file and write to Staging table task failed because of downloaded file is not a well formed XML.
Here, FTP task is completed successfully; it failed only in the second task. When I re-run the package; it starts from the second [Read from XML file and write to staging table] task because of check pointing.
Is it possible for me to restart from the FTP task on a custom condition where it executed successfully?
For example..
select * from mytable where MyNum = 7
If this brings back more than 1 row, I want to display a message that says,
Print 'There is more than one row returned'
Else (If only 1 row returned), I don't want to print anything.
Can I do this? Thx!
Dear friends,
I'm having a problem... maybe it's very simple, but with soo many work, right now I can't think well...
I need to filter rows in a dataflow...
I created a condition spli to that... maybe there is a better solution...
And the condition is: Datex != NULL(DT_DATE)
(Some DATE != NULL)
[Eliminar Datex NULL [17090]] Error: The expression "Datex != NULL(DT_DATE)" on "output "Case 1" (17123)" evaluated to NULL, but the "component "Eliminar Datex NULL" (17090)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.
What is wrong??
Regards,
Pedro
I am trying to write an visibility function to have message shown based on two different IIF conditions:
If behavior is to Add a customer ( if message =NAME ALREADY EXISTS, return " NAME ALREADY EXISTS", otherwize return " NAME CREATED")If behavior is to Delete a customer (( if message =NAME DOES NOT EXIST, return "NAME DOES NOT EXIST", otherwize return "NAME SUCCESSFULLY DELETED")
I tried the following which doesn't work:
=IIF((UCase(First(Fields!Message.Value, "DataSetName")) = "NAME ALREADY EXISTS"), "WARNING: NAME ALREADY EXIST", "NAME CREATED"),
IIF((UCase(First(Fields!Message.Value, "DataSetName")) = " NAME DOES NOT EXIST"), "WARNING: NAME DOES NOT EXIST", " NAME DELETED")
It's because of this one or maybe more people that the whole world of the internet becomes so much of crappy messy place... *sighs*
Since there is really no specifics on deleting a topic, this is the best I could do.
How can I search for all the O'Connor's in my SQL Server database?eg: SELECT [Surname] ,[FirstName] FROM [myDB].[dbo].[Names] WHERE ([Surname] = 'O'Connor')
View 5 Replies View RelatedHi I have a table in my database and one of the columns is called 'Month'. Here only one month is inserted according to what the user selects from a dro down list.
WHat i want to be able to do is find the average of the column 'ScoreOut' on a month by month basis. My code works fine if th database contains at least one row with every month of the year represented. I run into problems when one of the months is not represented. here is my code:string month = "February";
SqlConnection myConn = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True");String selectCommand = "Select AVG(CAST(ScoreOut as float)) from tblRound where Name = '" + lblName.Text + "' AND Month = '" + month + "'";
SqlCommand myCmd = new SqlCommand(selectCommand, myConn);SqlDataAdapter da = new SqlDataAdapter(myCmd);DataTable dt = new DataTable();
da.Fill(dt);
myConn.Open();
myCmd.ExecuteNonQuery();string info = String.Format("{0:0.00}", Convert.ToDecimal(myCmd.ExecuteScalar()));
myConn.Close();
lblHole2.Text = info;
Does anyone know how to get around this problem. If a month is not represented, i would like the label lblHole2.text to be equal to 0.
thanks
hi,
i'm wondering if there's a possibility for me to insert values into a table without knowing fix number of fields? in other words, i won't know how many fields i have in a single table until users enter data.
any suggestions or recommendations are welcome. thanks in advance.
Hello,
I have a global report which contains many countries, and I want a specific user (let's say French) to access only to a specific country (France).
So far in SSAS, I have created a role, and assigned my user to this role. When I browse the cube with the new role, I see only the information regarding the country France.
However when the user access to the report through the web interface he can see all the countries. Moreover when I browse the cube with the user (DomainUserName) which belong to the role, I can see all the countries.
So I think I have missed something about role security and the link SSAS-SSRS...
Do you have ideas about how to deal with my problem?
Any help is welcome...
Thanks,
Guillaume
Hi,
My company is starting to use nvarchar columns in our database products. We just found out that, suppose table T1 has a my_nvarchar_col column, and there is a row containing a unicode text say "some Chinese", if you want to select that row, you have to append "N" in front of the unicode constant in the "WHERE" clause. That is:
select * from T1
where my_nvarchar_col = N'some Chinese'
will return that row, while
select * from T1
where my_nvarchar_col = 'some Chinese'
will return NOTHING.
This brings us a huge problem - we have tens of thousands of such queries in our existing PowerBuilder code base. Do we have to go through all of them to add "N" to the "WHERE" clause? Is there a way we can set some attribute of SQL Server so that we do not need to do that?
Project stalled and I am under extreme pressure so please help ASAP!!
First we need to run all packages for initial loading and then we need to run all packages every night.
But inside all packages we need to mark some records as invalid data.
How to deal with all these invalid data after data migration?
If I reject to transfer these data, I will get reference error during next data migration loading.
Hi please could someone give me a explination of how SQLserver deals with multiple databases, We have multiple website that are all run from the same server all from SQLserver. Now we have a couple of databases which are very busy and have a lot of people constantly accessing them and then we have quite a few with much less traffic.
How would these databases effect each other? Would the busy database, mean that the many small databases will all be slowed dramtically?
Everything is okay at the moment but we are considering putting another fairly large database on the server and am worried about it having a sever effect on the smaller databases. Cheers Ed
Try hard to become familiar with T-SQL.Can anybodey tell me the best way to deal with set's provided by astored procedure. Til yesterday I thougt trapping set in temp tableusing INSERT EXEC is a way out of this, but then I struggeled withnested INSERT EXEC's.What are all the system proc's good for if the results cannot beevaluated? The approach of modular programming is to have code doingsimilar things in one place.If I try to make use of sp_helprolemember to get login names for moreroles, pack the logins in one table and return the result set in a SP,the procedure which calls that is unable to evaluate the set.On the other hand I read the advice, not to access system tablesdirectly.Is there a way out?
View 9 Replies View RelatedHi there,
Can anybody tell me how the character of hard return expressed in SQL Server. For example if I wanna convert any hard return character in a varchar field to something else, like a space, how could I write Replace function? Or if I wanna search any hard return character, how could I write where clause?
thanks a lot
Hi, all experts here,
Thanks for your kind attention.
I have a question on missing_value substitution. As we know in the algorithm parametres setting, there is an option for us to select the method to deal with the missing_value: None, Previous, or Mean. My question is: to numeric data column, we can select Mean method to deal with its missing value. But how about discrete type of data column? What is the best practice to deal with this kind of data? I do think it to a great extent matters the quality of the models being trained?
Can please any of you guys here give me any advices and suggestions on this based on what best practices you have got?
I am looking forward to hearing from you shortly and thanks a lot in advance.
With best regards,
Yours sincerely,
I write a query to get some data as the following. but i need when a user check specified condition a query parameter change to specified condition :
create proc proc_ReservationDetails
(
@status nvarchar(50) = null
)
as
begin
select reservationId, reservationStatus, reservationDesc
[Code] .....
when I backup the SQL Server 2005 database ,always get prompt
Error description: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'E'.
+48166A04.0004 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
+48166A04.0004 [Microsoft][ODBC SQL Server Driver][SQL Server]The label 'E' has already been declared. Label names must be unique within a query batch or stored procedure.}
how to deal with it, thanks
I can get following data information from table1Name DescriptionA AviationA01 Aviation GeneralCA01 CapitalCA01-006 NEW CTB SignageA02 CapitalCA05-005 East End RoadwayCA05-006 Modify RoadHow do I write stored procedure to get following results based on the data from table1Code Name Description Level 1 A Aviation 1 1.01 A01 Aviation General 2 1.01.01 CA01 Capital 3 1.01.01.006 CA01-006 NEW CTB Signage 4 1.02 A02 Capital 2 1.02.01 CA05-005 East End Roadway 3 1.02.01.006 CA05-006 Modify Road 4Thank a lot!
View 1 Replies View RelatedHi, all here,
I currently have a question about unknown member in cubes (as we know, there is always an additional member called unknown in an OLAP dimension)for data mining. I mean is there any way for us to deal with unknown member in cubes when mining cubes?
Thanks a lot in advance for any guidance and help.
First a bit of background.
We use the Windows-logon to authenticate en identify the user. With this logon we check within most of our reports what a user is allowed to see.
Depending his level or geographical location he is allowed to run reports on one to all sites. He can selects the sites from a Multiple Select box. This works fine.
Now, we want to use data-driven subscriptions, I understand why we can't use this parameter, but how do you people deal with this kind of configurations.
I would prefer not to 'double' the reports in a normal and a subsciption version .....
I try to convert a Procedure that join 8 tables with INNER AND OUTER JOIN, my understanding is that the Lookup task is the one to use and I should break these joins into smaller block, it takes a long time to load when I do this, since each of these tables had 10-40mill. rows and I have 8 tables to go thru, currently this Stored Procedure took 3-4min to run, by converting this to 8 Lookup tasks, it ran for 20min. has anyone run into this issue before and know a work around for this.
Thanks
Hi,
Does anyone know how to deal with image data type within stored procedure when using SQL2000?
I have a table and there is an image data type column. In this table I need to make a copy of one row within a table through a SP /it means to retrieve the whole row within SP, change another columns data (but, that the image is not modified) / and save the modified row back to the same table.
Problem is, that within SP is not alowed to use local varaibles of image data type. Does anyone know a solution for this? Please.
Thank you.
I have added a page refresh in combination with 'Command Notification' to ensure that my data is fresh,( it is updated on the sql server database approx. every 5 mins).
However, I also allow filtering on the table contents and every time I refresh the filtering is lost. Is it possible to maintain the filtering through the refresh?
<meta http-equiv="refresh" content="30"/>..<asp:SqlDataSource ID="SqlDataSource1" runat="server" ..SelectCommand="SELECT ... EnableCaching="true" CacheDuration="Infinite" CacheExpirationPolicy="Absolute" SqlCacheDependency="CommandNotification" >
I have a table that I am exporting via a SQL server table to a csv file, via the flat file connection manager within SSIS. My issue is that one of the fields in my table contains "," (commas), so when the file is created it creates new columns in the csv file because the field is "," comma delimited on the column. Is there anyway to get round this without having to get rid of commas in my table (which I've tried and works)?
View 12 Replies View Related