sql - Alias of window function field causing a "not found" error when used in HAVING and WHERE clauses -


consider following bigquery query:

select     tn.object object_alias,     tn.attribute1 attribute1_alias,     tn.attribute2 attribute2_alias,     tn.score score_alias,     row_number() on (partition attribute1_alias, attribute2_alias order score_alias desc) row_num_alias     [datasetname.tablename] tn having # causes error when using     row_num_alias <= 20

in query, reference row_num_alias field in having clause causing following error: field 'row_num_alias' not found. same error when replacing having clause where clause, , seems error thrown all window functions.

is bug in bigquery? or there error in query?

possibly related:

one workaround convert subquery , move where clause outside subquery (see below), seems cumbersome (and isn't necessary).

select     object_alias,     attribute1_alias,     attribute2_alias,     score_alias,     row_num_alias     (select         tn.object object_alias,         tn.attribute1 attribute1_alias,         tn.attribute2 attribute2_alias,         tn.score score_alias,         row_number() on (partition attribute1_alias, attribute2_alias order score_alias desc) row_num_alias             [datasetname.tablename] tn     )     row_num_alias <= 20

column aliases not work in where clause, in bigquery. there no guarantee work in having clause either, although databases support that. column aliases can used in order by; think support standard part of phasing out reference-by-number.

you know right solution, use subquery.

by way, having clause no group by looks awkward. such construct used in mysql, work-around -- subqueries in mysql incur more overhead in other databases because optimizer not sophisticated.


Comments

Post a Comment

Popular posts from this blog

php - Wordpress website dashboard page or post editor content is not showing but front end data is showing properly -

Image
recently have uploaded wordpress site onto server. have uploaded wordpress site in sub-folder under root folder ../root(domain root folder)/wordpress (where host wordpress folder). now problem content data in page or post in dashboard of wordpress admin panel not showing - see image. but data showing in front view - see image. if not browser cache issue , not issue happened tinymce editor during upgrade; try add config file: define('concatenate_scripts', false);
Read more

How to get the ip address of VM and use it to configure SSH connection dynamically in Ansible -

i trying ip address of vm , trying use establish ssh vm. following script: --- - hosts: localhost become: yes connection: local gather_facts: false serial: 1 vars_files: - createvmvars.yml tasks: - name: gathering vm info. vsphere_guest: vcenter_hostname: "{{vcenter_hostname}}" username: "{{vcenter_username}}" password: "{{vcenter_password}}" guest: "{{guest_name}}" vmware_guest_facts: yes register: var - debug: msg="{{var.ansible_facts.hw_eth0.ipaddresses[0]}}" - name: establishing ssh connection. script: /home/shasha/devops/scripts/ssh_configure.sh "{{var.ansible_facts.hw_eth0.ipaddresses[0]}}" following content of ssh_configure.sh : #!/bin/sh ssh-keygen -t rsa ssh-copy-id $1 but when running playbook, getting following error while executing third task: error! 'var' undefined but debug module(second task) printin
Read more

javascript - Get parameter of GET request -

i make request: createxhrrequest( "get", fileurl, function( err, response ) { if( err ) { alert( "error get!" ); } alert(response); }); and full response: { "status" : "ok", "message" : "jvberi0xljqkjdduxdgkmy how can jvberi0xlj ? i try response.message , response["message"]. nothing works. just simple access this: var json = json.parse(response); json.message
Read more