Thursday, October 10, 2013

[T-SQL] Conversion Error

[T-SQL] Conversion Error

Conversion Error

Posted: 09 Oct 2013 11:05 PM PDT

I have a error message in my queryMsg 245, Level 16, State 1, Line 72Conversion failed when converting the nvarchar value 'H-00001' to data type int.After using this,CAST(columnname as NVARCHAR(1000)) the error message persists.Please help.

Why are virtual tables in a FROM clause unable to reference aliases from the outer query?

Posted: 09 Oct 2013 02:29 AM PDT

I guess I am asking "Why doesn't a virtual table in a FROM clause behave like other subqueries in SELECT or WHERE that are able to make use of the aliases in the FROM clause?"Is it because the aliases in the outer FROM haven't been created yet because the virtual table has to be created first?Forgive me for not posting sample data. I was hoping this example would speak for itself. It fails with "multi-part identifier "ce.id" could not be bound". Also my formatting is being mangled a bit by quote paste.[quote]SELECT ce.id as ClientEnrollmentKey, max(cs.ID) as MaxClientStatusKeyFROM ClientEnrollment ceINNER JOIN ClientPacket cp on ce.ID = cp.ClientEnrollmentKeyINNER JOIN ClientStatus cs on cp.ID = cs.PacketKeyINNER JOIN (SELECT max(cp.Packet_Date) as MaxClientStatusDate FROM ClientPacket cp INNER JOIN ClientStatus cs on cp.ID = cs.PacketKey where cp.ClientEnrollmentKey = ce.id) t1 -- ce from outer from is invisible on cp.Packet_date = t1.MaxClientStatusDategroup by ce.id[/quote]

Data Insertion

Posted: 09 Oct 2013 11:18 PM PDT

Hi,I am stuck into a scenario of data insertion.please help[code="sql"]Create table table1( OfficeID int, ProjectID int, TaskID int,TaskName varchar(100) constraint pk_table primary key( OfficeID, ProjectID, TaskID))[/code]I am inserting data into this table through an XML file in which taskid is 0 always.[code="sql"]><INPUT><CID>17064</CID><OwnerID>17064</OwnerID><TaskId>0</TaskId>><MSG>test follow up</MSG></INPUT><INPUT><CID>17064</CID><OwnerID>17064</OwnerID><TaskId>0</TaskId>><MSG>test follow up 123</MSG></INPUT>[/code]Everytime when data gets insert into table1 taskid should be max on basis of officeid and ProjectID. I am storing this xml into a temp table then put join on base table with this temp table.Output desired:OfficeID PRojectID TaskID TaskName17064 17064 1 Test follow up1 ----- Already exist in table17064 17064 2 Test follow up217064 17064 3 Test follow up 123

Capitalizing only first letter of each Word in a Column using Query

Posted: 09 Oct 2013 07:00 PM PDT

Hi , I am Having a Column full of Capital Letter words like "ABC DEF GHI " , i want to convert that into "Abc Def Ghi" Please help me out its very Urgent !! I have googled it but i am getting all Stored procedures i dont want Stored procedures . I just want to use update command and finish it . Please help me out .Thank you

String Split and LEFT OUTER JOIN in Two Tables

Posted: 09 Oct 2013 07:44 AM PDT

Hello EveryoneI am working on an issue that involves string splitting. It really bothers me that people will Not design a database correctly, and ends up storing a delimited list in a single column.I do have the function named "DelimitedSplit8K" I love that function.The thing I am working on is that I need to split two different strings, place the now "rows" of data into table, each of their own.(Done) Using a LEFT OUTER JOIN, select the rows that are in one table, and not in the other.(Need Assistance)I know how to use the string splitter, and it works fine. But I am having trouble performing the LEFT OUTER JOIN.[code="sql"]CREATE TABLE #FirstDataString( FDataString varchar(250))CREATE TABLE #SecondDataString( SDataString varchar(250))INSERT INTO #FirstDataString( FDataString)VALUES( '1,3,6,8,9,10,12,13,15,16,20,21,24,26,28,31,33,34,35,49,51,52,53,57,58,61,63,66,69,70,71,75,76,77,78,79,81,82,85,88,90,91,92,93,94,95,96,97,98,99,100,105,107,110,111,112,118,122,124,125')INSERT INTO #SecondDataString( SDataString)VALUES( '1,3,5,8,9,10,13,15,16,20,24,26,28,31,33,35,49,51,52,57,58,61,63,69,70,75,76,77,78,79,81,85,88,90,91,92,94,95,96,97,98,100,105,110,111,118,122,125')SELECT Item AS FirstItemFROM #FirstDataString fCROSS APPLY dbo.DelimitedSplit8k(F.FDataString,',') splitSELECT Item AS SecondItemFROM #SecondDataString sCROSS APPLY dbo.DelimitedSplit8k(S.SDataString,',') splitDROP TABLE dbo.#FirstDataString;DROP TABLE dbo.#SecondDataString;[/code]I hope that someone can assist me with this query. I feel that I am close, but something is just not making it, and I cannot figure that part out.Thank You in advance for all your assistance, comments and suggestions.Andrew SQLDBA

query to order by multiple columns

Posted: 09 Oct 2013 04:33 AM PDT

I have a query to pull the first contact of students.The table has all contacts like parent/guardian, friends family, emergency contact etc.I would like to the first priamry contact in this order, 1. initial contact, 2. same as student address and also have to be parents, 3. live with and also a parent, 4. parents 5. friends.I don't know how to pull 2 and 3 . because it looks like it needs to concacenate the columns.here is my initial querySelect Min(U2.Id) Keep (Dense_Rank First Order By U2.Initial_Contact Desc, U2.Same_As_Students_Address Desc,u2.lives_with DESC,U2.Guardian Desc) From ContactsThanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog