Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info.
Please enter one or more search terms:
search (case insensitive):

order by:

SEARCH RESULT Results 1 - 25 of 722 for Subquery. (0.390015 seconds)

a question about select - sql subqueries
Thread ID: 266218 Fedor Daniel Morales Sueno a question about select - sql subqueries hi everyone... i have a question.... i has a table like this our freight company charges as follows: (1)the maximum price, i mean, 34 * 19.23 (2)district with its own price, i mean 6 * 14.23 + 13.23 * 3 + 12.23 * 4 + .... (3)and to maximum cargo, i mean, 15.23 (bcoz the maximum cargo = 10) *34 in fact, the whole operation is charged in one of the above terms and freight depend on the plan ... i do this select operacion, max(Freight), max(Tons) from table group by operation where operation = 0002022 for the first two options... but the third option is the problem... i want to use a select subquery

Access vfp cursor from sql exec
Thread ID: 343329 tom knauf Access vfp cursor from sql exec Hello to all, I have data in a foxpro cursor c_test (pk_test I, xname C(40),...) with 10-100 records (Prepared from remote views, added records, deleted others,...) Now I want to get data from sql server tables based on pks of this cursor, in VFP a subquery (or join,..) Something like Sqlexec(v_handle,"Select * from sqltable where pk_sql in (Select pk_test from c_test)","c_result") This does not work because sql-server does not know about vfp cursor. What will be the best way to solve this ? I do not want to create all with joins. Of course I can skip through c_test and get data with a single query for each record, but maybe there is a more

aggregate function in having clause
Thread ID: 233346 jean-sébastien gagnon aggregate function in having clause Hi I have this query that returns an error "invalid having clause". Is this the right way of using MAX() in the having clause? select notaux from taux HAVING MAX(datedebut) where taux.noempl=227 INTO CURSOR test I would use TOP 1 with ORDER BY to achieve the results but this is a subquery thanks !

Avoid repeating subquery in Where?
Thread ID: 294746 Samir H. Avoid repeating subquery in Where? The following WHERE clause is the same for my 2 queries, I will use it twice: In the query below and in the following one. The problem is that there is a subquery in that Where clause. I would like to avoid running the same subquery twice. How can I do that? I have tried to create the subquery first, as a cursor, and then put cursor name as a subquery in the IN of the WHERE clause..._rec IN ; ; ; && The subquery below is the one I want to re-use (SELECT cours_rec FROM cours_stud WHERE stud_rec=nStudRec) ; ) ; INTO CURSOR xAllComp NOFILTER BROWSE * my second query uses the exact

break union query ?
Thread ID: 112214 christ break union query ? dear all, How 2 change query that using UNION clause with SubQuery or break this UNION query to 2 different query than\ks

CASTing .NULL. as empty string
Thread ID: 145070 Ilya Rabyy CASTing .NULL. as empty string Colleagues, I need to convert .NULL. into empty string. This .NULL. results from subquery in a CAST((subquery) AS C(N)) AS FldName construct in a SELECT statement. Here's the code snippet: USE COMPANY IN 0 ALIAS COMPANY SHARED USE COMPANY IN 0 ALIAS COM_PARENT SHARED AGAIN SELECT COMPANY.com_Name AS Company_Name, ; CAST((SELECT com_Name FROM COM_PARENT WHERE ; COM_PARENT.com_PK=COMPANY.com_ParentComFK) AS C(75)) AS Parent_Name ; FROM COMPANY INTO CURSOR test WHERE COMPANY.com_PK BETWEEN lcMin AND lcMax This SELECT subquery inside the CAST() may return .NULL. if the company has no "parent" company

Corelated subquery problem
Thread ID: 111727 Deane Robieson Corelated subquery problem Hello All. I am having some trouble with a query I have written and am hoping someone can tell me what I am doing wrong. BACKGROUND... I have developed a system for a Pawnbroker. When the Pawnbroker makes a loan, he accepts 1 or more articles of value as security that are placed on shelves in his premises. A copy of the loan agreement (with the loan number printed as a bar-code) is attached to each article. Each shelf is numbered (barcode) and the Pawnbroker enters the shelf number in the system so he can easily locate the customers article(s) once the loan is repaid. I want to enable the Pawnbroker to do regular 'stocktakes' by scanning

Data from another table (no join)
Thread ID: 243813 tom knauf Data from another table (no join) Hello to all, I have a maybe stupid simple question : I tried to combine data from 2 tables without joining them, just to have the info in all records, something like 2 selects in one call not being a subquery. Example : I want the fields nname and pname from table "adr" and want to add a remark value from table "remarks". I think of forcing a only 1 remark by top 1 : Select nname, pname from adr, (select top 1 rem from remarks where remdate=date() ) as arem into cursor mytest It does not work, I get an error error in select statement Is something like this possible ? How ? (Yes, I could add a field space(20) as arem and fill

Error 34
Thread ID: 249800 Jim Carls Error 34 I'm trying to test the first exe of a major revision to an existing app, and I'm getting a strange error when it opens certain views. The error is error 34, "Operation is invalid for a Memo, Blob, General, or Picture field." I found that my executable had a typo in the SET ENGINEBEHAVIOR 70 statement that was correct in the setup for my development environment, and that fixed the problem. However, the help section's explanation for error 34 did not make any sense when I looked at the SQL statement that triggered it, so I'm wondering what really was wrong. The only thing unusual about the SQL was the use of a subquery that consisted of a primary key and two SUMmed values

Europa preview, Wednesday evening, teasing Yer all
# of unions (ok you know … 9 now) Unlimited # of tables in an SQL statement (now 30) Unlimited # of IN() arguments (now 24)… Multiple subquery nestings… An example? Select … where … (select … where … group by …(select … where

Get MAX(DATE) in SQL Statement
Thread ID: 407845 Cecil Champenois Get MAX(DATE) in SQL Statement Sometimes, we can restrict too tightly our SQL statements so that the objective is not met. We end up not seeing records we should see. I am experiencing that phenomena right now where if I do use a JOIN statement that has a subquery in it, I actually screen out records I want to see. I don't want to have two or more records where I want the latest Effective Date, but in placing it in the subquery... is a subquery but too restrictive somehow. SELECT ; cv.grpid, ; cv.grpnum, ; ins.LastName, ; ins.FirstName, ; cv.bnf AS Vol, ; cv.planKey, ; cv.Status, ; cv.Eff_Date, ; cv.Chg_Date, ; CV.Maint_Dat ; FROM

Help to build a query
Thread ID: 19896 Edgar Salcedo Help to build a query Hello i have the half of my query builded: select * from compromisos where Sys(11,compromisos.feccompromiso) >= sys(11,m.ini) and; Sys(11,compromisos.feccompromiso) <= Sys(11,m.fin); order by cliente; into cursor curcompromisos Repor form compromisos previ this query is for a range of date, but i need to include here to validate the date <= to m.ini and a logical field equal .T. How i do this, do i have to do a subquery if it is so, how? Thanks for your help.

Help with SQL subquery
Thread ID: 153059 Benny Thomas Help with SQL subquery I have a invoice table, among other fields, there are invoice_date, and client_code. I need to build a cursor of those invocies within a given date (eg. 01/01/07 to 31/12/07) for those client_code who has no invoice for the corresponding period in the previous year (ie., 01/01/06 to 31/12/06). The idea is to check the new clients during the given period. Can somebody guide me with the SQL Statement for this query? Thanks. Benny

In Query to VFP using OLEDB
Thread ID: 164458 Cetin Basoz In Query to VFP using OLEDB I can't believe I'd ask this question one day when I was giving suggestions on the same subject to others:) Anyway here it goes: I need to construct an IN subquery to VFP over VFPOLEDB. The values are in a local cursor and assume all are integer PKs. How would I do it in an easy and efficient way? Any idea? create cursor PKList (pk i) for ix = 1 to 5000 insert into PKList values (m.ix) endfor Now how to make an IN query with this cursor using VFPOLEDB keeping in mind I'm after an efficient one? Currently the most effective approach I have is to create a temp table, insert and then join. Second option creating an SP that accepts

invalid sub query
Thread ID: 228579 ven chin invalid sub query Dear all, I would like to retrieve the desc of my origin, destination code joining with some other tables. My SQL as below but it kept prompting me invalid subquery. I tried to search from here and modified my query base on the suggestion but it still fail. Below are my 2 query which both also invalid. SELECT h.custcode+h.custcode+h.vslcode+h.voycode+h.cbound as BatchID, c.cust_cn, h.vslname, h.voycode, h.cbound, ; h.invdate, d.etapol, d.origin, d.pol, d.pod, d.deliveryp, ; d.blno, d.equiptype, d.qty, 'P' as freight, d.chrgcode, IIF(d.chrgccy = 'THC','CNY',d.chrgccy) as chrgccy, ; d.chrgrate, IIF(d.chrgccy = 'USD', d.amt, 0.00) as usdamt, IIF(d.chrgccy

Invalid Subquery
Thread ID: 140236 Marlon Levy Invalid Subquery Good Day all Can anyone indicate why the below query yields a result of invalid subquery when executed. Both queries on their own run prefect SELECT * FROM adjustmentmst ; WHERE adjust_id; IN (SELECT adjustmentdtl.adjust_id,SUM(adj_bal) ; FROM adjustmentdtl GROUP BY 1 WHERE adjustmentdtl.adjust_id=1) There are two tables one being sort of like a invoice master, however, the invoice total is to be calculated based on the detail records and not stored on the master record. this one has got me stuck for days. Any help would be appreciated Marlon Levy marlonlevy@gmail.com

Invalid Subquery Ponder
Thread ID: 221755 Steve Korbie Invalid Subquery Ponder Good day foxites, It has been a long while since I have posted on here. I produced the following statement in SQL and it worked fine on the SQL table, but when I transported it in to the foxpro command window to run on a foxpro dbf, I received an 'Invalid use of subquery' error. My ultimate gold is to update the active flag based on the 'Where' criteria below. I have researched a lot of sites, but nothing seems to work to resolve the error SELECT DISTINCT INVENTOR.partno, INVENTOR.category, INVENTOR.subcategory, ; INVENTOR.title, INVENTOR.qtyonhand, INVENTOR.active ; FROM INVENTOR ; INNER JOIN PARTHIST ON INVENTOR.PARTNO

Joining subquery vs. separate cursor
Thread ID: 232265 Jim Carls Joining subquery vs. separate cursor I have a view for a "worksheet" grid that includes a subquery. The view lists the contents of rooms, where the contents are assemblies or "objects" of one or more components, with the #1 component (defined by the Obj_Order field) always included as the primary or identifying component (so there is only one record per object per room in the result). The purpose of the subquery is to build a list of the objects that meet variable conditions that any of their components may satisfy (e.g., a list of objects that use a particular vendor in any component). The subquery is joined to the tables in the relational structure to return

Need help with SQL SELECT statement
anyway, and I don't like it. Can something similar be done with a subquery on that LUP table? TIA! Regards, Ilya

Numeric widths and NVL()
Thread ID: 236664 Jim Carls Numeric widths and NVL() I'm having a problem with a defined view that shows purchase orders in a project-oriented application. I have added a macroed filter to the WHERE clause that may result in an empty set, but when this happens, I get the old "view definition has changed" error. Although I've checked for null values that need an NVL() wrapper, I believe the problem is in the subquery containing the filter...
Philadelphia VFP User Group-Tues July 12
as you need something a little more complex, you get tangled up in inner joins vs. outer joins and where to use a subquery and so forth. This session will bridge the gap between simple and complex queries with a deep dive into the JOIN

possible in a single select statement?
Thread ID: 334155 noah hamm possible in a single select statement? I'm trying to select all fields from a table for a given id and the most recent timestamp. multiple records with same id, different timestamp. I'd really like to do this with a single select statement. but have not figured out how. I've been trying to rewrite this in a single select statement so it can be run from R. (without success so far) When I tried to use Max() in a subquery fieldlist I got the max value for the table not for the grouping by id. Thanks! Noah SET ANSI on SELECT distinct guniqueid as gq, CTOT("") as ts FROM nestlog INTO CURSOR GU readwrite SELECT nestlog COUNT ALL TO ct FOR i=1 TO ct GO i STORE guniqueid

Query with a subquery
Thread ID: 312726 Cecil Champenois Query with a subquery I keep getting an error that states "ALIAS not found". Why? SELECT Contract + GOVT_ID ; FROM cDetail ; WHERE ; ALLTRIM(Contract)+ALLTRIM(GOVT_ID) NOT IN (SELECT ALLTRIM(Contract)+ALLTRIM(GOVT_ID) ; FROM ECS_OFAC) AND ; NOT DELETED(Contract) ; ORDER BY Contract, GOVT_ID ; INTO CURSOR tmpContract Cecil Champenois, Jr.

Query with Filter and Outer Join?
, I have not been able to find a way to list out the rest of their injury codes as well. Is there a way to do this with an outer join or subquery? Thanks in advance for any insight...

RE: "GROUP BY is missing or invalid"
subquery. Regards, Ilya

Result Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29