|
I thought about what you both said, and would like to say thanks and show gratitude as well.
I went back and keep the Select statement raw, took the convert out.
The dumped my CRUD function to add the record, and converted the calling function to SQL.
Then used PHP to convert the raw date object I picked up to a string, and declared it in SQL.
I suppose I could go back to my add CRUD function and do the conversion there instead
$result2 = sqlsrv_query($conn, $query2) or die(" setDbProjectCost " . LINE . " - " . $query2 . " - " . print_r(sqlsrv_errors()));
if (sqlsrv_has_rows($result2)) {
$row2 = sqlsrv_fetch_array($result2);
$addDateString = $row2[13]->format('Y-m-d H:i:s') . '.000';
$query3 = "
DECLARE @addDate AS VARCHAR(33) = '$addDateString';
INSERT INTO [proj_cost]
VALUES ... @addDate
If it ain't broke don't fix it
Discover my world at jkirkerx.com
|
|
|
|
|
If I run a SQL statement, like this:
SELECT * FROM my_table
is there any SQL statement / trick to find the name of every column from '*' ?
Because if I write:
SELECT id, name, age FROM my_table
yes, I could extract the columns name, by text analyze. But how about '*' case ?
|
|
|
|
|
|
Yes, I know that, but the question is, how can I know the columns name for the following SELECT (programmatically):
SELECT * FROM table1, table 2 WHERE table1.id = table2.id;
?
modified 3-Jun-21 12:17pm.
|
|
|
|
|
I'd just do it the same way as for a single table:
- obtain column names for table1;
- obtain column names for table2;
- ...
|
|
|
|
|
So, you are saying that I need to do a static analyze on SELECT text and see what columns I have, if I enumerate the columns in the SELECT statement, then I have it, if I don't (I have '*') then I have to query the tables names / columns name and will find them. I thought there is a SQL trick to find the columns name in any circumstances.
|
|
|
|
|
I'd implement such a "trick" in a stored procedure that gets a query as an argument, then creates the temporary View, uses
SELECT [TABLE_NAME]
,[COLUMN_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS]
where TABLE_NAME = <your_temp_view> to return the recordset with the tables/columns names,
then delete the temp view.
PS: this should work with SQL Server 2008 and above.
|
|
|
|
|
Sound good ! Because this solution should be cross server platform, it is available on the other SQL servers, like Oracle, Informix, MySQL, and so on ?
|
|
|
|
|
You have to test it yourself! Just check out the links I gave you here
|
|
|
|
|
|
AFAIK, the OP develops in C++.
However, I may be wrong.
|
|
|
|
|
|
But it is managed C++/CLI. 
It has nothing to do with the native C++. 
|
|
|
|
|
Yeah, but ...
Using ADO.NET in MFC Projects
(And it's a "database" forum)
Before ADO.NET there was ADO, and MFC did ADO.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
modified 4-Jun-21 15:09pm.
|
|
|
|
|
Interesting idea. Thank you!
BTW, I used ADO in my big VS2010 project (C++/MFC with ADO with SQL Server) from 2009 to 2015, of course without any mixture with managed code! 
|
|
|
|
|
Try this select * from table1 where 1=1 this should return an empty datatable with all the columns. Simply iterate the columns to get their names.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Try:
SELECT [name]. [column_id] AS [Ordinal]
FROM sys.columns
WHERE [object_id] = OBJECT_ID('MyTable')
ORDER BY [column_id];
That will get the column names and ordinal position.
|
|
|
|
|
I suggest you read the documentation:
Column IDs might not be sequential.
If a column has ever been dropped from the table, you will end up with gaps in the column ID sequence.
To get a true ordinal position, you'd need to use the ROW_NUMBER windowing function - for example:
SELECT [name], ROW_NUMBER() OVER (ORDER BY [column_id]) As [Ordinal]
FROM sys.columns
WHERE [object_id] = OBJECT_ID('MyTable')
ORDER BY [column_id]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Can anyone help me write the answers to this question in sql, I couldn't manage to upload, a full question, can anyone help with a simple demonstration on this question based on any assumption
I) Find the Employers name, address of employers and the number of
students sponsored by each employee.
iv)
Find the average fees of students sponsored by employer with number 02
|
|
|
|
|
I guess no one could help you without seeing the database structure and the exact problem you have to do it yourself.
|
|
|
|
|
As Victor said, there is no way anyone can help without knowing what the table structure is.
Where are you stuck?
|
|
|
|
|
Have you read point #11 here?
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"You can easily judge the character of a man by how he treats those who can do nothing for him." - James D. Miles
|
|
|
|
|
You need to:
select from employers
join to students
group by
count
Then join that query to fees
group by
avg
Seriously if this is beyond you then withdraw from the course.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I'm working on a personal app to help me better manage my family's car maintenance schedules. These are two of the tables being used and their relationship: Schema. For a given car, a schedule is created where at least one row is added to the service_schedules table and multiple rows are added to the schedule_intervals table depending on the options chosen.
One example would be if a "rotate tires every 7500 miles" schedule was created, one row would be added to the service_schedules table and 33 related rows would be added to the schedule_intervals table, one for each of the 7500 mile intervals up to 250000 miles.
Another example would be if a "change oil every 25000 miles or 12 months" schedule was created, one 'mileage' row would be added to the service_schedules table and 10 related rows added to the schedule_intervals table, one for each of the 25000 mile intervals up to 250000 miles. A 'time' row would also be added to the service_schedules table and 15 related rows added to the schedule_intervals table, one for each year up to 15 years.
With this schema, I can easily do 'past due services' and 'upcoming services' queries. For example, I can find all upcoming services for vehicle 4 with current mileage of 163451 using:
SELECT * FROM schedule_intervals WHERE schedule_id IN (SELECT _id FROM service_schedules WHERE vehicle_id = 4) AND (163451 < mileage OR 1621540799076 < date) For vehicle 4, it has six service schedules for things like oil/filter (mileage or date), tires (mileage), spark plugs (mileage), transmission (mileage), etc, all of which create 155 rows in the schedule_intervals table. Using the above query, 114 rows are returned for any upcoming services. The issue I'd like to resolve, if possible, is to only show one of each service type (one of mileage and one of date). In other words, instead of showing all of the oil/filter services that are due (175000, 200000, 225000, and 250000, April 2022, April 2023, April 2024, ..., April 2036), I'd like to just show the next one of each type (175000, April 2022), as all other ones past that are irrelevant.
At this point, I don't know if I need to modify either of the two tables, and/or add a bit more complexity to the query.
Thoughts or ideas?
Thanks.
DC
"One man's wage rise is another man's price increase." - Harold Wilson
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
"You can easily judge the character of a man by how he treats those who can do nothing for him." - James D. Miles
|
|
|
|
|
You need to make the query quite a bit more complex. Probably half a dozen based on each vehicle and service type and the date and the mileage
Select Top 1 fieldname from tablename order by (date or mileage)
filter by vehicle and service type.
You can then either pivot the results or accept multiple rows.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
