SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Optimizing query
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  12:57:00  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
quote:
Originally posted by X002548

I'm wondering how that's possible

http://support.microsoft.com/kb/908711

Do you have replication?

I mean CORE has values UID right?

What does SELECT * FROM CORE WHERE UID IS NULL return

should be

(0) rows returned

YES BRETT, IT DOESNT have duplicate UIDs.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam





Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  12:58:11  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
Brett,

It is just the table names and nothing else to make it easier to understand.




quote:
Originally posted by X002548

quote:
Originally posted by rockingdesi

DPT_CRF is same as the table CRF....i was referring it as CRF instead of DPT_CRF....

DPT_CRF = CRF...both are same....just the names are different





Confusing things doesn't help us much

Are you changing the code



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam





Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  13:00:26  Show Profile  Reply with Quote
Well that's not looking for dups, rather nulls, as the error suggests

Just for kick, remove UID from the SELECT and the Column List, and lose the IDENTITY_INSERT Statement, and then try it

Or try this and see what it does


SELECT UID 
	, LEFT(ACCT_NUM_MIN,16) AS LEFT_ACCT_NUM_MIN
	, LEFT(ACCT_NUM_MAX,16) AS LEFT_ACCT_NUM_MAX
	, BIN
	, BUS_ID
	, BUS_NM
	, ISO_CTRY_CD
	, REGN_CD
	, PROD_TYPE_CD
	, CARD_TYPE
INTO #CRF
  FROM CORE o 
 WHERE NOT EXISTS (SELECT * FROM CRF i1
		    WHERE o.ACCT_NUM_MIN = i1.ACCT_NUM_MIN
		      AND o.ACCT_NUM_MAX = i1.ACCT_NUM_MAX)
       AND EXISTS (SELECT  LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		     FROM CRF i2
		    WHERE o.ACCT_NUM_MIN = i2.ACCT_NUM_MIN
		      AND o.ACCT_NUM_MAX = i2.ACCT_NUM_MAX 
		 GROUP BY LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		   HAVING COUNT(*) = 1)
 AND NOT EXISTS (SELECT * FROM CRF i3
		    WHERE o.UID = i3.UID)

SELECT * FROM #CRF



You know what, try the code into the #temp table first and tell us what happens



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam




Edited by - X002548 on 09/18/2007 13:17:02
Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  13:04:36  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
quote:
Originally posted by X002548

Well that's not looking for dups, rather nulls, as the error suggests

Just for kick, remove UID from the SELECT and the Column List, and lose the IDENTITY_INSERT Statement, and then try it

Or try this and see what it does


SELECT UID 
	, LEFT(ACCT_NUM_MIN,16)
	, LEFT(ACCT_NUM_MAX,16)
	, BIN
	, BUS_ID
	, BUS_NM
	, ISO_CTRY_CD
	, REGN_CD
	, PROD_TYPE_CD
	, CARD_TYPE
INTO #CRF
  FROM CORE o 
 WHERE NOT EXISTS (SELECT * FROM CRF i1
		    WHERE o.ACCT_NUM_MIN = i1.ACCT_NUM_MIN
		      AND o.ACCT_NUM_MAX = i1.ACCT_NUM_MAX)
       AND EXISTS (SELECT  LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		     FROM CRF i2
		    WHERE o.ACCT_NUM_MIN = i2.ACCT_NUM_MIN
		      AND o.ACCT_NUM_MAX = i2.ACCT_NUM_MAX 
		 GROUP BY LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		   HAVING COUNT(*) = 1)
 WHERE NOT EXISTS (SELECT * FROM CRF i3
		    WHERE o.UID = i3.UID)
SELECT * FROM #CRF


This particular line gives me a syntax error.


You know what, try the code into the #temp table first and tell us what happens



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam





Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  13:07:04  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
quote:
Originally posted by rockingdesi

quote:
Originally posted by X002548

Well that's not looking for dups, rather nulls, as the error suggests

Just for kick, remove UID from the SELECT and the Column List, and lose the IDENTITY_INSERT Statement, and then try it

Or try this and see what it does


SELECT UID 
	, LEFT(ACCT_NUM_MIN,16)
	, LEFT(ACCT_NUM_MAX,16)
	, BIN
	, BUS_ID
	, BUS_NM
	, ISO_CTRY_CD
	, REGN_CD
	, PROD_TYPE_CD
	, CARD_TYPE
INTO #CRF
  FROM CORE o 
 WHERE NOT EXISTS (SELECT * FROM CRF i1
		    WHERE o.ACCT_NUM_MIN = i1.ACCT_NUM_MIN
		      AND o.ACCT_NUM_MAX = i1.ACCT_NUM_MAX)
       AND EXISTS (SELECT  LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		     FROM CRF i2
		    WHERE o.ACCT_NUM_MIN = i2.ACCT_NUM_MIN
		      AND o.ACCT_NUM_MAX = i2.ACCT_NUM_MAX 
		 GROUP BY LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		   HAVING COUNT(*) = 1)
 WHERE NOT EXISTS (SELECT * FROM CRF i3
		    WHERE o.UID = i3.UID)
SELECT * FROM #CRF


This particular line gives me a syntax error.
Incorrect syntax near the keyword 'WHERE'.



You know what, try the code into the #temp table first and tell us what happens



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam







Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  13:07:19  Show Profile  Reply with Quote
Try it now, I fixed it (oye)


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  13:12:17  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
Hi Brett,

This is the error which comes up.

Server: Msg 8155, Level 16, State 1, Line 1
No column was specified for column 2 of 'DPT_CRF'.
Server: Msg 8155, Level 16, State 1, Line 1
No column was specified for column 3 of 'DPT_CRF'.



quote:
Originally posted by X002548

Well that's not looking for dups, rather nulls, as the error suggests

Just for kick, remove UID from the SELECT and the Column List, and lose the IDENTITY_INSERT Statement, and then try it

Or try this and see what it does


SELECT UID 
	, LEFT(ACCT_NUM_MIN,16)
	, LEFT(ACCT_NUM_MAX,16)
	, BIN
	, BUS_ID
	, BUS_NM
	, ISO_CTRY_CD
	, REGN_CD
	, PROD_TYPE_CD
	, CARD_TYPE
INTO #CRF
  FROM CORE o 
 WHERE NOT EXISTS (SELECT * FROM CRF i1
		    WHERE o.ACCT_NUM_MIN = i1.ACCT_NUM_MIN
		      AND o.ACCT_NUM_MAX = i1.ACCT_NUM_MAX)
       AND EXISTS (SELECT  LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		     FROM CRF i2
		    WHERE o.ACCT_NUM_MIN = i2.ACCT_NUM_MIN
		      AND o.ACCT_NUM_MAX = i2.ACCT_NUM_MAX 
		 GROUP BY LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		   HAVING COUNT(*) = 1)
 AND NOT EXISTS (SELECT * FROM CRF i3
		    WHERE o.UID = i3.UID)

SELECT * FROM #CRF



You know what, try the code into the #temp table first and tell us what happens



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam





Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  13:16:37  Show Profile  Reply with Quote
oye...feels like Monday

Try it now



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  13:43:27  Show Profile  Reply with Quote
Peter!

Where are you?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  13:58:35  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
I am here Brett......
quote:


Originally posted by X002548

Peter!

Where are you?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam





Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/18/2007 :  13:59:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
My first thought is "why only 16 characters when column is 21?".

Try to make the composite columns indexed with "IGNORE_DUP_KEY = ON".
Then just insert all you want.

If duplicate is found you get a warning, not an error, and all duplicates are prohibited.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/18/2007 :  14:03:48  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Create an empty staging table first
SELECT UID 
	, LEFT(ACCT_NUM_MIN,16) AS AcctNumMin
	, LEFT(ACCT_NUM_MAX,16) AS AcctNumMax
	, BIN
	, BUS_ID
	, BUS_NM
	, ISO_CTRY_CD
	, REGN_CD
	, PROD_TYPE_CD
	, CARD_TYPE
INTO #CRF
  FROM CORE 
WHERE 1 = 0
Then create an unique index as this
CREATE UNIQUE INDEX IX_CRF ON #CRF (AcctNumMin, AcctNumMax) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
And last insert all records
INSERT	#CRF
SELECT UID 
	, LEFT(ACCT_NUM_MIN,16)
	, LEFT(ACCT_NUM_MAX,16)
	, BIN
	, BUS_ID
	, BUS_NM
	, ISO_CTRY_CD
	, REGN_CD
	, PROD_TYPE_CD
	, CARD_TYPE
  FROM CORE
To verify the result
select * from #crf
What result do you get?



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  14:04:12  Show Profile  Reply with Quote
I still want to know if this returns anytrhing


SELECT  LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		     FROM CRF
		 GROUP BY LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		   HAVING COUNT(*) > 1




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/18/2007 :  14:06:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
quote:
Originally posted by X002548

Peter!

Where are you?
Just doing some investigation
here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89158
and here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89238
and here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89569



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  14:15:18  Show Profile  Reply with Quote
Ah,

It's the circle game

http://www.lyricsfreak.com/j/joni+mitchell/circle+game_20075389.html



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 09/18/2007 :  14:19:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes, I thought especially you should appreciate http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89158



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

rockingdesi
Starting Member

USA
31 Posts

Posted - 09/18/2007 :  15:19:01  Show Profile  Send rockingdesi a Yahoo! Message  Reply with Quote
Hi Brett,

Sorry i just stepped out...

Now the query u send me works fine with the temp table....i mean the syntax is fine.....now what should i do to implement in my application....?

You are of so much help Brett...I really appreciate your great help....
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  15:33:56  Show Profile  Reply with Quote
Like I said, go look at what results are in the table to see if it looks, ok

But my guess is really really that the truncating of the account columns are causing the dups

Did you run the COUNT(*) > 1 query I posted?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/18/2007 :  15:34:44  Show Profile  Reply with Quote
Run this


SELECT  LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		     FROM CRF
		 GROUP BY LEFT(ACCT_NUM_MIN,16), LEFT(ACCT_NUM_MAX,16)
		   HAVING COUNT(*) > 1




and tell us if you get any rows back



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000