Basic Query: Alternatives To 'Group By' For NText Column
Apr 19, 2004
I am having some difficulty writing a relatively basic query. The objective is to retrieve the new stories (headlines) for the past 3 days from the database. Since each headline can be assigned multiple categories (topics) the query returns a row for every headline assignment. I can't use the 'Group By' expression because one of the columns is nText.
So basically if there is an article written yesterday, "I Love Cats" that gets assigned both topics 'CATS' and 'PETS' I only it returned with the first topic assigned... 'CATS'. Here is a little image of the three tables being called:
http://64.225.154.232/temp_dbDiagram.gif
I don't think that this query is too difficult, but I'm just getting my feet wet with writing queries that are more than select * from whatever. Any insight or recommendations are greatly appreciated.
SELECT headline.HEADLINE_ID, headline.HEADLINE_TITLE, headline.HEADLINE_DATE, headline.HEADLINE_THUMBNAIL,
topic.TOPIC_NAME, topic.TOPIC_URL
FROM tbl_CCF_Headlines headline INNER JOIN
tbl_CCF_Headlines_Topics ON headline.HEADLINE_ID = tbl_CCF_Headlines_Topics.HEADLINE_ID INNER JOIN
tbl_CCF_Topics topic ON tbl_CCF_Headlines_Topics.TOPIC_ID = topic.TOPIC_ID
WHERE (headline.HEADLINE_DATE IN
(SELECT TOP 3 HEADLINE_DATE
FROM tbl_CCF_HEADLINES
GROUP BY HEADLINE_DATE
ORDER BY HEADLINE_DATE DESC))
ORDER BY headline.HEADLINE_DATE DESC
View 2 Replies
ADVERTISEMENT
Oct 30, 2007
Hello.
I have a query which spends a lot of time calculating my CASE WHEN -statements.
My query looks like this
SELECT TOP 250
UserId,
CASE WHEN
(someCol*0.4+someOtherCol*0.3) > 99 THEN 99
ELSE
(someCol*0.4+someOtherCol*0.3)
END
FROM
(
SELECT
UserId,
CASE WHEN @myparam > 50 THEN
CASE WHEN Col5+@myincrease > 99 THEN 99 ELSE Col5+@myincrease END
ELSE
CASE WHEN Col6+@myincrease > 99 THEN 99 ELSE Col6+@myincrease ENDEND as someCol,
CASE WHEN Col8+@myincrease3 > 99 THEN 99 ELSE Col8+@myincrease3 END as SomeOtherCol
FROM
SomeTable
) t1
This is just a little bit of the full query. I cannot write the whole query since it contains alot of different views and calculations, but I have traced it down to that all these case when-statements is taking a lot of time to do. So I hope that this example will be enough for you to understand my problem.
I know about some tricks that can replace a CASE WHEN, for example using COALESCE or BETWEEN but that does not seem to work in my case.
Any suggestions?
View 3 Replies
View Related
Apr 7, 2006
I have one SELECT statement that needs to return one ntext field fromone table and count something from other table, problem is that allfileds that are not in count have to be in group by and ntext can't bein group by... i hope you understend what i want to say here :), so isthere any solution to this problem or what is the best workaraund youwould use?example:TABLE projectsproject_id intproject_title nvarchar(50)project_description ntextTABLE usersuser_id intproject_id intSELECT projects.project_id, projects.project_title,projects.project_description, count(users.*) AS number_od_usersFROM projects LEFT JOIN users ON projects.project_id=users.project_idGROUP BY projects.project_id, projects.project_title,projects.project_description;this select is something that would work for me...if it wasn't for thattiny problem with ntext field
View 4 Replies
View Related
Jan 21, 2015
How can we write the query using groupby. I need to have group by only one column. Is it possible through subquery?
select col1, col2, col3
from testtable
group by col1.
View 4 Replies
View Related
Sep 29, 2015
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' Â values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) Â must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName          Clm ABC-X
X
Page2:
GroupName          Clm ABC-Y
Y
Page3:
GroupName          Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
View 4 Replies
View Related
Aug 11, 2005
Hi,
Please help me to find out the ntext column size in a table.
Thanks in advance
Regards
Karthik
View 2 Replies
View Related
Feb 27, 2004
I have some text in an NText column that could be quite long.
for the web page that displays it, i want to show the first 200 characters and then have a "more..." link to bring up the full text.
i'd like to create a stored procedure that takes the left 200 characters and copies them to a ShortText column (NVarChar) for initial display and then id like to set a bit column to indicate if the length of the NText column is greater than 200.
Len and Left cant be used against NText so how can i work with the NText data ??
View 2 Replies
View Related
Mar 14, 2008
Hi,
I have an SSIS Package where I am looping through all input columns and creating an XML out of it. The Problem comes in one of the tables where I have an Ntext column. Instead of giving me the value of the column, it gives me
"Microsoft.SqlServer.Dts.Pipeline.BlobColumn"
I am trying to get the string value from this blob column. For that I am using the below code. Can you please point me to where I am going wrong. I am not very good in VB so I might be doing some casting error.
Dim blobCol As Microsoft.SqlServer.Dts.Pipeline.BlobColumn
Dim ByteVal As String
For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
columnValue = rowType.GetProperty(column.Name)
Select Case column.DataType
Case (DataType.DT_NTEXT)
blobCol = DirectCast(columnValue.GetValue(Row, Nothing), Microsoft.SqlServer.Dts.Pipeline.BlobColumn)
ByteVal = System.Text.Encoding.Unicode.GetString(blobCol.GetBlobData(0, CInt(blobCol.Length)))
newXML += ((FormatElement(column.Name) + ReplaceSpecialChars(ByteVal) + FormatElement(column.Name, True)))
Case Else
newXML += ((FormatElement(column.Name) + ReplaceSpecialChars(columnValue.GetValue(Row, Nothing).ToString()) + FormatElement(column.Name, True)))
End Select
Next
Thanks in advance.
Regards
View 8 Replies
View Related
May 25, 2006
I'm trying to run the following SQL against my ntext column, Testtbl.Task I want to replace where the ampersand sign got HTML encoded to "&", and I want to strip it down to only the ampersand sign and deletethe "amp" and the ";".It executes, but I still have the HTML encoding for the ampersand sign if the issue is NOT the first character in the ntext column. If it's the first part of the ntext column, then it works.
View 5 Replies
View Related
Jan 16, 2007
Hi,
I am working on an application which accesses SQL database. I have a table complaint in which i have a coulmn 'history' of type "ntext". When i insert a value, the value gets stored in the db correctly, but I cannot access the complete data from that field using a simple select query. I had to export the table to a text file only then i could view the complete data of the field.
I wrote the query in query analyzer as:
"select history from complaint where pkey=142"
& the result i got is:
"
###### admin: Jan 8, 2007 3:53:57 PM GMT Type: Inactive (Key 10) Description: Activity yet to be started State: Planned "
This is only a part of the entire string that I had entered.
My requirement is that I need to access the data from the ntext field & save it in a variable & then display the entire data in my application. Can anyone tell me how should i use the select query so that i get the entire data without having to go thru the export data procedure.
Please help!!!
View 2 Replies
View Related
Sep 19, 2007
Hi,
I have a table column type as nText, however there are some Chinese character stored in that field and it is a messed up as it is not readable.
In my vb.net code, I did Convert to unicode by getting the byte of each character and encode it with UTF8 e.g:
Public Shared Function ConvertToUnicode(ByVal s As String) As String
' Convert To Unicode
Dim MyBytes As Byte() = Encoding.Default.GetBytes(s)
Dim GBencoding As Encoding = System.Text.UTF8Encoding.UTF8
Return GBencoding.GetString(MyBytes)
End Function
This works well but ,the problem is that it slows down the process quite alot, and I wonder are there any text encoding method I can use in SQL that can run when i do the SELECT Statement?
SELECT
Convert(MyNTEXTColumn)
....
something like that?
Thanks.
Jon
View 4 Replies
View Related
Nov 28, 2007
Hi,
I have exported a report to excel that contains an ntext column. The exported excel file shows "#Error" for the ntext column in some of the rows. If I see the same report in reporting services I don't see any problem. I have checked that this is not any issue with the length of the text. Could you suggest me something on this?
Thanks
Soni
View 5 Replies
View Related
Jul 13, 2015
SQL syntax to split a column that contains ntext values that are separated with this value : ;#
I need it to create 3 new columns
i.e.: I have a column that contains values such as:
ROW1: ;#typea;#typeb;#typec;#typed;#
ROW2: ;#typeb;#typec;#
ROW3: ;#typeb;#
I need it to convert to 3 new columns and strip out the ;#:
E.g.:
Column 1
typea
typeb
typeb
Column 2
typeb
typec
blank
View 16 Replies
View Related
Feb 28, 2014
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year
Month
Date
And the following row groups:
Region
Product
SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
View 0 Replies
View Related
Nov 28, 2007
I am running this query to an sql server 2000 database from my aspcode:"select * from MyTable whereMySqlServerRemoveStressFunction(MyNtextColumn) = '" &MyAdoRemoveStressFunction(MyString) & "'"The problem is that the replace function doesn't work with the ntextdatatype (so as to replace the stresses with an empty string). I hadto implement the MySqlServerRemoveStressFunction, i.e. a function thattakes a column name as a parameter and returns the text contained inthis column having replaced some letters of the text (the letters withstress). Unfortunately, I could not do that because user-definedfunctions cannot return a value of ntext.So I have the following idea:"select * from MyTable whereCheckIfTheyAreEqualIngoringTheStesses(MyNtextColum n, '" & MyString &"')"How can I implement the CheckIfTheyAreEqualIngoringTheStessesfunction? (I don't know how to combine these functions to do what Iwant: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT)
View 2 Replies
View Related
Aug 3, 2007
Howdy folks! (I feel im creating too many topics...but all my questions are unrelated :-/)
Dev environment: SSCE, Native c++ OLE code, Win CE 5
I have a routine that calculates the size of a set of columns in order to create a byte buffer for retrieving data from an IRowset and inserting it into a custom class. This method works great for all data types except ntext. When I look at the column information from an ntext field, the DBCOLUMNINFO::ulColumnSize member holds 536,870,911 (the max length of an ntext type). Obviously I dont want to allocate 1GB of memory for each ntext column. Is there anyway (natively) to determine the actual number of characters in a field?
View 12 Replies
View Related
Mar 27, 2008
Hi!
I recently was confronted with a problem where a piece of text that was included in many NTEXT column values in a table needed to be replaced with another piece of text. You can't issue normal REPLACE statements against NTEXT columns, so this seemed to be a bit of a challenge €” issuing a REPLACE() against a TEXT or NTEXT column in SQL Server yields error
I tried following
UPDATE CaseTypeDefs SET definition = replace(LTRIM(RTRIM(definition)), '<![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]>', '<![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]>')
But this is producing following error
Server: Msg 8116, Level 16, State 1, Line 1Argument data type ntext is invalid for argument 1 of replace function.
For Example: I want to replace string <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage]]> with <![CDATA[sp_YOTAssetAdditionalOffences 0, ArgParamHearingsId, ArgParamLanguage, ArgParamReferralId]]> in NTEXT column values in a table.
Need help, how to do it?.
Thanks In Advance
Devloper
Anil Kumar Dwivedi
View 4 Replies
View Related
Mar 2, 2007
I have records in a table as :
Col1 Col2 Col3 Col4
1115090122084511
1119610122084511
How do I query to get records where count(Col4) >1 and Col3 =12
Please note that Col4 is ntext datatype.
TIA,
Andy
View 2 Replies
View Related
Sep 5, 2006
Is there any difference between a nText field value='' and value=NULL ?
View 4 Replies
View Related
Jun 2, 2015
I have a table that is used to build rules. The rules point to other columns in other tables and usually contain only one value (i.e. ABC). But one of the options is to add a comma-separated list of SSNs (i.e. 123123123,012012012,112231122). I am trying to build a single query that allows me to leverage that list to get multiple rows from another table.
This obviously works:
SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN ('123123123','012012012','112231122')
But this does not:
SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN (
SELECT '''' + REPLACE(CONVERT(VARCHAR(4000),txtFieldValue), ',', ''',''') + ''''
FROM MassProcessing_Rules PR
WHERE PR.intRuleID = 10
)
View 5 Replies
View Related
Jan 10, 2014
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
View 3 Replies
View Related
Jul 11, 2014
I have Table Like this
t_id w_id t_codew_name
358553680A1100EVM Method Project
358563680A1110EVM Method Project
358453684A1000Basic
358463684A1010Basic
358473685A1020Detail
[Code] ....
View 1 Replies
View Related
Jul 14, 2007
Suppose we get two following tables:
Table A: [USER_ID] [varchar] (11) NOT NULL , [COURSE_ID] [varchar] (11) NOT NULL ,
Table B:
[COURSE_ID] [varchar] (11) NOT NULL , [COURSE_NAME] [varchar] (50) NOT NULL , [COURSE_NO] [varchar] (15) NULL , [BEGIN_DATE] [datetime] NULL , [END_DATE] [datetime] NULL , [CREATER] [varchar] (11) NOT NULL ,
and during the execution of my program, I can get the current use's id (USER_ID), say U0001.
How can I retrieve the result set containing [COURSE_NAME], [COURSE_ID], but the current user's id (U0001) have Not been assigned in Table A.
Thanks in advance.
Ricky.
View 3 Replies
View Related
Jul 14, 2001
I'm not getting it. The query below "e.g." is exactly as I think it should be except for the <Join Clause> as there is only the match on site name. I tried joing on the site name, but only got the sites in the log. I want all the site names even if they don't have a log item for a given date range.
I'd post my real query if it would help, it's just that I'm chopping up some strings and converting some time stamps to dates as varchar for readability for the final report, which is an MSExcel pivot table.
My original post lies below.
TIA
jEfFp...
Reply...
Date: Basic Query Help (reply)
RickD (Rick.Davis@Schroders.com)
7/11/01 6:41:26 AM
Make log an outer join to site.
e.g
select s.name, l.ondate from
site s
RIGHT OUTER JOIN log l ON <Join Clause> where
substring(id,3,6) in(select name from site)
and ondate between getdate()-8 and getdate()-1
You didn't seem to have any logical join between the tables, but i'm sure you have, just put this in the <Join Clause>.
Oh, and read BoL, it helps no end.....
------------
Original Post...
Jeff Proctor at 7/10/01 11:18:31 PM
my 7 day query....
select s.name, l.ondate from
site s, log l where
substring(id,3,6) in(select name from site)
and ondate between getdate()-8 and getdate()-1
This returns those sites that exist in the current log, however what I want is a result set that has a row for each name regardless if they are in the log.
I have 2 tables...
table1 sites
name
smithj
anandt
burtot
proctj
etc....
table2 log
id ondate
02anandt 07/01/01
02anandt 07/03/01
02anandt 07/03/01
01smithj 07/03/01
03burtot 07/05/01
01smithj 07/05/01
03burtot 07/05/01
etc.....
View 1 Replies
View Related
Aug 7, 2013
I am trouble getting the count of applications. In the below query MerApp.ApplicationID represents the applications. I'm trying to get a results of a count of applications for each sales team member.Before adding the COUNT() function. My query results show 3811 because it shows the sales team member numerous times because he has many applications with different IDs. For example sales team member A shows up in 75 rows because he has 75 different applications assigned to him.
SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], MerApp.Assignedto, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus
FROM MerchantApplication MerApp
INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID
INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
WHERE MerApp.ActiveStatus=1
GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus
I want sales team member A to show up once with the count of applications to be 75 because that's how many are assigned to him.
This didn't work for me:
SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], MerApp.Assignedto, COUNT (MerApp.ApplicationID) AS [Applications], stm.UserID, MerApp.ActiveStatus
FROM MerchantApplication MerApp
INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID
INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
WHERE MerApp.ActiveStatus=1
GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus
It didn't count the number of applications for each person.I have about 14 different sales team members. So I'm trying to have a query that produces 14 rows not 3811 rows
View 5 Replies
View Related
Nov 23, 2005
ARRRRRRRGGGGGHHHHH!!Please can you help, I'm going round the bend with this.I have a simple and small table called STOCKCATS, which I need to query toget back a dataset in a particular order, but although it looks simple Ican't get it to work. My table schema plus sample data to see the problemis as follows:DROP TABLE IF EXISTS `STOCKCATS`;CREATE TABLE `STOCKCATS` (`CATID` varchar(30) NOT NULL default '',`LEVEL` varchar(30) default NULL,PRIMARY KEY (`CATID`),KEY `indxCATEGORYID` (`CATID`));INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('A001',''),('A002','A001'),('A003','A001'),('A004','A001'),('A005','A001'),('PCHW01',''),('MHW01',''),('FD01',''),('ELEC01',''),('MHW02','MHW01');INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('MHW03','MHW01'),('MHW04','MHW01'),('MHW05','MHW01'),('PCHW02','PCHW01'),('PCHW03','PCHW01'),('PCHW04','PCHW01'),('PCHW05','PCHW01'),('PCSW01',''),('MSW01',''),('C001',''),('C002','C001'),('C003','C001'),('MV',''),('SUZ','MV'),('ALF','MV'),('PLASMA','ELEC01'),('T01','ELEC01'),('HEATING',''),('RAD','HEATING'),('P01',''),('B01','P01'),('BB','HEATING'),('FS','HEATING'),('WM','HEATING'),('AEROSOL',''),('SOLVENTS','AEROSOL'),('DGC','');INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('DGXWINDOWS','DGC'),('DGXEXTRA','DGC'),('DGXCON','DGC');As you can see from the table structure, this table consists of 2 fieldvalues. The 1st is the category code and the 2nd is the level is at. If acatid has a level of nothing, eg '', then it means that it is a root levelcategory. If a catid has a another cat's catid in it's level, eg B01 hasP01, then it is a sub-category of this category, eg B01 is a sub-cat of P01.All I want to do is query this table and bring back the data so thatalphabetically it goes root level cat A1, then all the sub-cats for thisroot level, then root level A2, then all sub-cats for this root level and soon. An example using the above would be as follows:^ ^ A to G of root level cats plus their sub-cats....HEATING << root levelBB << sub-cat of heatingFS << sub-cat of heatingWM << sub-cat of heating/ / I to Z of root level cats plus their sub-cats....A few posters kindly gave me a solution of ORDER BY COALESCE(CATID,LEVEL),CATID and I thought this had done it, but I was looking at the ('A001',''),('A002','A001'), ('A003','A001'), ('A004','A001'), ('A005','A001')entries as these naturally fell into place. If you use this order commandon the above you will see that ('P01','') and it's associated ('B01','P01')sub-cat just don't come together.Does any body have any ideas?ThanksLaphan
View 6 Replies
View Related
Jul 20, 2005
Ok, I'm a beginner so forgive me for my ignorance. Could someone helpme with this?tbl_x has two fields xid and xlistxlist being a list of numbers.....1, 5, 6, 8i want to create a new table from tbl_x that converts each number inthat xlist into a row in tbl_newso for exampletbl_x has 3 rowsxid xlist1 1, 4, 52 2, 3, 73 2, 1, 7i need a query or sql script that will convert that table with a listinto the following....tbl_newid xid xlid1 1 12 1 43 1 54 2 25 2 36 2 77 3 28 3 19 3 7
View 2 Replies
View Related
Nov 20, 2007
Hi I am having two very basic problems which are very frustrating.
I have read http://blogs.msdn.com/smartclientdata/archive/2005/08/26/456886.aspx and changed my DB to "Copy if newer" however when I use the connection string "|DataDirectory|MyDB.sdf" MyDB remains unchanged. (If I put the full path to MyDB in the connection string I will see the changes.) I have tried copying the DB file in the bindebug directory and opening the copy separately, and the changes still do not appear.
This leads me to my second issue. While trying to verify if the insert worked, I am trying to query the DB for the row I just inserted. I have found very little documentation describing how to do this; am I missing something/someplace obvious? Here is my code:
string strCommand = "SELECT * FROM Log WHERE Severity = @Sev AND Message = @Msg";
SqlCeResultSet resultSet = null;
SqlCeCommand sqlCommand = new SqlCeCommand(strCommand, pConn);
sqlCommand.Parameters.Add(SevParam);
sqlCommand.Parameters.Add(MsgParam);
try
{
pConn.Open();
resultSet = sqlCommand.ExecuteResultSet(ResultSetOptions.None);
}
finally
{
pConn.Close();
}
return !(resultSet == null);
resultSet always returns non-null, so I am clearly doing something wrong. Do I need to "read through" the resultSet to make sure there is something there? And if so, how? Should I be using a DataReader instead? (and if so how?) Am I going in the wrong direction? Just to be clear, I want to insert a row into my DB and then check that that row has been inserted by querying the DB.
Thanks!
=Spencer Whitman
View 1 Replies
View Related
Apr 28, 2006
Ok, I have a table that contains a number of columns, one of these columns contains a 'unitref' e.g.AC02/001D.
I import a new set of records, approx 7,000 per week in a DTS package from CSV Flat File into the table.
What I need to achieve at either the point of import of new data weekly, or once the new data is sitting in its final resting home, is a copy of the first two 2 Chars of the UnitRef, in the example above, this would make it 'AC' and then place that in a column named 'site_ref'.
Having posted the question on this forum relating to grabbing the first two chars of a value and placing them in a temporary table by utilising the Left(field,2) command in SQL (Kindly answered by CryptoKnight), I was wondering how I can do this possibly by using the inesrt into type command. I have many columns that get imported this is only a tiny step of many things that ideally would need to happen on an import,
Regards
View 1 Replies
View Related
May 19, 2015
i dont't know how to select row with max column value group by another column. I have T-SQL
CREATE PROC GET_USER AS
BEGIN
SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] ,
MAX(T.[Second Start Date]) AS [Max Second Start Date],
T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME
FROM (
[code]....
View 3 Replies
View Related
Oct 9, 2007
Hello Guys,Have been getting this error(
Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query.
) when running on the live environment but it was fine when run locally. If anyone has similar problem please let me know the fix you have done.
Thank you.
View 2 Replies
View Related
Jul 23, 2005
Hi,I have an SQL assignment to do and at my school we use SQL *Plus therehowever I don't have Oracle at home, where I would like to do the work ,so Iwas wondering whats the easiest way to get an SQL environment up so I cancode in that then just paste it into SQL *Plus later.I don't really want to install Oracle on my home pc and I was wondering ifthere are other IDE's for SQl that would fit my need for this.I discovered an instant SQL *Plus client that sounded really promising butwhen i unpacked it, it was just a load of dll's so I think it wasn't what Ithought it was.So does anyone know of anything that might be able to help me out here?Any advice much appreciated!Thanks--Ant
View 2 Replies
View Related
Jan 28, 2005
How would I convert this query to ANSI 92 standard?
select p.InternalID,
n.LastName
from tblPatient p,
tblName n
where p.PatientID = n.EntID and
n.EffDt = (
select max(n2.EffDt) from tblName n2 where n.EntID = n2.EntID and n2.EffDt <= getdate()
)
View 1 Replies
View Related