sql select statement troubles 1

[emilioantonio]

Hi guys

i have to retrieve data from tables of an existing Pervasive database using, for example, the following statement

..."SELECT * FROM LITHOLOGY", CON

the problem arises when the table has a composite name (i.e. DEVIATION SURVEYS"). In such a case a syntax error occurs when i run the query

..."SELECT * FROM DEVIATION SURVEYS", CON

I already enclosed table name between square brakets [DEVIATION SURVEYS] and put table name into a variable, but in both cases didn't work

thanks for your help

emilioantonio

 

[cmahon]

Hi,

I would have thought that having a space in a table name like that would be invalid - I would generally have called the table deviation_surveys instead.

Can you query the table from any interface? Does it work in SQL Plus or Oracle? Would it be possible to create a view of the table and query that instead?

 

[Golom]

What version of Access are you running?

This

"SELECT * FROM [b][red][[/red][/b]DEVIATION SURVEYS[b][red]][/red][/b]"

is the standard way to reference table and/or field names that contain spaces or reserved words.

 

[SkipVought]

Hi,

and to that end your TABLE ought to be a variable...

"SELECT * FROM [" & sMyTable & "]", CON

Skip,
[sub]
[red][/red]
[/sub]

 

[JoeAtWork]

I found this in the Pervasive SQL help in regards to column names, so it might apply to table names as well

You can have spaces in the name, but if you have spaces, you must always enclose the name in quotes whenever you are referring to it in SQL statements. Avoiding the use of spaces is recommended.

Googling on the error message (which you neglected to give us) may give you a more authoritative answer.

 

[zemp]

Becareful when building your SQL to contain quotes. To will need to build similar to this.

"SELECT * FROM " & chr(32) & "DEVIATION SURVEYS" & chr(32)

If you have the PCC (Pervasive control center) you can build the statement and then grab it with a debug.print and check the syntax there.

zemp

 

[emilioantonio]

hi zemp

your code was exactly what i nedeed.

I've got the PCC and i built the statement there.... but what do you mean "..grab it with a debug.print..."?

Ciao

Emilioantonio

 

[cmahon]

Grab it with a debug.print means to put a line of code in straight after building the statemenet and then do a debug.print. So you would have:

SQLString = "SELECT * FROM " & chr(32) & "DEVIATION SURVEYS" & chr(32)
debug.print SQLString

 

[emilioantonio]

Hi cmahon

What i do not understand is how to grab with debug.print a statement built with PCC (Pervasive Control Center).

Thanks

emilioantonio

 

[zemp]

What I meant that you can use the Debug.print in VB to place the actual SQL into the Immediate window. From there you can copy it to the windows clipboard and then paste it into the PCC execute Query window. This allows you to basically run the SQL statement within Pervasive and check the syntax. I do this to avoid retyping the SQL statement.

Sorry for the confusion.

zemp

 

[emilioantonio]

OK Zemp

i got it

 

[sudhakarpothu]

Hello Everyone,

I have a problem with retreiving data from my oracle database using sql select statement.

here I am using a user form in vb to get the data from a begining date to ending date, my database has different coloumns for Year, month and Day

I am using six variables to get the valus for begining and end of year, month and day

Byear for beging year
Eyear for end year....................etc

so when i use the sql select statement

select Year, Monat, Day from Emp where Year>='" & Byear & "' and Year <='" & Eyear & "' and Month>='" & Bmonth & "' and Month<='" & Emonth & "' and Day>='" & Bday & "' and Day<='" & Eday & "'"

it is giving me only part of data

for example if i take begining year as 2005 month 11 and day 1 to end year 2006 month 12 and day 30

it giving me data for only 2005-11-1 to 30 and 2006-12-1 to 30 but not for all months and dates between 2005 and 2006

please write me if anyone know the solution for this problem may be a nested select statemnet or something.

Thank you.

 

[SkipVought]

Use Real Dates...

...

where DateSerial([Year],[Month],[Day]) Between Dateserial([Byear],[Bmonth],[Bday]) and Dateserial([Eyear],[Emonth],[Eday])

Skip,
[sub]
[red][/red]
[/sub]

 

[HarleyQuinn]

I'm probably going to be proven horribly wrong here but I'm pretty sure that Oracle doesn't support DateSerial, so while formatting the variables in that manner would be fine you'd have to convert your three columns values into a date using other methods (e.g. CAST or TO_DATE).

Cheers

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[SkipVought]

Yes, To_Date instead of DateSerial as ...

To_Date(Substr(ISSUDATE_275,1,4)||'/'||Substr(ISSUDATE_275,5,2)||'/'||Substr(ISSUDATE_275,7,2),'yyyy/mm/dd')

Skip,
[sub]
[red][/red]
[/sub]

 

[JRHEIN]

HEY EVERYONE,
I'M HAVING A PROBLEM WITH A PROGRAM I WROTE TO CONVERT AN ACCESS DB INTO EXCEL SHEET.
BASICALLY, WHAT I'M DOING IS EXTRACTING SOME INFO FROM PERVASIVE AND CONVERTING INTO ACCESS. THEN WROTE A FAIRLY SIMPLE EXE. IN VB FOR USERS TO VIEW IN EXCEL.THE PROGRAM WORKS WELL AND THE USER (MONKEYS) I DEVELOPED IT FOR LIKE IT! HOWEVER, I'M HAVING TROUBLE EXTRACTRING SPECIFIC INFO. THE QUERY IN VB LOOKS LIKE THIS:

Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & txtAccessFile.Text & ";" & _
"Persist Security Info=False"
conn.Open

Set rs = conn.Execute( _
"SELECT * FROM query1 ORDER BY loc_no", , _
adCmdText)

IS THERE A WAY TO SELECT * FROM [....PARAMETER] WHERE [...PARAMETER]
WHAT IS THE SYNTAX?

PLEASE HELP!!!!!

IS ADMIN
JRHEIN