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
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
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
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
* 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
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.
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
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)…
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.
cv.bnf AS Vol, ;
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.
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)
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
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 ;
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
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
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?
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.
SET ANSI on
SELECT distinct guniqueid as gq, CTOT("") as ts FROM nestlog INTO CURSOR GU readwrite
COUNT ALL TO ct
FOR i=1 TO ct
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 ;
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"