Permutation And Combination Between Source And Lookup Table?

Jun 21, 2015

how this can be achieved by writing a procedure in SQL Server DB.Remember that if there is a non-null value in the benchmark that is not equal to the equivalent criteria in the Hosting input then there is no match. Therefore those with region = IN would not match any of the given benchmarks. It is best fit as long as the non-matching values are to null value benchmark criteria.

Hosting_Site (Source File)
YEAR MONTH HOSTING CLASS REGION HOSTING COST
2015 1 A UK 75
2015 1 A IN 80
2015 1 B IN 60
2015 1 C US 100

BENCHMARK (Lookup Table)
Row Year Month Hosting class Region BENCHMARK COST
1 2015 1 A US 100
2 2015 1 B US 200
3 2015 1 A UK 50
4 2015 1 A null 70

STANDARD CLASS COST (If the criteria between source file and the lookup file doesn't match then have to load this cost)

Row Year Hosting class BENCHMARK COST
1 2015 A 22
2 2015 B 33

Then result should be:

FACT LOAD:

YEAR MONTH HOSTING CLASS REGION HOSTING COST BENCHMARK COST
2015 1 A UK 75 50
2015 1 A IN 80 70
2015 1 B IN 60 33 (Since for B class there is no region = IN or = null the Standard Class Cost must be used)
2015 1 C US 100 ??? (Reject record since there is no default value in Standard Class Cost or in Benchmark)

View 13 Replies


ADVERTISEMENT

Query For Permutation And Combination

May 12, 2014

I have one table and I am trying to accomplish two tasks, generate all possible combinations and the probability of these combinations occuring. My sample data is below:

Time of Incident
7AM
8AM
9AM
10AM
11AM
12PM
1PM
2PM
3PM
4PM
5PM
6PM
7PM

Location
Off-Site
OnSite PC
Off-Site Virtual

IssueStatic
CR
CR Unable to Hear caller
Caller Unable to Hear CR
Garbled Speech
Echo

Call disposition
Able to Handle Call
Caller Initiated Disconnect
Transfered to 5508
Transfered to CCV

Provider
AT&T U-verse
Cox Cable
Mercury
Time Warner
VA Network

View 9 Replies View Related

SQL Server 2012 :: Query To Produce Permutation Combination

Jun 4, 2015

I need a query to produce permutation combination.

declare @t2 as table (tab varchar(100))
insert into @t2 values ('V')
insert into @t2 values ('VL')
insert into @t2 values ('1099')
insert into @t2 values ('VOI')

declare @t1 as table (tab varchar(100))
insert into @t1 values ('I')
insert into @t1 values ('U') from the above I need following output (attached output),

View 3 Replies View Related

Transpose Source Data From A System Via Metadata Lookup Table Into Destination Table

Apr 1, 2014

I am stuck on finding a solution to transpose source data from a system via a metadata look-up table into a destination table. I need a method to transpose/pivot the source data into columns (which are by various data-types). The datatypes for each column are listed in a metadata table.

Source Data Table:

Table Name: Source

SrcID AGE City Date
01 32 London 01-01-2013
02 35 Lagos 02-01-2013
03 36 NY 03-01-2013

Metadata Table:

Table Name:Metadata

MetaID Column_Name Column_type
11 AGE col_integer
22 City col_character
33 Date col_date

Destination table:

The source data to be loaded into the destination table(as shown below):

Table Name: Destination

SrcID MetaID col_int col_char col_date
01 11 32 - -
01 22 - London -
01 33 - - 01-01-2013
02 11 35 - -
02 22 - Lagos -
02 33 - - 02-01-2013
03 11 36 - -
03 22 - NY -
03 33 - - 03-01-2013

View 7 Replies View Related

Performance Expectations For Fuzzy Lookup Against 25mill Row Lookup Table

Oct 31, 2007

We did some "at scale" fuzzy lookup tests today and were rather disappointed with the performance. I'm wanting to know your experience so I can set my performance expectations appropriately.

We were doing a fuzzy lookup against a lookup table with 25 million rows. Each row has 11 columns used in the fuzzy lookup, each between 10-100 chars. We set CopyReferenceTable=0 and MatchIndexOptions=GenerateAndPersistNewIndex and WarmCaches=true. It took about 60 minutes to build that index table, during which, dtexec got up to 4.5GB memory usage. (Is there a way to tell what % of the index table got cached in memory? Memory kept rising as each "Finished building X% of fuzzy index" progress event scrolled by all the way up to 100% progress when it peaked at 4.5GB.) The MaxMemoryUsage setting we left blank so it would use as much as possible on this 64-bit box with 16GB of memory (but only about 4GB was available for SSIS).

After it got done building the index table, it started flowing data through the pipeline. We saw the first buffer of ~9,000 rows get passed from the source to the fuzzy lookup transform. Six hours later it had not finished doing the fuzzy lookup on that first buffer!!! Running profiler showed us it was firing off lots of singelton SQL queries doing lookups as expected. So it was making progress, just very, very slowly.

We had set MinSimilarity=0.45 and Exhaustive=False. Those seemed to be reasonable settings for smaller datasets.

Does that performance seem inline with expectations? Any thoughts to improve performance?

View 4 Replies View Related

Help Me Getting A Unique Combination From Table

Jun 18, 2004

Hi, I have following table

BetId GameId
==== ======
500 108
500 109
501 108
501 109
501 110
502 108
502 109

My query would have the form: select BetId where GameId in(108,109)
from Bets then it has to get me BetId : 500 and 502.
Not 501,since this is different combination(108,109,110) ;)

View 3 Replies View Related

Combination Of Columns Not In Another Table

May 13, 2014

I want a list of rows in one table where a combination of two columns aren't in another table. One column is character and the second column is date/time. This happens to be SQL 2005. My first attempt using only one column worked OK:

SELECT * FROM TABLEA
WHERE CUSTID NOT IN (SELECT CUSTNO FROM TABLEB)

But when I added the combination of customer number and sales date, it made the query "run away" - I had to cancel it and it didn't return anything:

SELECT * FROM TABLEA
WHERE CUSTID + CONVERT(VARCHAR, SLS_DATE, 101) NOT IN
(SELECT CUSTNO + CONVERT(VARCHAR, SALES_DATE, 101) FROM TABLEB)

I wonder what might be wrong, but also whether this is the correct approach to begin with.

View 3 Replies View Related

SQL Server 2008 :: Compare If Row-Combination Exists In Other Table

Mar 10, 2015

I've the following two tables:

create table #temp1 (name varchar(5), id int)
insert into #temp1 (name, id)
(
select 'a', 5
union
select 'a', 8

[code]....

As a result I would need every name from #temp2, where both searchred id's (5 & 8) from #temp1 are included.In this example i would like to get 'e' as a result, because in #temp2 'e' has the id's 5, 8 and 25.I've tried using cross apply, but cross apply returns every Name that have one of the ids... in this case it would also return 'c'...

Selectdistinct b.name
from(
Selectdistinct name
, id
from#temp1
wherename = 'a'
) as a
cross join(
Selectdistinct name
, id
from#temp2
) as b
wherea.id = b.id

View 3 Replies View Related

How To Fetch Record From Temp Table - Unique Combination

Sep 23, 2014

Table :StudentTeacherRelation

Id StdId TeacherName Day subject
1 1 Archana Monday English
2 1 Archana Tue Marathi
3 1 Shama Wed Hindi
4 1 shama Thus Hindi
5 1 Kavita Fri Hindi
6 2 Archana Mon english
7 2 Dipti Tues Hindi

Second table : Student

Id Sname Cid
1 Shalini 1
2 Monika 1
3 Rohan 3

I want to fetch uniq combination of stuid and subject.Result should show all subject of student whether may be teachername and day. If I choose shalini whose stuid is 1,all subject for shalini(hindi,english,marathi) should come. Record from either of three should come

Id StdId TeacherName Day subject
3 1 Shama Wed Hindi
4 1 shama Thus Hindi
5 1 Kavita Fri Hindi

I want fetch studentname along with teachername,day and subject whose cid = 1 here is my query

select Student.Sname,TeacherName, Day,subject
from StudentTeacherRelation
inner join Student
Student.id = StudentTeacherRelation.StuId
where cid = 1

I want place result of it in temp,Want fetch max(id) from temp table by doing group by on Sname and Subject.find all id from temp table where that id present in max id.

show
Id StdId TeacherName Day subject
where (1,2,3,4,5,6,7-- all id from temp) in (1,2,5,6,7 -- max id from temp by doing group by on Sname and subject)

So it will show record Id StdId TeacherName Day subject where id is 1,2,5,6,7.Only five record should come.How to do that?

View 1 Replies View Related

Lookup For Compare The Source And Destination Data

Nov 19, 2007



Hi

i want to compare the source and destination data my source is Oledb source and destination is sql server destination I want to Compare the Data in destination with source and also want to add validation like iF Any row is there is destination but not in source delete that row if any row in source but not in destination Insert that row nd compare source with destination respect to Primary key if There is change in any column of that row Update that if not change than move to next record. Please tell me what is the sutable object and what to do
Please Help me Out
Thanks
Sandeep

View 10 Replies View Related

Lookup For Compare The Source And Destination Data

Apr 24, 2008

Hi,

I am new to SSIS can u help me out in this i have a source(Flat File) and target SQL Server 2005. Source has got 2 columns- Col1 and Col2 even target has got 2 col's Col1 being the PK. I have created a package that checks if target and if the record exist it updates and if it does not it inserts. My package looks like

Source - lookup on target- Conditional split- Derived Column and then 2 OLE DB Destinations 1 for inserts and 1 for updates.

I have created a relationship in lookup with col1 from source and col1 from target and col2 as lookup col and connected red output to 1 OLE DB for inserts and redirect rows to it. Green out put i have taken to conditional split and gave condition like Col2 from source is not equal to lookup col2. I run the package with this it is inserting new records but not updating it.

I tried to add derived column after conditional split but lacks in writing expression that says update col2 records if they changed at source. Can u help me out in this scenario. I would appreciate if you could get back to me ASAP.

Thanks

View 5 Replies View Related

Integration Services :: How To Use Lookup Transformation Using Excel As A Source

Aug 27, 2015

i want to use lookup transformation using Excel as a source.i am having two excel files .

file1 one of the column contains 'Andhrapradesh'
file2 one of the column contains 'ap'

here want to match these using lookup.

View 5 Replies View Related

Transact SQL :: Comparing 2 Records / Multiple Instances In The Same Table For A Specific Combination?

Jun 10, 2015

I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.

Givens: All SQL Server 2008 or 2012 tools at your disposal.

Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.

-- A player of our game

-- Table greater than 10 million rows

CREATE
TABLE [dbo].[User]
(
[UserId]              
[bigint] NOT
NULL  
,[country]             
[int] NULL   
-- User’s home country
,[name]                
[nvarchar](15)
NULL  -- User’s displayable name (‘John’, ‘Bill’)
,[subscriptionTier]    
[int] NULL
)
-- 0 == free, 1 == paid, for instance

Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.

Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation). 

For instance, if the sample data were:

-- example of [Events.UserLogon] data  -- not the query output we want

EventId     UserId               TotalMoneyEarned LogonDate
----------- -------------------- ---------------- -----------------------
1           1               1000             2010-10-16 00:19:56.460
2           1               1500             2010-10-16 00:20:56.460
3           1               3000             2010-10-16 00:21:56.460
4           1               10000            2010-10-16 00:29:56.460

Event 1 is okay because there’s nothing to compare it against

Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute

Event 3 should be flagged, as the value grew 1500 in a minute

Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)

Query Output (your query should return data in a format like this):

User      Flagged Logon Time    Rate Since Last Logon (money/minute)
John      2010-10-16 00:21:56   1500
Dave      2010-10-16 00:30:50   3200
Bill      2010-10-16 00:35:23   1000

It is likely that you will need to create sample data for both the User and [Events.Logon] tables.  We are looking for a single query that returns data like what is represented in Query Output.

View 3 Replies View Related

Transact SQL :: Query To Get List Of Permutation And Combinations

Aug 27, 2015

Below query:

Declare @table table
(
consumerID varchar(10),
customerNumber varchar(10)
)

Insert into @table
Select 1,123
union all

[Code] ...

--Expected output concatenation of consumer numbers all permutation and combinations having same

customernumber
1 2
1 3
1 4
2 3
2 4
3 4
5 6
5 7
6 7

View 4 Replies View Related

Multiple Columns In Table That Reference 1 Lookup Table

May 4, 2006

Hello,I have a query that I need help with.there are two tables...Product- ProductId- Property1- Property2- Property3PropertyType- PropertyTypeId- PropertyTypeThere many columns in (Product) that reverence 1 lookup table (PropertyType)In the table Product, the columns Property1, Property2, Property3 all contain a numerical value that references PropertyType.PropertyTypeIdHow do I select a Product so I get all rows from Product and also the PropertyType that corresponds to the Product.Property1, Product.Property2, and Product.Property3ProductId  |  Property1  |  Property2  |  Property3  | PropertyType1  | PropertyType2  |  PropertyType3 PropertyType(1) = PropertyType for Property1PropertyType(2) = PropertyType for Property2PropertyType(3) = PropertyType for Property3I hope this makes sence.Thanks in advance.

View 3 Replies View Related

Fuzzy Lookup Error When Adding Additional Lookup Columns

Sep 26, 2007

I'm working with an existing package that uses the fuzzy lookup transform. The package is currently working; however, I need to add some columns to the lookup columns from the reference table that is being used.

It seems that I am hitting a memory threshold of some sort, as when I add 3 or 4 columns, the package works, but when I add 5 columns, the fuzzy lookup transform fails pre-execute:

Pre-Execute
Taking a snapshot of the reference table
Taking a snapshot of the reference table
Building Fuzzy Match Index
component "Fuzzy Lookup Existing Member" (8351) failed the pre-execute phase and returned error code 0x8007007A.

These errors occur regardless of what columns I am attempting to add to the lookup list.

I have tried setting the MaxMemoryUsage custom property of the transform to 0, and to explicit values that should be much more than enough to hold the fuzzy match index (the reference table is only about 3000 rows, and the entire table is stored in less than 2MB of disk space.

Any ideas on what else could be causing this?

View 4 Replies View Related

SQL Table Lookup Methods

Aug 29, 2006

I am looking for a good method to do table lookups in a 2003 SQL database. I am retrieving products from an item table and then want to do value lookups in related tables in the same database like category name, category type, brand name etc. These values are referenced in the item master file by guid links to related tables.I am using a datareader to loop through the Select records and then I am writing results  to a tab delimited data file for a data conversion.  I am limited to net 1.1 and so can't use the 2.0 executescalar for single lookups.What would be the more efficienct method to use?'Dim myItemData As New DataReader Dim myItemReader As SqlClient.SqlDataReaderDim myItemCommand As New SqlClient.SqlCommand Dim myConnection = New SqlClient.SqlConnection("server=sql01.xxx") myItemCommand.Connection = myConnectionmyItemCommand.CommandText = "SELECT ""mf_items"".""item_guid"", ""mf_items"".""item_description"", ""mf_items"".""item_wholesale_price"", ""mf_items"".""item_description_title"",""mf_items"".""item_cd"",""mf_items"".""category_guid"" FROM ""CDB006"".""dbo"".""mf_items"" WHERE ""mf_items"".""item_closed"" = 0 And ""mf_items"".""item_visible"" = 1 AND ""mf_items"".""item_available"" = 1"myItemCommand.Connection.Open()myItemReader = myItemCommand.ExecuteReader()Dim myfileout As StreamWritermyfileout = File.CreateText(Server.MapPath("out.txt"))myfileout.WriteLine("link" & vbTab & "title" & vbTab & "description" & vbTab & "price" & vbTab & "image_link" & vbTab & "category" & vbTab & "id")While myItemReader.ReadIf myItemReader.Item(4).ToString() > "" And Val(myItemReader.Item(2).ToString) > 0 Then'what method to to look up for example the category name using the category_quid referenced in the item master'Write out record for parts file 

View 2 Replies View Related

Using A View As A Lookup Table

Jan 29, 2004

Hi

I'm designing a database for my final year project at University, and have run into a bit of a dilemma with one of the tables. Basically, it's an equipment loan database where both students and lecturers need to be able to book and borrow equipment. To avoid having two separate bookings tables (i.e. StudentBookings and LecturerBookings) I've got students and lecturers combined into one table called 'Borrowers'. The trouble comes because the clients want the lecturer storing in the booking information table, which can get quite messy because lecturer information is stored in the same table as borrower information meaning that I basically need to relate both the Booking.BorrowerID and Booking.LecturerID to Borrowers.BorrowerID.

In theory, I think this could be solved by creating a View called Lecturers that pulls the required information for Lecturers out of the Borrowers table and then link Booking.LecturerID to the ID of the view. In practice, I've got a couple of queries:

1) Is it possible to perform a JOIN on a view in the way that I would need to here?

2) To keep things nice and clear, is it possible to 'rename' the BorrowerID to LecturerID whilst creating the View?

Sorry if some of this isn't too clear. Please ask any questions to clarify what I mean if you need to.

Cheers
Jon

View 2 Replies View Related

Extract Value From A Lookup Table

Feb 23, 2006

Hi,
I'm inserting records into a Table1 with the following fields :
tabID int 4ClientID int 4..etc etc.
In the insertitem template, I want the user to enter a Clientcode into textbox1.  Then I want that client code validated against a lookup table, ClientTable and if valid, get the ClientId value from that ClientTable, so that I can populate the ClientID field in Table1.
I've got a mental block and I cannot see how I can do that !
Any ideas most gratefully appreciated.
Thanks
Sunil
 
 
 
 

View 6 Replies View Related

How Do I Create A Lookup Table In SQL?

May 12, 2008

Table 1
Skill_ID SkillName
1 xml
2 oracle
3 sql
4 vb

Table 2
EMP_NAME SKILL_ID_1 SKILL_ID_2 SKILL_ID_3
JOHN DOE 1 4 null
JANE COLE 3 4 null
SAM COOK 1 2 4


I want to return the following:

RETURN DATA
EMP SKILL_ID_1 SNAME1 SKILL_ID2 SNAME2
JOHN DOE 1 xml 4 vb
JANE COLE 3 sql 4 vb
SAM COOK 2 oracle 3 sql

View 1 Replies View Related

Lookup Results In Another Table

Jul 11, 2013

I have a table "Ratings" in which I store rating categories like the following:

FROM TO Rating
1 10 1
11 20 2
21 30 3

In another table "Results" I have the actual results like:

Employee ID Results Rating
120 6 ?
130 18 ?

Now I would like calculate the rating in the results table in the rating column (where "?" in the above example)

How can I do that with an SQL update command - maybe using a Case statement - but I don't know how to do that by looking up values from another table...

View 5 Replies View Related

Populating A Lookup Table

Jul 9, 2006

I'm trying to do a table lookup in t-sql and runing into problems. I have two tables, City and County..



Table City:

CityID CountyID CountyName

1 3 NULL

2 2 NULL

3 1 NULL



Table County:

CountyID CountyName

1 Los Angeles

2 Contra Costs



I want to populate the NULL "CountyName" field in table City with the values from table County but I can't make it work! Any help appreciated.



Barkingdog

View 1 Replies View Related

Lookup Table Structure

Jun 26, 2007

I have another lookup table question

so say i have an employee table, and a department table. all the department table will do is store the names of the different departments so how should this be set up using MSSQL

Employee
EmployeeId
FN
LN
DeptName (fk)

Department
DeptId (pk) unique
DeptName unique

*** Or could i do this to make the employee record more meaningful without needing a join. ***

Employee
EmployeeId
FN
LN
DeptName (fk)

Department
DeptId (pk) unique
DeptName unique


*** Or should i just do this and get rid of the Id Field***
Employee
EmployeeId
FN
LN
DeptName (fk)

Department
DeptName (pk) unique


thanks all

View 3 Replies View Related

Create A Lookup Table In SQL

Mar 23, 2008

i have very very basic knowledge of SQL like the ability to add, delete, update, select records and the ability to drop/create tables but i want to know how to integrate a lookup table into a field (very much like MS Access users can do with ease).

here is my example;

i have two tables - Club and Player

Club:

Club_Id
ClubName
Abbr

Player:

Player_Id
Name
Club_Id

the idea is that a single club can have several players. in the players table, i want the Club_Id field to act as the drop down whereas if a player changes club at any time, i can just refer to that player and change his Club_Id by scrolling down a list of values in SQL Server Management Studio 2005.

how can this be achieved by any chance, help would be very much appreciated.

View 8 Replies View Related

How To Add A Column By Getting Value From A Lookup Table

Feb 13, 2007

Hello,
I have few columns at some level, and now I need to add a column from another database table based on the values from one of the existing columns.

It seems that Lookup Transformation doesnt support parameterized query.


Any clue ?

Thanks
Fahad

View 7 Replies View Related

Dataflow With 2 Lookup Using The Same Table

Oct 26, 2006

Hi there

I'm getting into trouble everytime I want to setup a dataflow containing 2 or more flows, where I want to make a lookup into 1 other table.

This fails, of some reason I do not know of.

ex.

Table 1 -> Lookup Currency Table -> Table2 with currency
Table 3 -> Lookup Currency Table -> Table 4 with currency

One of the lookup will fail.

How do you avoid this?

/Bjarne

View 1 Replies View Related

Lookup From One Table To Other Master Table

Apr 21, 2015

I imported an Excel file in the form of a table(11,000 rows) in SQL Server 2012 database called Sanbox, I am trying to link this table to MS-CRM leads table so that I can link all the street addresses from table in Sandbox DB to the respective tables in MS-CRM leads table (200,000 rows), my end goal is to link the owner names from MS-CRM table to Sandbox DB's table with the matching criteria of 'street address'

My simple query looks like this, but it returns 200,000 rows, but I only want 11,000 rows -

SELECT *
FROM Sandbox.dbo.Sheet1$ A
LEFT JOIN [IPlus_MSCRM].[dbo].[FilteredLead] B
ON A.[Street 1] = B.address1_line1

View 4 Replies View Related

Reporting Services :: SSRS Lookup - Can Use More Than One Field When Doing Lookup

Sep 23, 2015

Say I want to lookup a value in another dataset, but there is a grouping that requires you to know what the values for each level is in order to get to the correct detail record.   Can you still use the lookup function with more than one field to compare against? So for example

Department
\___SalesPerson
     \___Measure

I want to be able to add a new row at the Measure level, but lookup each field from another dataset.  In order to do that I will need the Department AND SalesPerson values to do the lookup, but I dont think the Lookup function will let us do that will.

View 2 Replies View Related

Simple Table Update With Lookup

Feb 9, 2006

Hi i have three tables

T1 is a table of data imported by DTS, it has staff numbers and location names

T2 is a table that contains system data about staff and has a column for the location id

T3 is a table for the location names and their ID's

i am trying to write the sql to update T2 with the new location IDs for staff that have moved location but am not getting what i need.

so far i have :
update
T2
set T2.LocationId =T3.LocationID
from T3
inner join T1
on T3.departmentname = T1.contact_location

the update runs but sets all rows to the same value. am now burried deep in the [I've stared at this too long and cant see the wood for the trees] stage :mad:

Thanks
FatherJack

View 2 Replies View Related

Lookup Table Foreign Key Null

May 1, 2007

I'm using SQL Server 2000. I have an EXTERNAL_FUNDING table and an EXTERNAL_FUNDING_TYPE table. The DDL for the tables is listed below. Each EXTERNAL_FUNDING record has a type field, which is a foreign key to a record in the EXTERNAL_FUNDING_TYPE table. However, I would like for the type to be allowed to be null. When displaying the data, if the type is null, I'd like to show blank text. Here is the query I'm trying to use:
SELECT EXTERNAL_FUNDING.id, date_requested as [Request Date],source as [Source/Sponsor],EXTERNAL_FUNDING_TYPE.type AS [Type],amount as [Amount], granted as [Granted]
FROM EXTERNAL_FUNDING,EXTERNAL_FUNDING_TYPE
WHERE (project_id = @project_id) AND
(EXTERNAL_FUNDING.type = EXTERNAL_FUNDING_TYPE.id)
ORDER BY date_requested ,source,type

But this query does not return the records with NULL for the type field. Can someone explain to me how I can also return the rows with a NULL type? Thanks!

Table DDL:
CREATE TABLE [dbo].[EXTERNAL_FUNDING] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[project_id] [int] NOT NULL ,
[date_requested] [datetime] NULL ,
[source] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type] [int] NULL ,
[amount] [money] NULL ,
[granted] [bit] NULL ,
[date_granted] [datetime] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[EXTERNAL_FUNDING_TYPE] (
[type] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[id] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]

View 2 Replies View Related

Lookup Table Relationships Best Practice

Jul 23, 2005

What is the best way to setup relationships between one lookup tableand many other tables. The tables did not have any lookup tablerelationships which I am adding. One lookup table is used for same datain several different places.To use one lookup tables with several tables, I had to disable "CascadeUpdate" and only have "enforce relationships for updates and inserts"checked.Any pros/cons?Thanks in advance.P

View 1 Replies View Related

Lookup Not Detecting New Column On Table

Aug 13, 2007

I have added a new column to a table, but it is not showing up in the column mapping tab of the lookup editor.

It does show up in the preview.

Without deleting and recreating the component (with all the ensuing broken metadata fixes 'downstream' that this always entails), how can I get it to recognise the change?

In future I will do all LUs as a sql statement so at least I can control columns. This again defeats the purpose of the drag and drop environment..... If it were a harry potter character, it would be a dementor

View 5 Replies View Related

Fuzzy Lookup Index Table

Feb 26, 2008



Can anybody tell me when the index table is created? I have a Ole DB Command transformation that deletes rows from a table, and the pipeline continues on to a fuzzy lookup. The fuzzy lookup is returning matches to some of the rows which were delted in the aformentioned Ole DB Command. If the index table is created during pre-execute, this would make some sense to me since those rows which get deleted still exist before the pipeline reaches the Ole DB Command transformation which deletes those rows. If this is the case, is there a way to delay the index table creation? If this is not the case, has anybody else ran into anything like this...is there some solution? Thanks for any help.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved