Re: split datatable column using c# - Database Discussion Boards

Re: How to change multiple date formats in Sql server

Mycroft Holmes6-Aug-14 14:16

6-Aug-14 14:16

If you convert the garbage you have to a single string format and store it as varchar you are perpetuating the same garbage as you are having to deal with now. USE THE DATETIME DATA FORMAT.

And yes I did mean to shout.

You might need to do a number of passes through the data using the suggestion from the other replies to convert the data. Records that are ambiguous may require the mark I eyeball to get the context of the date from other data in the record.

Altogether a thoroughly nasty job 100% caused by not storing the data in the correct format. I know it is a legacy app but if you can add new fields in the correct format then your life will be possible and the users cannot repeat the input of garbage.

Never underestimate the power of human stupidity
RAH

Re: How to change multiple date formats in Sql server

AzeeM_R11-Aug-14 8:14

AzeeM_R

11-Aug-14 8:14

Hi thanks for your help
we have to do it fast as we did not have time so i just copy past all values of column and paste in excel file and change the desired format Big Grin | :-D

and paste it in database Smile | :)

Handling Duplicate records

Ambertje5-Aug-14 4:35

Ambertje

5-Aug-14 4:35

Hello everyone,

I came across a problem when I loaded my records from SSIS to SQL DB.
My source file is a txt file.

I use 5 fields to make my records unique but unfortunately there are some records that can't be made unique.
Even the records that aren't unique must be loaded into my table although in SSIS the duplicate records are written to an error log and are excluded out of the load into the DB.

This is a small example of a few records:
record 1 and 2 are unique, 3 and 4 not.

CSS

HEADER  WH  DATE_CREATED    TIME_CREATED    PO  RECEIPT ARTICLE QTY_PBS CW  CODE_DATE_RECEIVED  SYSTEM_FLAG QTY_PBL FLOW_RECEIVING_FLAG FLOW_DEMAND_FLAG    VENDOR_ID   SHIPPING_UNIT   WH_FROM AANT_PALLETS    SSCC_NR EMPTY_FACTURATION_FORM
1010    3   30/07/2014  11:49:24    861819693   71663   1610570 4   36507   18/08/2014  S   0   N   N   B986        77000   0   7   054001090102087337
1010    3   30/07/2014  11:49:24    861819693   71663   6750    1   0   18/08/2014  S   0   N   N   B986        77000   0   7   054001090102087337  I
1010    3   30/07/2014  11:49:24    861819693   71663   6681    4   0   18/08/2014  S   0   N   N   B986        77000   0   7   054001090102087337  I
1010    3   30/07/2014  11:49:24    861819693   71663   6681    16  0   18/08/2014  S   0   N   N   B986        77000   0   7   054001090102087337  I

In SQL I use a View to transform the data and load only the data I need from the txt file:

SQL

ALTER VIEW [dbo].[F_Received_Colli_VW]
AS
SELECT
     CAST(NULL AS Numeric(8, 0))                                                                AS DT_Start_Dates
    , CAST(NULL AS NVARCHAR(6))                                                                 AS TI_Start_Times
    , CAST(NULL AS Numeric(8, 0))                                                               AS DT_Code_Dates
    , NULL                                                                                      AS FK_Article
    , NULL                                                                                      AS FK_FlowType

    , CAST(CAST(RIGHT(dbo.AD.DATE_CREATED, 4) + SUBSTRING(dbo.AD.DATE_CREATED, 4, 2)
        + LEFT(dbo.AD.DATE_CREATED, 2) AS int) AS Numeric(8, 0))                                AS CREATE_DATE

    , CAST(dbo.LPAD(LEFT(dbo.AD.TIME_CREATED, 2) + SUBSTRING(dbo.AD.TIME_CREATED, 4, 2)
        + RIGHT(dbo.AD.TIME_CREATED, 2) , 6, '0') AS NVARCHAR(6))                               AS CREATE_TIME

    , CAST(CAST(RIGHT(dbo.AD.CODE_DATE_RECEIVED, 4) + SUBSTRING(dbo.AD.CODE_DATE_RECEIVED, 4, 2)
    + LEFT(dbo.AD.CODE_DATE_RECEIVED, 2) AS int) AS Numeric(8, 0))                              AS CODE_DATE

    , CAST(dbo.AD.VENDOR_ID AS nvarchar(255))                                                   AS Vendor_ID_Cd
    , CAST(CASE WHEN dbo.AD.WH IS NULL THEN 0 ELSE dbo.AD.WH END AS nvarchar(255))              AS WH_Cd
    , dbo.AD.PO                                                                                 AS BK_PO_Cd
    , dbo.AD.RECEIPT                                                                            AS BK_Receipt_Cd

    , CAST(dbo.Devide_Shipping_Unit(dbo.AD.SHIPPING_UNIT, dbo.AD.ARTICLE) AS Nvarchar (255))    AS BK_Shipping_Unit_Cd

    , dbo.AD.SSCC_NR                                                                            AS BK_License_Plate_Cd
    , dbo.AD.ARTICLE                                                                            AS BK_Article_Cd

    , CAST(dbo.Devide_Shipping_Unit(dbo.AD.SHIPPING_UNIT, dbo.AD.ARTICLE) AS Nvarchar (255))    AS SHIPPING_UNIT

    , dbo.AD.ARTICLE

    , CASE DWH.dbo.D_Article.FlowType WHEN 'SAP' THEN ISNULL(CAST(dbo.AD.QTY_PBS AS int), 0)
        ELSE ISNULL(CAST(dbo.AD.QTY_PBL AS int), 0) END                                         AS M_Nr_Of_Colli

    , CAST(ISNULL(dbo.AD.CW, 0) AS numeric(15,2)) / 100                                         AS M_Received_Weight
    , 1                                                                                         AS M_Nr_of_Pallets

FROM
(SELECT        dbo.GETRESTARTTIMESTAMP(N'F_Received_Colli') AS restartTS) AS tmp CROSS JOIN
            dbo.AD LEFT OUTER JOIN
            DWH.dbo.D_Article ON (dbo.AD.ARTICLE = DWH.dbo.D_Article.BK_Article_Cd) and CAST(dbo.Devide_Shipping_Unit(dbo.AD.SHIPPING_UNIT, dbo.AD.ARTICLE) AS int) = Cast(DWH.dbo.D_Article.BK_Unit_Ship_Case_Cd AS int) 
WHERE    (dbo.AD.HEADER = '1010')

The fieldNames starting with BK_ are the fields I'm using to make a record unique. So I have 5 BK fields to make a record unique in the View:
1) BK_PO_Cd
2) BK_Receipt_Cd
3) BK_License_Plate_Cd
4) BK_Article_Cd
5) BK_Shipping_Unit_Cd

I think I can solve this by sorting on the BK's and then checking if the record exists.
If the record exists then the amount of field "QTY_PBS" should be added to the first record.
In case of my example it would be 1 record with an amount of 20 instead of 2 records with amount 4 and 16.

I tried to create a SP to resolve this issue but I'm failing misserably (lack of knowledge).

Can somebody please help me with this???

USE [DWH]
GO

/****** Object:  StoredProcedure [dbo].[SP_Check_Duplicates]    Script Date: 5/08/2014 16:19:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE Check_Duplicates

(
@License_Plate VARCHAR(18),
@Article VARCHAR(18),
@PO VARCHAR(20),
@Receipt VARCHAR(5),  
@M_Nr_Of_Colli Int  
)

AS

BEGIN

Set @License_Plate = '054001090102087337'        
Set @Article = '0006681'
Set @PO = '0861819693'
Set @Receipt = '71663'
Set @M_Nr_Of_Colli = 16

      IF EXISTS (SELECT [M_Nr_of_Colli] FROM F_Received_Colli_VW 
					WHERE [BK_License_Plate_Cd]=@License_Plate AND BK_Article_Cd = @Article)

      BEGIN

            INSERT INTO F_Received_Colli_VW VALUES(@License_Plate,@Article,@PO, @Receipt, @M_Nr_Of_Colli)

            PRINT 'New Record Insert Successfuly'

      END

      ELSE

      BEGIN

            PRINT 'Id All Ready Exist'

      END
END

Kind regards,
Ambertje

split datatable column using c#

Jaimin H. Soni4-Aug-14 22:31

Jaimin H. Soni

4-Aug-14 22:31

Hi i want to split column into multiple columns using c#.
Ex.

100 rows in COLUMN_A , convert into
COLUMN_A1(Firt 20 records)
COLUMN_A2(next 20 records)
COLUMN_A3(next 20 records)
COLUMN_A4(next 20 records)
COLUMN_A5(next 20 records)

is it posiblie?

modified 5-Aug-14 4:43am.

Re: split datatable column using c#

Eddy Vluggen5-Aug-14 2:35

Eddy Vluggen

5-Aug-14 2:35

Jaimin H Soni wrote:
is it posiblie?

Yes, simply read it row by row and stuff the contents in a column. That'd be assuming that a "row" could fit in a single "column".

Can you show an example row?

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: split datatable column using c#

Jaimin H. Soni6-Aug-14 1:12

Jaimin H. Soni

6-Aug-14 1:12

I have datatable that have one column in that 112 rows and i want to convert like that

col1 20 rows (1 to 20)
col2 20 rows (21 to 40)
col3 20 rows (41 to 60)
col4 20 rows (61 to 80)
col5 20 rows (81 to 100)
col6 20 rows (100 to 112)

Re: split datatable column using c#

Eddy Vluggen6-Aug-14 1:56

Eddy Vluggen

6-Aug-14 1:56

Then you'd have to add some columns to the datatable, and loop the records.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: split datatable column using c#

Jaimin H. Soni6-Aug-14 2:00

Jaimin H. Soni

6-Aug-14 2:00

I tried looping but i cannot get exactly what i want...

Re: split datatable column using c#

Eddy Vluggen6-Aug-14 2:30

Eddy Vluggen

6-Aug-14 2:30

Show us your code Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Help with XML

byka31-Jul-14 6:36

byka

31-Jul-14 6:36

Could you help figure out why my code not working?

DECLARE @xmlPolicyType XML
SET @xmlPolicyType= REPLACE('<?xml version="1.0" encoding="utf-16"?>
				<NewDataSet>
				<PolicyType>
					MED  
				</PolicyType>
				<PolicyType>
					SPORT
				</PolicyType>
				</NewDataSet>
				','utf-16','UTF-8')

DECLARE @xmlHandle INT 
	IF NOT @xmlPolicyType IS NULL
		BEGIN
				
						DECLARE @PolicyTypes TABLE 
							( 
							   PolicyType	CHAR(5) 
							) 

							
						/* Create the XmlDocument  */
						  EXEC sp_xml_preparedocument @xmlHandle output, @xmlPolicyType 
						    
						/*   Use the OPENXML method to query the XmlDocument starting at  /NewDataSet/PolicyType node. */ 
							--INSERT INTO @PolicyTypes 
							SELECT   PolicyType
							FROM OPENXML (@xmlHandle, '/NewDataSet/PolicyType',1) 
							WITH ( PolicyType CHAR(5) '@PolicyType'  )
						 
							/* Remove the document from memory */ 
							EXEC sp_xml_removedocument @xmlHandle
						 
						 
						 
					 
						 SELECT t.value('(PolicyType/text())[2]','nvarchar(120)')AS PolicyType 
						 FROM @xmlPolicyType.nodes('/NewDataSet/PolicyType')AS TempTable(t)

Re: Help with XML

PIEBALDconsult31-Jul-14 7:01

PIEBALDconsult

31-Jul-14 7:01

What about it isn't working? Is there an error message? What database system?

You'll never get very far if all you do is follow instructions.

Re: Help with XML

byka31-Jul-14 7:04

byka

31-Jul-14 7:04

Values is not selected

Re: Help with XML

Richard Deeming31-Jul-14 7:29

Richard Deeming

31-Jul-14 7:29

Your OPENXML query doesn't work because the node value contains white-space, which is not getting trimmed. Since you're only taking the first 5 characters, you'll get a string containing nothing by white-space.

Your .nodes() query doesn't work because you're getting the PolicyType nodes, and then trying to find the third PolicyType node within that node.

Try this:

SQL

DECLARE @xmlPolicyType XML
SET @xmlPolicyType= REPLACE('<?xml version="1.0" encoding="utf-16"?>
<NewDataSet>
<PolicyType>
     MED  
</PolicyType>
<PolicyType>
     SPORT
</PolicyType>
</NewDataSet>
','utf-16','UTF-8');

DECLARE @PolicyTypes TABLE 
( 
    PolicyType char(5) 
);

INSERT INTO @PolicyTypes
(
    PolicyType
)
SELECT
    LTrim(Replace(Replace(Replace(
        T.PolicyType.value('.', 'varchar(120)')
    , Char(13), ''), Char(10), ''), Char(9), ''))
FROM
    @xmlPolicyType.nodes('/NewDataSet/PolicyType') As T (PolicyType)
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Cannot create a foreign key reference (probably my fault)

Richard MacCutchan31-Jul-14 0:38

Richard MacCutchan

31-Jul-14 0:38

I create a new SQL CE database and try to add two tables with a key linkiing them. The first table is created successfully with the statement:

SQL

CREATE TABLE Classes(ClassId INTEGER NOT NULL PRIMARY KEY, Class NVARCHAR(64) NOT NULL )

and I add 3 rows to it successfully.

I then try to create the second table with a foreign key thus:

SQL

CREATE TABLE Accounts(Account NVARCHAR(64) NOT NULL, Telephone NVARCHAR(128), ClassId INTEGER FOREIGN KEY REFERENCES Classes(ClassId) )

but this fails with the message:

The constraint specified is not valid

What am I doing wrong?

Re: Cannot create a foreign key reference (probably my fault)

Simon_Whale31-Jul-14 1:27

Simon_Whale

31-Jul-14 1:27

Have a read of the first answer in this StackOverflow Question[^] on the same subject.

The first answer suggests that you remove the FOREIGN KEY part of your create table statement

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

Re: Cannot create a foreign key reference (probably my fault)

Richard MacCutchan31-Jul-14 1:36

Richard MacCutchan

31-Jul-14 1:36

Thanks for the reply, my syntax was wrong, Eddy found the answer.

Re: Cannot create a foreign key reference (probably my fault)

Eddy Vluggen31-Jul-14 1:28

Eddy Vluggen

31-Jul-14 1:28

Richard MacCutchan wrote:
What am I doing wrong?

Specifying the constraint, below code should work for ~~sqlite3~~;

The question is about SQL CE Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Cannot create a foreign key reference (probably my fault)

Richard MacCutchan31-Jul-14 1:39

Richard MacCutchan

31-Jul-14 1:39

Thanks Eddy, your suggestion did work (for SQL CE). I have been staring at this for over an hour and could not see the subtlety of the optional parameters in the documentation[^].

[edit]
For the record the correct syntax is:

SQL

CREATE TABLE Accounts(Account NVARCHAR(64) NOT NULL, Telephone NVARCHAR(128), ClassId INTEGER, FOREIGN KEY (ClassId) REFERENCES Classes(ClassId) )

[edit]

modified 31-Jul-14 9:18am.

Re: Cannot create a foreign key reference (probably my fault)

Eddy Vluggen31-Jul-14 11:08

Eddy Vluggen

31-Jul-14 11:08

Richard MacCutchan wrote:
your suggestion did work (for SQL CE).

Cool - one of those rare cases where both implemented the same sql-standard.

Makes me curious how easy (or hard) it is to switch from one to the other.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Insert into temptable ( #tmp1 ) that execute result from 1 procedure ( return 2 results )

LTMKH30-Jul-14 22:30

LTMKH

30-Jul-14 22:30

Dear All,

I would like to show you the following questions ( SQL server ):
- I have one procedure that return 2 result after execute
Eg:
create procedure spTest
as
begin
select * from table1
select * from table2
end

( this is just sample procedure that return 2 result after execute )
- I also have one temp Table name #tmpTable1, i want to insert into this temp table, but get result from procedure above, but i can't use this statement:

insert into #tmpTable1 exec spTest
( it will not work, because this procedure return 2 result )

Do you have any idea to get result from this procedure into my temp table?

Thank you in advance for your good idea...

Mr. LTM KH

Re: Insert into temptable ( #tmp1 ) that execute result from 1 procedure ( return 2 results )

Mycroft Holmes30-Jul-14 22:51

Mycroft Holmes

30-Jul-14 22:51

You have a some of choices, the obvious one is to split the child procedure into 2 procs, this is by far the better choice.

You could also use a global temp table in the child procs declare as ##TableName. IE create the global temp in the parent proc and call the insert proc, then consume the results in the parent proc. I would consider this a really crappy solution as it is fraught with problems.

You could also modify the child proc to use a UNION and return only 1 table (assumes if you are going to insert the data into 1 table then it is the same structure)

MS did a great disservice when they allowed multiple result sets to be returned from a single proc.

Never underestimate the power of human stupidity
RAH

Re: Insert into temptable ( #tmp1 ) that execute result from 1 procedure ( return 2 results )

LTMKH31-Jul-14 15:53

LTMKH

31-Jul-14 15:53

Dear Mycroft Holmes,

Thanks you so much for your solutions, I will test it with global temp table...
But this may not the best choice for me..

Regard

Mr.LTM-KH

Re: Insert into temptable ( #tmp1 ) that execute result from 1 procedure ( return 2 results )

Mycroft Holmes31-Jul-14 16:15

Mycroft Holmes

31-Jul-14 16:15