help on query

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

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:ZrydnROKGqA3psrfRVn-uQ@.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
>|||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" <jbellnewsposts@.hotmail.com> wrote in message
news:42578d55$0$26738$db0fefd9@.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:ZrydnROKGqA3psrfRVn-uQ@.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|||Hi

Creating a view would overcome that problem.

John

"B" <no_spam@.no_spam.com> wrote in message
news:JMSdnZXT2LmwK8rfRVn-iQ@.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" <jbellnewsposts@.hotmail.com> wrote in message
> news:42578d55$0$26738$db0fefd9@.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:ZrydnROKGqA3psrfRVn-uQ@.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
>>>>>>>
>>|||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.