IF funcionality in SQL server views

3 Apr 2006 8:38 AM

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

3 Apr 2006 8:49 AM

Will

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

3 Apr 2006 8:51 AM

hongju

If you want to conditional view, you can make a view using Multi-statement
Function.

"the_raj"ë‹˜ì´ ìž‘ì„±í•œ ë‚´ìš©:

Show quoteHide quote

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

3 Apr 2006 9:32 AM

Omnibuzz

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