Hello,
I'm running a very complicated query against oracle to return some data.
I'm running it manually because django can't natively do what I am doing
from what I have read.
I run the query but django doesn't get any values in return. I recently
switched from my dev database to my production to run the query because it
has live data and the required
data doesn't exist in dev yet because its a new feature.
The query executes but doesn't return anything. However there is another
very similarly query that I am running that is returning data. Also if I
output the exact statement
django is using to make the query against oracle for the data that is not
returning and then if i goto terminal and execute the query against the sql
server manually it will return the data.
I also output the fetchall to the page as a debuging tool and the working
query has all the info but the none working one outputs blank list.
At first I thought it was the query but i have proven the query django is
using works but django gets no data. The only logical answer is django is
trying to use the dev database or the models table something is off.
However I ran syncdb and sql and no errors came up... If I run the query
against dev it doesnt return data, but does against production.
Here is the query I am running.
if 'customer_licenses_all' in request.POST:
return_query_type = 'customer_licenses_all'
cursor.execute("alter session set time_zone='UTC'")
selected_customer_id = int(selected_customer['selected_customer'])
rundate = selected_customer['compute_date']
date2 = datetime.strptime(rundate, "%d-%b-%Y")
date_list = ()
date_list = (date2.day, date2.month, date2.year)
last_date = get_last_day(date_list)
last_date = last_date.strftime("%d-%b-%Y")
sql = (('''select customer_name, count(*) license_count,
sum(cpu_ghz_hours) CPU_Core_Hours, sum(ram_gb_hours) RAM_GB_Hours,
licensing_authority, product
from customers a, vm_groups b, vms c,
vm_license_histories d, licenses e, vm_compute_usage_histories f,
license_authorities g
where a.customer_id = b.customer_id
and b.vm_group_id = c.vm_group_id
and c.vm_id = d.vm_id
and d.license_id = e.license_id
and f.vm_id = c.vm_id
and e.license_authority_id = g.license_authority_id
and trunc(f.datetime) = to_date(%s,'DD-MON-YYYY')
and inactive = 'N'
and (deassignment_date is null or
trunc(deassignment_date) between to_date(%s,'DD-MON-YYYY') and
last_day(to_date(%s,'DD-MON-YYYY')))
and cpu_ghz_hours > 0
and g.license_authority_id not in (28,27,31)
group by customer_name, licensing_authority, product
order by 1,5,6''')% ("'"+rundate+"'","'"+ rundate+"'" ,"'"+last_date+"'"))
cursor.execute(sql)
return_query = cursor.fetchall()
context = Context({'customers': customers,
'return_query': return_query,
'return_query_type':return_query_type,
'rundate':rundate,
'last_date':last_date,
'sql':sql,
})
the django output
select customer_name, count(*) license_count, sum(cpu_ghz_hours)
CPU_Core_Hours, sum(ram_gb_hours) RAM_GB_Hours, licensing_authority,
product from customers a, vm_groups b, vms c, vm_license_histories d,
licenses e, vm_compute_usage_histories f, license_authorities g where
a.customer_id = b.customer_id and b.vm_group_id = c.vm_group_id and c.vm_id
= d.vm_id and d.license_id = e.license_id and f.vm_id = c.vm_id and
e.license_authority_id = g.license_authority_id and trunc(f.datetime) =
to_date('01-Jun-2014','DD-MON-YYYY') and inactive = 'N' and
(deassignment_date is null or trunc(deassignment_date) between
to_date('01-Jun-2014','DD-MON-YYYY') and
last_day(to_date('30-Jun-2014','DD-MON-YYYY'))) and cpu_ghz_hours > 0 and
g.license_authority_id not in (28,27,31) group by customer_name,
licensing_authority, product order by 1,5,6
[]
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/c6fcf80a-7511-4e34-b31e-74f2524cd4be%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
