Solved

payments and trackingnumbers

Posted on 2011-03-17
18
274 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

691 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