help on query

[B]

I need advice on how to approach this. To simplify with a sample, below is a
list of various calculations based from ITEMS table:
ITEM_NO FIELD_CALCULATION
123 cost*qty
111 cost-discount
222 sales*discount
333 cost-freight
etc...
There are 20 additional line items with different calculations.

From my SP, how do I incorporate the above to say:
SELECT ??
INTO #tmp1
FROM GL_ACCOUNT GL,
ITEMS I
WHERE GL.ITEM_NO = I.ITEM_NO

TIA!
Bob

[John Bell]

Hi

You could try something like:

SELECT I.Item_No,
CASE I.Item_No WHEN 123 THEN GL.cost*GL.qty
WHEN 111 THEN GL.cost-GL.discount
WHEN 222 THEN GL.sales*GL.discount
WHEN 333 THEN GL.cost-GL.freight
END AS [Calculation]
FROM GL_ACCOUNT GL,
JOIN ITEMS I ON GL.ITEM_NO = I.ITEM_NO

John

"B" <no_spam@no_spam.com> wrote in message
news:Zr********************@rcn.net...

I need advice on how to approach this. To simplify with a sample, below is
a
list of various calculations based from ITEMS table:
ITEM_NO FIELD_CALCULATION
123 cost*qty
111 cost-discount
222 sales*discount
333 cost-freight
etc...
There are 20 additional line items with different calculations.

From my SP, how do I incorporate the above to say:
SELECT ??
INTO #tmp1
FROM GL_ACCOUNT GL,
ITEMS I
WHERE GL.ITEM_NO = I.ITEM_NO

TIA!
Bob

[B]

I was hoping to use the table created as a source without having to hardcode
since it will be used by other SP.

Thank you for your time.

"John Bell" <jb************@hotmail.com> wrote in message
news:42***********************@news.zen.co.uk...

Hi

You could try something like:

SELECT I.Item_No,
CASE I.Item_No WHEN 123 THEN GL.cost*GL.qty
WHEN 111 THEN GL.cost-GL.discount
WHEN 222 THEN GL.sales*GL.discount
WHEN 333 THEN GL.cost-GL.freight
END AS [Calculation]
FROM GL_ACCOUNT GL,
JOIN ITEMS I ON GL.ITEM_NO = I.ITEM_NO

John

"B" <no_spam@no_spam.com> wrote in message
news:Zr********************@rcn.net...

I need advice on how to approach this. To simplify with a sample, below isa
list of various calculations based from ITEMS table:
ITEM_NO FIELD_CALCULATION
123 cost*qty
111 cost-discount
222 sales*discount
333 cost-freight
etc...
There are 20 additional line items with different calculations.

From my SP, how do I incorporate the above to say:
SELECT ??
INTO #tmp1
FROM GL_ACCOUNT GL,
ITEMS I
WHERE GL.ITEM_NO = I.ITEM_NO

TIA!
Bob

[John Bell]

Hi

Creating a view would overcome that problem.

John

"B" <no_spam@no_spam.com> wrote in message
news:JM********************@rcn.net...

I was hoping to use the table created as a source without having to
hardcode
since it will be used by other SP.

Thank you for your time.

"John Bell" <jb************@hotmail.com> wrote in message
news:42***********************@news.zen.co.uk...

Hi

You could try something like:

SELECT I.Item_No,
CASE I.Item_No WHEN 123 THEN GL.cost*GL.qty
WHEN 111 THEN GL.cost-GL.discount
WHEN 222 THEN GL.sales*GL.discount
WHEN 333 THEN GL.cost-GL.freight
END AS [Calculation]
FROM GL_ACCOUNT GL,
JOIN ITEMS I ON GL.ITEM_NO = I.ITEM_NO

John

"B" <no_spam@no_spam.com> wrote in message
news:Zr********************@rcn.net...

>I need advice on how to approach this. To simplify with a sample, below is >a
> list of various calculations based from ITEMS table:
> ITEM_NO FIELD_CALCULATION
> 123 cost*qty
> 111 cost-discount
> 222 sales*discount
> 333 cost-freight
> etc...
> There are 20 additional line items with different calculations.
>
> From my SP, how do I incorporate the above to say:
> SELECT ??
> INTO #tmp1
> FROM GL_ACCOUNT GL,
> ITEMS I
> WHERE GL.ITEM_NO = I.ITEM_NO
>
> TIA!
> Bob
>
>
>
>
>

[--CELKO--]

Have you ever had a software engineering course? Probably not, because
this approach is a violaiton of the principle of cohesion and it
confuses data and code.