Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

payments and trackingnumbers

Posted on 2011-03-17
18
Medium Priority
?
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

610 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