Solved

payments and trackingnumbers

Posted on 2011-03-17
18
261 Views
Last Modified: 2012-05-11

CREATE TABLE [dbo].[trackingnumbers](
      [trackingnumberid] [int] IDENTITY(1,1) NOT NULL,
      [date] [datetime] NOT NULL CONSTRAINT [DF_trackingnumbers_date]  DEFAULT (getdate()),
      [orderid] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [trackingnumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [shipper] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [cost] [decimal](10, 2) NULL,
      [void] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [address] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK__trackingnumbers__2B3F6F97] PRIMARY KEY CLUSTERED
(
      [trackingnumberid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[payments](
      [paymentid] [int] IDENTITY(1,1) NOT NULL,
      [orderid] [int] NOT NULL,
      [dateentered] [datetime] NULL,
      [datebilled] [datetime] NULL,
      [type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [number] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [expiration] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CV2] [int] NOT NULL,
      [confirmation] [varchar](70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [resultcode] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [debit] [money] NOT NULL,
      [paymentinfo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ebayquantity] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [paymentfee] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [email] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [business] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_payments] PRIMARY KEY CLUSTERED
(
      [paymentid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]






I want every orderid from payments table that has type='visa'   that has an orderid in trackingnumbers table with more than one trackingnumber

but orderid is int in payments and varchar in trackingnumbers
0
Comment
Question by:rgb192
  • 6
  • 6
  • 6
18 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 35164061
Something like this?
select P.orderid
from dbo.payments P
where P.type = 'visa'
	and exists (select * from dbo.trackingnumbers TN 
			where TN.orderid = P.orderid
			having COUNT(*) > 1)

Open in new window

0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35165811
I will try to refrain from commenting on the practice of using different datatypes to store data that is supposed to be the same. ;-)

Compare the execution plan for my query to the plan for ValentinoV's query and see which is better.  (Mine uses the currently advisable syntax, though. ;-)
SELECT P.OrderID
FROM dbo.payments P
INNER JOIN dbo.tracking T
ON P.orderid = CONVERT(VarChar(50), T.orderid)
WHERE P.type = 'visa';

Open in new window

0
 

Author Comment

by:rgb192
ID: 35165840
select P.orderid
from dbo.payments P
where P.type = 'visa'
      and exists (select * from dbo.trackingnumbers TN
                  where TN.orderid = P.orderid
                  having COUNT(*) > 1)



Msg 248, Level 16, State 1, Line 1
The conversion of the varchar value '190365396412HRT' overflowed an int column. Maximum integer value exceeded.


because orderid is int in payments and orderid is varchar with some non int values in tracknumbers
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 35165864
Wow, then I think there's an issue with the plan here.  How are you going to join the tables if the order IDs are not comparable?  (I assumed that only the data type was different)
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35165881
That's why I explicitly contraolled the conversion and converted the integer instead of allowing the implicit conversion that, all too often, will try to convert the VarChar to an integer.
0
 

Author Comment

by:rgb192
ID: 35165902
8080 diver



SELECT P.OrderID
FROM dbo.payments P
INNER JOIN dbo.trackingnumbers T
ON P.orderid = CONVERT(VarChar(50), T.orderid)
WHERE P.type = 'visa';


same errror
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35165909
If all of the VarChar orderid's have some sort of trailing alpha characters then you should use something more like the following:

SELECT P.OrderID
FROM dbo.payments P
INNER JOIN dbo.tracking T
ON P.orderid like CONVERT(VarChar(50), T.orderid)
WHERE P.type = 'visa';

Open in new window

0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35165924
Shazbot!

I wrote the ON clause backwards from what I was thinking!
SELECT P.OrderID
FROM dbo.payments P
INNER JOIN dbo.tracking T
ON T.orderid like CONVERT(VarChar(50), P.orderid)
WHERE P.type = 'visa';

Open in new window

0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 35166014
8080_Diver: the conversion to varchar is indeed a good idea in this case.

However, I think one part of the requirement is currently missing: "that has an orderid in trackingnumbers table with more than one trackingnumber"
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 22

Expert Comment

by:8080_Diver
ID: 35166163
ValentinoV, Ah, indeed!

SELECT P.OrderID
FROM dbo.payments P
INNER JOIN 
(
SELECT T.orderid, COUNT(orderid)
FROM dbo.tracking T
GROUP BY T.orderid
HAVING COUNT(orderid) > 1
) T
ON T.orderid like CONVERT(VarChar(50), P.orderid)
WHERE P.type = 'visa';

Open in new window

0
 

Author Comment

by:rgb192
ID: 35166240
with more than one trackingnumber... is missing

so orders more than one trackingnumber have more than one row with the same trackingnumbers.orderid, payments.orderid
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 35166864
It's no longer missing in that last query by 8080_Diver.  That's what the HAVING line is for.
0
 

Author Comment

by:rgb192
ID: 35259717
8080_Diver

No column was specified for column 2 of 'T'.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35260056
rqb192,

So, give it a name. ;-)  That's the one that has the COUNT.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 35273201
Or remove it from the SELECT.  If you don't need to retrieve it, there's no reason to have it in the SELECT list (the HAVING is sufficient for the "more than one" requirement).
0
 

Author Comment

by:rgb192
ID: 35298633
>>So, give it a name. ;-)  That's the one that has the COUNT.
>>Or remove it from the SELECT.  If you don't need to retrieve it, there's no reason to have it in the SELECT list (the HAVING is sufficient for the "more than one" requirement).

I dont understand how to change the query
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 35301300
Here's the version with a name for column two (even though it's not used in the list of fields retrieved).
To clarify this a bit: the "list of fields retrieved" is found on line one in the code snippets.

SELECT P.OrderID
FROM dbo.payments P
INNER JOIN 
(    SELECT T.orderid, COUNT(orderid) as OrderCount
     FROM dbo.tracking T
     GROUP BY T.orderid
     HAVING COUNT(orderid) > 1
) T
ON T.orderid like CONVERT(VarChar(50), P.orderid)
WHERE P.type = 'visa';

Open in new window


And here's the version with the COUNT removed from the SELECT (as it's not used anyway):

SELECT P.OrderID
FROM dbo.payments P
INNER JOIN 
(    SELECT T.orderid
     FROM dbo.tracking T
     GROUP BY T.orderid
     HAVING COUNT(orderid) > 1
) T
ON T.orderid like CONVERT(VarChar(50), P.orderid)
WHERE P.type = 'visa';

Open in new window


If you compare both queries with the original one, you'll see the differences and learn how to name "calculated fields" in the process...

Hope that helps you a bit further?
0
 

Author Closing Comment

by:rgb192
ID: 35301564
thanks
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
create index to c1, c2 and c3 9 68
MS SQL page split per second is high 19 77
SQL Server 2012 express 24 36
Problem with SqlConnection 4 162
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now