How to accomplish a join when the join condition has to be parsed from another column? I need a list of all the exchange part numbers with exchange part pricing, but need the description from the original part number. Example:
Part Number Description Cost
123-456-1 A ROUND THINGIE 5
150-387-1 EXCH - 123-456-1 2
Needed record is
Part number ExchangePart Description Cost
123-456-1 150-387-1 A ROUND THINGIE 2
Something like
SELECT PART# as exchangepart,
case
when substring(descr, 1,4) = 'EXC-' then trim(substring(DESCR, 5, 25))
when substring(descr, 1,3) = 'EXC-' then trim(substring(DESCR, 4, 26))
when substring(descr, 1,6) = 'EXC - ' then trim(substring(DESCR, 7, 23))
when substring(descr, 1,5) = 'EXCH-' then trim(substring(DESCR,6,24))
I am trying to parse a query column that has both a city name and state code within it. What I would like to do is parse the queryed column and seperate them by the ",". Can someone please help?
example: Select Name, Address1, Left(Address2, ",") as City, Right(Address2, ",") as State From tblPersonalInfo
Hi,I have a varchar column with values like:000 BNP=Item one HOP=Item two LLT=Item three001 LLT=Item one002 BNP=Item one LLT=Item twoI want to parse this rows into a table similar to the following output:000 BNP=Item one000 HOP=Item two000 LLT=ItemThree001 LLT=Item one002 BNP=Item one002 LLT=Item twoHas anybody done a similar assignment? Can you share me your solution?Thank you much!Edgar J.
I have data in SQLSever 2008 R2 in one column as following. I would like to run a sql statement and capture them into individual columns. Would that be possible? The column separator is |
I'm new to SSRS (We are using SQL 2014) and have been asked to create a report from SharePoint list data. One of the columns in my list contains XML data. I need to pull three different fields from this XML. I see lots on using xml as a datasource but I'm using a SharePoint list as my datasource. Before I get too far down the wrong road I thought I'd ask here if there was a built in means of parsing XML or if I should look at using the Text Function under Common Functions in the Epression builder?
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
There is another table called Equipment with fields
ID is the same in Boats as the ID in Equipment (Foreign Key).There can be multiple Parts linked to ID.I want to be able to return all the rows from Boats but only the Parts for each ID from Equipment. I dont want the other fields appearing more than once in the query.
I have 2 tables, my vehicle data table and my config table. I need a query to join them by a datarow and a data column. Heres my tables...config table--------------------id name type--------------------1 make varchar2 model varchar3 color varcharveh table--------------------------id make model color--------------------------1 chevy s10 white2 ford ranger silver2 chevy blazer brownrecordset needed for veh.id=1---------------------------id name type value---------------------------1 make varchar chevy2 model varchar s103 color varchar white Thanks for any helpRyan
I have two tables, one of which is a key table with a subaccount number and a set of attributes that define that subaccount. I am trying to join this key table with the table with all the attributes and come up with one table of subaccounts. The Subaccounts should only lookup the attributes associated with them, not all of the attributes, so I put OR [attribute] IS NULL in the WHERE clause so it only matched on the appropriate columns. This worked great in an initial test with two attributes but when I put all 9 attributes in it crashed with this message "Msg 415, Level 16, State 1 The current query would require an index on a work table to be built with 15 keys. The maximum allowable number of keys is 16" like this CREATE VIEW subaccounts (Subacct_no, balance) AS SELECT (s.Account_No + "." + r.Subacct_Ext, S.Balance) FROM Acct_Rcd r, Subacct_Key s WHERE (s.attr1 = r.attr1 OR s.attr1 IS NULL) AND (s.attr2 = r.attr2 OR s.attr2 IS NULL)
Hi, I am writing a small search engine. There are two tables. The first one holds the search engine main index, the second one is link table. I have the following query that retrieves results. I would like to sort the results by: dbo.OCCURS2(LOWER(:query),se_links.anchor). se_links.anchor obviously comes from se_links table, so I get an error. Is it possible to done in one query? I'm using MSSQL 2005. Thanks. PS. Function OCCURS2 returns number of occurrences of one string in other.
Code:
select id as Id, uri as ElementUri, size as Size, modified_date as ModifiedDate, title as Title, text as Text, dbo.OCCURS2(LOWER(:query),Title) as TitleOcc, dbo.OCCURS2(LOWER(:query),Text) as BodyOcc FROM se_index WHERE (title LIKE :query) OR (text LIKE :query) OR (id IN (SELECT se_links.target_index_id FROM se_links INNER JOIN se_index AS se_index_1 ON se_links.target_index_id = se_index_1.id AND se_links.anchor LIKE :query))
We have here 3 tables which are linked by Order number. there is one more table we need to use to get the Shipping zone code. This column however is 10 pos. ( the order number on that table)whilst the others are all 8. We want to join on MHORDR in the table MFH1MHL0, then we are done.
SELECT ALL T01.OHORDD, T03.IHINV#, T01.OHORDT, T01.OHJOB3, T01.OHORD#, T02.IDPRLC, T02.IDNTU$*(IDSHP#) AS EXTSHP, T02.IDPRT# FROM ASTDTA.OEORHDOH T01 LEFT OUTER JOIN ASTDTA.OEIND1 T02 ON T01.OHORD# = T02.IDORD# LEFT OUTER JOIN ASTDTA.OEINHDIH T03 ON T01.OHORD# = T03.IHORD# WHERE T01.OHOSTC = 'CL' AND T01.OHORDD >= 20120101 ORDER BY T01.OHORD# ASC
Group,Passing inline values to a udf is straightforward. However, how or is itpossible to pass a column from the select of one table into a udf thatreturns a table variable in a join to the original table. The goal is toexplode the number of rows in the result set out based on the result of theudf. Although the example I am providing here is simplified, we are tryingto parse out multiple values out of a text column and using a select into sothe result will be one row for each of row in the original table compoundedby the number of occurrences in the text field.(I know bad table design but that's out of my control)Thanks,Raycreate table TableOne(Col1 int,Col2 char(5))goinsert TableOne values (1, 'One')insert TableOne values (2, 'Two')insert TableOne values (3, 'Three')goCreate Function dbo.udfTableTwo(@Id int)RETURNS @tbl TABLE (TID int, TChar char(1))ASBEGINDeclare @test intset @test = @IdWhile @test >= 0BeginINSERT @tbl VALUES (@Id, Char(65+@test))set @test = @test - 1EndRETURNEND--worksselect a.*, b.* from TableOne a join dbo.udfTableTwo(2) bon a.col1=b.TID--Fails with Line 1: Incorrect syntax near '.'.select a.*, b.* from TableOne a join dbo.udfTableTwo(a.col1) bon a.col1=b.TIDdrop table TableOnegodrop function dbo.udfTableTwogo
Thanks in advance for any help.I'm trying to attempt the following with one table/query.I have a table called TABLE_1It has the following fields:SSNTITLELOCATION322EXDALLAS568REPFT_WORTH536EXDALLAS485SLSYOUNGSTOWN854BROFEEDER258EXEVANSVILLE478TEMPTROY861SLSDALLASI want toselect SSN, TITLE, LOCATIONfrom table_1where ssn = ('322', '536', '258', '478', '861')and title = 'EX'and location = 'DALLAS'The problem, however, it that I want all of the SSN records on my list.My table should look like this:SSNTITLELOCATION322EXDALLAS536EXDALLAS258nullnull478nullnull861nullnullI can easily do this on two tables with a left outer join. I do notknow how to accomplish same (theory) with one table.Any help on how to do this would be appreciated. Thanks
I'm trying to join two columns together to form a new column
My code is basically in the form of can't post the actual since it would be cheating--school assignment
SELECT Column1Name,Column2Name, Column3Name,Column4Name, Column1Name+Column2Name AS NewColumn1 Column3Name+Column4Name AS NewColumn1 FROM OriginalTable
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....
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
Hi,very new to SQL queries, and strugling with join conceptI had to do a join based on a single field:select*fromtableA, tableBwheretableA.value = tableB.value(+);this works finebut how can i do the same thing while comparing multiple columns ... iwas thinking something like this: (obviously doesn't work)select*fromtableA, tableBwhere[tableA.value1, tableA.value2] = [tableB.value1,tableB.value2](+);Is there some sort of "tuple" comparison I can do?Thank you.
Perhaps is just brain drain but i cannot seem find an efficient query to join two tables (inv and supplier) such that an inv item can have multiple suppliers and i would like to choose the prefered supplier based on the current 'weight' column.
Hi, I was wondering wich is teh easiest way to solve this:
I have one data set that comes from a sql query with many columns.
I have another data set that comes from a txt file that I am reading.
Both data set have one column in common (for example ID).
In the sql data set, I have many rows and I want to keep only the ones that have the same ID from the data set of my txt file. It looks like a inner join if we talk in sql terms.
So, which is the easiest way to solve this? Thanks!
update a tables value(a single column), based on a query of another database, using a common column.
Here's what I've cooked up so far:
Declare @trackingNo nvarchar (50) Set @trackingNo = ( select tracking_no from P21_Shipping.dbo.shipping_data t1 inner join P21.dbo.oe_hdr t2 on t1.order_no = t2.order_no
[Code] ...
print @trackingNoThe error it's returning is:
Msg 512, Level 16, State 1, Line 3 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
So, I'm wanting to query Shipping_data for order_No that matches the same orderNo column value in P21 Database(oe_hdr table), then update P21 oe_hdr table with "trackingNo from Shipping_data
I have two tables. They both have an identity field to join them together. One of the tables has a column of data that I want to put in the other table. I need to make sure the data is brought over using the join so it updates that column for the right record.
I am struggling with this. Should I use a update or an insert? I'm leaning toward update but I can't figure out how with the join.
This is what I have tried so far:
Update grpcon.GroupID = groups.GroupID from grpcon Inner Join groups on grpcon.GroupNum=groups.GroupNum
Hello, I have data coming in from two sources, one being SQL and the other being Oracle. The end result needs to be a CSV file with the columns in a specific order. I have a Data Flow task setup that takes both sources and does a Merge Join on them. I can add a Sort Transformation and manually set the sorting of all 156 columns that end up going to a CSV file destination. However, I have a table setup that holds the names of the 156 columns and the order that the CSV file expects them to be in. I would much rather do this step dynamically as the column names and order may change in the future. Anyone who has used the Sort Transformation for a large number of columns knows how tedious it can be and how adding a column in the middle will cause you to change the sort # for each of the columns that come after it.
So I added a Script Component between the Merge Join and the Flat File Destination hoping that I could alter the order of the columns there. However I added the following code and found that the SortKeyPosition is ReadOnly.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim column As IDTSInputColumn90
For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
column.SortKeyPosition = 1
Next
End Sub
I was hoping to add some code to get the sort index from my table for each of the columns and set it to the SortKeyPosition. Has anyone out there done this before or seen an example that might point me in the right direction? I've searched for 2 days without coming up with much.
Does anyone have a clue as to why DT_R4, DT_R8 are not allowed as join columns? This means I cannot join tables on AccountNumbers, InvoiceNumbers, etc. What a pain...
This is my syntax, but it keeps presenting an error of 'Invalid Column name Last Resort
Select fb.foo, fb.man, cz.choo, 'Hi' As [Last Result] From foobar As fb Inner Join chezter As cz On fb.username = cz.username and [fb].[Last Result] = [cz].[Last Result]
At present I have a logic implemented like below code example where I can get a combination of columns cid and ani in 'config' table to which I have to map columns cid and ani present in my 'current' table.
Now, a new column pid is added to both 'config' and 'current' tables, like
alter table config add pid integer alter table current add pid integer
Now I can have data in a combination of cid and ani (where pid can be NULL) or cid and pid (where ani can be NULL) or a row where all cid, ani and pid can have values. In this scenario, how should I make the changes in the current implementation given below?
create table current (cid integer, ani integer, resetdate datetime, threshold integer) create table config (cid integer, ani integer, resetdate datetime, threshold integer ) SELECT cid, ani from ( SELECT A.cid, A.ani, CASE WHEN C.cid is null then B.resetdate ELSE C.resetdate END AS resetdate,
I'm creating a sql stored procedure inside this proc it returns some information about the user, i.e location, logged in, last logged in, etc I need to join this on to the photos table and return the photo which has been set as the profile picture, if it hasn't been set then return the first top 1 if that makes sense?
The user has the option to upload photos so there might be no photos for a particular user, which I believe I can fix by using a left join
My photos table is constructed as follows:
CREATE TABLE [User].[User_Photos]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [UserId] [bigint] NOT NULL, [PhotoId] [varchar](100) NOT NULL, [IsProfilePic] [bit] NULL,
[Code] ....
Currently as it stands the proc runs but it doesn't return a particular user because they have uploaded a photo so I need to some how tweak the above to return null if a photo isn't present which is where I'm stuck.
Not sure if this is ever going to be possible, but I'm trying to do a dynamic join on EXECUTE, but the execution string has to come from a table column.... i.e
select * from (select table1.theSql,table1.userid from table1 ) as a
