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

Identity range managed by replication is full and must be updated by a replication agent.

Hello,

I'm getting the following error message when I try add a row using a
Stored Procedure.

"The identity range managed by replication is full and must be updated
by a replication agent".

I read up on the subject and have tried the following solutions
according to MSDN without any luck.(http://support.Microsoft.com/kb/
304706 )

sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/
library/aa239401(SQL.80).aspx ) has no effect

For Testing:

I've reloaded everything from scratch, created the pulications from by
running the sql scripts generated,created replication snapshots and
started the agents.

I've checked the current Identity values in the Agent Table:

DBCC CHECKIDENT ('Agent', NORESEED)
Checking identity information: current identity value '18606', current
column value '18606'.

I check the Table to make sure there will be no conflicts with the
primary key:

SELECT AgentID FROM Agent ORDER BY AgentID DESC
18603 is the largest AgentID in the table.

Using the Table Article Properties in the Publications Properties
Dialog, I can see values of:

Range Size at Publisher: 100,000
Range Size at Subscribers: 100
New range @. percentage: 80

In my mind this means that the Publisher will assign a new range when
the Current Indentity value goes over 80,000?

The Identity range for this table cannot be exhausted! I'm not sure
what to try next.

Please! any insight will be of great help!
Regards,
Bm(miller.brettm@.gmail.com) writes:

Quote:

Originally Posted by

I'm getting the following error message when I try add a row using a
Stored Procedure.
>
"The identity range managed by replication is full and must be updated
by a replication agent".
>
I read up on the subject and have tried the following solutions
according to MSDN without any luck.(http://support.Microsoft.com/kb/
304706 )
>
sp_adjustpublisheridentityrange (http://msdn2.microsoft.com/en-us/
library/aa239401(SQL.80).aspx ) has no effect

You have better luck in microsoft.public.sqlsever.replication. Myself,
I have very little experience of replication.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx