A problem with SQL select statement

[sudhakarpothu]

Hello Friends,

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 values 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, Month, Day from table 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 statement or something.

sorry for taking your valuable time

Thank you.

Sudhakar Pothu.

 

[HarleyQuinn]

Hi,

How about something along the lines of:

Dim strStartConcat as String
Dim strEndConcat as String
Dim strSQL as String

strStartConcat = Byear & Bmonth & Bday
strEndConcat = Eyear & Emonth & Eday

strSQL = "SELECT Year, Month, Day FROM table WHERE to_char(year ||''|| month ||''|| day) between '" & strStartConcat & "' and '" & strEndConcat"'"
Debug.Print strSQL

That should display all of the dates in your table between the two given dates. This is the simplest way I can think of to achieve your desired outcome. This of course deals with the data and variables as char and thus may not be the absolute best way to achieve this. Another way (not using date/char conversion/comparison) to do it would be to build dates out of your variables and data and compare them as dates.

Hope this helps


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

 

[sudhakarpothu]

Hi HarleyQuinn

Here the problem is, the earlier programmer created the tables such that they take date in year, month and day as separate columns.

when i try to retreive data its checking these columns as separate fields but not as date.

my sql select staterment is

select wölbung_vorn, wölbung_hinten, Year, Month, Day from rsv.twm60 where Year>='" & Byear & "' and Year <='" & Eyear & "' and Month>='" & Bmonth & "' and Month<='" & Emonth & "' and Day>='" & Bday & "' and Day<='" & Eday & "'"

what i am trying is to take the input as is

Byear=2005 and Eyear=2006 ie is year between 2005 and 2006
Bmonth=1 and Emonth= 12 ie is month between 1 and 12
Bday=1 and Eday=8 ie is day between 1 and 8

so i am expecting it to return me all values from 1-1-2005 to 8-12-2006 but thats not happening it is just giving values of 2005 january 1 to 8 and december 1 to 8
and values from 2006 january 1 to 8 and december 1 to 8.

I will appreciate any further help

thank you.

 

[HarleyQuinn]

Hi,

The code I posted compares the data as char (which you are able to do as far as I can tell) and should return the data you require. It allows you to keep the format of the data as you get from the user and uses that to create a string that you can compare to an Oracle value. It Did you try the code I posted?

Cheers


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

 

[sudhakarpothu]

Hi HarleyQuinn,

I tried your code, but when i try to retreive data of 1 and 2 months it is also giving me data of 11 and 12 months, i dont know the reason behind it, i will appreciate if you say something about this.

thankyou very much for your cooperation.

Sudhakar.

 

[HarleyQuinn]

Would it be possible for you post the value of a debug.print of the SQL statement that is generated when you fit my code in your app please? Just so I can have a look and try and solve your problem.

Thanks

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

 

[sudhakarpothu]

Hi HarleyQuinn,

this is the Debud.print of the sql statement, and i guess the 11 in '200511' taking it as 11th month but actually it is 2005-1-1.

select wölbung_vorn, wölbung_hinten, year, month, day FROM rsvadm.twm_bmw60 WHERE to_char(year ||''|| month ||''|| day) between '200511' and '2005230'

Sudhakar.

 

[gmmastros]

Dim strStartConcat as String
Dim strEndConcat as String
Dim strSQL as String

strStartConcat = Byear & [!]Right("00" + [/!]Bmonth[!], 2)[/!] & [!]Right("00" + [/!]Bday[!], 2)[/!]
strEndConcat = Eyear & [!]right("00" +[/!] Emonth[!], 2)[/!]  & [!]Right("00" + [/!]Eday[!], 2)[/!]

strSQL = "SELECT Year, Month, Day FROM table WHERE to_char(year ||''|| month ||''|| day) between '" & strStartConcat & "' and '" & strEndConcat"'"
Debug.Print strSQL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[HarleyQuinn]

Hi Sudhakar,

Yes, that is what is happening, you would have to pad the one digit numbers with a leading 0 for the code I posted to work.

For example (providing Bmonth and Bday aren't Integers):

strStartConcat = Byear & Right("0" & Bmonth,2) & Right("0" & Bday,2)
strEndConcat = Eyear & Right("0" & Emonth,2) & Right("0" & Eday,2)

Hope this helps

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

 

[HarleyQuinn]

Please ignore the line "(providing Bmonth and Bday aren't Integers)", I'm not sure what I was thinking of when I wrote that!

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

 

[sudhakarpothu]

Hi,

using these it is not retreiving any values

strStartConcat = Byear & Right("0" & Bmonth,2) & Right("0" & Bday,2)
strEndConcat = Eyear & Right("0" & Emonth,2) & Right("0" & Eday,2)

select wölbung_vorn, wölbung_hinten, jahr, monat, tag FROM rsvadm.twm_bmw60 WHERE to_char(jahr ||''|| monat ||''|| tag) between '20050101' and '20050225'

actually the values in database has no leading zeros so
should i use to_char r no.

Sudhakar.

 

[HarleyQuinn]

You can pad the month and day values:

to_char(jahr ||''|| LPAD(monat,2,'0') ||''|| LPAD(tag,2,'0'))

Hope this helps

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

 

[gmmastros]

I assume that the Year, Month and Day are stored in the database as integers. If so, you could try...

select wölbung_vorn, wölbung_hinten, jahr, monat, tag FROM rsvadm.twm_bmw60 
WHERE  [!]jahr * 10000 + monat * 100 + tag[/!] between 20050101 and 20050225

Basically, you can construct an integer value and then compare the 'dates' as though they are integers. Notice that I removed the apostrophes from the start date and end date.

Dim strStartConcat as String
Dim strEndConcat as String
Dim strSQL as String

strStartConcat = Byear & Right("00" + Bmonth, 2) & Right("00" + Bday, 2)
strEndConcat = Eyear & right("00" + Emonth, 2)  & Right("00" + Eday, 2)

strSQL = "SELECT Year, Month, Day FROM table 
WHERE  jahr * 10000 + monat * 100 + tag between " & strStartConcat & " and " & strEndConcat
Debug.Print strSQL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[HarleyQuinn]

I must admit, comparing them as numerical values makes more sense

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

 

[sudhakarpothu]

Thank you very much HarleyQuinn,

I guess i got it completely. once again thanks for your help and all of you.

Sudhakar.

 

[sudhakarpothu]

Thanks George,

you have a point about getting them as integers, i will try and reply as soon as possible.

Sudhakar.

 

[gmmastros]

I suspect the integer approach will work out well for you. There is, however, a potential problem that should be noted here.

Essentially, your where clause involves math to determine the date that is stored in the database. This will work and give you accurate results. The problem is that your performance may suffer because you won't be able to use any available indexes.

Ultimately, my recommendation would be to create a date column in the database. Properly indexed, you could re-write the query to use the index and your performance would improve.

I understand that sometimes there are reasons why you can't change the structure of the database. Just be aware that performance issues may result from the query, and that there are ways to improve it with simple database structure changes.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[HarleyQuinn]

Going with the indexed column idea would also allow you to accept a date value (albeit you may have to format it into a format that Oracle can use in a query depending on the Oracle settings you use) from your users rather than having to play around with Years, Months and Days in the VB code prior to your SQL statement generation.

Cheers

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

 

[sudhakarpothu]

Thank you Gmmastros and HarleyQuinn

I implimented the code with integer coversion and its working properly, thank you once again for all your advice.

Sudhakar Pothu.