[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

payments and trackingnumbers

Posted on 2011-03-17
18
Medium Priority
?
286 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
 
LVL 1

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
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

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

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

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

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

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

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

608 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