Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
-
SQL Syntax
Use a correlated sub-query to count distinct OrderNo's up to this row's OrderNo. update tablename t1 set toUpdate = (select count(distinct order) from tablename t2 where t2.OrderNo <= t1.OrderNo) -
Update Query with a MAX(Date) subquery
update orderstable t1 set Amt = (select max(Amt) from orderstable t2 where t2.SO = t1.SO) where not exists (select 1 from orderstable t3 where t3.SO = t1.SO and t3.Date > t1.Date) Set Amt to the highest Amt value for that SO (first... -
left join with "soft" condition
One possible solution: select * from t1 as tm1 left join t2 as tm2 on tm1.id = tm2.id and ABS((dt1 - dt2) day(4)) = (select min(ABS((dt1 - dt2) day(4))) from t1 join t2 on t1.id = t2.id where t1.id = tm1.id... -
Does this function follow ansi sql standard?
Does the table always contain exactly one row? If not, a cursor is needed! (Or a where clause that restricts the result set to one row...) You may also have to specify access mode to allow the table update: CREATE FUNCTION INCREMENT() RETURNS INT MODIFIES SQL DATA BEGIN ... -
Retrieve transaction report
Yeah, thats a part of the beaty of SQL, the same problem can be solved in completely different ways. (Somewhat confusing for beginners?) Note that my solution is Core SQL-99 compliant! (http://developer.mimer.com/validator/parser99/, a great time saver when writing "portable" SQL.) -
Retrieve transaction report
I guess you want something like: SELECT * FROM table_collection WHERE IC IN (SELECT IC FROM table_collection WHERE Mode_Of_Payment = 'Cash') i.e. all rows for a transaction where cash has been involved at least once. -
Delete Attachments for Table
Have you tried: delete from table1 where type = 'pdf' -
Match unlinked data based on datetimes
Why do you require separate fu-calls for different discharge dates? I mean, Bob Smith was discharged twice before he got his first fu-call, why does this call only count for the first discharged date? (If it doesn't count for both days, don't store that fu_call for both discharged days.) -
Retrieve duplicate records by date time
And an EXISTS solution: SELECT * FROM CLASS_TIME_TABLE A WHERE EXISTS ( SELECT * FROM CLASS_TIME_TABLE B WHERE A.NAME = B.NAME AND A.Date = B.Date AND A.Start_time = B.Start_time AND A.End_time = B.End_time GROUP BY NAME, Date, Start_time, End_time HAVING COUNT(*) > 1) Core SQL-99... -
SQL 2008 exclude parent records in results where multiple child records exist in 2nd table
Check out GROUP BY in combination with HAVING and COUNT(DISTINCT)! To find persons with two (or more) different id values: SELECT NameFirst, Surname, DOB FROM C GROUP BY NameFirst, Surname, DOB HAVING COUNT(DISTINCT id) > 1 The easiest way to get the result you're looking for is to add this... -
SQL 2008 exclude parent records in results where multiple child records exist in 2nd table
First, if you really want OUTER JOIN's instead of regular INNER JOIN's, put the join conditions in the ON clauses, not in the where clause. And also put the "outer" tables conditions in the ON clause. Personally, to make things easier to read and to understand, I'd turn the query around and use... -
Robohelp or ePublisher?
For many years I've been using FrameMaker 6, Adobe Acrobat 4, and WebWorks Publisher 7. The documentation source is a set of 4 different documents (different FM books, 1500 pages in total), with links/cross references between them, and lots of text set as conditional. The generated output is... -
SQL: Select Case when ...
Use CAST or SUBSTRING to get first 3 characters. -
SQL: Select Case when ...
ANSI SQL has no built-in functionality to convert dates to the format you want. Check your specific dbms manual, it might have something you can use. In ANSI SQL you can use EXTRACT to get each specific part of a date, i.e. EXTRACT(day from market_date). But then you have to use CAST to convert... -
SQL: Select Case when ...
The GROUP BY construction is typically used to "merge" several rows together into one row. Since you want one row per day, do group by the date column: SELECT market_date FROM yourtable GROUP BY market_date But for each date you do also want mid-values for 'FR.EUR.GBY.0.0.1' rows returned in a... -
SQL: Select Case when ...
How many rows per day does your table have? How many rows per day do you want to be returned? How many different "EURFRGBONDnY" columns are you actually having, is it just EURFRGBOND1Y and EURFRGBOND2Y, or do you have a whole bunch of them? The better you describe your problem, the better... -
SQL: Select Case when ...
Use CAST to convert values to a specific data type, e.g. cast(market_date as date). Note that the ANSI date is in format YYYY-MM-DD! It seems you want data per day? GROUP BY is used for SELECT market_date, setfn(CASE WHEN (rate_code='FR.EUR.GBY.0.0.1') THEN mid else 0 end) as... -
How to update a field while inserting a row
Have you considered a view, to get both the firm_code and the firm_id? I mean skip the firm_code in the office table, instead create a view that also includes the firm_code column. create view offices_view (office_id, office_code, firm_id, firm_code, name) as select office_id... -
Convert Date of Birth to Age, results a negative number
The Oracle date/time handling isn't ANSI compliant, so I suppose you'd better ask the Oracle people, http://www.tek-tips.com/threadminder.cfm?pid=1177 -
How to update a field while inserting a row
First, why do you want to store firm_id AND firm_code in the offices table? (See http://en.wikipedia.org/wiki/Data_redundancy) When it comes to your insert, if you choose the trigger solution you'll need one insert trigger and one update trigger to make sure firm_id and firm_code always are...
