How Can I Split Fields And Depending One Column Decide The Foreing Key
I´m wondering how to solve the following scenario with SSIS
I have a CITY table and a STATE table, I have to load a file with the information regarding to the CITY:
the state table is like this:
StateCode(PK) stateLegalCode stateName
============= ============== =========
1 01 Florida
the city table is like this:
citycode(PK) cityLegalCode cityname StateCode(FK)
============ ============= ======== =============
1 1001 Quakertown 1
the file has the following information
cityLegalCode cityName
============= ========
01-1001 Quakertown
...
how can I load the file into CITY table:
1-) with the file's cityLegalCode I have to split the string and if the two initial digits are 01 the registry must have 1 in the StateCode(FK).
how can I do something like that using SSIS???
thanks
View Complete Forum Thread with Replies
Related Forum Messages:
How To Split A Field Into Two Fields
I have the following fields in table A: GL_ID| Date |GL_Name_VC | Amount Period_TI|Year_SI =============================================== 1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005 =============================================== 1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006 =============================================== 1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006 =============================================== 1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006 The above database is running on Microsoft SQL Server 2000 and i would like to query for a report that looks something as below: Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance =============================================== 01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000 01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0 01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0 01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |6,000 The formula for the above calculated fields are as below: Opening Balance = carried forward balance from Year 2005 Debit = All positive amount Credit = All negative amount Net Change = Total Credit - Total Debit in Period 01 Balance = Total of Net Change + Opening Bal Guys, hope someone out there can help me with the sql command for the above report?
View Replies !
How To Split A Field Into Two Fields
I have the following fields in table A: GL_ID| Date |GL_Name_VC | Amount |Period_TI|Year_SI =============================================================== 1000|31/12/2005 | Sales | -8,000.00 | 12 | 2005 =============================================================== 1000|06/01/2006 | Sales | -6,000.00 | 01 | 2006 =============================================================== 1000|20/01/2006 | Sales | 2,000.00 | 01 | 2006 =============================================================== 1000|28/01/2006 | Sales | -4,000.00 | 01 | 2006 The above database is running on Microsoft SQL Server 2000 and i would like to query for a report that looks something as below: Period | Date | GL_Name_VC | Debit | Credit| Net Change | Balance ===================================================================== 01 |01/01/2006|Opening Bal | 0 | 0 | 0 | 8,000 01 |06/01/2006|Sales | 0 | 6,000 | 0 | 0 01 |20/01/2006|Sales | 2,000 | 0 | 0 | 0 01 |28/01/2006|Sales | 0 | 4,000 | 8,000 |16,000 The formula for the above calculated fields are as below: Opening Balance = carried forward balance from Year 2005 Debit = All positive amount Credit = All negative amount Net Change = Total Credit - Total Debit in Period 01 Balance = Total of Net Change + Opening Bal Guys, hope someone out there can help me with the sql command for the above report?
View Replies !
How To Sum A Column Depending Of Another Colum Into Different Vars?
Hi all, I am trying to sum a column into different variables depending on another column. Let me explain my self better with an example DECLARE @Initial decimal(18,2), @incomings decimal(18,2), @outgoings decimal(18,2) SELECT @initial = CASE WHEN type = 1 THEN SUM(amount) END, @incomings = CASE WHEN type = 2 THEN SUM(amount) END, @outgoings = CASE WHEN type = 3 THEN SUM(amount) END, FROM Transactions WHERE date = '05/14/2006' AND STATION = 'apuyinc' GROUP BY type, amount What I am trying to do is to sum all of the incomings transactions into @incomings, all of the outgoing transactions into @outgoings and the initial transaction into @initial where The incoming transactions is type 2, outgoing transactions is type 3 Thanks for the help @puy
View Replies !
Number Of Rows Depending On A Column Value
Hi all, I have a table with artikels and count, sample: Art Count ------------ 12A 3 54G 2 54A 4 I would like to query this table and for each 'count' retrieve one row: query result: Art Count ------------ 12A 3 12A 3 12A 3 54G 2 54G 2 54A 4 54A 4 54A 4 54A 4 Is this possible? Thanks, Perry
View Replies !
Insert Identity Depending On The Value Of A Column
Would like to have Identity Inserted wrt a date column Eg: ProdDate ID Details ============================ 2008.04.01 1 afafafaf 2008.04.01 2 GAFSGHFGF 2008.04.02 1 GAGJAGSDH 2008.04.02 2 QYTYTT 2008.04.03 3 QYTWRRT At present it is not an Auto increment , I check for the Maximum ID and add One to the next Inserted record. Is there any simpler method?
View Replies !
Trigger To Work Depending On Datatype And Column Name
Hi, I have a trigger that fires on update, populating a varchar field [ExampleDate_Str] with the dd/mm/yy format of the inserted datetime field [ExampleDate]. It works, but I don't want to rewrite it for the thousands of datetime fields in hundreds of tables in my db. So I am looking for a way to do something like this in a trigger - for each column in triggered tableset @ColName = (the column name)if datatype(@ColName) = datetimeset @ColName + "_Str" = convert(varchar(8),ExampleDate,3)next Obviously the above doesn't work in SQL or indeed any other language yet invented - is it possible to make it so that it does, and if so, how? The main points are to read the column name into a variable, check the datatype, and modify the column named @ColName + "_Str". Thanks...
View Replies !
UNION With A Max On Split Date/Time Fields
I have 2 tables, each with one ID field, a separate Date and Time fields and a number of other fields. The tables contain duplicates on the ID field. I want to do a UNION keeping only the record with the latest Date and Time. This would work: SELECT MyTab.myKeyField, Max(MyTab.myDate) AS myDate FROM (SELECT myKeyField, myDate from Table1 union SELECT myKeyField, myDate from Table2) AS MyTab GROUP BY MyTab.myKeyField But is only taking care of Date, not Time (some records have the same date but different times) The other problem is, when I add more fields, I have to include them in the GROUP BY clause, and this way I end up with duplicates (because some other fields have different values) Is there a way to do this?
View Replies !
Split Fields And Display Query Result
Hi, I'm having a problem in spliting the fields I need to ru the following query to join two tables and getting the output as shown. Query: select cusl.user_name, pmts.bill_ref_info, pmts.payee_acid, pmts.cust_acid, pmts.txn_amt,pmts.pmt_id from cusl, pmts where cusl.ubp_user_id = pmts.ubp_user_id and pmts.ubp_user_id= 'testinglive' Output: user_name bills_ref_info payee_acid cust_acid txn_amt SAMEER ALLA0210181#123456#Amita 378902010021095 383702070051411 1.000 16318 SAMEER BARB0GNFCOM#6788990#Vikram Kalsan 378902010021095 383702070051411 1.000 16327 SAMEER BKID0000200#378902010099678#Vikram 378902010021095 383702070051411 1.000 14031 SAMEER undefined#123456789123456#Vikram 378902010021095 383702070051411 1.000 13918 Now I need to display the second field which is a #-separated field as individual fields alongwith tghe other fields that are shown on execution of the query. Can this be done? Please guide me on this...
View Replies !
Set Column Value Depending As % Of Another Column Value (was: Help!!)
Hi, I'm not sure if this can even be done but I will ask anyway. I have a table that has a sales price field in it. I have another field in that table called commission. The commission is 15% of the sale price. Is there any SQL that will calculate and automatically put that value in it, or only allow you to put the correct value in? Hope this makes sense and hope someone can help. Thanks for your time. Liam (Using MS SQL by the way)
View Replies !
Split A Column
Hi everybody Does any body know how to split a field in a table into two fields eg usermaster(table) userid(field) usermaster has 40 users with user id 1 to 40 i want to get data as userid userid 1 21 2 22 3 23 . . . . . . 20 40 Thanks you very much
View Replies !
Split A Column Into 2 Columns
Hi everyoneI guess this should be a simple question for the gurusI have a Data in a column which is to be places in 2 columns instead ofone. How do i go about doing it in MS SQL server? Could someone pleasehelp me. I could do it in access with an update query but things are alittle different in SQL server so I am a little lost.Eg.NameJohn?Doeto be split intoName LastNameJohn DoeThanks in advance.Prit
View Replies !
How To Split Three-value Column Into The Same Target?
Hi everyone, We've got a source file which owns three different values: 'A','B','M'. Where 'A' stands for "New Rows" and 'B' for "Delete rows" and 'M' for 'Update rows' Using Conditional Split task we can redirect each subset into a OLEDB Destination but we are wondering how can we do the same using only one OLEDB? We've got only one table. Thanks for your input and time,
View Replies !
Split Values From Within Column
I have been trying to separate firstname,last name,middle from name column Existing Format Column Name =FIRST,LAST M Desired First Last M I would llike to divide one column into three columns...How can i achieve it.. Please let me know
View Replies !
Split Column Into Severl Ones
Hello, I have a table which contains a column like that: Comment ----------------------------------------------------------------------- User: Toto Password: Toto-Toto I'd like to have in the same table: Comment User Password -------------------------------------------------------------------------------------------------------- User: Toto Password: Toto-Toto Toto Toto-Toto Do you have an idea of how to do it in SSIS? Thanks a lot for your help.
View Replies !
How To Split A Database Column ?
Hi, I have a column, for example Prod_ID count is 100 (contains Raw Matl & Finished Matl). I want to split this 2 columns as Raw Matl Finished Matl 60 40 Can anyone please help me how to do this in SQL Server. Thanks in Advance Rajesh
View Replies !
Split Data In Column
hai all, This is my first question to this forum. here is my situtation: I am into report testing I need to test a report for which i have write a query,iam using qery analyser for runing query Database : sql server tabel name :job_allocations column naME :technicain code Based on techincain code in joballocation tablei need to get technician cost from other table for the particular technician. Based on the technician code user chooses column will be updated if single data will be TC01 if more than one then data will be TC01:TC02:TC03 user can choose any number of techincian for a job MY problem is :How to split tha when there is multiple technician and calculate cost for the job Ineed it in single excecution query Table structure job_allocation table jobcardn_fk Technician_code jc01 TC01 jc02 Tco1:Tco2:Tc03...... I need it in jobcardno_fk TEchnician_code jco1 Tc01 jco2 Tc01 jco2 TC02 jc02 Tc03 TKs ands Regards Diwakar.R
View Replies !
Split One Column Into Multiple Columns
Hi all, I have a requirement like this , I have Address Column.It is containing data like Mr. K KK Tank Guntur Jal Bhavan, Univercity Road, Rajkot 9843563469 I have to split this into 3 more columns like(Address1,name,phoneno)-- Means i have 4 columns including Address Column.(Address,Address1,name,phoneno) Example: Address:Rajkot Address1:Univercity Road Name:Mr. K KK Tank Guntur Jal Bhavan PhoneNO:9843563469 How can i acheive this one with out data lose in Address Column. Thanks in advance.
View Replies !
Query Split Column In 2 Columns In SQL
I like to push 1 column into 2 different columns just to show it on the screen. So no import in another table ore something like that. I have a table like this: Select Name from Cars; Result: Col1 BMWMercedesFordAudi But i like to make a query so it is displayed like this: Col1 Col2 BMW FordMercedes Audi So i can bound a table directly to that column!Is this possible with SQL, and how can i build it.Thanks.
View Replies !
Split Data Into Two Column Table
Hello all, Little layout question. Assume my dataset returns the following data: A B C D E How can I present this data in a table (or list, or matrix) splitted into two columns: A B C D E Any idea will be very appreciated! Thanks a lot! TG
View Replies !
Query To Split A Database Column ?
How can i write a query to split a database column and shows 2 new columns. In my database column I have 2 mixing items and need to split out to 2 columns. Normally I have to write a query and change parameter and run another query. For example a database column with average number and range number. Thanks Daniel
View Replies !
How Do I Split A Column Result By A Nonalphanumeric Character?
I have a column that returns client numbers. The client numbers are 4-6 characters in length. A period (.) is added to the end of the client number, and then one last digit (1-4) is affixed at the end to denote a categorization. In SQL, I need to figure out how to divide these results into two columns, one for the client number and one for the categorization number. EG: client #4334.1 would become 4334 for client # and 1 for categorization number or Client #424561.3 would become 424561 for Client # and 3 for categorization number. I have to strip out the period in the process and leave myself with just the numeric characters divided into two columns. Ive been researching my brains out on string queries and substring queries and I can't figure out how to parse out the period and/or to have SQL understand that I need everything BEFORE the period for one column and everything AFTER the period for the second. Is it possible to do this? I really need help on this one. Thank you :)
View Replies !
Comparing A Column List Split To A Table.
Let me see if I can explain my situation clearly.I have a table with the columns:answer_id, question_id, member_id, answer- answer_id is the primary key for the table.- question_id relates to another table with questions for a user. Thetable holds the question and the possible choices in a varchar fieldseparated by a delimiter.- member_id is self-explanatory- answer is a varchar field of all the choices the user selected,separated by a delimiter.Here is my problem.I am trying to search all members that have answered, say, question_id= 2 where they selected 'brown' as one of their choices.i can do this if they selected ONLY that item, but not multiple items.The problem is this portionanswer in(select valu from dbo.iter_intlist.....I need this to be something like....function_to_return_all_separated_answers(answer) in(select valu from dbo.iter_intlistThe current way, it is only returning members that have an answer'Brown', not 'Brown, Blue' in their answer field. Make any sense? So,what I need to do is separate the list of answers and say :select member_id from profile_answers whereANY ANSWER in function_to_split(answer) MATCHES ANY OF THESE (selectvalu from dbo.iter_intlist...It seems I might have to join or something, I am just a little lostright now.Here is my proc.ALTER procedure search_detailed_get_ids@question_id as integer,@answers as varchar(8000),@member_ids ntextasdeclare @v as varchar(8000)--get the delimited string of all possible answersset @v = (select bind_data from profiles_questions where question_id =@question_id)--prepare it for the function only accepting 1 charset @v = replace(@v, '||', '|')--gimme all members that matchselect member_id from profiles_answers where question_id = @question_idand answer in(select valu from dbo.iter_intlist_to_table(@v, '|') where listpos in(select valu from dbo.iter_intlist_to_table(@answers, ',')))and member_id in (select valu from dbo.iter_intlist_to_table(@member_ids, ','))returngo
View Replies !
Split A Single Column Data In To 2 Columns
Hi This is probably a very basic question for most people in this group. How do i split the data in a column in to 2 columns? This can be done in access with an update query but in MS SQL server I am not sure. Here is an example of what i want to acheive FName John?Doe FName LName John Doe thanks for the help prit
View Replies !
Split Column Data Into Multiple Lines
Hi, I have a scenario, where I have a string column from database with value as "FTW*Christopher,Lawson|FTW*Bradley,James". In my report, I need to split this column at each " | " symbol and place each substring one below the other in one row of a report as shown below . "FTW*Christopher,Lawson FTW*Bradley,James" Please let me know how can I acheive this?
View Replies !
Problem With A Foreing Key
I'm a beginner in using SQLServer and I 'm trying to bring a db Schemawritten for DB2 into SQLServer.My problem is this: using a tool to translate the script for creating theDB, I obtain the following code:ALTER TABLE PROJECT.RSURETTA ADD FOREIGN KEY(VOCE )REFERENCES PROJECT.RSANVOCE(VOCE ) ON DELETE SET NULL ON UPDATE NO ACTIONBut, when I try to run the script the system says:Incorrect syntax near the keyword 'SET'.Can I assign a null value to an other table with a reference?Thank youFede
View Replies !
How To Split A Delimited Column Into Mulitple Rows In The Dataflow?
I'm sure there is probably a very easy solution that I am just not seeing or can't Google... I have a DataFlow that includes a column of Delimited values (i.e. Value1,Value2,etc..). As this DataFlow is populating a parent table, I need split the values into their own dataflow and populate a child table. I've tried a script transformation and couldn't figure out how to accept 1 delimited input row and output multiple rows after a split. Any ideas? TIA, Matthew
View Replies !
Replication Foreing Key Problem
Hi, I want to replicate in the same Server 2 tables, first I test with the same data datbases in other Server but 1 table and all is ok , when I try to do the same in the production server occurs an error, Distibution Agent Error Details show me this: Could not drop object 'SER_CURRENCY' because it is referenced by a FOREIGN KEY constraint. Like I said first I run a test, in this test I check IDENTITY: YES (Not for replication), ENFORCE RELATIONSHIPS FOR REPLICATION is unchecked. My scenario is: - Windows XP, SQL Server 2000 Developer Edition sp4. Please, I want to know other reason for this error. Thanks - Neyver.
View Replies !
Obtaining Foreing Keys Dependences
I want to know how to obtain the relationships of foreing keys when they have diferent names in diferent tables using SQL querys from VB.net in SQL Server 2000. Example: Table Person ------------ IDPerson Name Address Table Customer -------------- IDCustomer . . . Table Employee --------------- IDEmployee . . . The dependencies are: IDPerson--->IDCustomer IDPerson--->IDEmployee
View Replies !
Need To Find An Easy Way To Split A Column In Table Without Using Cursor Or Temp Tables
Hi , I have two tables within a SQL database. The 1st table has an identified column and column which lists one of more email identifers for a second table, e.g. ID Email -- ---------- 1 AS1 AS11 2 AS2 AS3 AS4 AS5 3 AS6 AS7 The second table has a column which has an email identifier and another column which lists one email address for that particular identifier, e.g. ID EmailAddress --- ------------------ AS1 abcstu@emc.com AS2 abcstu2@emc.com AS3 abcstu3@emc.com AS4 abcstu4@em.com AS5 abcstu5@emc.com AS6 abcstu6@emc.com AS7 abcstu7@emc.com AS11 abcstu8@emc.com I need to create a stored procedure or function that: 1. Selects an Email from the first table, based on a valid ID, 2. Splits the Email field of the first table (using the space separator) so that there is an array of Emails and then, 3. Selects the relevant EmailAddress value from the second table, based on a valid Email stored in the array Is there any way that this can be done directly within SQL Server using a stored procedure/function without having to use cursors? Many Thanks, probetatester@yahoo.com
View Replies !
How To Decide If A Set Is Included In An Other Set?
For example: we have 2 set {1,4,8}, {1,2,3,4,5,8,9,0} Is there any other fast way to decide {1,4,8} is in cluded in {1,2,3,4,5,8,9,0} ? beside loop, or insert them into two table then check not exists?
View Replies !
Hi Need Help To Decide The Indexers
Hello, I have a situation here... where we are inserting data of candidate...which includes fields... 1. First Name, Middle Name, Last Name ... (varchar(50)) 2. Passport ID--- (no duplicates allowed but may be null ) 3. Telephone number... ( may be null but no duplicates allowed ) 4. Date of Birth --- ( mandatory ) 5. Email id .......(no duplicates allowed but may be null ) The problem is that we already have about 1 lakh enteries in the database.. and it is expected to grow with time... We need to define indexers such that it doesnot take time for searching or while inserting a new datafield into the database. I hope that i am clear. since, i suspect that that as it grows it will become more and more slow...! This is database to store "resumes of different candidates". So have decided...that 1. name 2. Passport no. 3. Email id: 4. Contact number be the fields to prevent duplicacy of candidate data... This involves checking for candidates existence every time anybody tries to make a new entry into database. Please help to decide over the indexers... Any good suggestions are welcomed... Thanks for the reply... With regards, Girish R. Pawar
View Replies !
ODBC Driver Bug ??? Or Is It RDO.... You Decide
Hello all... I am having a really strange problem with RDO 2.0 and the SQL Server ODBC driver. I have a table in an application which I am developing whose sole purpose is to hold notes which are related to records in other tables in the system (this is to stop those nasty 2K BLOB pages lurking in all the tables which require notes) Here is the build script for the table : CREATE TABLE dbo.Notes ( NOTE_ID int IDENTITY (1, 1) NOT NULL , NOTE_DATA text NULL ) I have a stored procedure which the client application uses to write text into the NOTE_DATA column. Here is the stored procedure : Create Procedure AddNote (@NOTE_ID int = 0, @NOTE text) As ---------------------------------------------------------- -- Check the note we are about to update exists ---------------------------------------------------------- SELECT @NOTE_ID = NOTE_ID FROM NOTES WHERE NOTE_ID = @NOTE_ID IF @NOTE_ID = 0 OR @@ROWCOUNT = 0 BEGIN ---------------------------------------------------------- -- New note record is required ---------------------------------------------------------- INSERT INTO NOTES (NOTE_DATA) VALUES (@NOTE) RETURN @@IDENTITY END ELSE BEGIN ---------------------------------------------------------- -- Change the existing notes record ---------------------------------------------------------- UPDATE Notes SET NOTE_DATA = @NOTE WHERE NOTE_ID = @NOTE_ID RETURN @NOTE_ID END Basically you pass the stored procedure the identity of an existing notes record and a lump of text. The SP updates the existing record (completely replaces the text) or returns the identity of a newly created record (i.e. the record didn't exist or you forced a new record to be created). You can call the stored procedure via ISQL/w or Enterprise manager as follows : EXEC AddNote, 1, 'This is my test note' In this case the NOTE_DATA column of the Notes record with NOTE_ID 1 will be updated to contain 'This is my test Note'. Okay so far... nice little interface for holding notes. If I call the stored procedure via RDO using ODBC call syntax {? = CALL AddNote(?, ?) } the text 'This is my test note' gets appended to the end of what was already in the NOTE_DATA column. NOTE this only happens using RDO and the SQL Server ODBC driver. If I call the procedure again via enterprise manager it works fine. This is a very strange problem you agree ??? I've looked on the MSDN, I've read the Hitchhikers guide (our good friend Mr Vaughn quitely skips access to text and binary objects apart from pictures). Theres no need for us to use GetChunk AppendChunk either. We want to keep this logic on the sever. This is definately not a problem with SQL Server. It works fine on 6.5 and 7.0. I think its an ODBC driver problem. Has anyone else come across a similar problem ??? Thanx in advance Ian
View Replies !
Need To Decide Between SSIS / Replication
Hi- I have a scenario where I need to decide between SSIS and Replication. Customer has 50 GB Database in Oracle. And they want around 150 tables from Oracle database to be copied into SQL Server 2005 database. And this has to be done every night at 10.00 pm to synchronize the data. I want to know whether the following options are possible to do and which one will give me the best performance. Option : 1 Use SSIS to get the data (150 tables) from Oracle and import into SQL Server Option : 2 Use Replication to get the data from Oracle and import into SQL Server. Is that possible to Replicate the 150 tables alone (not the entire Database) from Oracle to SQL? Option : 3 Use Replication to copy the entire 50 GB Database from Oracle and store into SQL Server. On these three options which one will give me the best performance? Thanks in advance.
View Replies !
How Does Xp_cmdshell Decide Success Vs. Failure?
Hi all, I have a stored proc that uses xp_cmdshell to boot off a batch file on the NT side of the box (box OS is Windows 2000 Advanced Server). Here is the pertinent code:/*----- Kick off the NT bat job to suck over the data through the web service pipe*/ SELECT @NTCommand = 'D:TradeAnalysisWondaDataStoreJobsPullFromWONDA _InstitutionalRankings.bat ' + CONVERT(varchar(10), @ReqDate, 101) EXECUTE @e_error = master.dbo.xp_cmdshell @NTCommand SELECT @m_error = CASE WHEN ISNULL(@e_error, 0) <> 0 THEN (@e_error + 50000) ELSE @@Error END IF @m_error <> 0 GOTO ErrorHandlerThe trouble is that the batch file is failing (soft error, caught internally to the batch file, which then kills itself, screaming loudly all the way). The batch file is using the following "voice" in which to scream in pain as it dies (a.k.a., using this code to terminate itself, which kills the cmd shell and returns 13 as an error code)REM WonDBService.exe says we failed Date /T Time /T EXIT /B 13 Meanwhile, back at the ranch (errr...back in stored procedure), what is being returned is a ZERO (in the first code block, @e_error is being set to ZERO when the xp_cmdshell returns from the bat file. So, now that I have ruled out the obvious *LOL* how can I get my xp_cmdshell to realize it has failed miserably at the one, tiny, simple, not-too-much-to-ask, job that it is designed to do?
View Replies !
Using A Case To Decide On The Join Statement?
I'm trying to join a table and based on the value of a given column I would join using the column in question, however if the column is NULL then I want to make the join without the column in the join. so I think I want to do something like this: Case E.a when NULL then LEFT JOIN EPD ON EPD.b = D.b AND EPD.SD = (SELECT MAX(E1.SD) FROM E1 WHERE E1.b = EPD.b AND E1.a = EPD.a AND E1.SD <= T.WD) Else LEFT JOIN EPD ON EPD.a = D.a and EPD.b = E.b AND EPD.SD = (SELECT MAX(E1.SD) FROM E1 WHERE E1.a = EPD.a AND E1.b = EPD.b AND E1.SD <= T.WD) end however T-Sql does not seem to like my Case statement mixed into my From/join clauses. Thanks, Mark E. Johnson
View Replies !
Dynamically Decide The Conditions In The 'WHERE' Clause
I have a report and in it there is a dataset that of course contains a query. I want the query conditions to be changed automatically (the 'WHERE' clause) according to the environment it runs on, so if I put the same report on different customer computers, it will act differently according to the relevant 'WHERE' clause conditions. Is it possible to use a parameter or "solution configurations" (or something else) in order to decide the conditions in the 'WHERE' clause? Help will be really appreciated. Thanks in advance.
View Replies !
How To Decide That How Many Max. Users Can Work With Dbase Efficiently
Hi everybody, Rightnow we have developed a aplication & after few days it's going to become live but before that we want to know that how many max. users can work efficiently with this dbase. we are having 4GB dbase, SQL SERVER 7.0 with service pack 2 & 1151 MB RAM , WIN NT4.0 service pack6 & two processors. Is there any performance counter or something else exist who can calculate & tell me that for your server you can max. this much conccurent users.Basically we want to have 600-700 conccurent users. is it possible with this much RAM & processor.we are having front-end as Delhpi & users does select most & less update ,insert & delete eagarly waiting for reply thanks a lot Raj
View Replies !
Fields Within An XML Column
Hi, We have a "Change Table" in SQL Server 2005, that contains the following information: ChangeGUID (ID) ChangeAction (Update or Insert flag) ObjectType (Business Object Affected) ChangeDateTime ObjectContent (XML Column with the new version of the changed / inserted record) This table is populated by triggers and it records data changes to tables we are interested in. The first 4 fields are meta data, while the 5th is an XML column that contains the changed record. What I need to do is read this table using SSIS, then access the fields within the XML column so that they can be transformed / converted etc for insert into Oracle RDB, via ODBC. So, How do I go about accessing the data within an XML column? The only XML handling component I can find deals with XML files, and not columns. Hope I've explained the issue clearly. Thanks, Jason.
View Replies !
Is It Possible To Decide Whether To Enable/disable Interactive Sort At Runtime?
I have a parameter for each field that lets me know if a field can be sorted or not. What I want to be able to do is turn on or off interactive sorting for that column based on that parameters value. In the dialog you have a check box that says enable interactive sorting. When that is clicked it appears that you get the two arrows no matter what you put in the expression. I have tried =IIF(allowSorting,Fields!myColumn.Value,"") and =IIF(allowSorting,Fields!myColumn.Value,Nothing) but both result in the arrows still being there, just the sort doesn't work. Is it possible to put an expression on the sort arrows appearing at all?
View Replies !
How Do I Get Group By Fields As Column Name?
Dear experts, Hi, please do give me your expert advise and opinon on this matter: I have a table name PerformaceRecords with a few columns, one of which is performance banding. i.e. PerformanceBanding ------------ Outstanding Good Average Good Poor When I use a group by clause, i.e. Select PerformanceBanding, Count(PerformanceBanding) as ResultCount from PerformanceRecords group by PerformanceBanding I got the result as PerformanceBanding ResultCount --------------------------------- Good 2 Poor 1 Average 1 Outstanding 1 What I want to get is the PerformanceBanding as columns and the Result as rows i.e. Good Poor Average Outstanding ---------------------------------- 2 1 1 1 how do I go about modifying my SQL select statement to achieve this result? Thank you in advance for assisting me.
View Replies !
Combine Column Fields Together Into One Field
Hello to All, I needs help to combine these together but how would I eliminate necessarily zero in front of "PropertyHouseNumber". Table: DirectHome Column fields....... PropertyHouseNumber, PropertyStreetDirection, PropertyStreetName, PropertyMODE 0000001091 , W , 000026TH , RD Thank you RV
View Replies !
Altering Column Fields With A Stored Procedure
I have some columns of data in SQL server that are of NVARCHAR(420)format but they are dates. The dates are in DD/MM/YY format. I want tobe able to convert them to our accounting system format which isYYYYMMDD. I know the format is strange but it will make things easierin the long run if all of the dates are the same when working betweenthe 2 different databases. Basically, I need to take a look at theyear portion (with a SUBSTRING function maybe) to see if it is greaterthan 50 (there will not be any dates that are less than 1950) and ifit is concatenate 19 with it (ex. 65 = 1965). Then, concatenate themonth and day from the rest to form the date we need in NUMERIC(8).So, a date of January 17, 2003 (currently in the format of 17/01/03)would become 20030117. In VB, the function I would write is somethinglike the following:/*Dim sCurrentDate as StringDim sMon as stringDim sDay as StringDim sYear as StringDim sNewDate as StringsCurrentDate = "17/01/03"sMon = Mid(sCurrentDate, 4, 2)sDay = Mid(sCurrentDate, 1, 2)sYear = Mid(sCurrentDate, 7, 2)If sYear < 50 ThensYear = "20" & sYearElseIf sYear > 50 ThensYear = "19" & sYearEnd ifsNewDate = sYear & sMon & sDay*/I was thinking of doing this in a Stored Procedure but am really rustywith SQL (it's been since college).The datatype would end up being NUMERIC(8). How I would write it if Inew how to write it would be: grab the column name prior to theprocedure, create a temp column, format the values, place them intothe temp column, delete the old column, and then rename the tempcolumn to the name of the column that I grabbed in the beginning ofthe procedure. Most likely this is the only way to do it but I have noidea how to go about it.
View Replies !
Adding New Column Fields Into A Big Table Issue
I have an existing table which has about 70 columns with 3 million rows in it. I was asked to add additional 50 new columns into the table. I have tried to add them in through the Enterprise manager design table but experiencing some problems. The adding process seemed never going to be end. Is there any good efficient way to do it??? I appreciate the help! J8
View Replies !
Column Count Percentage Of Not Null Fields
Hello folks, I am stuck at a problem, not sure on how to go about writing a query that will return as a percentage the number of fields in a row that are null. For instance, a row from my table: Row1 : field1 field2 field3 If field3 is empty or null, my query should return 67%. So far I have gotten the number of fields: select count(1) from information_schema.columns where table_name='myTable' I could loop through the fields but I am sure there is a simpler way of doing it, I have seen something simpler in the past with some builtin SQL functions. I am using MS SQL 2005. Thanks for your help Mike
View Replies !
|
TRACKER
