Identity column: tables

Showing posts with label tables. Show all posts

Friday, March 30, 2012

if i want to join two sql tables ...

do they have to have a common primary key?

Not necessarily. You can get a CROSS JOIN of both tables but I am not sure how much help that would be.

|||

I have these two tables

id int Unchecked
CategoryID int Checked
ListTitle nvarchar(50) Checked
Blurb nvarchar(250) Checked
FileName nvarchar(50) Checked
ByLine nvarchar(50) Checked
HTMLCopy nvarchar(MAX) Checked
MainStory bit Checked
MainStoryImageFile nvarchar(50) Checked
Publish bit Checked
Date smalldatetime Checked

and

CategoryID int Unchecked
CategoryTitle nvarchar(50) Checked

and i need to get to the categoryTitle along with stuff from the first table. any idea?

|||

What does 'Checked' and 'UnChecked' mean? Also post some sample data from each of the tables and expected output, makes it easier for me to work on some data.

|||

ooops. checked and unchecked was just the allow nulls check box ticked or not in my table definition. i need to get category name and stuff from the second table shown here. Is this what you mean.

1Making It2Spending It3Staying In4Going Out5Style6Driving7Sport8Travel9Mind GamesNULLNULL

28710 WORST TENNIS PLAYERSBlurb about the 10 worst tennis players goes in here.Bravo2.jpgThey Were Very GoodNo 10. Jimmy Connors 
he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good
No 9. Vitas Geralitis 
he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good
No 8. Bjorn Borg 
he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very good he was not very goodTrueO2.jpgTrue21/08/2007 13:43:0029710 BEST DEFENDERSBlurb about the 10 best defenders players goes in here.Yahoo.jpgCouldn't beat these guysmain list copy goes here.TrueBravo2.jpgTrue21/08/2007 17:00:0030710 BEST SQUASH PLAYERSBlurb about the 10 best squash players goes in here.Five.jpgGood raquet guysThis is were the main list copy goesTrueTWI.jpgTrue21/08/2007 17:01:0031410 FUNNIEST FILMSBlurb about the 10 funniest films goes in here.Bravo2.jpgThese were very funny filmsHere is the main copy for the funniest films here.TrueFive.jpgTrue21/08/2007 17:06:0032410 SCARIEST FILMSBlurb about the 10 scariest films goes in here.O2.jpgHold on to your seat.This is where the scary story copy goes.TrueBravo2.jpgTrue21/08/2007 17:07:0033410 BEST THEATRES IN LONDONBlurb about the 10 best theatres goes in here.BBC.jpgGreat places to see playsHere is the list copy for the theatresTrueYahoo.jpgTrue21/08/2007 17:10:0034710 BEST BADMINTON PLAYERSBlurb about the 10 best badminton players goes in here.Bravo2.jpgThese were great playersCOPY FOR THISTrueTWI.jpgTrue21/08/2007 17:12:0035510 BEST JEAN LABELBlurb about the 10 best jeans labels goes in here.Five.jpgJeans with the best fitCopy about the jeans goes in hereTrueYahoo.jpgTrue21/08/2007 22:29:0036310 BEST DVD'SBlurb about the 10 best dvd's goes in here.IMG World.jpgEnjoy these filmsworkTrueO2.jpgFalse21/08/2007 22:44:0037310 BEST BOARD GAMESBlurb about the 10 best board games goes in here.Yahoo.jpgHave playing these games at homeNo. 10 Chess 
This is the classic board game
No. 9 Backgammon 
Hope you are good with numbers.TrueYahoo.jpgTrue21/08/2007 22:49:0038710 GREATEST FOOTBALL TEAMSBlurb about the 10 greatest football teams goes in here.BBC.jpgThese teams created footballNo10. Westham United 
The greatest team of all time. The greatest team of all time. The greatest team of all time.The greatest team of all time.
No9. Arsenal 
Not a bad outfit now Henry has left. Not a bad outfit now Henry has left. Not a bad outfit now Henry has left. Not a bad outfit now Henry has left.
No 8. Liverpool 
Three times European Champions. Three times European Champions.Three times European Champions. Three times European Champions. Three times European Champions.TrueBravo2.jpgTrue21/08/2007 23:06:0039710 CLEVEREST RULES IN FOOTBALLBlurb about the 10 cleverest rules goes in here.Five.jpgThese are stupid rules<h3>No.10 Taken off for treatment</h3>
in football Fifa rules state that a player must leave the pitch if in need of treatment for an injury which can easily tempt a nervous team into ankle-stamping the opposition's best player minutes before the whistle. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to him back on could prove vital.
<h3>No.9 No concessions for disabled people in golf</h3>
American Casey Martin was no world-class golfer, but in the late 90s he was doing reasonably well on the PGA's minor league Nike Tour. Martin had a birth defect in his leg attributed to Klippel Trenaunay Weber syndrome, which made walking painful. But the PGA wouldn't let him use a golf cart to get round. So he sued them.
<h3>No.8 No coaching mid-match in tennis</h3>
Think of every sport and you'll see a ranting manager or trainer somewhere on the sidelines. Except tennis, where players have to wait until they change ends for a hurried chat with the man who takes 15 per cent. Even toilet breaks are escorted, to ensure the rule isn't broken.
<h3>No.7 Not being allowed to over-celebrate in football</h3>
In a career that spanned over 500 matches, Alan Hansen scored just 14 goals. True, he was a defender, but had the day ever come that the dour Scot popped one in for his national team (0 goals from 26 games), he'd have gone absolutely bananas. And if today, probably booked.
<h3>No.6 Taken off for treatment</h3>
in football Fifa rules state that a player must leave the pitch if in need of treatment for an injury which can easily tempt a nervous team into ankle-stamping the opposition's best player minutes before the whistle. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to him back on could prove vital.
<h3>No.5 No concessions for disabled people in golf</h3>
American Casey Martin was no world-class golfer, but in the late 90s he was doing reasonably well on the PGA's minor league Nike Tour. Martin had a birth defect in his leg attributed to Klippel Trenaunay Weber syndrome, which made walking painful. But the PGA wouldn't let him use a golf cart to get round. So he sued them.
<h3>No.4 No coaching mid-match in tennis</h3>
Think of every sport and you'll see a ranting manager or trainer somewhere on the sidelines. Except tennis, where players have to wait until they change ends for a hurried chat with the man who takes 15 per cent. Even toilet breaks are escorted, to ensure the rule isn't broken.
<h3>No.3 Not being allowed to over-celebrate in football</h3>
In a career that spanned over 500 matches, Alan Hansen scored just 14 goals. True, he was a defender, but had the day ever come that the dour Scot popped one in for his national team (0 goals from 26 games), he'd have gone absolutely bananas. And if today, probably booked.
<h3>No.2 Taken off for treatment</h3>
in football Fifa rules state that a player must leave the pitch if in need of treatment for an injury which can easily tempt a nervous team into ankle-stamping the opposition's best player minutes before the whistle. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to bring him back on could prove vital. Those precious seconds waiting for the ref to him back on could prove vital.
<h3>No.1 No concessions for disabled people in golf</h3>
American Casey Martin was no world-class golfer, but in the late 90s he was doing reasonably well on the PGA's minor league Nike Tour. Martin had a birth defect in his leg attributed to Klippel Trenaunay Weber syndrome, which made walking painful. But the PGA wouldn't let him use a golf cart to get round. So he sued them.TrueBravo2.jpgTrue22/08/2007 10:13:0027410 BEST HOME MADE CAKESBlurb about the 10 best home made cakes goes in here.Yahoo.jpgxxTrueBravo2.jpgFalse21/08/2007 13:38:0016710 BEST FOOTBALLERSBlurb about the 10 best football players goes in here.BBC.jpgThese were the besthtml copy goes here html copy goes here html copy goes here html copy goes here html copy goes here html copy goes here html copy goes here html copy goes here html copy goes hereTrueFive.jpgTrue20/08/2007 17:44:0040710 BEST WORLD CUP GOALSBlurb about the 10 world cup goals goes in here.Bravo2.jpgThe best goals everNo 10. Pele 
he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it
No 9. Maradonna 
This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals
No 8. Zico 
Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal
No 7. Zico 
Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal Another Fantastic goal
No 6. Pele 
he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it
No 5. Maradonna 
This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals
No 4. Pele 
he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it
No 3. Maradonna 
This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals
No 2. Pele 
he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it he was the master and this goal proves it
No 1. Maradonna 
This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finals This goal sank England in the 1998 World Cup finalsTrueBravo2.jpgTrue22/08/2007 10:32:0041710 BEST FAST BOWLERSBlurb about the 10 best fast bowlers goes in here.O2.jpgPhil Hilton10. Sir Richard Hadlee: 
Small but perfectly formed, Sir Richard’s ‘tache was like many Kiwi attempts to keep up with the cricketing powerhouses, not bad but a little short of what is required for world class. 
Sir Richard 
 
9. Jack Russell: 
Eccentric wicket-keeper who could tell to within a matter of seconds how long his Weetabix had been soaked in milk for. Perhaps England’s greatest ever gloveman, now an artist, his moustache was as bedraggled as his trademark floppy hat. 
 
8. Clive Lloyd: 
A powerful batsman who, as captain from 1974 to 1985, was largely responsible for the West Indies’ extraordinary success. Also a star for Lancashire, the world’s greatest county cricket club. Great man, great ‘tache. 
 
7. Robin Smith: 
Nicknamed The Judge because of his hair, Smith combined his ‘tache with a mullet. The Judge did not wear a grill on his helmet, which meant the bowlers had a clear view of that top lip while he was hammering the fastest of bowling with supersonic cuts and hooks. He visibly enjoyed the regular snatches of chin music he received from the West Indian quicks. 
 
6. Kapil Dev: 
An Indian legend, Kapil Dev was a fine batsmen and the greatest pace bowler his country has ever produced. Competed in an era of great all-rounders, and like Botham and Hadlee he also had a decent ‘tache. 
 
5. Sir Ian Botham: 
The great man had to make an appearance. Not many men can look good with shoulder length, semi-permed hair and a moustache. But Beefy did, plus he managed to be the world’s greatest ever all-rounder at the same time. What a legend! 
 
4. Graham Gooch: 
Goochie ran the full gauntlet of facial hair during his career, from facial hair to full beard via designer stubble and his famous Zapata moustache. He was at his best though when sporting the ‘tache, as his 333 against India would testify. Interestingly, his 333 was boosted by hundreds from Allan Lamb and Robin Smith in a marvellous Test match for hairy-lips. 
 
3. David Boon: 
Tasmania’s all-time favourite cricketing son, Boonie would have held the Australian prize for the greatest cricketing ‘tache, but for the presence of the world beating Big Merv. Rumour has it that he was considered to be featured on the Aussue $5 note but they couldn’t fit his moustache on. 
 
2. Adam Hollioake: 
Perhaps a surprising pick for second place, Hollioake managed to do what other England captain’s have never, that is win an international ODI tournament when he led an inexperienced team to the Champions Trophy in Sharjah. His Aussie background maybe why he sported an incredible handlebar moustache for the start of the 2004 season. 
 
1. Merv Hughes: 
The clear winner by a hairy mile. According to Cricinfo, the big-hearted Australian fast bowler “was a lively character armed with an imposing run-up and delivery action, a classic fast bowlers’ glare down the pitch, a mischievous sense of humour and a moustache of incredible proportions”. Merv’s facial appendage is listed in Wikipedia as one of the all-time leading handlebar moustaches. Today’s “metrosexual” modern cricketers would do well to follow his lippy lead.TrueBravo2.jpgTrue22/08/2007 12:10:00

|||

I presume you're looking at the tables through Database Explorer - and that by Checked/Unchecked you are referring to the "Allow Nulls" column in the Table Editor. So describe your columns instead as "id int NOT NULL" or "CategoryID int NULL" and people will understand you.

To answer your question, you'll be doing an INNER JOIN on the tables by the CategoryID.

However, be careful about your first table's definition where all but the id column are nullable i.e. may be unknown. That immediately looks like poor design. Does every record in the first table have to belong to a category? Then the CategoryID field shouldn't be null in the first table, and you should define a foreign key relationship between it and the Category table (you can do this through point-and-click within the Explorer tool).

If you do so, then when you use the Query Editor in Database Explorer to graphically build your query, you'll see them joined together in exactly the way you want.

|||

Assuming your first table (with listtitle, filename etc) is called Category and second table with Title is called CategoryTitle you can write a query as:

SELECT C.id ,C.CategoryID ,C.ListTitle ,C.Blurb ,C.FileName ,C.ByLine ,C.HTMLCopy,C.MainStory ,C.MainStoryImageFile ,C.Publish ,C.Date ,C.TitleFROM Category CJOIN CategoryTitle CTon C.CategoryID = CT.CategoryID

|||

my first table is called shortlist and the second is called categories so can i write the below or do i have to put those Cs in??

SELECT id ,CategoryID ,ListTitle ,Blurb ,FileName ,ByLine ,HTMLCopy,MainStory ,MainStoryImageFile ,Publish ,Date ,CategoryTitleFROM shortlistJOIN CategoryTitleon shortlist.CategoryID = Categories.CategoryID

|||

Looking at the structure of your tables it looks like you want to select all the data for a specific category? The only value in the second table that is not in the first is CategoryTitle. So if you want to select all the data from table1 along with the CetegoryTitle Value from Table2... use something like this:

Select a.*, b.CategoryTitle from table1 a
inner join table2 b
on a.CategoryID = b.CategoryID
WHERE a.CategoryID = 'value'

I am assuming you are passing a CategoryID. Remember to replace the table1 and table2 with the table names and the value with the correct param(CategoryID). If this is not what you were trying to do, just send me what you would like to select and what value you are passing into query!

|||

Just saw that you posted your table names, so the query will look like this:

Select a.*, b.CategoryTitle from shortlist a
inner join categories b
on a.CategoryID = b.CategoryID

OR:

Select a.*, b.CategoryTitle from shortlist a
inner join categories b
on a.CategoryID = b.CategoryID
WHERE a.CategoryID = 'value'

If you want a specific category, hope this helps and good luck!

|||

SELECT id,S.CategoryID,ListTitle ,Blurb ,FileName ,ByLine ,HTMLCopy,MainStory ,MainStoryImageFile ,Publish ,Date ,CategoryTitleFROM shortlist SJOIN CategoryTitle Con S.CategoryID = C.CategoryID

When you have a column that is present in both tables, you will have to specify from which table you want the value retrieved from, otherwise SQL Server will complain

with an "Ambiguous column name CategoryId". So you need to put prefix the column name with the tablename or alias. If the column names are long its a lot of typing.

So we alias it with an acronym that is meaningful. Here I used S for Shortlist and C for CategoryTitle so you can prefix the CategoryId with either "S." or "C.".

|||Thank you very much. that worked. What if i want to make the CategoryTitle uppercase? :)|||

This will make the CategoryTitle upper case:

Select a.*, UPPER(b.CategoryTitle) from shortlist a
inner join categories b
on a.CategoryID = b.CategoryID

|||

I would do it at the front end, but there is an UPPER() function if you have to do it in SQL.

IF funcionality in SQL server views

Hi,
I am working with several tables and views. My goal is to create a view
with critical reporting data from these tables and views. I have
managed to get the majority of data but am having difficultly with the
final step.
For a record in the master dataview, add additional record information
by appending data from another view based on the first 2 letters of the
document number and the document number i.e
Document number
SC11111
DN22222
SI33333
For SC11111 look up data in Sales Credit table/view for doument SC11111
and append to that line
For DN22222 look up data in Delivery Note table/view for document
DN22222and append to that line
For SI33333 look up data in Sales Invoice table/view for document
SI33333 and append to that line
Any help appreciated.
Thank you in advance,
RajCan I just clarify what you're trying to do...
as I understand it, you want to select from different tables/views
depending on what the data is.
if so, could you left join on all 3 tables, then using join filters and
using isnull() in your select you could just get the data from the
relevant table.
Otherwise, could you explain a bit further what the problem is.
Cheers
Will|||If you want to conditional view, you can make a view using Multi-statement
Function.
"the_raj"ë'ì?´ ì'ì?±í' ë?´ì?©:
> Hi,
> I am working with several tables and views. My goal is to create a view
> with critical reporting data from these tables and views. I have
> managed to get the majority of data but am having difficultly with the
> final step.
> For a record in the master dataview, add additional record information
> by appending data from another view based on the first 2 letters of the
> document number and the document number i.e
> Document number
> SC11111
> DN22222
> SI33333
> For SC11111 look up data in Sales Credit table/view for doument SC11111
> and append to that line
> For DN22222 look up data in Delivery Note table/view for document
> DN22222and append to that line
> For SI33333 look up data in Sales Invoice table/view for document
> SI33333 and append to that line
> Any help appreciated.
> Thank you in advance,
> Raj
>|||Since you haven't provided the ddls I will try to explain it with a snippet.
CREATE VIEW MASTER_VIEW
AS
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_CREDIT B
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SC'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, C.*
FROM MASTER_DATA A, DELIVERY_NOTE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'DN'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_INVOICE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SI'
AND <JOIN CODITIONS>
--Note: do not use select *
-- and I assume the 3 selects that are unioned will be having the same schema

IF funcionality in SQL server views

Hi,
I am working with several tables and views. My goal is to create a view
with critical reporting data from these tables and views. I have
managed to get the majority of data but am having difficultly with the
final step.
For a record in the master dataview, add additional record information
by appending data from another view based on the first 2 letters of the
document number and the document number i.e
Document number
SC11111
DN22222
SI33333
For SC11111 look up data in Sales Credit table/view for doument SC11111
and append to that line
For DN22222 look up data in Delivery Note table/view for document
DN22222and append to that line
For SI33333 look up data in Sales Invoice table/view for document
SI33333 and append to that line
Any help appreciated.
Thank you in advance,
Raj
Can I just clarify what you're trying to do...
as I understand it, you want to select from different tables/views
depending on what the data is.
if so, could you left join on all 3 tables, then using join filters and
using isnull() in your select you could just get the data from the
relevant table.
Otherwise, could you explain a bit further what the problem is.
Cheers
Will
|||If you want to conditional view, you can make a view using Multi-statement
Function.
"the_raj"?? ??? ??:

> Hi,
> I am working with several tables and views. My goal is to create a view
> with critical reporting data from these tables and views. I have
> managed to get the majority of data but am having difficultly with the
> final step.
> For a record in the master dataview, add additional record information
> by appending data from another view based on the first 2 letters of the
> document number and the document number i.e
> Document number
> SC11111
> DN22222
> SI33333
> For SC11111 look up data in Sales Credit table/view for doument SC11111
> and append to that line
> For DN22222 look up data in Delivery Note table/view for document
> DN22222and append to that line
> For SI33333 look up data in Sales Invoice table/view for document
> SI33333 and append to that line
> Any help appreciated.
> Thank you in advance,
> Raj
>
|||Since you haven't provided the ddls I will try to explain it with a snippet.
CREATE VIEW MASTER_VIEW
AS
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_CREDIT B
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SC'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, C.*
FROM MASTER_DATA A, DELIVERY_NOTE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'DN'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_INVOICE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SI'
AND <JOIN CODITIONS>
--Note: do not use select *
-- and I assume the 3 selects that are unioned will be having the same schema
sql

IF funcionality in SQL server views

Hi,
I am working with several tables and views. My goal is to create a view
with critical reporting data from these tables and views. I have
managed to get the majority of data but am having difficultly with the
final step.
For a record in the master dataview, add additional record information
by appending data from another view based on the first 2 letters of the
document number and the document number i.e
Document number
SC11111
DN22222
SI33333
For SC11111 look up data in Sales Credit table/view for doument SC11111
and append to that line
For DN22222 look up data in Delivery Note table/view for document
DN22222and append to that line
For SI33333 look up data in Sales Invoice table/view for document
SI33333 and append to that line
Any help appreciated.
Thank you in advance,
RajCan I just clarify what you're trying to do...
as I understand it, you want to select from different tables/views
depending on what the data is.
if so, could you left join on all 3 tables, then using join filters and
using isnull() in your select you could just get the data from the
relevant table.
Otherwise, could you explain a bit further what the problem is.
Cheers
Will|||If you want to conditional view, you can make a view using Multi-statement
Function.
"the_raj"?? ??? ??:

> Hi,
> I am working with several tables and views. My goal is to create a view
> with critical reporting data from these tables and views. I have
> managed to get the majority of data but am having difficultly with the
> final step.
> For a record in the master dataview, add additional record information
> by appending data from another view based on the first 2 letters of the
> document number and the document number i.e
> Document number
> SC11111
> DN22222
> SI33333
> For SC11111 look up data in Sales Credit table/view for doument SC11111
> and append to that line
> For DN22222 look up data in Delivery Note table/view for document
> DN22222and append to that line
> For SI33333 look up data in Sales Invoice table/view for document
> SI33333 and append to that line
> Any help appreciated.
> Thank you in advance,
> Raj
>|||Since you haven't provided the ddls I will try to explain it with a snippet.
CREATE VIEW MASTER_VIEW
AS
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_CREDIT B
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SC'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, C.*
FROM MASTER_DATA A, DELIVERY_NOTE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'DN'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_INVOICE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SI'
AND <JOIN CODITIONS>
--Note: do not use select *
-- and I assume the 3 selects that are unioned will be having the same schem
a

IF funcionality in SQL server views

Hi,
I am working with several tables and views. My goal is to create a view
with critical reporting data from these tables and views. I have
managed to get the majority of data but am having difficultly with the
final step.
For a record in the master dataview, add additional record information
by appending data from another view based on the first 2 letters of the
document number and the document number i.e
Document number
SC11111
DN22222
SI33333
For SC11111 look up data in Sales Credit table/view for doument SC11111
and append to that line
For DN22222 look up data in Delivery Note table/view for document
DN22222and append to that line
For SI33333 look up data in Sales Invoice table/view for document
SI33333 and append to that line
Any help appreciated.
Thank you in advance,
RajCan I just clarify what you're trying to do...
as I understand it, you want to select from different tables/views
depending on what the data is.
if so, could you left join on all 3 tables, then using join filters and
using isnull() in your select you could just get the data from the
relevant table.
Otherwise, could you explain a bit further what the problem is.
Cheers
Will|||If you want to conditional view, you can make a view using Multi-statement
Function.
"the_raj"?? ??? ??:

> Hi,
> I am working with several tables and views. My goal is to create a view
> with critical reporting data from these tables and views. I have
> managed to get the majority of data but am having difficultly with the
> final step.
> For a record in the master dataview, add additional record information
> by appending data from another view based on the first 2 letters of the
> document number and the document number i.e
> Document number
> SC11111
> DN22222
> SI33333
> For SC11111 look up data in Sales Credit table/view for doument SC11111
> and append to that line
> For DN22222 look up data in Delivery Note table/view for document
> DN22222and append to that line
> For SI33333 look up data in Sales Invoice table/view for document
> SI33333 and append to that line
> Any help appreciated.
> Thank you in advance,
> Raj
>|||Since you haven't provided the ddls I will try to explain it with a snippet.
CREATE VIEW MASTER_VIEW
AS
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_CREDIT B
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SC'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, C.*
FROM MASTER_DATA A, DELIVERY_NOTE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'DN'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_INVOICE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SI'
AND <JOIN CODITIONS>
--Note: do not use select *
-- and I assume the 3 selects that are unioned will be having the same schem
a

IF funcionality in SQL server views

Hi,

I am working with several tables and views. My goal is to create a view
with critical reporting data from these tables and views. I have
managed to get the majority of data but am having difficultly with the
final step.

For a record in the master dataview, add additional record information
by appending data from another view based on the first 2 letters of the
document number and the document number i.e

Document number
SC11111
DN22222
SI33333

For SC11111 look up data in Sales Credit table/view for doument SC11111
and append to that line
For DN22222 look up data in Delivery Note table/view for document
DN22222and append to that line
For SI33333 look up data in Sales Invoice table/view for document
SI33333 and append to that line

Any help appreciated.

Thank you in advance,

RajCan I just clarify what you're trying to do...

as I understand it, you want to select from different tables/views
depending on what the data is.

if so, could you left join on all 3 tables, then using join filters and
using isnull() in your select you could just get the data from the
relevant table.

Otherwise, could you explain a bit further what the problem is.

Cheers
Will

Wednesday, March 28, 2012

If Else Condition

Hi guys,

I have 2 tables which are connected to each other by casenumber:

CodeTable

CaseNumber OldCode

001 05

002 05

003 05

004 05

005 06

ConnectionTable

CaseNumber ConnectionType

001 G

001 H

001 N

002 M

002 H

003 G

003 H

003 I

003 N

004 X

004 N

With following Mapping Condition:

OldCode NewCode

05 (with connectionType G) GE

05 (with ConnectionType H) GP

05 (With ConnectionType I) GPE

05 (With ConnectionType G OR H) GPE

With the following Rules:

1. Search for connection type that is mapped and disregard all other connection types.

2. If none of the mapped types are connected to the case, map as follows : 05 to GPE

3. If multiple types are connected to the case, map as follows: 05 to GPE.

I would like to map the old code to the new code on the CodeTable without getting duplicate CaseNumber.

My code:

SELECT DISTINCT c1.CaseNumber,

CASE WHEN c1.OldCode = '05' THEN

CASE c2.ConnectionType WHEN 'G' THEN 'GE'

WHEN 'H' THEN 'GP'

WHEN 'I' THEN 'GPE'

ELSE 'GPE' END END AS NewCode,

FROM CodeTable AS c1

LEFT JOIN(SELECT MIN(ConnectionType) [ConnectionType],CaseNumber

FROM ConnectionTable GROUP BY CaseNumber) AS c2

ON (c1.CaseNumber = c2.CaseNumber)

I did not get all answer correctly because let say i have Casenumber 001 with connection G, H, and N; as given in the rule, I need to map the multiple types to GPE, but what i get is GE (which is OldCode 'G'). This is probably because i select MIN from connectionType and the first one i get is G, that's why i get GE for the new code instead of GPE.

I hope you guys will help me on this. Thanks so much!!!

Jul.

If I understand your requirements correctly, perhaps something like this:

Code Snippet

SET NOCOUNT ON

DECLARE @.Codes table
( CaseNumber varchar(10),
OldCode varchar(5)
)

INSERT INTO @.Codes VALUES ( '001', '05' )
INSERT INTO @.Codes VALUES ( '002', '05' )
INSERT INTO @.Codes VALUES ( '003', '05' )
INSERT INTO @.Codes VALUES ( '004', '05' )
INSERT INTO @.Codes VALUES ( '005', '05' )
INSERT INTO @.Codes VALUES ( '006', '05' )
INSERT INTO @.Codes VALUES ( '007', '05' )
INSERT INTO @.Codes VALUES ( '008', '06' )
INSERT INTO @.Codes VALUES ( '009', '06' )

DECLARE @.Connections table
( CaseNumber varchar(10),
ConnectionType varchar(5)
)

INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
INSERT INTO @.Connections VALUES ( '002', 'M' )
INSERT INTO @.Connections VALUES ( '003', 'G' )
INSERT INTO @.Connections VALUES ( '003', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'I' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'G' )
INSERT INTO @.Connections VALUES ( '005', 'H' )
INSERT INTO @.Connections VALUES ( '006', 'I' )
INSERT INTO @.Connections VALUES ( '007', 'G' )
INSERT INTO @.Connections VALUES ( '008', 'H' )

SELECT DISTINCT
c1.CaseNumber,
c1.OldCode,
NewCode = CASE
WHEN ( c1.OldCode = '05' ) THEN
CASE
WHEN ( dt.CaseCount = 1 ) THEN
CASE WHEN ( c2.ConnectionType = 'G' ) THEN 'GE'
WHEN ( c2.ConnectionType = 'H' ) THEN 'GP'
ELSE 'GPE'
END
WHEN ( dt.CaseCount <> 1 ) OR ( dt.CaseNumber IS NULL ) THEN 'GPE'
END
ELSE 'GPE'
END
FROM @.Codes c1
LEFT JOIN (SELECT
CaseNumber,
CaseCount = count( CaseNumber )
FROM @.Connections
GROUP BY CaseNumber
) dt
ON c1.CaseNumber = dt.CaseNumber
LEFT JOIN @.Connections c2
ON c1.CaseNumber = c2.CaseNumber

CaseNumber OldCode NewCode
- - -
001 05 GPE
002 05 GPE
003 05 GPE
004 05 GE
005 05 GP
006 05 GPE
007 05 GE
008 06 GPE
009 06 GPE

|||

Thanks Arnie,

CaseNumber ConnectionType

001 G

001 H

001 N

002 M

002 H

003 G

003 H

003 I

003 N

004 X

004 N

The appropriate result is supposed to be:

CaseNumber NewCode
001 GPE (Because there are multiple types refer rul 3)
002 GP (Because H is in there, and ignore M because M is not

in one of the mapping criteria of code 05) [refer to rule 1.)
003 GPE (Because there are multiple types refer rule 3)
004 GPE (Because none of the mapped types are connected to the case -- refer rule 2)

Can you help me on this please? thanks........

|||

Hi Jul

Hope this suits your every requirement, I have done few modification to the code Arnie. Assuming that u require something similar like this.

Regards

Vijai K

SET NOCOUNT ON

Declare @.final table(

CaseNumber varchar(10),

NewCode varchar(5)

);

DECLARE @.Codes table(

CaseNumber varchar(10),

OldCode varchar(5)

)

INSERT INTO @.Codes VALUES ( '001', '05' )

INSERT INTO @.Codes VALUES ( '002', '05' )

INSERT INTO @.Codes VALUES ( '003', '05' )

INSERT INTO @.Codes VALUES ( '004', '05' )

INSERT INTO @.Codes VALUES ( '005', '05' )

INSERT INTO @.Codes VALUES ( '006', '05' )

INSERT INTO @.Codes VALUES ( '007', '05' )

INSERT INTO @.Codes VALUES ( '008', '06' )

INSERT INTO @.Codes VALUES ( '009', '06' )

INSERT INTO @.Codes VALUES ( '010', '05' )

DECLARE @.Connections table(

CaseNumber varchar(10),

ConnectionType varchar(5)

)

INSERT INTO @.Connections VALUES ( '001', 'G' )

INSERT INTO @.Connections VALUES ( '001', 'H' )

INSERT INTO @.Connections VALUES ( '001', 'N' )

INSERT INTO @.Connections VALUES ( '002', 'M' )

INSERT INTO @.Connections VALUES ( '002', 'H' )

INSERT INTO @.Connections VALUES ( '003', 'G' )

INSERT INTO @.Connections VALUES ( '003', 'H' )

INSERT INTO @.Connections VALUES ( '003', 'I' )

INSERT INTO @.Connections VALUES ( '003', 'N' )

INSERT INTO @.Connections VALUES ( '004', 'X' )

INSERT INTO @.Connections VALUES ( '004', 'N' )

INSERT INTO @.Connections VALUES ( '005', 'H' )

INSERT INTO @.Connections VALUES ( '005', 'X' )

INSERT INTO @.Connections VALUES ( '005', 'N' )

INSERT INTO @.Connections VALUES ( '006', 'I' )

INSERT INTO @.Connections VALUES ( '007', 'G' )

INSERT INTO @.Connections VALUES ( '007', 'N' )

INSERT INTO @.Connections VALUES ( '008', 'H' )

INSERT INTO @.Connections VALUES ( '009', 'H' )

INSERT INTO @.Connections VALUES ( '010', 'G' )

INSERT INTO @.Connections VALUES ( '010', 'X' )

insert into @.final

SELECT DISTINCT

c1.CaseNumber,

NewCode = CASE

WHEN ( c1.OldCode = '05' ) THEN

CASE

WHEN ( dt.CaseCount = 1 ) OR ( dt.CaseCount = 2 ) THEN

CASE WHEN ( c2.ConnectionType = 'G' ) THEN 'GE'

WHEN ( c2.ConnectionType = 'H' ) THEN 'GP'

ELSE 'GPE'

END

WHEN ( dt.CaseCount > 2 ) OR ( dt.CaseNumber IS NULL ) THEN 'GPE'

END

ELSE 'GPE'

END
FROM @.Codes c1

LEFT JOIN (SELECT CaseNumber, CaseCount = count(CaseNumber)

FROM @.Connections

GROUP BY CaseNumber) dt

ON c1.CaseNumber = dt.CaseNumber

LEFT JOIN @.Connections c2

ON c1.CaseNumber = c2.CaseNumber

delete from @.final where Newcode = 'GPE' and casenumber in( select Casenumber from @.final group by casenumber having count(*) >1 )

select * from @.final

|||Here's a guess. It's probably not quite there yet, but it may give you some ideas.

Code Snippet

DECLARE @.Codes table
( CaseNumber varchar(10),
OldCode varchar(5)
)

DECLARE @.Connections table
( CaseNumber varchar(10),
ConnectionType varchar(5)
)

DECLARE @.MappedTypes table (
ID int,
OldCode varchar(5),
mappedType varchar(5)
)
INSERT INTO @.MappedTypes VALUES (0, '05', 'G')
INSERT INTO @.MappedTypes VALUES (1, '05', 'H')
INSERT INTO @.MappedTypes VALUES (2, '05', 'I')

DECLARE @.Mapping table (
OldCode varchar(5),
Connections varchar(10),
Bitmask varbinary(16),
newCode varchar(5)
)
INSERT INTO @.Mapping VALUES ( '05', 'G', 0x1, 'GE')
INSERT INTO @.Mapping VALUES ( '05', 'H', 0x2, 'GP')
INSERT INTO @.Mapping VALUES ( '05', 'I', 0x4, 'GPE')
INSERT INTO @.Mapping VALUES ( '05', NULL, NULL, 'GPE');
-- last row indicates code to use when there is some
-- mapped value present, but when the particular collection
-- of mapped values does not have a specific mapping.

with B(CaseNumber, Bitmask, OldCode) as (
select
C.CaseNumber,
sum(distinct coalesce(power(2,ID),0)) as Bitmask,
D.OldCode

from @.Connections as C
join @.Codes as D
on D.CaseNumber = C.CaseNumber
join @.MappedTypes AS M
on C.ConnectionType = M.mappedType
and D.OldCode = M.OldCode
group by C.CaseNumber, D.OldCode
)
select
CaseNumber,
coalesce(newCode,(select newCode from @.Mapping where Bitmask is null))
from B
left outer join @.Mapping as M
on M.OldCode = B.OldCode
and M.Bitmask = B.Bitmask

Steve Kass
Drew University
http://www.stevekass.com
|||

Jul,

I looked at this problem again, and came to the conclusion that using a Mapping table would be useful. I think that this satisfies your rules and matches your expected output.

Code Snippet

SET NOCOUNT ON

DECLARE @.Codes table
( CaseNumber varchar(10),
OldCode varchar(5)
)

DECLARE @.Connections table
( CaseNumber varchar(10),
ConnectionType varchar(5)
)

INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
INSERT INTO @.Connections VALUES ( '002', 'H' )
INSERT INTO @.Connections VALUES ( '002', 'M' )
INSERT INTO @.Connections VALUES ( '003', 'G' )
INSERT INTO @.Connections VALUES ( '003', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'I' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'X' )

DECLARE @.CodeMap table
( OldCode char(2),
OldType char(1),
NewCode varchar(3)
)

INSERT INTO @.CodeMap VALUES ( '05', 'G', 'GE' )
INSERT INTO @.CodeMap VALUES ( '05', 'H', 'GP' )

SELECT
c.CaseNumber,
NewCode = CASE
WHEN count( m.NewCode ) = 1 AND min( m.NewCode ) IS NOT NULL THEN min( m.NewCode )
ELSE 'GPE'
END
FROM @.Codes c
LEFT JOIN @.Connections c2
ON c.CaseNumber = c2.CaseNumber
LEFT JOIN @.CodeMap m
ON c2.ConnectionType = m.OldType
GROUP BY c.CaseNumber

CaseNumber NewCode
- -
001 GPE
002 GP
003 GPE
004 GPE
005 GPE

|||

The following Code BY Arnie is the most appropriate answer. Thank's guys!

SET NOCOUNT ON

DECLARE @.Codes table
( CaseNumber varchar(10),
OldCode varchar(5)
)

DECLARE @.Connections table
( CaseNumber varchar(10),
ConnectionType varchar(5)
)

INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
INSERT INTO @.Connections VALUES ( '002', 'H' )
INSERT INTO @.Connections VALUES ( '002', 'M' )
INSERT INTO @.Connections VALUES ( '003', 'G' )
INSERT INTO @.Connections VALUES ( '003', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'I' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'X' )

DECLARE @.CodeMap table
( OldCode char(2),
OldType char(1),
NewCode varchar(3)
)

INSERT INTO @.CodeMap VALUES ( '05', 'G', 'GE' )
INSERT INTO @.CodeMap VALUES ( '05', 'H', 'GP' )

CaseNumber NewCode
- -
001 GPE
002 GP
003 GPE
004 GPE
005 GPE

|||

Arnie, seems like it does not work if instead of having

INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )

but i'm having these values:

INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )

On the rules given, if I have H (without G or I) then map to 'GP'

If i have multiple matched connections let say 'H' AND 'G' (or 'H' AND 'I') then map to 'GPE'.

But i have a case where the connection in the case is like the values above, which is H, H, and N. When i tried your code, it gives me 'GPE', what i need is 'GE'. Everything else works perfect. I am really a beginner in sql, hope you can help. thanks.

|||

Of course, I overlooked that possiblitilty. Thanks for bringing it to my attention.

If you add 'DISTINCT' to the count( m.NewCode ) it 'should' take care of that situation.

Code Snippet

NewCode = CASE
WHEN count( DISTINCT m.NewCode ) = 1 AND min( m.NewCode ) IS NOT NULL THEN min( m.NewCode )

And this 'should' handle any additional 'OldCodes' just by inserting rows into the Mapping table. (Unless your rules change substaintially.)

|||

Awesome........Thanks so much!!!!!!!!

Julia

|||A tip 'o the hat to Steve Kass for nudging me to consider a mapping table...

Monday, March 26, 2012

if date_made > or = to birth_date +30

I have a question...I have two tables, one with an ID number and year_made in it, and another one with a birth_date and death_date.

I'm trying to bring back ID numbers where the artist was 30 years of age or younger when the item was made. It requires a calulation to figure out the birth date + 30 years, and to return any id numbers where date_made falls into one of those 30 years between the birth_date and birth_date + 30.

However, I've not the foggiest on how to set it up in SQL. Or rather, I have too much fog, and not enough clear vision. ;)

Can someone help me?

Thanks ahead of time!There's datediff() in TSQL and JETSQL, not sure about the PL/SQL equivilent..|||Hey,

Thanks, that got me on the right track--until I realized the date fields in question were not true dates, because some are entered as "circa 1900" and the like. Gah.

Thank you again. :)|||Bad database design ... why is a date column created using the character datatype?

Teddy, why did you mention PL/SQL? Is it Oracle's Procedural Languange extension to SQL? If so, would it be more precise to talk about Oracle SQL (rather than PL/SQL) equivalent function?

And, if it was about Oracle, you could use ADD_MONTHS function (by multiplying 30 (years) with 12 (months)); something like

WHERE date_made <= ADD_MONTHS(birth_date, 30 * 12)

(If the artist was 30 years of age or younger, wouldn't you rather need <= instead of >= as you suggested in the thread subject?)

IF Condition in Join?

Dear Group

I'd be grateful if you can send me on the right track in achieving this.

I have three tables A,B,C outlined as follows:

Table: A
Field: RowID
Field: EntityID
Field: TypeIdentifier

Table: B
Field: RowID
Field: Name

Table: C
Field: RowID
Field: Name

Let's assume I've the following records:

Table A:
1,1,0
2,1,1

Table B:
1,Smith

Table C:
1,XYZCorporation

The table joins are as follows:

A.EntityID = B.RowID
A.EntityID = C.RowID

I would like to select all records from Table A and display the Names from
either Table B or Table C, depending on the Field TypeIdentifier.
E.g.: SELECT Name FROM A JOIN B ON (A.EntityID = B.RowID) JOIN C ON
(A.EntityID = C.RowID) IF TypeIdentifier = 0 SELECT Name FROM B IF
TypeIdentifier = 1 SELECT Name FROM C

Resultset:

Smith
XYZCorporation

Is this somehow possible?

Thanks very much for your time & efforts!

MartinSELECT COALESCE(B.name,C.name) AS name
FROM A
LEFT JOIN B
ON A.entityid = B.rowid
AND A.typeidentifier = 1
LEFT JOIN C
ON A.entityid = C.rowid
AND A.typeidentifier = 0

--
David Portas
SQL Server MVP
--|||Thanks very much David!

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:X4GdnRjemKI4mOndRVn-uA@.giganews.com...|||In the past, I have done something like this:

LEFT JOIN B ON A.entityid = B.rowid
LEFT JOIN C ON A.entityid = C.rowid"

with a Case statement in the select. Your version
is much nicer, thanks.

Bill

David Portas wrote:

> SELECT COALESCE(B.name,C.name) AS name
> FROM A
> LEFT JOIN B
> ON A.entityid = B.rowid
> AND A.typeidentifier = 1
> LEFT JOIN C
> ON A.entityid = C.rowid
> AND A.typeidentifier = 0

If body contains two tables, report preview breaks are getting upset

When there are two tables in the body, if page break is added for first table, second table always go to a new page in report preview. But print layout preview is working fine.

Keep your two tables side by side.

Friday, March 23, 2012

Identity/Seed Values

I have a question regarding using the Identity/Seed values. I have migrated
a number of tables form Access to SQL. In Access the identity/seed values
were called autonumbers.
The big problem with autonumbers in Access was that they could change at any
time on you, getting renumbered or sometimes even becoming negative values,
etc.
Now, in my application I have need for a unique number as the primary key AS
LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
formatted a special way, I do not need it to be sequential, etc. But I do
need it to stay the same after it has been entered.
So, my question is this: I know in Access i could not rely on the autonumber
to stay the same. Does the identity/seed value propoerties of a SQL table
have this same issue? Or can I plan on this value staying the same over
time?
Thanks
JoeIdentity values in SQL Server start at the seed value. By default it
increments by 1 and cannot be manually inserted or updated.
"Joe Williams" <joe@.anywhere.com> wrote in message
news:OVk7QQCGFHA.2144@.TK2MSFTNGP09.phx.gbl...
> I have a question regarding using the Identity/Seed values. I have
migrated
> a number of tables form Access to SQL. In Access the identity/seed values
> were called autonumbers.
> The big problem with autonumbers in Access was that they could change at
any
> time on you, getting renumbered or sometimes even becoming negative
values,
> etc.
> Now, in my application I have need for a unique number as the primary key
AS
> LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
> formatted a special way, I do not need it to be sequential, etc. But I do
> need it to stay the same after it has been entered.
> So, my question is this: I know in Access i could not rely on the
autonumber
> to stay the same. Does the identity/seed value propoerties of a SQL table
> have this same issue? Or can I plan on this value staying the same over
> time?
> Thanks
> Joe
>|||Hi
Once the Identity value is stored in a table, it reamains the same and does
not change.
Regards
Mike
"Joe Williams" wrote:

> I have a question regarding using the Identity/Seed values. I have migrate
d
> a number of tables form Access to SQL. In Access the identity/seed values
> were called autonumbers.
> The big problem with autonumbers in Access was that they could change at a
ny
> time on you, getting renumbered or sometimes even becoming negative values
,
> etc.
> Now, in my application I have need for a unique number as the primary key
AS
> LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
> formatted a special way, I do not need it to be sequential, etc. But I do
> need it to stay the same after it has been entered.
> So, my question is this: I know in Access i could not rely on the autonumb
er
> to stay the same. Does the identity/seed value propoerties of a SQL table
> have this same issue? Or can I plan on this value staying the same over
> time?
> Thanks
> Joe
>
>|||When you say that the autonumbers change in Access, do you mean that
previously inserted values change (with no intervention on the user or
programmer) or just that the values inserted do not always increment
sequentially?
"Joe Williams" <joe@.anywhere.com> wrote in message
news:OVk7QQCGFHA.2144@.TK2MSFTNGP09.phx.gbl...
> I have a question regarding using the Identity/Seed values. I have
migrated
> a number of tables form Access to SQL. In Access the identity/seed values
> were called autonumbers.
> The big problem with autonumbers in Access was that they could change at
any
> time on you, getting renumbered or sometimes even becoming negative
values,
> etc.
> Now, in my application I have need for a unique number as the primary key
AS
> LONG AS THE NUMBER WILL NOT CHANGE IN THE FUTURE. I do not need it to be
> formatted a special way, I do not need it to be sequential, etc. But I do
> need it to stay the same after it has been entered.
> So, my question is this: I know in Access i could not rely on the
autonumber
> to stay the same. Does the identity/seed value propoerties of a SQL table
> have this same issue? Or can I plan on this value staying the same over
> time?
> Thanks
> Joe
>|||> I have need for a unique number...
What you absolutely do need is a proper natural key in your data. IDENTITY
is not a substitute for this. Logically you should always be able to remove
IDENTITY and its referencing columns and replace it with another key in your
table or with another artificial key without changing its meaning. Don't
expose the IDENTITY key to users otherwise you build business processes on
something over which you don't have complete control in all cases - for
example, if you need to integrate data from multiple tables with IDENTITY
keys or in some replication scenarios.
Don't make assumptions about the sequence of IDENTITY values, its continuity
(there may be gaps) or even its uniqueness (except when it's defined with a
PK or unique constraint).
Having said all that, SQL Server doesn't change the IDENTITY value
automatically for any reason once it's assigned and you cannot change an
IDENTITY yourself except by deleting and then inserting a row (another reaso
n
why you shouldn't tie external meaning to an arbitrary IDENTITY value).
David Portas
SQL Server MVP
--|||you need see "SET IDENTITY_INSERT" en SQL libray
"JohnnyAppleseed" wrote:

> When you say that the autonumbers change in Access, do you mean that
> previously inserted values change (with no intervention on the user or
> programmer) or just that the values inserted do not always increment
> sequentially?
> "Joe Williams" <joe@.anywhere.com> wrote in message
> news:OVk7QQCGFHA.2144@.TK2MSFTNGP09.phx.gbl...
> migrated
> any
> values,
> AS
> autonumber
>
>

Wednesday, March 21, 2012

Identyfying which tables have gone into a view

I am trying to find the list of tables that make up any particular view in SQL server 2000. The information schema - VIEW_TABLE_USAGE would be the best tool, if only the sysdepends table worked! When I use the schema I get some but not all of my views.

Has anyone got a solution, prefably one without cursors, that can identitfy the source tables for a view?

I have used the following SQL, but unfortunately it gives too many results:

SELECT VIEWS.name AS VIEW_NAME,
TABLES.name AS TABLE_NAME,
VIEW_SQL.text
FROM sysobjects VIEWS
INNER JOIN
syscomments VIEW_SQL
ON VIEWS.id = VIEW_SQL.id
INNER JOIN
sysobjects TABLES
ON VIEW_SQL.text LIKE '%.' + TABLES.name + '%'
WHERE (VIEWS.xtype = 'V')
AND (TABLES.xtype = 'U')
ORDER BY VIEWS.name, TABLES.name

JustinLook for SQL Dependency Viewer from Red-Gate software. It's a free download (though I think it's still beta).

http://www.red-gate.com/products/sql_dependency_viewer/

Regards,

hmscott|||Look for SQL Dependency Viewer from Red-Gate software. It's a free download (though I think it's still beta).

http://www.red-gate.com/products/sql_dependency_viewer/

Regards,

hmscott
Thanks, I have tried this tool and it should be good when its complete, however I actually want the names of the tables rather then a graph view. I am using the table names to provide some meta data for a database I am developing.

Regards
Justin

Identity/Seed Values

Identity values is checnges by initial snapshot

Hi, it's me again
We have merge replication setup between three servers, a push to one server
and a pull to the other server. There are many tables which use identity
fields. We are not using identity ranges. All were created with the not for
replication option on the identity declaration. I noticed that after the
initially snapshot is applied, that the first identity value available at
the subscriber is not the seed value, but one greater than the largest value
in the snapshot for that table. To get all the subscribers to generate
identities independent of the publisher, I placed DBCC CheckIdent( table,
ReSeed, 1 ) statements for each table in the post snapshot script. The
system appears to hang trying to reseed the identity field. When I tried to
check the value in Query Analyzer with: DBCC CheckIdent( table, NoReSeed ),
the command ran for over fifteen minutes before I killed it. The identity
values are independent after the snapshot is applied, but can I, how can I
reseed the identity for each subscriber.
Tia,
Paul
Hello Paul,
I have similar implementation that I install at my customers. I have devised
some extensive tools to manage all the identity stuff and work well. One of
them is to create a job that executes the DBCC command. This may apply to
you.
Hope this helps,
Raj
|||> To get all the subscribers to generate
> identities independent of the publisher, I placed DBCC CheckIdent( table,
> ReSeed, 1 ) statements for each table in the post snapshot script.
I do not understand what you were trying to do.. What exactly you expect
from such an approach ? If you have merge replication, and you would change
(somehow) identity seed in table on the subscriber database to 1 (as you
have tried), then inserting a new value into this table would cause a
generation of value 1 in identity column. But that is nonsence, because you
might already have such value. It is absolutely normal that after applying
snapshot seed is changed to "max value + 1".
If you do not want to use SQL server identity range handling, then I would
suggest you to change identity increment to a number of databases involved
in merge replication - if you have publisher and two subscribers, identity
increment should be at least three. Then after aplying snapshot you should
change identity seed to following values :
on publisher = Max value + 1
on publisher = Max value + 2
on publisher = Max value + 3
Of course instead of "Max value" you could use any value bigger than "Max
value".
With such an approach all further inserts into those databases will have
identity values which do not intersect - so any collisions in this area are
almost impossible

Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"PaulW" <MSNewsGroup@.Digi-Sol.com> wrote in message
news:Oebqgo%23GEHA.240@.TK2MSFTNGP12.phx.gbl...
> Hi, it's me again
>
> We have merge replication setup between three servers, a push to one
server
> and a pull to the other server. There are many tables which use identity
> fields. We are not using identity ranges. All were created with the not
for
> replication option on the identity declaration. I noticed that after the
> initially snapshot is applied, that the first identity value available at
> the subscriber is not the seed value, but one greater than the largest
value
> in the snapshot for that table. To get all the subscribers to generate
> identities independent of the publisher, I placed DBCC CheckIdent( table,
> ReSeed, 1 ) statements for each table in the post snapshot script. The
> system appears to hang trying to reseed the identity field. When I tried
to
> check the value in Query Analyzer with: DBCC CheckIdent( table,
NoReSeed ),
> the command ran for over fifteen minutes before I killed it. The identity
> values are independent after the snapshot is applied, but can I, how can I
> reseed the identity for each subscriber.
>
> Tia,
> Paul
>
>
|||Actually Kestutis, you will end up with repeated identities any way unless
you set ranges - which Paul specifically said that he is not doing. This
also makes sense if the identity is matched with some form of server or
location value to make up a unique value.
Ron L
"Kestutis Adomavicius" <kicker@.nospam-mail.lt> wrote in message
news:Ot6qAgLHEHA.3584@.TK2MSFTNGP09.phx.gbl...
table,
> I do not understand what you were trying to do.. What exactly you expect
> from such an approach ? If you have merge replication, and you would
change
> (somehow) identity seed in table on the subscriber database to 1 (as you
> have tried), then inserting a new value into this table would cause a
> generation of value 1 in identity column. But that is nonsence, because
you
> might already have such value. It is absolutely normal that after applying
> snapshot seed is changed to "max value + 1".
> If you do not want to use SQL server identity range handling, then I would
> suggest you to change identity increment to a number of databases involved
> in merge replication - if you have publisher and two subscribers, identity
> increment should be at least three. Then after aplying snapshot you should
> change identity seed to following values :
> on publisher = Max value + 1
> on publisher = Max value + 2
> on publisher = Max value + 3
> Of course instead of "Max value" you could use any value bigger than "Max
> value".
> With such an approach all further inserts into those databases will have
> identity values which do not intersect - so any collisions in this area
are
> almost impossible

> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
> "PaulW" <MSNewsGroup@.Digi-Sol.com> wrote in message
> news:Oebqgo%23GEHA.240@.TK2MSFTNGP12.phx.gbl...
> server
> for
at
> value
table,
> to
> NoReSeed ),
identity
I
>
|||There is a way of avoiding repeated identities, by controlling the increment
rather than the seed:
a.. Machine 1 has a seed of 1 and an increment of 2 (odd, positive
numbers)
b.. Machine 2 has a seed of 2 and an increment of 2 (even, positive
numbers)
For 4 machines:
a.. Machine 1 has a seed of 1 and an increment of 2 (odd, positive
numbers)
b.. Machine 2 has a seed of 2 and an increment of 2 (even, positive
numbers)
c.. Machine 3 has a seed of -1 and an increment of -2 (odd, negative
numbers)
d.. Machine 4 has a seed of -2 and an increment of -2 (even, negative
numbers)
This is Michael Hotek's 'invention' -
http://www.mssqlserver.com/replicati..._identity.asp.
On the site above he has the algorithm for a larger number of machines. If
this is used, you can allocate a massive range and then effectively forget
about it.
Regards,
Paul Ibison
|||Paul
The fact here is that we want them repeated. We want Server1 to have
sequence numbers 1 - ..., Server2 to have sequence numvers 1 - ..., etc.
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23DhdlwMHEHA.3528@.TK2MSFTNGP09.phx.gbl...
> There is a way of avoiding repeated identities, by controlling the
increment
> rather than the seed:
> a.. Machine 1 has a seed of 1 and an increment of 2 (odd, positive
> numbers)
> b.. Machine 2 has a seed of 2 and an increment of 2 (even, positive
> numbers)
> For 4 machines:
> a.. Machine 1 has a seed of 1 and an increment of 2 (odd, positive
> numbers)
> b.. Machine 2 has a seed of 2 and an increment of 2 (even, positive
> numbers)
> c.. Machine 3 has a seed of -1 and an increment of -2 (odd, negative
> numbers)
> d.. Machine 4 has a seed of -2 and an increment of -2 (even, negative
> numbers)
> This is Michael Hotek's 'invention' -
> http://www.mssqlserver.com/replicati..._identity.asp.
> On the site above he has the algorithm for a larger number of machines. If
> this is used, you can allocate a massive range and then effectively forget
> about it.
> Regards,
> Paul Ibison
>
|||No, you probably missed the point.. Identity increment is set to a number of
databases participating in transaction and on every database identity
increment is set to a number increased by 1. So identities on different
databases would be following:
Participating databases = Identity increment = 3
DB1 (publisher) - 1, 4, 7, 10, ...
DB2 (subscriber) - 2, 5, 8, 11, ...
DB3 (subscriber) - 4, 6, 9, 12, ...
If for some reason such approach is not suitable for you it does not mean
that it will not work, because it WORKS

Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Ron Lounsbury" <rlounsbury@.bogusAddress.com> wrote in message
news:eoTXGFMHEHA.2432@.TK2MSFTNGP11.phx.gbl...
> Actually Kestutis, you will end up with repeated identities any way unless
> you set ranges - which Paul specifically said that he is not doing. This
> also makes sense if the identity is matched with some form of server or
> location value to make up a unique value.
> Ron L
>
> "Kestutis Adomavicius" <kicker@.nospam-mail.lt> wrote in message
> news:Ot6qAgLHEHA.3584@.TK2MSFTNGP09.phx.gbl...
> table,
> change
> you
applying
would
involved
identity
should
"Max
> are
identity
not
the
> at
> table,
tried
> identity
can
> I
>
|||What is the point of having identity column (usually primary keys) values
repeated ..?
If this column is not a primary key, then I think you will have to create
not identity column and increment it manually (on insert trigger) on every
database..
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Ron Lounsbury" <rlounsbury@.bogusAddress.com> wrote in message
news:eSX$rTNHEHA.3564@.TK2MSFTNGP09.phx.gbl...
> Paul
> The fact here is that we want them repeated. We want Server1 to have
> sequence numbers 1 - ..., Server2 to have sequence numvers 1 - ..., etc.
> Ron L
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:%23DhdlwMHEHA.3528@.TK2MSFTNGP09.phx.gbl...
> increment
If
forget
>
|||Statement "Identity increment is set to a number of databases participating
in transaction and on every database identity increment is set to a number
increased by 1." should be "Identity increment is set to a number of
databases participating in transaction and on every database identity seed
is set to a number increased by 1."
Sorry for mistype
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Kestutis Adomavicius" <kicker@.nospam-mail.lt> wrote in message
news:eYHzmyTHEHA.1944@.TK2MSFTNGP11.phx.gbl...
> No, you probably missed the point.. Identity increment is set to a number
of
> databases participating in transaction and on every database identity
> increment is set to a number increased by 1. So identities on different
> databases would be following:
> Participating databases = Identity increment = 3
> DB1 (publisher) - 1, 4, 7, 10, ...
> DB2 (subscriber) - 2, 5, 8, 11, ...
> DB3 (subscriber) - 4, 6, 9, 12, ...
> If for some reason such approach is not suitable for you it does not mean
> that it will not work, because it WORKS

> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
|||I have discovered what was happening. There is a bug in SQL Server where a
process "appears" to be non-yielding on scheduler. This is why the post
snapshot script hung. The same script works on a different machine. There is
a fix for the problem, but you have to call Microsoft, an incident. If they
decide it is the bug, they have a fix and credit you the incident. This is
the second hot fix I I needed since installing SP3A.
Thanks,
Paul
"Raj Moloye" <rkmoloye@.hotmail.com> wrote in message
news:eSieV1AHEHA.1432@.TK2MSFTNGP12.phx.gbl...
> Hello Paul,
> I have similar implementation that I install at my customers. I have
devised
> some extensive tools to manage all the identity stuff and work well. One
of
> them is to create a job that executes the DBCC command. This may apply to
> you.
> Hope this helps,
> Raj
>

Identity Values

Hello,
I have the Following Problem:
I have some tables on a SQL Server database that have primary keys
without the identity property.
This was necessary for importing data from old databases...
Now I want to change some primary key columns to be an Identity.
Of crourse the identity seed should be set to a value higher than the
highest existing value in the column.
I can do that easily with the Enterprise Manager, but how can I do that
with transact sql statements?
Regards FerdinandMake the changes in the table designer of EM but don't save them yet. Then
look on the toolbar for the button that is 3rd from the left. It will show
you how EM makes the changes.
Andrew J. Kelly SQL MVP
"Ferdinand Zaubzer" <ferdl@.gmx.at> wrote in message
news:uXnBWaeHGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have the Following Problem:
> I have some tables on a SQL Server database that have primary keys without
> the identity property.
> This was necessary for importing data from old databases...
> Now I want to change some primary key columns to be an Identity.
> Of crourse the identity seed should be set to a value higher than the
> highest existing value in the column.
> I can do that easily with the Enterprise Manager, but how can I do that
> with transact sql statements?
> Regards Ferdinand|||Why would you destroy a perfectly good primary key by replacing it with an
identity column?
I know there are different schools of thought on this one, but I have always
found a logical primary key based on the actual values in the table to be
far more intuitive than an identity field, which is little more than an
artificial row number in my book. Having keys based on real values makes
joining to other tables far easier, even if slightly more typing and storage
is used in the process.
From a programming and maintainability perspective, I would stick with the
original primary keys.
Of course, this is just my opinion. Some folks consider an identity field
to be a requirement on every table. I avoid using them as a general rule.
"Ferdinand Zaubzer" <ferdl@.gmx.at> wrote in message
news:uXnBWaeHGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have the Following Problem:
> I have some tables on a SQL Server database that have primary keys
> without the identity property.
> This was necessary for importing data from old databases...
> Now I want to change some primary key columns to be an Identity.
> Of crourse the identity seed should be set to a value higher than the
> highest existing value in the column.
> I can do that easily with the Enterprise Manager, but how can I do that
> with transact sql statements?
> Regards Ferdinand

Identity Specification limit

what happens when a column marked as Identity Specification reaches the limit? for example, I have some code tables using tinyints as keys, the actual number of entries will be 20 or so but there is some volatility, so eventually the 255 limit will be reached, what happens then?

the same thing applies to ints or bigints used as keys, eventually the database must run out of numbers

information will be appreciated

David Wilson.

Hi,

When the maximum has been reached, an error will be generated. Here is an example:

--CREATE TABLE IDENTITYTEST
--(
-- ID TINYINT IDENTITY(1, 1),
-- TEXTVALUE VARCHAR(50)
--)

DECLARE @.COUNTER INT
SET @.COUNTER = 0

WHILE @.COUNTER < 260
BEGIN
INSERT INTO IDENTITYTEST(TEXTVALUE) VALUES ('VALUE ' + CAST(@.COUNTER AS VARCHAR(3)))
SET @.COUNTER = @.COUNTER + 1
END

/*
RESULT:
Msg 8115, Level 16, State 1, Line 12
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.
*/

Reference: http://msdn2.microsoft.com/en-us/library/ms186775.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

right, I did about the same thing (not quite as elegant :-)) - with the same result

the question is - How do you fix it? and how do you code something into a daily checkup routine or the like to find it and fix it before it happens?

Monday, March 19, 2012

Identity seed reset in SQL table

I have a test database that is being moved to the production server. Currently in one of the tables I have an identity seed for each record. Is there a way to reset it back to zero. I have deleted all my records but it still doesnt work, and I dont want to create a new table.

ThanksCheck out DBCC CHECKIDENT. This will do what you need.

FromSQL Server 2000 Books Online:

C. Force the current identity value to 30
This example forces the current identity value in the jobs table to a value of 30.

USE pubs
GO
DBCC CHECKIDENT (jobs, RESEED, 30)
GO

Terri|||I believe that if you issue a TRUNCATE TABLE command on the table, the identity seed will be reset to its original value.

Overall, a better strategy, since TRUNCATE TABLE is much faster than the corresponding delete statement...|||how do I do this?|||From within SQL Query Analyzer:

TRUNCATE TABLE tablename
GO

Be careful. This will delete all of the records in your table, swiftly and immediately, upon hitting that magic F5 key (or upon hitting the run arrow).|||mmmm truncate table... :) I've scared many of people with that.

Best thing to do is put that thing in a transaction just in case.

identity seed in sql server not picking next val after getting tables from access

Hi All
I am converting access database into sql server and i am using DTS to import the database into sql server.My question is that my identity columns are not picking the next value for identity col,it picks some random value.I want this to pick the next value of the max value which it imported from access table ( autonumber column) what do i do
Any suggestions would be helpful
Thank
sudhaUse the DBCC CHECKIDENT command to fix the value (example from Books
OnLine):
USE pubs
GO
DBCC CHECKIDENT (jobs)
GO
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"sudha" <anonymous@.discussions.microsoft.com> wrote in message
news:69143797-CAA1-448D-86A5-7729FD6B04ED@.microsoft.com...
> Hi All,
> I am converting access database into sql server and i am using DTS to
import the database into sql server.My question is that my identity columns
are not picking the next value for identity col,it picks some random value.I
want this to pick the next value of the max value which it imported from
access table ( autonumber column) what do i do.
> Any suggestions would be helpful.
> Thanks
> sudha|||thanks for the info.

identity seed in sql server not picking next val after getting tables from access

Hi All,
I am converting access database into sql server and i am using DTS to import
the database into sql server.My question is that my identity columns are no
t picking the next value for identity col,it picks some random value.I want
this to pick the next value
of the max value which it imported from access table ( autonumber column) wh
at do i do.
Any suggestions would be helpful.
Thanks
sudhaUse the DBCC CHECKIDENT command to fix the value (example from Books
OnLine):
USE pubs
GO
DBCC CHECKIDENT (jobs)
GO
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"sudha" <anonymous@.discussions.microsoft.com> wrote in message
news:69143797-CAA1-448D-86A5-7729FD6B04ED@.microsoft.com...
> Hi All,
> I am converting access database into sql server and i am using DTS to
import the database into sql server.My question is that my identity columns
are not picking the next value for identity col,it picks some random value.I
want this to pick the next value of the max value which it imported from
access table ( autonumber column) what do i do.
> Any suggestions would be helpful.
> Thanks
> sudha|||thanks for the info.

Identity Seed gone bad in merge replication

Sever: sql 2000
Replication: Merge

Issue:
I am having an issue with my audit table, This table is filled by Triggers on various tables through the database. All triggers are defied with "not for replication"

I have allocated 500k ranges, with 80% threshold to the publisher and subscriber databases for this table. The table only holds 225,000 records.

From time to time I get the following error "The identity range managed by replication is full and must be updated by a replication agent. The INSERT conflict occurred in database 'PublicationName', table 'AuditHistory', column 'AuditID'. Sp_adjustpublisheridentityrange can be called to get a new identity range."

When I looked into the issue yesterday I noticed that the identity range being used by replication was 334300001 -> 334799999, however the maximum value in the table was 334300096, meaning that only 95 records were inserted, which means it is no where near the 80% threshold.

Somehow the identity seed on the AuditHistory table had been changed to 334800104, which is outside the allowable range.

My question is what could cause the identity seed to get set to such a high number??

any thoughts would be great!Identity values are used up even if the insert is rolled back. I doubt you had 490,000 rollbacks, though.|||I agree, we also use hibernate in the weblayer if that makes any difference!

Friday, March 30, 2012

if i want to join two sql tables ...

IF funcionality in SQL server views

IF funcionality in SQL server views

IF funcionality in SQL server views

IF funcionality in SQL server views

IF funcionality in SQL server views

Wednesday, March 28, 2012

If Else Condition

Monday, March 26, 2012

if date_made > or = to birth_date +30

IF Condition in Join?

If body contains two tables, report preview breaks are getting upset

Friday, March 23, 2012

Identity/Seed Values

Wednesday, March 21, 2012

Identyfying which tables have gone into a view

Identity/Seed Values

Identity values is checnges by initial snapshot

Identity Values

Identity Specification limit

Monday, March 19, 2012

Identity seed reset in SQL table

identity seed in sql server not picking next val after getting tables from access

identity seed in sql server not picking next val after getting tables from access

Identity Seed gone bad in merge replication

Identity column

Blog Archive

About Me