Showing posts with label advice. Show all posts
Showing posts with label advice. Show all posts

Monday, March 26, 2012

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.

Monday, March 12, 2012

help needed to bring in text files..

background: sql2k on nt5.
can someone give me some advice on how to bring in text files located on a
file server directory into a sql server database?
the text files will always have the same structure, but the file names are
not static (but following a naming standard, such as
location+date.txt, such as
or_05102004
wa_05122004
and so on.
so each file will be broung in as a table such as or_05102004, and
wa_05122004 table.
thank you.
Will you be loading all of the text files into one table, or does each =
type (or, wa, ...) have a different table that it should be loaded into? =
=20
Perhaps you could use BCP or BULK INSERT to import the text files. You =
can define a format file if you choose to use BCP. This format file =
will define the layout of the file and it will determine how it gets =
loaded into the destination table. You could create a stored procedure =
that would take a filename as a param and use a bit of dynamic sql to =
generate the appropriate BCP statement that uses the appropriate format =
file.
Another method would be DTS. You could create a DTS package to insert =
the data into SQL Server. This solution might be difficult to maintain =
if the file names change often. Then again, you could create a routine =
that imports from a standard file name. When you get a new file (on the =
network share) you could copy it to the specific location and file name =
that your DTS package knows about.
--=20
Keith
"=3D=3D Steve Pdx=3D=3D" <lins@.nospam.portptld.com> wrote in message =
news:uCHOz%23sNEHA.1196@.TK2MSFTNGP11.phx.gbl...
> background: sql2k on nt5.
>=20
> can someone give me some advice on how to bring in text files located =
on a
> file server directory into a sql server database?
> the text files will always have the same structure, but the file names =
are
> not static (but following a naming standard, such as
> location+date.txt, such as
> or_05102004
> wa_05122004
> and so on.
>=20
> so each file will be broung in as a table such as or_05102004, and
> wa_05122004 table.
>=20
> thank you.
>=20
>

help needed to bring in text files..

background: sql2k on nt5.
can someone give me some advice on how to bring in text files located on a
file server directory into a sql server database?
the text files will always have the same structure, but the file names are
not static (but following a naming standard, such as
location+date.txt, such as
or_05102004
wa_05122004
and so on.
so each file will be broung in as a table such as or_05102004, and
wa_05122004 table.
thank you.Will you be loading all of the text files into one table, or does each =
type (or, wa, ...) have a different table that it should be loaded into? =
=20
Perhaps you could use BCP or BULK INSERT to import the text files. You =
can define a format file if you choose to use BCP. This format file =
will define the layout of the file and it will determine how it gets =
loaded into the destination table. You could create a stored procedure =
that would take a filename as a param and use a bit of dynamic sql to =
generate the appropriate BCP statement that uses the appropriate format =
file.
Another method would be DTS. You could create a DTS package to insert =
the data into SQL Server. This solution might be difficult to maintain =
if the file names change often. Then again, you could create a routine =
that imports from a standard file name. When you get a new file (on the =
network share) you could copy it to the specific location and file name =
that your DTS package knows about.
--=20
Keith
"=3D=3D Steve Pdx=3D=3D" <lins@.nospam.portptld.com> wrote in message =
news:uCHOz%23sNEHA.1196@.TK2MSFTNGP11.phx.gbl...
> background: sql2k on nt5.
>=20
> can someone give me some advice on how to bring in text files located =
on a
> file server directory into a sql server database?
> the text files will always have the same structure, but the file names =
are
> not static (but following a naming standard, such as
> location+date.txt, such as
> or_05102004
> wa_05122004
> and so on.
>=20
> so each file will be broung in as a table such as or_05102004, and
> wa_05122004 table.
>=20
> thank you.
>=20
>

help needed to bring in text files..

background: sql2k on nt5.
can someone give me some advice on how to bring in text files located on a
file server directory into a sql server database?
the text files will always have the same structure, but the file names are
not static (but following a naming standard, such as
location+date.txt, such as
or_05102004
wa_05122004
and so on.
so each file will be broung in as a table such as or_05102004, and
wa_05122004 table.
thank you.Will you be loading all of the text files into one table, or does each =type (or, wa, ...) have a different table that it should be loaded into? =
Perhaps you could use BCP or BULK INSERT to import the text files. You =can define a format file if you choose to use BCP. This format file =will define the layout of the file and it will determine how it gets =loaded into the destination table. You could create a stored procedure =that would take a filename as a param and use a bit of dynamic sql to =generate the appropriate BCP statement that uses the appropriate format =file.
Another method would be DTS. You could create a DTS package to insert =the data into SQL Server. This solution might be difficult to maintain =if the file names change often. Then again, you could create a routine =that imports from a standard file name. When you get a new file (on the =network share) you could copy it to the specific location and file name =that your DTS package knows about.
-- Keith
"=3D=3D Steve Pdx=3D=3D" <lins@.nospam.portptld.com> wrote in message =news:uCHOz%23sNEHA.1196@.TK2MSFTNGP11.phx.gbl...
> background: sql2k on nt5.
> > can someone give me some advice on how to bring in text files located =on a
> file server directory into a sql server database?
> the text files will always have the same structure, but the file names =are
> not static (but following a naming standard, such as
> location+date.txt, such as
> or_05102004
> wa_05122004
> and so on.
> > so each file will be broung in as a table such as or_05102004, and
> wa_05122004 table.
> > thank you.
> >