I am trying to build a query on a SQL2000 text field which contains XML
string. The query is like "select requestnumber from history where
requestnumber is like '%re1%'". As you can see in the following sample
records, the xml string has database structure and the requestnumber is
a node of the XML. I wonder if it is possible to have SQL server parse
this field and allow me to do the query. If not, any suggestion would
be appreciated as to how to store XML data in SQL2000. I am not sure
if I misused the SQL2000 XML feature correctly. So far I pass the raw
query result to ADO and manipulate it in XMLDOM.
The table is to capture history of changes in any record in my
database. So I need to keep it simple so any record from any table can
be stored in here. The structure of the table is like this:
sysObjectNumber(int, not null)
recordKeyValues(char(30), not null)
archiveTime(datetime, not null)
history(text, null)
A sample record would be like the following:
sysObjectNumber recordKeyValues arvhiveTime History
=============== =============== =========== =======
Hi,I been reading various web pages trying to figure out how I can extract some simple information from the XML below, but at present I cannot understand it. I have a MS SQL 2005 database with which contains a field of type text (external database so field type cannot be changed to XML)The text field in the database is similar to the one below but I have simplified it by remove many of the unneeded tags in the <before> and <after> blocks. I also reformatted it to show the structure (original had no spaces or returns) For each text field in the SQL table contain the XML I need to know the OldVal and the NewVal. <ProductMergeAudit> <before> <table name="table1" description="Test Desc"> <product id="OldVal"> </table> </before> <after> <table name="table1" description="Test Desc"> <product id="NewVal"> </table> </after></ProductMergeAudit>
I am trying to build a simple search engine using Sql Server 2000 to scan information about approximatelly 20.000 products.
Heres what I am doing:
I created a table called keywords that contains a reference for each product.
keyword -> varchar(100) items -> Text
keyword data example:
[keyword] [items] car 1, 3, 5, 7 blue 3, 5 compact 1,7
I am not using clustered index.
To search basically I run the "AND" or "OR" to select the keywords I want to target. I need to run another select that would compare the data in the items field depending of the condition selected. If "AND" clause is used I would need to compare all the items that contains the same reference, for example:
looking for car compact using "AND" clause result = 1
looking for car compact using "OR" clause result = 1,3,5,7
There is no table that holds references. The items are stored in a text field in the keyword table. I can compare data using script like AsP spliting the items by comma or space, but that can be too slow and use up a lot of RAM. Another solution would be to use a table to hold the references but that would affect performance dramatically because of the large number of records created and storage space used. One example, if I have 60.000 keywords and each keyword has an avereage of 200 references, I would have to generate 12 milion records.
I want to know if there is a function or routine in SQL server to compare matched references on the fly in the server between two or more fields and how should I do it.
In addition, in this scenario, how should a clustered index help?
Please let me know if you come across any name strings this function cannot parse. CREATE function FormatName(@NameString varchar(100), @NameFormat varchar(20)) returns varchar(100) as begin --blindman, 11/04 --FormatName parses a NameString into its component parts and returns it in a requested format. -- --@NameString is the raw value to be parsed. --@NameFormat is a string that defines the output format. Each letter in the string represents --a component of the name in the order that it is to be returned. --[H] = Full honorific --[h] = Abbreviated honorific --[F] = First name --[f] = First initial --[M] = Middle name --[m] = Middle initial --[L] = Last name --[l] = Last initial --[S] = Full suffix --[s] = Abbreviated suffix --[.] = Period --[,] = Comma --[ ] = Space
--Example: select dbo.Formatname('Reverend Gregory Robert Von Finzer Junior', 'L, h. F m. s.') --Result: 'Von Finzer, Rev. Gregory R. Jr.'
--Test variables -- declare@NameString varchar(50) -- declare@NameFormat varchar(20) -- set@NameFormat = 'L, h. F m. s.' -- set@NameString = 'Reverend Gregory Robert Von Finzer Junior'
--Prepare the string --Make sure each period is followed by a space character. set@NameString = rtrim(ltrim(replace(@NameString, '.', '. '))) --Eliminate double-spaces. while charindex(' ', @NameString) > 0 set @NameString = replace(@NameString, ' ', ' ') --Eliminate periods while charindex('.', @NameString) > 0 set @NameString = replace(@NameString, '.', '')
--If the lastname is listed first, strip it off. set@TempString = rtrim(left(@NameString, charindex(' ', @NameString))) if@TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') set @TempString = rtrim(left(@NameString, charindex(' ', @NameString, len(@TempString)+2))) ifright(@TempString, 1) = ',' set @LastName = left(@TempString, len(@TempString)-1) iflen(@LastName) > 0 set@NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))
--Get rid of any remaining commas while charindex(',', @NameString) > 0 set @NameString = replace(@NameString, ',', '')
--Get Honorific and strip it out of the string set@TempString = rtrim(left(@NameString, charindex(' ', @NameString + ' '))) if@TempString in ('MR', 'MRS', 'MS', 'DR', 'Doctor', 'REV', 'Reverend', 'SIR', 'HON', 'Honorable', 'CPL', 'Corporal', 'SGT', 'Sergeant', 'GEN', 'General', 'CMD', 'Commander', 'CPT', 'CAPT', 'Captain', 'MAJ', 'Major', 'PVT', 'Private', 'LT', 'Lieutenant', 'FATHER', 'SISTER') set @Honorific = @TempString iflen(@Honorific) > 0 set@NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))
--Get Suffix and strip it out of the string set@TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))) if@TempString in ('Jr', 'Sr', 'II', 'III', 'Esq', 'Junior', 'Senior') set @Suffix = @TempString iflen(@Suffix) > 0 set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString)))
if @LastName is null begin --Get LastName and strip it out of the string set@LastName = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))) set@NameString = rtrim(left(@NameString, len(@NameString) - len(@LastName))) --Check to see if the last name has two parts set@TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))) if@TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') begin set @LastName = @TempString + ' ' + @LastName set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString))) end end
--Get FirstName and strip it out of the string set@FirstName = rtrim(left(@NameString, charindex(' ', @NameString + ' '))) set@NameString = ltrim(right(@NameString, len(@NameString) - len(@FirstName)))
--Anything remaining is MiddleName set@MiddleName = @NameString
--Create the output string set@TempString = '' while len(@NameFormat) > 0 begin if @IgnorePeriod = 'F' or left(@NameFormat, 1) <> '.' begin set @IgnorePeriod = 'F' set @TempString = @TempString + case ascii(left(@NameFormat, 1)) when '72' then case @Honorific when 'Dr' then 'Doctor' when 'Rev' then 'Reverend' when 'Hon' then 'Honorable' when 'Maj' then 'Major' when 'Pvt' then 'Private' when 'Lt' then 'Lieutenant' when 'Capt' then 'Captain' when 'Cpt' then 'Captain' when 'Cmd' then 'Commander' when 'Gen' then 'General' when 'Sgt' then 'Sergeant' when 'Cpl' then 'Corporal' else isnull(@Honorific, '') end when '70' then isnull(@FirstName, '') when '77' then isnull(@MiddleName, '') when '76' then isnull(@LastName, '') when '83' then case @Suffix when 'Jr' then 'Junior' when 'Sr' then 'Senior' when 'Esq' then 'Esquire' else isnull(@Suffix, '') end when '104' then case @Honorific when 'Doctor' then 'Dr' when 'Reverend' then 'Rev' when 'Honorable' then 'Hon' when 'Major' then 'Maj' when 'Private' then 'Pvt' when 'Lieutenant' then 'Lt' when 'Captain' then 'Capt' when 'Cpt' then 'Capt' when 'Commander' then 'Cmd' when 'General' then 'Gen' when 'Sergeant' then 'Sgt' when 'Corporal' then 'Cpl' else isnull(@Honorific, '') end when '102' then isnull(left(@FirstName, 1), '') when '109' then isnull(left(@MiddleName, 1), '') when '108' then isnull(left(@LastName, 1), '') when '115' then case @Suffix when 'Junior' then 'Jr' when 'Senior' then 'Sr' when 'Esquire' then 'Esq' else isnull(@Suffix, '') end when '46' then case right(@TempString, 1) when ' ' then '' else '.' end when '44' then case right(@TempString, 1) when ' ' then '' else ',' end when '32' then case right(@TempString, 1) when ' ' then '' else ' ' end else '' end if ((ascii(left(@NameFormat, 1)) = 72 and @Honorific in ('FATHER', 'SISTER')) or (ascii(left(@NameFormat, 1)) = 115 and @Suffix in ('II', 'III'))) set @IgnorePeriod = 'T' end set @NameFormat = right(@NameFormat, len(@NameFormat) - 1) end
USE TESTTABLE GO CREATE TABLE Example ( Description varchar(500)) GO INSERT INTO [TESTTABLE].[dbo].[Example] ([Description]) VALUES ('hello This is a test query.
[Code] ....
I want to extract three things from the above data stored ina single field
1. LAST NAME (All characters before COMMA till a special character is met) 2. FIRST NAME (All characters AFTER COMMA till a special character is met) 2. ID (All numbers after the word ID. Ignore all special characters before and after the numbers)
The Output will be :
FIRST NAME LAST NAME ID John Doe 123456 John Doe 123456 John Doe 123456
How can I use substrings to extract this data in SQL 2005
I have a table, tblstudents that has the fields strfirstname and strlastname. The table was imported from MS Acess where they were storing the first name and the lastname in the same field. Sucks doesn't it? Anyway, I need to write a script to extract the first name from the lastname and insert it into the first name field which is blank. So far I figure I can use select into, but need to find a way to tell sql server to take the fart of the field after the comma (the first name) and to delete the comma becuase I won't need it anymore.
Help!
Select '%,' into tblclients as strfirstname from tblclients
I know this won't work, but I want to show you all I'm at least trying!
In MS Access 2000 if I have a String such as:Column1Delta CC: 123Charley CC: 234Foxtrot CC: 890and I wanted to extact just the numbers in to a field called CCI could use this formula in a calculated field:CC: Mid([Column1],Instr(1,[Column1],"CC")+3,50)resulting in:CC123234890Any idea on what the code should be within a view in SQL Server?also -- what is a good reference that can help with these types ofproblems.Any help appreciated!RBollinger
Hello, I need some help being pointed in the right direction. I have a field in my Customer table called Name that is "LastName,FirstName". I need to be able to return a result set with "FirstName,LastName". Any ideas?
Hello All,I'm trying to parse for a numeric string from a column in a table. WhatI'm looking for is a numeric string of a fixed length of 8.The column is a comments field and can contain the numeric string inany positionHere's an example of the values in the column1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-982) wed.kx10/26 Netrez 95860536Now I need to parse through these lines and return only the 8 digitnumbers in itThe result set should be2706893595860536This is what I've done so farDeclare @tmp table(Comments_Txt varchar(255))Insert into @tmpselect Comments_Txt from Reservationselect * FROM @tmp where Comments_Txtlike ('%[0-9][0-9][0-9][0-9][0-9][0**9]%')But it returns the entire comments field in the result set. What I needis a way to return just those 8 digits.Any Ideas??Thanks in advance!!!
Hello,I am loading data from our MS Active Directory into our datawarehouse. (check out Mircosofts's Logparser, it can pull data fromADS, server event logs and more. It can also create text files or loaddirectly to SQL. Its free and a pretty useful tool)There is a field that contains the direct reports of a manager. Thedirect report users are delimited by a pipe symbol.I want to breakup the field into multple rows. There can be none, oneor many direct report users in this field.<disclaimer>This is a snippet of an example. This is only an example. I know thatI have not defined PK nor indexes. My focus is how to solve a problemof parsing a field that has multple values into multple rows.</disclaimer>Thanks for any help in advance.RobCREATE TABLE "dbo"."F_ADS_MANAGERS"("MANAGER_KEY" VARCHAR(255) NULL,"DIRECT_REPORTS_CN" VARCHAR(255) NULL);INSERT INTO F_ADS_MANAGERS (MANAGER_KEY, DIRECT_REPORTS_CN)VALUES ('CN=Marilette, 'CN=RobertD,OU=TechnologyGroup,DC=strayer,DC=edu|CN=RobertCamarda,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Mi chelleC,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Magnolia B,OU=TechnologyGroup,DC=strayer,DC=edu|CN=Lee K,OU=TechnologyGroup')I want to end up with 5 rows, 1 row for each user that is seprated bythe PIPE symbol.CN=Marilette CN=Robert D,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=RobertCamarda,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Michelle C,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Magnolia B,OU=TechnologyGroup,DC=strayer,DC=eduCN=Marilette CN=Lee K,OU=TechnologyGroup
how to separate names but i cannot make work in this case. The name field might contain anywhere from only one name with no delimeters to five names with four delimeters. I want to replace the delimeter with a space and reorder the names.
Original data format: Name2/Name1/Name3/Name4/Name5. Desired data format: Name1 Name2 Name3 Name4 Name5. Examples of source data
Company ABCDoe/JohnSmith/Jim/EtalJones/Jeff/Jr/& Sally Bush/Jim/Sr/Etal/Trustee
I have a third party application with a ntext field that I need to parse the data out of. The data looks like this: <xmlF><FNumber type="int">2421</FNumber><AttachmentPath type="string" /><RequesterId type="int">232</RequesterId><Requester type="string">John Smith</Requester><RequestDate type="DateTime">3/24/2008 11:23:27 AM</RequestDate</xmlF> The fieldname is Data and the tablename is ProcessData Again, this looks like xml, but the field type is ntext. I would like to create a view displaying the parsed data in fields. How would I go about parsing the data? Thanks.
I am trying to parse data separated through text (ie abc1, abc2, abc3, abc4, etc).
ID ParseData 1 [abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.] 2 [abc1.Gray Shoes/abc2.Striped jacket /abc3./abc4./abc5./abc6./abc7./abc8.] 3 [abc1.Blue jeans/abc2./abc3./abc4./abc5./abc6./abc7./abc8.]
New Data (abc1, abc2, abc3, etc each have a field in the new data set) ID ParseData abc1 abc2 abc3 abc4 abc5 abc6 abc7 abc8 1 [abc1.Pants...abc8.] Pants Orange hat Purple shirt 2 [abc1.Gray...abc8.] Gray Shoes Striped jacket 3 [abc1.Blue...abc8.] Blue Jeans
If I only want the data in between abc1 and abc2, between abc2 and abc3, etc, what would be the best way to do that?
My code so far looks like: DECLARE @string varchar(100) = '[abc1.Pants/abc2.Orange hat /abc3.Purple shirt /abc4./abc5./abc6./abc7./abc8.]', @searchString1 varchar(20) = 'abc1', @searchString2 varchar(20) = 'abc2';
SELECT newstring FROM dbo.SubstringBetween(@string,@searchString1,@searchString2);
This returns 'Pants.'How do I continue to parse between abc2 and abc3? between abc3 and abc4?And then continue to ID2?Should I be referencing the ParseData field instead of string of data that I want to parse?
I've got some machines that output text files after each shot of parts. I'd like to take the data in those files and parse it and insert it into a SQL Server database for future massaging. The text files look like the example I've posted below. Can SSIS parse out the set points and actual values even though the file isn't CSV or tab delimited and the data is kind of 'strewn' all over the report? Each report does have the exact same format so the report format doesn't change from report to report, just the data. Thanks in advance.
Ernie
WP4.57 C Y C L E P R O T O C O L
Order data 18.05.06 11:27:57
Order number : 2006 Recipe no. : 15761
Machine number : 7 Recipe name : Stabilizer Bar Innsulator
Machine Operator: 1257 Art.descrip.: Stabilizer Bar Grommet
Any way to parse out a text value (not varChar, using text data type) that is > than 8000 characters long? I'm looping through 1 big string passed to the DB that is pipe delimited, but I find myself needing the substring function to keep track of which segment I'm acting on (after an update, I then need to take that segment and remove it from the string)...but the subString function won't take anything larger than 8000 chars.
Say I have this string that is text data type...
'aaa|bbb|ccc|ddd|....'
..and so on, surpassing 8000 char length, how could you parse it out using the pipes as the delimter, then do an Update using that segment? Afterward, return to that string and find the next segment, then use it, and so on (in a loop). I tried using an update to set the string = replace(string, segmentJustUsed, '') to "erase" it, but replace can't take text as the datatype. Any help? Hope this isn't to confusing.
I need to parse some text files and load them to database - these files are mostly CSV files or fix width columns format and the column names (first line) may vary in text (e.g. different abbreviation), order and extra columns, etc.
Is it a good idea have this done using script task of SSIS? How it compare to Perl on performance? Or any other tools good for this?
I have a data field in a SSRS Report that contains the requestor's User Id. Their ID is prefixed with "PRIV"...And I'm assuming that is the direct result of the network domain. I need to create a SSRS expression to determine if the User ID is prefixed with "PRIV" and then parse that out and use what's behind the "" as their true User Id.
example...."PRIVID123456" should appear as "ID123456" in the report data line.
You have helped me resolved my previous problem with the LIKE statement, and now I'm running into this TEXT STRING problem that I desperately need your help and guidance again.
The following is the set of various descriptions in a PRODUCT_DESC field. I need to be able to calculate the Squared Meter of these products. As you can see, I need to be able to extract the part in the middle (like 2x60YD, etc.) for each record and perform some sort of calculation and conversion. The problem is that I can't find a way to do this effectively. Can someone please help me? Thanks.
HI All. I'm trying to tweak the Transfer Logins task to exclude Windows Logins that are local to the Server (e.g. servernameusername) which obviously can't be transferred off the server. Annoying that we have a couple of local Logins on this system instead of all Domain Groups, but we're stuck with them due to firewall issues, and a policy excluding SQL Logins.
My idea is to create a text file as part of my Package that lists Logins to be Excluded From the Transfer - I think I then need to create a New File Connection to the Text File as a Connection Manager, then somehow get that data into a Variable, and then use an Expression to populate the 'LoginsList' Collection from syslogins where loginame not equal to logins in my textfilevariable?
Or maybe I'm over complicating this, and there's an easier solution? Lots of info in Books Online about Expressions and Variables, but having trouble finding examples that I can use. As a DBA, this is my first foray into SSIS, and as you can possibly tell, I'm floundering....
SELECT SID, SAmAccountName, DOMAIN, EmployeeID, CustAtr, DisplayName, UPN, Date, flag, Date_Mod, Date_Exp, IsActive, OU, Description, IDType FROM dbo.CORP_EMP_IDS as corp_emp_ids WHERE (EmployeeID BETWEEN 'A' AND 'Z') AND (IsActive = 1) AND (EmployeeID NOT IN ('gen', 'G', 'M', 'S', 'T', 'TT', 'AdminAcct', 'TestAcct', 'ConfRoom', 'AcctAdmin')) AND (Date_Exp > GETDATE() OR Date_Exp = '') AND (SAmAccountName NOT LIKE '%$%')
UNION
SELECT SID, SAmAccountName, DOMAIN, EmployeeID, CustAtr, DisplayName, UPN, Date, flag, Date_Mod, Date_Exp, IsActive, OU, Description, IDType FROM dbo.CORP_EMP_IDS AS CORP_EMP_IDS_1 WHERE (CustAtr BETWEEN 'A' AND 'Z') AND (IsActive = 1) AND (CustAtr NOT IN ('gen', 'G', 'M', 'S', 'T', 'TT', 'AdminAcct', 'TestAcct', 'ConfRoom', 'AcctAdmin','AdminAccts')) AND (Date_Exp > GETDATE() OR Date_Exp = '') AND (SAmAccountName NOT LIKE '%$%')
Im a programmer for an university webportal which uses php and msssql. When an user creates a new entry and his text is too long the entry is cut short and weird characters appear at the end of the entry.
For example: http://www.ttz.uni-magdeburg.de/scripts/test-messedb/php/index.php?option=show_presse&funktion=presse_show_mitteilung&id=333
How can I set the text limit to unlimited? Could it be something else? Is there a way of splitting an entry to several text fields automatically?
Thanks in advance for any help you can give me, Chris
In SQL 2012, this fails with the error message, cannot find the text qualifer for field.
To get around this, we are having to import the data into a Dirty Data column of aTEMP table, ID, Dirty Data, Clean data - perform multiple updates and change the text qualifier and ensure they are only changed in the right places so we can keep the ". In this example, we changed the text qualifier to PIPES.
After these updates, we then export the data from CLEAN data back out to CSV, then reimport it into the origional destination table with a new text qualifer.
I have a column in a table that has a type TEXT,when I pull the length of a row it returns 88222 but when I select from that column it dows not show all the text in the result set.
I'm importing an Access database to SQL Server 2000. The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all).
I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data.
Is this some sort of an encoding problem that arose during database import? I would appreciate any pointers.
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
i've a reasonable amount of experience with MS Access and less experience with SQL Server. I've just written an .NET application that uses an SQL Server database. I need to collate lots of data from around the company in the simplest way, that can then be loaded into the SQL Server database.
I decided to collect the info in Excel because that's what most people know best and is the quickest to use. The idea being i could just copy and paste the records directly into the SQL Server database table (in the same format) using the SQL Server Management Studio, for example.
Trouble is, i have a problem with line feed characters. If an Excel cell contains a chunk of text with line breaks (Chr(10) or Chr(13)) then the copy'n'paste doesn't work - only the text up to the first line break is pasted into the SQL Server database cell. The rest is not pasted for some reason.
I've tried with MS Access too, copying and pasting the contents of a memo field into SQL Server database, but with exactly the same problem. I've tried with 'text' or 'varchar' SQL Server database field formats.
Since i've no experience of using different types of databases interacting together, can someone suggest the simplest way of transferring the data without getting this problem with the line feeds? I don't want to spend hours writing scripts/programs when it's just this linefeed problem that is preventing the whole lot just being cut'n'pasted in 5 seconds!
Can anyone point me any solution how to export a MEMO field from an Access database to a TEXT field from an MS SQL Server 2000. The import export tool from SQL server doesn't import these fields if they are very large - around 9000 characters.
I am very new to using SQL. Our department usually uses Brio to query the various databases under our control. However, I have recently come against a problem that prompted me to create a custom SQL query which works well as far as it goes. My problem is looking for specific conditions in billing information I receive monthly. I would like to compare on of the date fields contained in the database with a field in the form of YYYYMM (200710, for October 2007) I have created a custom column generator that forms a date from the YYYYMM. I would like, however, do the translation on the fly and make the comparison during the query. The problem is that query without the date check returns a mass of data, only about 1 percent of which is what I want.
The beginning of the SQL query looks like this:
FROM From.T_Crs_Tran_Dtl WHERE T_Crs_Tran_Dtl.Crs_Bill_Yr_Mo IN ('200710', '200711', '200712') AND ((T_Crs_Tran_Dtl.Crs_Cde IN ('1G', '1V') AND (T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND (T_Crs_Tran_Dtl.Prev_Stats_Cde IN (' ', 'TK', 'TL') AND T_Crs_Tran_Dtl.Cur_Stats_Cde IN ('TK', 'TL') AND T_Crs_Tran_Dtl.Std_Tran_Typ_Cde='B') OR (T_Crs_Tran_Dtl.Prev_Stats_Cde='UN' AND T_Crs_Tran_Dtl.Cur_Stats_Cde='XX' AND€¦
It is the €œ(T_Crs_Tran_Dtl.Dptr_Dte < LastDay(ToDate(Substr ( Crs_Bill_Yr_Mo, 5, 2 )& "/1/"&Substr ( Crs_Bill_Yr_Mo, 1, 4 )))) AND€? part of the query that is just plain wrong. The business part of this statement takes the YYYYMM field and turns it into a date which is the last day of YYYYMM.
I hope someone out there can help me with making this comparison.
