Inserting a Row into a recordset

Question

Inserting a Row into a recordset

alorenzini

SSCarpal Tunnel

Points: 4620
More actions
March 17, 2008 at 3:39 pm

#184161

I have the following code:
SELECTDISTINCT A.TSOrderType
,A.TSUserType
,A.InternalUserName
,A.OrderTypeXID
,List.[ListType] AS OrderCategory --D.OrderCategory
,List.[ListType] AS OrderType -- D.OrderType
,A.OrderStatusXID
--,C.OrderStatus
,A.OrderGroupType
,CONVERT(VARCHAR(10),A.ORderCreateDate,101) AS OrderCreateDate
,'G'+ CONVERT(VARCHAR(20),A.ORderGroupNumber) AS OrderGroupNumber
,A.OrderNumber
,A.PartOneTotal
,A.PartTwoTotal
,A.PartThreeTotal
,A.Hostcredit
,A.SampleAllowanceAmount
,Case
WHEN List.[ListType] IN ('Client')THEN A.TSODiscountAmount
ENDAS 'TSODiscounts'
,Case
WHEN List.[ListType] NOT IN ('Client')THEN A.ConsultantDiscountAmount
ENDAS 'NONTSODiscounts'
,A.ConsultantDiscountAmount
,MAX(B.[ShippedDate]) AS ShippedDate
,'ShipDate' =
CASE A.OrderStatusXID
WHEN 32 THEN Convert(nvarchar(10),Max(B.ShippedDate),101)
WHEN 16 THEN 'Partial'
WHEN 8 THEN 'Unshipped'
WHEN 128 THEN 'Unshipped'
END
,A.ShipToFirstName + ' ' +A.ShipToLastName AS ShipToName
,A.ShippingTotal
,A.OrderTotal
,A.TaxTotal
,(SELECT @ConsultantID) AS ConsultantID
,(SELECT TOP 1 FirstName + ' ' + LastName FROM [Consultant] c
WHERE c.ConsultantID = @ConsultantID) AS ConsultantName
INTO #OrderListing FROM Consultant H
LEFT OUTER JOIN [uvw_OrderListingSummary] A ON
h.consultantid COLLATE SQL_Latin1_General_CP1_CI_AS = a.[ConsultantID]
LEFT OUTER JOIN dbo.OrderFormLineItem AS B ON
A.OrderGroupNumber = B.OrderGroupNumber
ANDA.OrderNumber = B.OrderNumber
LEFT OUTER JOIN SharedDimension.dbo.DimOrderType AS D With (NoLock) On
A.OrderTypeXId = D.XID
LEFT OUTER JOIN [SharedDimension].dbo.[DimListingType] AS List With (NoLock) ON
A.[OrderTypeXID] = List.[DimOrderTypeXID]
ANDCASE
WHEN A.UserTypeXID IS NULL THEN -1
ELSE A.UserTypeXID
END =
CASE
WHEN List.DimUserTypeXID IS NULL THEN -1
ELSE List.DimUserTypeXID
END
WHEREa.ConsultantID = @ConsultantID AND
A.ORderCreateDate Between @StartDate AND @EndDate
OR a.[OrderCreateDate] IS NULL
GROUP BY
A.TSOrderType
,A.TSUserType
,A.InternalUserName
,A.OrderGroupType
,A.ORderCreateDate
,A.ORderGroupNumber
,A.OrderNumber
,A.PartOneTotal
,A.PartTwoTotal
,A.PartThreeTotal
,A.Hostcredit
,A.SampleAllowanceAmount
,A.ConsultantDiscountAmount
,A.TSODiscountAmount
,A.ShippingTotal
,A.TaxTotal
,A.OrderTotal
,A.ShipToFirstName
,A.ShipToLastName
,A.OrderStatusXID
,A.ShipToEmail
,A.OrderTypeXID
--,D.OrderCategory
--,D.OrderType
,List.[ListType]
--,C.OrderStatus
,A.ConsultantID
,A.ConsultantName
ORDER BY OrderCreateDate Desc
,OrderGroupNumber
,OrderNumber
This works fine but I also found out that I need to insert a row for each of the ListTypes regardless if thaey have data for them. Is there a way to achieve this?
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

The table DDL, some sample data (in the form of UNIONed INSERT statements), and expected output based on the sample data will help us help you figure out what you need.

Thanks,

😎

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 2

See attached xls it's easier than explaining:

Need to insert rows for the Order Types that have no dollar amounts.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

That is interesting, but I'm not going to guess at the table structer or generate test data based on the excel spreadsheet. Please take the time to read the following article. It provides excellent guidance on how to ask for help that will let us help you more easily.

From Jeff Moden's signature line:

For better, quicker answers, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/

😎

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 4

I am trying to create some test data based on the article you gave me a link to:

SELECT 'SELECT '

+ QUOTENAME(TSORDERTYPE,'''')+','

+ QUOTENAME(TSUserType,'''')+','

+ QUOTENAME(InternalUserName,'''')+','

+ QUOTENAME(OrderTypeXID,'''')+','

+ QUOTENAME(OrderCategory,'''')

+ QUOTENAME(OrderType,'''')

+ QUOTENAME(OrderStatusXID,'''')

+ QUOTENAME(OrderCreateDate,'''')

+ QUOTENAME(OrderGroupNumber,'''')

+ QUOTENAME(PartOneTotal,'''')

+ QUOTENAME(PartTwoTotal,'''')

+ QUOTENAME(PartThreeTotal,'''')

+ QUOTENAME(HostCredit,'''')

+ QUOTENAME(SampleAllowanceAmount,'''')

+ QUOTENAME(TSODiscounts,'''')

+ QUOTENAME(NONTSODiscounts,'''')

+ QUOTENAME(ConsultantDiscountAmount,'''')

+ QUOTENAME(ShippedDate,'''')

+ QUOTENAME(ShipDate,'''')

+ QUOTENAME(ShipToName,'''')

+ QUOTENAME(ShippingTotal,'''')

+ QUOTENAME(OrderTotal,'''')

+ QUOTENAME(TaxTotal,'''')

+ QUOTENAME(ConsultantID,'''')

+ QUOTENAME(ConsultantName,'''')

+ ' UNION ALL' FROM OrderListing

But I receive this error:

Msg 451, Level 16, State 1, Line 37

Cannot resolve collation conflict for column 1 in SELECT statement.

Any ideas?

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

From the select statement, can't really help. I need two things: the DDL for the table (or tables) including collation for character based fields (need to know what collation they are using), plus the defualt collation for the database (or databases if a cross database query).

Once we see that, we should be able to help resolve the collation error in your select statement.

😎

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 6

CREATE TABLE [dbo].[OrderListing](

[TSOrderType] [int] NULL,

[TSUserType] [int] NULL,

[InternalUserName] [nvarchar](150) NULL,

[OrderTypeXID] [int] NULL,

[OrderCategory] [nvarchar](20) NULL,

[OrderType] [nvarchar](20) NULL,

[OrderStatusXID] [int] NULL,

[OrderGroupType] [int] NULL,

[OrderCreateDate] [varchar](10) NULL,

[OrderGroupNumber] [varchar](21) NULL,

[OrderNumber] [int] NULL,

[PartOneTotal] [decimal](18, 2) NULL,

[PartTwoTotal] [decimal](18, 2) NULL,

[PartThreeTotal] [decimal](18, 2) NULL,

[Hostcredit] [decimal](18, 2) NULL,

[SampleAllowanceAmount] [decimal](18, 2) NULL,

[TSODiscounts] [decimal](18, 2) NULL,

[NONTSODiscounts] [decimal](18, 2) NULL,

[ConsultantDiscountAmount] [decimal](18, 2) NULL,

[ShippedDate] [datetime] NULL,

[ShipDate] [nvarchar](10) NULL,

[ShipToName] [nvarchar](511) NULL,

[ShippingTotal] [decimal](18, 2) NULL,

[OrderTotal] [decimal](18, 2) NULL,

[TaxTotal] [decimal](18, 2) NULL,

[ConsultantID] [varchar](20) NULL,

[ConsultantName] [nvarchar](101) NULL

) ON [PRIMARY]

The collation for the DB is SQL_Latin1_General_CP1_CI_AS

Is there a way to script the collation for the DDL?

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Yes: Tools, Options, scripting. There is an option under Table and View options to include collation. That should be set to true.

I tested it on one of my own tables, and it works.

😎

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 8

That worked thanks. This is the DDL for the table.

CREATE TABLE [dbo].[OrderListing](

[TSOrderType] [int] NULL,

[TSUserType] [int] NULL,

[InternalUserName] [nvarchar](150) COLLATE Latin1_General_CI_AS NULL,

[OrderTypeXID] [int] NULL,

[OrderCategory] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[OrderType] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[OrderStatusXID] [int] NULL,

[OrderGroupType] [int] NULL,

[OrderCreateDate] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[OrderGroupNumber] [varchar](21) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[OrderNumber] [int] NULL,

[PartOneTotal] [decimal](18, 2) NULL,

[PartTwoTotal] [decimal](18, 2) NULL,

[PartThreeTotal] [decimal](18, 2) NULL,

[Hostcredit] [decimal](18, 2) NULL,

[SampleAllowanceAmount] [decimal](18, 2) NULL,

[TSODiscounts] [decimal](18, 2) NULL,

[NONTSODiscounts] [decimal](18, 2) NULL,

[ConsultantDiscountAmount] [decimal](18, 2) NULL,

[ShippedDate] [datetime] NULL,

[ShipDate] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ShipToName] [nvarchar](511) COLLATE Latin1_General_CI_AS NULL,

[ShippingTotal] [decimal](18, 2) NULL,

[OrderTotal] [decimal](18, 2) NULL,

[TaxTotal] [decimal](18, 2) NULL,

[ConsultantID] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ConsultantName] [nvarchar](101) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Here is what i have found. You have a couple of columns that have a different collation from both the database as well as other columns in the table itself. The two collations are: SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS. The first is a SQL Server collation the second is a Windows collation. You need to make all the character values the same collation, and as only two column are using the Windows collation, I'd do the following for those two columns and see what happens:

QUOTENAME(InternalUserName collate SQL_Latin1_General_CP1_CI_AS,'''')

QUOTENAME(ShipToName collate SQL_Latin1_General_CP1_CI_AS,'''')

Also, it looks like you are missing +',' for many of the columns as you go down the select statement.

😎

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 10

This is getting fustrating. I the query ran but it returns nothing but nulls. However, I did create and insert statement for my missing Order Types and that seems to have fixed my primary issue.

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Art

If you switch your LEFT JOIN to dbo.OrderFormLineItem for a derived table which performs the aggregate to obtain the most recent shipping date, then you could get lucky and lose both the DISTINCT clause - which is defensive anyway - and possibly also the GROUP BY in your main query. Like this...

,B.MaxShippedDate AS ShippedDate

,'ShipDate' =

CASE A.OrderStatusXID

WHEN 32 THEN Convert(nvarchar(10),Max(B.ShippedDate),101)

WHEN 16 THEN 'Partial'

WHEN 8 THEN 'Unshipped'

WHEN 128 THEN 'Unshipped'

END

,A.ShipToFirstName + ' ' +A.ShipToLastName AS ShipToName

,A.ShippingTotal

,A.OrderTotal

,A.TaxTotal

,@ConsultantID AS ConsultantID

,(SELECT TOP 1 FirstName + ' ' + LastName FROM [Consultant] c

WHERE c.ConsultantID = @ConsultantID) AS ConsultantName

INTO #OrderListing

FROM Consultant H

LEFT OUTER JOIN [uvw_OrderListingSummary] A ON

h.consultantid COLLATE SQL_Latin1_General_CP1_CI_AS = a.[ConsultantID]

LEFT OUTER JOIN (SELECT OrderGroupNumber, OrderNumber, MAX(ShippedDate) AS MaxShippedDate

FROM dbo.OrderFormLineItem

GROUP BY OrderGroupNumber, OrderNumber) AS B

ON A.OrderGroupNumber = B.OrderGroupNumber

AND A.OrderNumber = B.OrderNumber

...but be warned, this is obviously untested.

If it works, then say so - there's a few other improvements possible here.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

alorenzini SSCarpal Tunnel Points: 4620 More actions · Answer 12

This is the solution iI implemented:

,Ord.ShipToFirstName + ' ' +Ord.ShipToLastName AS ShipToName

,ISNULL(Ord.ShippingTotal,0)

,ISNULL(Ord.OrderTotal,0)

,ISNULL(Ord.TaxTotal,0)

FROM(SELECTDISTINCT con.[ConsultantID]

,Con.FirstName + ' ' + Con.LastName AS ConsultantName

,List.ListType

FROM[Consultant] AS Con With (NoLock)

,(SELECT DISTINCT ListType FROM [SharedDimension].dbo.[DimListingType] With (NoLock) WHERE (ListType = @OrderTypeFilter OR @OrderTypeFilter IS NULL)) AS List

WHERECon.ConsultantID = @ConsultantID) AS Listing

LEFT OUTER JOIN [uvw_OrderListingSummary] AS Ord ON

Listing.[ConsultantID] = Ord.[ConsultantID]

AND listing.listtype = ord.listtype

ANDOrd.[OrderCreateDate] BETWEEN @StartDate AND @EndDate

Thanks,
Art
Database Analyst
Tastefully Simple, Inc.

alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Hi Art

Does the query give you the results you are expecting and have you checked that they are correct? Can you post the whole query please?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 14

Am I missing something or are you guys making this much more difficult than it should be? In general, you can do this:

select <column list>

from whatever-to-get-original-resultset

union

select <column list>

from whatever-to-append-to-resultset

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms