Complex Conditional Join
Apr 27, 2008
Table A
Registation_key discharge_dt
1001 7/15/07
1002 6/4/07
1003 7/30/071
Table B
REgistration_key ID end_dt value
1001 879 07/11/07 1
1001 888 07/15/07 2
1002 777 06/01/07 5
1002 778 06/01/07 2
1002 779 06/30/07 6
1003 234 07/31/07 3
1003 234 07/31/07 1
1003 235 08/01/07 7
I am trying to join Table A to Table B using the below log . Table A should have one unique mathincg record from Table B
option 1. Using registration_key and discharge_dt , looking for exact matching date( end_dt) in table B , if there is more than one record that matches then select lowest ID
option 2.if there is no record that matches option1 then, serch for the previous record with end_dt in table A less than end_dt table B. if there is more than one record then select lowest ID
option 3. if there is no record option 2 then search the next record that matches .. discharge_dt greater than end_Dt , if there is more than one record then select lowest Id
so basically, I am looking for an exact matching date in the same registration_key .. if the exact dt doesn't exist looking for the previous record and get the most closer dt and if there is no prvious record than look for next record,.
so the output should look like
Table B
Registration_key ID end_dt discharge_dt value
1001 888 07/15/07 7/15/07 2
1002 778 06/01/07 6/4/07 2
1003 234 07/31/07 8/1/07 7
How can I do this conditional join ?
View 6 Replies
ADVERTISEMENT
Sep 7, 2003
table_a has patient_id, tran_id and other fields a,b,c
table_b has patient_id,tran_id, key_id
and other fileds d,e,f
table_a patien_id + tran_id is unique
table_b patient_id + tran_id is not unique, could be duplicated.
I have to create a query which will retrieve fields from table a a,b,c and fields d,e from table b where
table a. patient_id + tran_id =
tableb.patient_id + tran_id and table_b.key_id is the min key_id for that patient_id + tran_id.
I should retrieve just one record.
How would I be able to do that?
Please help!!
Thanks
View 2 Replies
View Related
Aug 26, 2005
Hey all,
I have 3 tables: 1 table with Module information (1 quiz-like part of an Exam), 1 table with the relationships between Modules and Exams (ratios) and 1 table with User information, that contains the Exam number, Module number, and score. I am trying to get a full list of modules' names for 1 exam, and the user's score for those modules they taken. All this would pertain to 1 single Exam. I am only getting records returned for those modules that the user has taken; I want the full list with NULL values for the scores of modules the user has not taken. Hope that made sense, here is my attempt:
Code:
SELECT ExamModules.Name, ExamUsers.Score
FROM ExamToModule LEFT OUTER JOIN
ExamModules ON ExamToModule.ModuleID = ExamModules.ID LEFT OUTER JOIN
ExamUsers ON ExamToModule.ModuleID = ExamUsers.MID AND ExamToModule.ExamID = ExamUsers.EID
WHERE (ExamToModule.ExamID = 1) AND (ExamUsers.UserName = 'dizzle')
In this case the user is Dizzle and the Exam's ID (primary key) is 1. I've changed LEFT to RIGHT and FULL, they all return the same few records. Help?
View 4 Replies
View Related
Jun 27, 2007
I need a little help and I hope I can write a clear description of the problem. I have to tables, customers and custrate. The customer table simply has a custid of other misc info. The custrate contains an entry for each rate for any particular customer (1 customer to many rates). However the rates are suppossed to come in pairs. For example, if a customer has a rate id of 120 then the customer should also have a corresponding custrate record with a rate_id of 200. If the customer has a rate_id of 130, then the customer should have a corresponding custrate record containing rate_id 300, and so on and so forth. How do I find the customer id with are missing the corresponding rate? Here's an example or what I'm trying to explain.customerid | name1 | fred 2 | tom3 | eric 4 | fredcustratecust_id | rate_id1 | 1201 | 2002 | 1302 | 3003 | 1204 | 130I looking for the sql statement that would return customer id of 3 and 4, since those are the only 2 records that don't have a corresponding rate. I have hunch that it requires a intra table join, but maybe i'm totally wrong. Can anyone help me please? I would greatly appreciate it!
View 8 Replies
View Related
Oct 16, 2006
This is a very complex query and i have tried everything with no sucess.
I'm having 3 Tables,
Orders which is having fields like CustomerID, ManufactureID, MerchID :- These all ID Fields (around 6) are foreign key of Contacts and Address Tables.
Address Table is having AddrID(Primary Key), ShortName
Contacts is having ContID (Primary Key), AddrID (Foreign Key), PersonName
I want to retreieve info in a single query which can return a single row with following columns :-
OrderNo, CustomerName, ManufactureName, MerchName etc. (all 6 columns) by joining these 3 tables
can anybody help in this.
View 5 Replies
View Related
Apr 23, 2008
If i want to do another join with this select join query, how would I do this I tried many different way, but keep failing or not getting the right answer.
I want to Inner Join tbSucess with the query below. Means that only return the result if the history does exists in tbSucess.
And
In a seperate select query.
I, I want to get only the records which are not found in tbSuccess . So how would I join those two.
Select r.History, LastName, FirstName, Convert(varchar(10), Pay, 101) as Pay,
from #rpt r inner join #history h on r.History = h.History
order by r.History, pay
View 8 Replies
View Related
Mar 3, 2008
Hi guys, I'm still new around here and still a noob for sql. Can you give me some example for some joins from the easy ones to the most complex and all of it kinds, I heard that there's a lot of different kind of join in sql. You can give me some link or maybe some code examples for me, I appreciate all kinds of help . Thanks for all your help. Regards.
View 3 Replies
View Related
Nov 3, 2004
I have a couple tables that I am exporting data from.
I have one table (call it TABLE1) that contains abbreviations instead of actual words.
For example instead of having the full word "New York City", the column would just contain "NYC".
In another table (call it TABLE2) it has all the abbreviations and the actual words.
So in TABLE2 one column name is FieldName, another Abbreviation and another is Value.
So on a web site, to display that actual name I do something like this
Code:
SELECT CityBoxlabel AS City
FROM TABLE1 INNER JOIN
TABLE 2 AS CityBox ON ISNULL(TABLE1.City, 'NYC') = CityBox.[Value]
WHERE CityBox.FieldName = 'City'
This is working great except that some of these columns contain more than one abbreviation such as "NYC,WDC"
which would stand for "New York City" and "Washington DC"
The items that have more than one abbreviation are not being pulled across because my query is looking for an "NYC,WDC" in TABLE2 to INNER JOIN on but it won't be in there. But "NYC" and "WDC" by themselves are in there.
I tried messing with the INNER JOIN statement by saying something like
TABLE2 AS CityBox ON ISNULL(TABLE1.City, 'NYC') IN CityBox.[Value] INNER JOIN
But it wasn't allowing that.
I can write a program to do this, but I would rather keep it in T-SQL.
Hope this wasn't too confusing!
Any help is greatly appreciated!!
View 5 Replies
View Related
Jul 20, 2005
hi thereanyone had an idea to join following 2 queries to 1????----- QUERY 1 ---------------------------------------------SELECT TMS_CaseF_2.Name AS TCDomain_0,TMS_CaseF_3.Name AS TCDomain_1,TMS.CaseF.Name AS TCFolder_2,TMS_CaseF_1.Name AS TCFolder_3,TMS.TestCase.Name AS TCName_4,TMS_TestCase_1.Name AS TCName_5,TMS.LogFolder.Name AS PlannedLogFolder_6,TMS.Log.Name AS PlannedLog_7,TMS.CaseResult.RecordedCaseName AS TCRecordedName_8,TMS.TestPlan.Name AS Plan_9FROM((((((((((TMS.Build INNER JOIN TMS.LogFolder ON TMS.Build.UID =TMS.LogFolder.Build)INNER JOIN TMS.Log ON TMS.LogFolder.UID = TMS.Log.LogFolder)INNER JOIN TMS.CaseResult ON TMS.Log.UID = TMS.CaseResult.Log)INNER JOIN TMS.TestCase ON TMS.CaseResult.TestCase =TMS.TestCase.UID)LEFT JOIN TMS.CaseF ON TMS.TestCase.Parent = TMS.CaseF.UID)LEFT JOIN TMS.TestCase AS TMS_TestCase_1 ON TMS.TestCase.Parent =TMS_TestCase_1.UID)LEFT JOIN TMS.CaseF AS TMS_CaseF_1 ON TMS_TestCase_1.Parent =TMS_CaseF_1.UID)LEFT JOIN TMS.CaseF AS TMS_CaseF_2 ON TMS_CaseF_1.Parent =TMS_CaseF_2.UID)LEFT JOIN TMS.CaseF AS TMS_CaseF_3 ON TMS.CaseF.Parent =TMS_CaseF_3.UID)INNER JOIN TMS.TestPlan ON TMS.TestCase.TestPlan = TMS.TestPlan.UID)WHERE (((TMS.LogFolder.Name) Like 'TR1%')AND ((TMS.Build.Name)='Planning_VD10A'))ORDER BY TMS.CaseF.Name,TMS_CaseF_1.Name,TMS.TestCase.Name,TMS_TestCase_1.Name;------------------------------------------------------------------ QUERY 2 ---------------------------------------------SELECT TMS.CaseResult.RecordedCaseNameFROM ((TMS.Build INNER JOIN TMS.LogFolder ON TMS.Build.UID =TMS.LogFolder.Build)INNER JOIN TMS.Log ON TMS.LogFolder.UID = TMS.Log.LogFolder)INNER JOIN TMS.CaseResult ON TMS.Log.UID = TMS.CaseResult.LogWHERE (((TMS.LogFolder.Name) Like 'VD%')AND ((TMS.Build.Name)='VD10A IT_APP'));
View 1 Replies
View Related
Jan 30, 2007
Im faced with the following design issue..
on my site there are different profiles: a city profile, a restaurant profile and a user profile.
in my DB:City profiles are stored in tbCities cityID int PK shortname nvarchar(50) forumID int FK (...)
Restaurant profiles are stored in tbRests restID int PK shortname nvarchar(50) forumID int FK (...)
User profiles are stored in tbUsers userID int PK shortname nvarchar(50) forumID int FK (...)
as you can see a single ID value (for CityID,restID or userid) might occur in multiple tables (e.g. ID 12 may exist in tbRests and in tbUsers)Each of these profile owners can start a forum on their profile.
forumID in each of the above tables is a FK to the PK in tbForums:forumID intforumname nvarchar(50) (...)
Now imagine the following:
a site visitor searches ALL forums...say he finds the following forums:ForumID Forumname1 you opinion on politics2 is there life in space?3 who should be the next president of the USA?
a user may want to click on the forum name to go to the profile the forum belongs to.And then there's a problem, because I dont know in which table I should look for the forum ID...OR I would have to scan all tables (tbCities,tbRests and tbUsers) for that specific forumid,which is time-consuming and I dont want that!
so if a user would click on forumID 2 (is there life in space?)
I want to do a conditional inner join for the tablecontainingforumID (which may be tbCities,tbRests or tbUsers)
select tablecontainingforumID.shortname FROM tablecontainingforumID tINNER JOIN tbForums f ON t.ForumID=f.ForumIDwhere f.ForumID=2
I hope my problem is clear..any suggestions are welcome (im even willing to change my DB design if that would increase effectivity)
View 6 Replies
View Related
Oct 4, 2006
I have three tables ...
tblWine tblSpecialOfferWine tblSpecialOffer
ID Name ID WineID SpecialOfferID ID Name IsLive
===================== ============================ ==========================
1 Mouton Rothschild 1 1 1 1 February Offer 0
2 Lafite Rothschild 2 1 2 2 March Offer 1
3 Chateau Teyssier 3 2 1
... and the current query I am using is the following along with it's result set ...
SELECT
tblWine.ID AS WineID,
tblWine.Name AS WineName,
tblSpecialOffer.ID AS SpecialOfferID,
tblSpecialOffer.Name AS SpecialOfferName
FROM
tblWine
LEFT OUTER JOIN tblSpecialOfferWine ON tblSpecialOfferWine.WineID = tblWine.ID
LEFT OUTER JOIN tblSpecialOffer ON tblSpecialOfferWine.SpecialOfferID = tblSpecialOffer.ID
Results
WineID WineName SpecialOfferID SpecialOfferName
================================================== =========
1 Mouton Rothschild 1 February Offer
1 Mouton Rothschild 2 March Offer
2 Lafite Rothschild 1 February Offer
3 Chateau Teyssier NULL NULL
... but the result set I want is All wines and their associated specials offers but only show details of the offer if the offer is live like so ...
Results
WineID WineName SpecialOfferID SpecialOfferName
================================================== =========
1 Mouton Rothschild 2 March Offer
2 Lafite Rothschild NULL NULL
3 Chateau Teyssier NULL NULL
... I've tried putting where clauses like ...
WHERE tblSpecialOffer.IsLive = 1 OR tblSpecialOffer.IsLive IS NULL
... but then that hides the wines that were on a previously associated on a special offer but is no longer live (see Wine #2).
Any ideas on the query I should be using?
Note: The queries and data above were made off the top of my head so may contain mistakes.
View 4 Replies
View Related
Apr 27, 2008
Table A
Registation_key discharge_dt
1001 7/15/07
1002 6/4/07
1003 7/30/07
Table B
Registration_key ID end_dt value
1001 879 07/11/07 1
1001 888 07/15/07 2
1002 777 06/01/07 5
1002 778 06/01/07 2
1002 779 06/30/07 6
1003 234 07/31/07 3
1003 235 07/31/07 1
1003 236 08/01/07 7
I am trying to join Table A to Table B using the below log . Table A should have one unique mathincg record from Table B
option 1. Using registration_key and discharge_dt , looking for exact matching date( end_dt) in table B , if there is more than one record that matches then select lowest ID
option 2.if there is no record that matches option1 then, serch for the previous record with end_dt in table A less than end_dt table B. if there is more than one record then select lowest ID
option 3. if there is no record option 2 then search the next record that matches .. discharge_dt greater than end_Dt , if there is more than one record then select lowest Id
so basically, I am looking for an exact matching date in the same registration_key .. if the exact dt doesn't exist looking for the previous record and get the most closer dt and if there is no prvious record than look for next record,.
so the output should look like
output
Registration_key ID end_dt discharge_dt value
1001 888 07/15/07 7/15/07 2
1002 777 06/01/07 6/4/07 5
003 234 07/31/07 7/30/07 3
How can I do this conditional join ?
View 1 Replies
View Related
Jul 23, 2005
Hi,I am trying to change an SP from dynamic SQL to proper SQL but I can'tfigure a way to conditionally add extra parts to the statement. Howcan I do the equivalent of the following?DECLARE @arg NVARCHAR(10)SELECT a.i, a.xFROM aTable aIF LEN(@arg)BEGININNER JOIN bTable b ON a.[id] = b.[id]ENDConditionally adding the INNER JOIN is very easy when building up a SQLstring but I can't see how to do it in pure SQL?Thanks.
View 5 Replies
View Related
May 28, 2008
I have these two tables =>
Table a
Name CEN VID
1. AA 01 11
2. BB 01 11
3. CC 02 12
Table b
CEN VID CName
01 11 ZZZZ
NULL 12 YYY
02 PR XXX
I want to join either on CEN or VID.
1. Join with CEN but If CEN is NULL in table b than the inner join will be VID (automatically in single query)
2. Join with VID but if VID in table b = 'PR' than the inner join will switch to CEN (automatically)
How can I approch this in a single SQL query.
Help me...
View 4 Replies
View Related
Mar 23, 2015
I have Two tables @master and @child
Master Table :
MasterID EntryNumber BranchId IsstockIn
1 1 1 1
2 1 1 0
Child Table:
CEntryNumber CBranchID EntryQty
1 1 10
1 1 20
1 1 -5
1 1 -4
My Query:
Select SEC.EntryQty from Item.StockEntryChild SEC
where SEC.CEntryNo =
(
select SEM.EntryNumber from item.StockEntryMaster SEM
where SEC.CBranchID=SEM.BranchID and SEC.CEntryNo=SEM.EntryNumber and SEM.MasterID=1 and SEM.isStockIn=1
)
My Result:
EntryQty
10
20
-5
-4
Expected Result:
10
20
View 6 Replies
View Related
Feb 8, 2008
I have a very confusing/complicated query that I am trying to run and getting not the results that i want.
Essentially I have three tables (t1, t2, t3) and I want to select data from two of them, but there are conditions on the data where I need fields to match across pairs of tables.
When I run my select query I am getting far too many records - it's giving me all possible combinations, instead of the proper combinations that I want.
Select t1.*, t3.field2, t3.field3
FROM, t1, t2, t3WHERE t2.field4=t3.field4 AND t1.field5=x AND t1.field6=t2.field6
I suspect there is plenty wrong with this query - where should I start correcting it?
View 10 Replies
View Related
Oct 24, 2006
I want to filter some search results using an inner join.The criteria are passed as a parameter.If the parameter has value, I want to inner join with it. If there is a NULL value, I just want to ignore the inner join. I don't want to:1) Build a string and do an exec() - too slow and hard to maintain2) use a (@parm IS NULL) OR ('inner join') AGAIN, too slow Suggestions appreciated
View 4 Replies
View Related
May 6, 2014
Table 1
PropertyId
1
2
3
4
5
6
Table 2
PropertyID PropertyCode PropList ID
1 2 45
3 4 67
5 6 89
Table 3
PropertyID IType
1 3
2 3
3 11
4 11
5 3
6 3
Target Table
PropertyID PropertyCode
1 45
2 45
3 67
4 67
5 89
6 89
Step1: Join Table 1 and Table 3
Steo 2: If Table 1.PropertyId.Itype = 3 Then Join Table 1 and Table 3 on Table1.PropertyID = Table2.PropertyId
Else
Step 3: If Table 1.PropertyId.Itype = 11 Then Join Table 1 and Table 2 on Table1.PropertyID = Table2.PropListId
View 4 Replies
View Related
Oct 10, 2007
Hello,
I want to write a query that joins data in a different table based on a column value. The table is for a "Playlist" and holds play list items. The items can be video, audio, images, etc. The playlist table looks like this:
Table_Playlist
-----------------
ID (int)
MediaType (char)
MediaId (int)
Table_Audio
-----------------
MediaId (int)
Table_Video
--------------
MediaId (int)
If the Table_Playlist.[MediaType] column value = "Audio" then I want to join to the Table_Audio table. If the value = "Video" then I need the video table.
Hope that makes sense. Thanks
View 1 Replies
View Related
Aug 25, 2005
Here is the situation
Table 1 : tbl_documents
docIDdocName
1aaa
2bbb
3ccc
Table 2 : tbl_Rating
ratIDratingdocID
131
251
321
432
The queary I need is to display the result in this form. must be like this
docIDdocName Avaragerating
1aaa3
2bbb3
3ccc0
NOTE : For getting the average I used this queary “SELECT SUM(rating) As
RatingSum, COUNT(*) As RatingCount FROM tbl_Rating WHERE tbl_rating.docID =
tbl_documents.docID”
PLs help me ?
Thx
View 3 Replies
View Related
Feb 7, 2006
I had this (what seems to be a) simple question asked today and I'm afraid I didn't like my answer. Does anyone know the proper answer to this one:
Any ideas on how I can constrain a lookup or merge join based on the dimension row's effective and expired dates so three criteria are needed as follows:
1. DataStagingSource.ModifyDate < DataWarehouseDimension.RowExpiredDate AND
2. DataStagingSource.ModifyDate >= DataWarehouseDimension.RowEffectiveDate AND
3. DataStagingSource.NaturalKey = DataWarehouseDimension.NaturalKey
-- Brian
View 3 Replies
View Related
Sep 11, 2007
Hi,
I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:
SELECT <published columns> FROM <filtered table> INNER JOIN <child table> ON <child table>.ID = <filtered table>.ID and <child table>.date > getdate()-30
After I upgraded to compact databse 3.5, for some weird reason whichever tables have both these Join filter and article filter together behaving improperly. If I insert any row in any of these table, the row is replicated properly to the server, but it does not send the new row to any other users. Again this thing works fine in older version. I have switched back tyo the old version of sql ce and again it's started working.
View 5 Replies
View Related
Mar 5, 2014
I Have Table Called 'Sales' and 'Voucher',I Need To Show Each Customer ""Dueamount"" Details Based Upon Customer Paid in 'Voucher' Table But One thing I have Not Maintained Transaction History For Customer in 'Sales' Table Means I Have Column named "CreditAmount" in 'Sales' and Column Named "VoucherAmount" in 'Voucher' ,For every transaction I am updating Column named "CreditAmount" in 'Sales', So finally 'Dueamount' Must be calculated according to "VoucherAmount" of customer in 'Voucher' Table....
Sales Table:
BillMasterId BillDate CustomerId NetAmount CreditAmount
26 03/03/2014 101 1000 1000
My Query:
SELECT CONVERT(varchar,BillDate,103) as BillDate,isnull(NetAmount,0) as BillAmount, case when VoucherAmount != 0 then sum(VoucherAmount)else 0 end as'AmountReceived',case when CreditAmount !=0 then CreditAmount else 0 end as 'DueAmount' from Voucher INNER join Sales on CustomerId=CustomerID and BillMasterID=BillMasterID WHERE CONVERT(varchar,BillDate,103)='03/03/2014' AND CustomerId=101
My Output:
BillDate BillAmount AmountReceived DueAmount
03/03/2014 1000 0 0
03/03/2014 1000 500 0
03/03/2014 1000 300 0
03/03/2014 1000 200 0
Exact Output:
BillDate BillAmount AmountReceived DueAmount
03/03/2014 1000 0 1000
03/03/2014 1000 500 500
03/03/2014 1000 300 200
03/03/2014 1000 200 0
View 7 Replies
View Related
May 13, 2015
I have a few tables I am trying to join to create a report. Everything was working fine until I tried to add an aggregate Sum function to a column (MaxCap) in table ctfBarn.Â
select
x.*, y.division, y.department, y.location
,(right(z.SvcMgrName,len(z.SvcMgrName)-len(left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))-2)+' '+
left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))AS SvcMgrName
,(right(z.SrSvcName,len(z.SrSvcName)-len(left(z.SrSvcName,CHARINDEX(', ',z.SrSvcName)-1))-2)+' '+
[Code] .....
I think I probable need to include a group by but can't figure out the correct syntax.
View 12 Replies
View Related
Aug 16, 2015
I need to add a join in my select query depending upon a variable @LoggedUser. the Join is to be there if @loggedUser is 1 else i do not need it. Currently I am using two different queries one with join and one without it under If (@LoggedUser check).
the join is like -
JOIN (SELECT CAST(CONVERT(VARCHAR(8),Analyst_Effective_date , 1) AS DATETIME) Analyst_Effective_date
FROM Users us (NOLOCK) JOIN Primary_Analysts (NOLOCK)
ON User_Count_Id = Analyst_Id_fk
WHERE User_Count_Id in ((SELECT VALUE FROM dbo.fParseString(@Analyst, ',')) )) Ana
ON dep.Departure_Code = Ana.Primary_Analyst_Departure_Code_fk
)
Any way that the join can be added conditionally in the query so i do not have to write the whole code again for one join.
View 4 Replies
View Related
Oct 4, 2015
I have two tables tabA (cola1, cola2, cola3) and tabB(colb1, colb2, colb3, colb4) which I need to join on all 3 columns of table A.
Of the 3 columns in tabA, few can be NULL, in that case I want to check the joining condition for the rest of the columns, so its conditional joining. Let me rephrase what I am trying to acheive - I need to check if the columns in joining condition is NULL in my 1st table (tabA), If so I need to check the joining condition for the rest of the two columns, if 2nd column is again NULL, I need to check the joining condition on the third column.
What I am trying to do is as below. Its working, but is very slow when one of the tables is huge. Can I optimize it or rewrite in a better way ?
--- First Create two tables
Create table tabA
(cola1 nvarchar(100), cola2 nvarchar(100), cola3 nvarchar(100))
Insert into tabA values (NULL,'A1','A2')
Select * from tabA
create table tabB
[Code] .....
View 7 Replies
View Related
Jan 31, 2007
I have a merge join that does a full outer join. I then have a conditional split that will breakout by unchanged, insert and update. The update is what I am having a problem with. The conditional split for the update is a follows:
!ISNULL(HISTORICAL) && !ISNULL(TRANSFORM)
Now I believe the problem is related to spaces in the key field let me explain.
The join field is defined as [char](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
There was an original mismatch between the two tables of VARCHAR(14) & Char(14) but now there both Char(14).
What's interesting is that the few records with Alpha characters correctly does the update but the records with starting numeric data all go down the insert path.
The data of the join columns look like
'308811151 ' - 5 Spaces This Data incorrectly goes to insert path
'TSTRWR02 ' 6 Space This Data correctly goes to update
The data is grouped and sorted by the Key field and the historical and transform column contain a 1 for all records.
while the Doc_nbr which is the key
I tested the join in SQL server and it works with and without the spaces.
From SSIS I also tried RTRIM in the SQL command of the source in addition to substring the first 9 with the right trim and I always received the same results.
I am guessing there is something going on with the not isnull in the conditional split but I can't figure it out if I am missing something or if this is a BUG.
Three paths of data after the merge join and the conditional split
Insert
DOC_NBR,HISTORICAL,TRANSFORM,DOC_NBR_historical
206352185 ,,1,
209900165 ,,1,
Update
DOC_NBR,HISTORICAL,TRANSFORM,DOC_NBR_historical
NEE ,1,1,NEE
New ,1,1,New
Unchanged
DOC_NBR,HISTORICAL,TRANSFORM,DOC_NBR_historical
,1,,0000521096
,1,,0000685216
Any suggestions, am I missing the obvious?
LL
View 3 Replies
View Related
Jul 15, 2014
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
View 6 Replies
View Related
Nov 12, 2003
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
View 7 Replies
View Related
Mar 31, 2008
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
View 4 Replies
View Related
Mar 7, 2008
Hello everyone,
Is there a way in order to execute a subscribed report based on a certain criteria?
For example, let's say send a report to users when data exist on the report else if no data is returned by the query
executed by the report then it will not send the report to users.
My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it.
Any help or suggestions will be much appreciated.
Thanks,
Larry
View 6 Replies
View Related
Apr 30, 2008
Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
View 1 Replies
View Related
Dec 15, 2006
I have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.
=SWITCH(Fields!Wrap.Value >= 3, "Red", Fields!Wrap.Value < 3, "Green")
I already tried =iif(Fields!Wrap.Value >= 3 , "Red", "Green") and got the same results.
Is it because this is a matrix report? What am I doing wrong?
Thanks in advance . . .
View 4 Replies
View Related
