FoxPro Help and Support - Re: Can I get Consultancy advice on SQL/Fox in the UK (South East)

Author

Re: Can I get Consultancy advice on SQL/Fox in the UK (South East)

Barley Man

2005-05-29, 11:24 am

Thanks Cindy, I am working through that but.....

My problem is the two Sequel Server tables within one database with which I
need to work are 'nastily' linked. I have managed to create views of each as
requierd. The principle is that it is a pair of files, (orderheader and
orderdetail). The key field is the orderheader.order field. It is a character
field which increments numerically (1000001, 1000002, 100003) with each new
order placed. Each order is made up of 'items' which can be ordered within a
single order (orderheader). Don't ask me why but the SQL designers have NOT
given an independent field for each part of an order but have concatenated a
'/' and the 'number' of the item from each order. So, header.order = 5678 and
it is assocaited with orderdetail.orderno 5678/1, 5678/2, 5678/3 and so on up
to an apparent max of 20 items on an order. I assume the need for the '/'
forced them to use character fields instead of numerical fields
(brilliant!!!).

Secondly, only the orderheader contains the date of the order and I need to
extract/collect a single file of combined orderheader and orderdetails for
'this week, last week and this week last year. I know that the
orderheader.orderdate is held in the Sequel files in 'smalldatetime'.

I would thus like to produce an SQL command that would let me specify a
single week to pull through those records into a single file. Once I can link
to two files and produce a foxpro 'table' form the sequel server request, I
can work out how to bring the rest of the data which I need. I simply do not
know is enough Sequel Server commands to combine that request into a single
command that I can initiate with SQLCONNECT, SQLPROP SQLPREPARE (etc.)
command.

Any assistance with that little lot would get me out of deep...............

Ian

"Cindy Winegarden" wrote:

> Hi Ian,
>
> Again, do NOT define your view to select all the records from the table. You
> can't possibly _read_ all the records so don't retrieve them. To filter the
> data so you get only a reasonable number of records, do the following:
>
> 1. Open the View Designer, choose a connection, and add a table (or
> combination of tables).
> 2. Fields tab - Use the Add> or Add All>> buttons to choose the fields
> (columns) you want to see.
> 3. Join tab - you won't need anything here unless you are combining records
> from more than one table in your view. I assume you are using only one
> table.
> 4. Filter tab - This is where you can limit the number of records. I've got
> a view to a Counties table. In the box under "Field Name" - I'm choosing
> "County_Name" which is a field in my Counties table. There's a button under
> "Not" which you can click if you want to choose records that are not equal
> to your criteria. In my example I'm going to leave this alone. In the
> "Criteria" column I'm going to use "=". In the Example column is where the
> filter actually happens so I'll talk about that next.
> 5. Example column -
> A) First let's assume I always want the same subset of rows from my
> view. I want Counties beginning with 'A'. I pull down the list under the
> Field Name and choose <Expression...> from the list. A box comes up where I
> type Left(Counties.County_Name, 1). Moving across I choose = for the
> Criteria and I put 'A' in the Example box. When I save the view and then
> open it I get counties beginning with A.
> B) Let's say I don't want the same criteria every time I open the view -
> I want to specify it each time. Instead of putting 'A' in the Example box I
> put "?MyCounty" (without the quotes) in the box. When I save the view and
> then open it I get an "input box" asking me to put in a value for MyCounty.
> I can put in 'A', 'B', or whatever I want. This gives me about 10 rows,
> which is a small enough amount that it is easy to work with.
> C) Let's say you don't want the input box coming up for the user to type
> in. You can specify a variable called MyCounty and give it a value. As long
> as the variable is in scope the view will find it. For example, I create a
> variable and give it a value by typing MyCounty = 'A' in the Command window.
> When I open the view, FoxPro notices that I've got a variable with the right
> name available and uses that for the view.
>
> Now, I can specify more than one criteria by adding rows to the list on the
> Filter tab. For example, rather than reading a whole telephone directory I
> could enter LastName = 'B' , City = 'Durham' , and Street = 'Elm'. This way
> I get only a few rows from the server.
>
> While you're in the View Designer, right-click and view the SQL code. You
> can create a view in code, without using the View Designer at all by typing
> code in a PRG file and then running it. You start out with "Create SQL View
> As ", followed by the SQL code. Check out the Create SQL View command in
> Help.
>
> Please post back with more questions after you've tried the steps above.
>
>
> --
> Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
> cindy_winegarden@msn
.com www.cindywinegarden.com
> Blog: http://spaces.msn.com/members/cindywinegarden
>
>
> "Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
> news:CC68B9B2-599A-439F-B489- A4816C6B74D9@microso
ft.com...
>
>
>

Barley Man

2005-05-29, 8:24 pm

PS. I didn't word some of the well. Key fields in the two tables are:-

orderheader.orderno char(8)

orderdetails.orderline char(12)

It's a one-to-many relationship with the orderheader being the 'one'. In
Fox, i'd use:-

LEFT(orderdetail.orderline, 8)=orderheader.orderno

I hope that expresses it better.

Ian

"Barley Man" wrote:
[color=darkred]
> Thanks Cindy, I am working through that but.....
>
> My problem is the two Sequel Server tables within one database with which I
> need to work are 'nastily' linked. I have managed to create views of each as
> requierd. The principle is that it is a pair of files, (orderheader and
> orderdetail). The key field is the orderheader.order field. It is a character
> field which increments numerically (1000001, 1000002, 100003) with each new
> order placed. Each order is made up of 'items' which can be ordered within a
> single order (orderheader). Don't ask me why but the SQL designers have NOT
> given an independent field for each part of an order but have concatenated a
> '/' and the 'number' of the item from each order. So, header.order = 5678 and
> it is assocaited with orderdetail.orderno 5678/1, 5678/2, 5678/3 and so on up
> to an apparent max of 20 items on an order. I assume the need for the '/'
> forced them to use character fields instead of numerical fields
> (brilliant!!!).
>
> Secondly, only the orderheader contains the date of the order and I need to
> extract/collect a single file of combined orderheader and orderdetails for
> 'this week, last week and this week last year. I know that the
> orderheader.orderdate is held in the Sequel files in 'smalldatetime'.
>
> I would thus like to produce an SQL command that would let me specify a
> single week to pull through those records into a single file. Once I can link
> to two files and produce a foxpro 'table' form the sequel server request, I
> can work out how to bring the rest of the data which I need. I simply do not
> know is enough Sequel Server commands to combine that request into a single
> command that I can initiate with SQLCONNECT, SQLPROP SQLPREPARE (etc.)
> command.
>
> Any assistance with that little lot would get me out of deep...............
>
> Ian
>
> "Cindy Winegarden" wrote:
>

Cindy Winegarden

2005-06-01, 11:24 am

Hi Ian,

Sorry to be late getting back to this - I was at the beach!

Sounds like you need to send something like the following SQL command string
to the database:

*-- -----------------------------------
ldWeekStart = dSomeDate
ldWeekEnd = dSomeDate

Text To lcSQLString TextMerge NoShow

Select
'This Week' As WhichWeek,
Oh.SomeFields,
Od.SomeFields
From
OrderHeader Oh
Left Join OrderDetails Od On Oh.OrderNo = Left(Od, 8)
Where
Oh.OrderDate Between '<<DtoS(ldWeekStart)>>' And '<<DtoS(ldWeekEnd)>>'

Union

Select
'Last Week' As WhichWeek,
Oh.SomeFields,
Od.SomeFields
From
OrderHeader Oh
Left Join OrderDetails Od On Oh.OrderNo = Left(Od, 8)
Where
Oh.OrderDate Between '<<DtoS(ldWeekStart - 7)>>' And '<<DtoS(ldWeekEnd -
7)>>'

Union

Select
'Last Year' As WhichWeek,
Oh.SomeFields,
Od.SomeFields
From
OrderHeader Oh
Left Join OrderDetails Od On Oh.OrderNo = Left(Od, 8)
Where
Oh.OrderDate Between '<<DtoS(ldWeekStart - 365)>>' And
'<<DtoS(ldWeekEnd - 365)>>'

EndText

nSuccess = SQLEx(nConn, lcSQLString, "ResultCursor")
*-- -----------------------------------

All of the SQL Server documentation is in the "Books Online" which is
downloadable from
http://www.microsoft.com/sql/techin...00/default.mspx .

Please note, I have not tested the above SQL so there may be typos, you may
need to adjust the number of characters in the Left() function, and you may
need to adjust for leap year.

--
Cindy Winegarden MCSD, Microsoft Visual Foxpro MVP
cindy_winegarden@msn
.com www.cindywinegarden.com
Blog: http://spaces.msn.com/members/cindywinegarden

begin 666 Try the new Microsoft Office System.url
M6TEN=&5R;F5T4VAO<G1C=71=#0I54DP]:'1T<#HO+V<N;7-N+F-O;2\P040P
M,# P42\V,3 X,#(N,3\_4$E$/3(R.3,Q,CDF54E4/4<F5&%R9V5T240],3 V
M,S8X." 9!3CTQ,#,R,R901SU#35
-343$-"DUO9&EF:65D/4(P0S)#0S@Y0S<V
)-D,U,#%"00T*
`
end

Sponsored Links