I have created many SQL VIEWs but the only way I have of maintaining a record of the SQL behind each logical view is to save the original create view SQL command in a txt document on my server. Can I query the AS400 somwehere to find the SQL behind a VIEW?
Thanks
http://search400.techtarget.com/tip/0,289483,sid3_gci1162160,00.html
I’m trying to write an SQL statement with the output as:
Day XXX of 2010
I can get the day number and the year with the following:
SELECT TO_CHAR(SYSDATE, ‘DDD YYYY’) as CurrDate
2 FROM DUAL;
But, how do I add the text, ‘Day’ and ‘of” to the output?
You should be able to splice the pieces together with string concatenation. Assuming this is Oracle, you use the really odd choice of || as the string concat operator.
Since you want text between the day and year part, you’ll need to break up the call to sysdate.
This should be close (don’t have Oracle handy to test it):
SELECT ‘DAY’ || TO_CHAR(SYSDATE, ‘DDD’) || ‘ of ‘ || TO_CHAR(SYSDATE, ‘YYYY’)
as CurrDate
FROM DUAL;
SELECT * FROM products WHERE quantity_on_hand > 0
0 is not in quotes because numeric values should not be. Textual values should be surrounded with quotes e.g.
SELECT * FROM users WHERE username = ‘Alice’
i need to find all those in the database who havn’t paid yet and they actual "paid" field is in the "itinerary" table. Would it be something along the lines of:
SELECT Paid
FROM Itinerary
WHERE people <> paid
Thanks
What is the data type of the ‘paid’ field?
If it is Boolean (true or false), the query will be:
SELECT people FROM itinerary WHERE paid=false;
If ‘paid’ field is a numeric field,
SELECT people FROM itinerary WHERE paid=0 or paid IS NULL;
i need to write a statement about all those who are over the age of 70 and under the age of 25 where the "age" is in the customers table. Would it be something like:
SELECT Age
FROM Customers
WHERE people = >70yrs old OR = <25 yrs old
Thanks
Yes..sorry i was meant to say "find those who are over the age of 70..and those who are below the age of 25"…thanks for correcting me
you shouldn’t use spaces between >= or <=. I think there may be english equivalents though (GE and LE?). these are greater than or equal, and less than or equal.
over means > 70
under means < 25
you are close.
SELECT people
FROM Customers
WHERE age > 70 AND age < 25
it is AND instead of OR. read your own description for hints. you used the word yourself. however, if you really think about it, it cannot be logically true, your description is logically faulty. re-read your problem. it probably is OR. think about it. a single person cannot be possibly be under 25 and over 70 at the same time, so it can’t be AND.
it wants me to sort by itinerary date and the actual "date" is from the itinerary table. Would it be something like
SELECT Date
FROM Itinerary
ORDER BY Itinerary date
Thanks
It would be like:
SELECT *
FROM Itinerary
ORDER BY Itinerary_date (Field/Column name containing date) asc/desc (optional)
Date cannot be a field name as it is a Keyword.
Basically, i am wanting to construct an SQL statement where i want to find those who have the surname "brown" and have booked cars where the surname is its own column in the "customers table"
Would it be something like:
SELECT Surname
FROM Customers
WHERE Surname = Brown AND have booked cars
Thanks
It entirely depends on your table structure.
All SQL select statements are made up of 3 basic parts.
The "select" is the part that you would declare what columns you wish to view (* Means all columns)
The "From" declares what table/s you want to get the data from
The Clauses "Where, And, Having" is where you define your criteria.
If you have all your data in one table you would use something like below. (Assuming booking is a Yes or No)
Select *
from customers
where upper(surname) = ‘BROWN’
and booking = ‘Yes’
I made the surname convert to upper as you may have BROWN, Brown, brown, bRoWn in your database so this matches the string not the casing
If you have a separate customers and bookings table it would look something like below. Assuming both tables have some commonality (I am assuming both have customerid)
Select *
from customers, bookings
where customers.customerid = bookings.customerid
and upper(customers.surname) = ‘BROWN’
and bookings.booking = ‘Yes’
Or
Select *
from customers
where upper(surname) = ‘BROWN’
and customerid in (select customerid from bookings)
I gave 2 ways to do the statement if there are multiple tables and both would return all the customer data if there is a booking. HOWEVER if you want to see some data from the bookings table you would use the first as you can define columns e.g. Customers.*, Bookings.Booking date
If you want more information in the form of a tutorial please see. http://www.mikespraggett.co.uk/Pages/HowTo/HowToViewer.aspx?Article=7&Title=Selecting%20Data
Please let me know if its possible to get the variables from one SQL, in a completely different SQL statement. Integrated Review is the tool I am working on.
Several ways you could do this (in most any platform):
1) Create a temporary table and insert values into it based on a query, then use it like any other table in subsequent queries, dropping it when done
2) Use a scripting language to create a cursor and process rows sequentially (if more than one row in result possible) or retrieve the columns into a set of local variables (if only one row would be retrieved)
3) If only a single value would be retrieved from the first query, make it a stored procedure you can invoke from subsequent queries
SELECT [Anabolic Steroids List].[BNF Name]
FROM [Anabolic Steroids List].[Max Dosage]
WHERE Max Dosage = 2;
Anabolic Steroids List = Table Name
BNF Name = Column Heading
Max Dosage = Column Heading
Max Dosage is set as a ‘number’ field and BNF Name is set as a ‘text’ field.
SELECT [Anabolic Steroids List].[BNF Name]
FROM [Anabolic Steroids List]
WHERE [Anabolic Steroids List].[Max Dosage] = 2
Where you went wrong -
You can use spaces in your Table /Column names, but if you do you -have- to surround the name with the square brackets.
The FROM clause references the Table not the Column.
In access, you can’t (shouldn’t) reference Column names without prefixing the Table name.
If you use the Wizard, you won’t get the syntax wrong!
i have a number field that needs cleaning up, for example
1) need to remove dash "-"
2) remove excess spaces in between numbers
3) prefix with some numbers if the length does not meet minimum length
4) remove text if found in the number field.
any help would be appreciated as i am nuts with sql..
1) use replace function
Update home Set address =replace(address,’-',”) From home where address like ‘%-%’
2) replace can work as well
select address , replace(address,’ ‘,”) From home where address like ‘% %’
3) sample:
declare