Scan and analytic queries

[AIIleG]

Hello,

not sure whether that's go-ora related or an underlying go sql issue (or even a bug). Either way, Scan fails at analytic queries. For example:

`SELECT * FROM (
    SELECT id, customer, ROW_NUMBER() OVER (ORDER BY id DESC) rnum
    FROM biz)
WHERE rnum < 51`

All I get from that is zeros or nils. If I loook at the raw returned rows however, they have all the data. Scan just doesn't catch it. Maybe I missed something and there is a simple solution? I checked the examples but there was nothing about ROW_NUMBER so I'm posting this here.

[sijms]

sorry for late replay this code is working with me

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/sijms/go-ora/v2"
    "os"
    "strconv"
    "time"
)

type TTB_DATA struct {
    Id   int64     `db:"VISIT_ID"`
    Name string    `db:"NAME"`
    Val  float64   `db:"VAL"`
    Date time.Time `db:"VISIT_DATE"`
}

func createTable(conn *sql.DB) error {
    t := time.Now()
    sqlText := `CREATE TABLE TTB_453(
    VISIT_ID    number(10)  NOT NULL,
    NAME        VARCHAR(200),
    VAL         number(10,2),
    VISIT_DATE  date,
    PRIMARY KEY(VISIT_ID)
    )`
    _, err := conn.Exec(sqlText)
    if err != nil {
        return err
    }
    fmt.Println("Finish create table :", time.Now().Sub(t))
    return nil
}

func dropTable(conn *sql.DB) error {
    t := time.Now()
    _, err := conn.Exec("drop table TTB_453 purge")
    if err != nil {
        return err
    }
    fmt.Println("Finish drop table: ", time.Now().Sub(t))
    return nil
}

func insertData(conn *sql.DB) error {
    t := time.Now()
    data := make([]TTB_DATA, 100)
    for x := 0; x < 100; x++ {
        data[x].Id = int64(1000000000 + x)
        data[x].Name = "test_ " + strconv.Itoa(x)
        data[x].Val = 100.23 + 1
        data[x].Date = time.Now()

    }
    _, err := conn.Exec("INSERT INTO TTB_453 (VISIT_ID, NAME, VAL, VISIT_DATE) VALUES(:VISIT_ID, :NAME, :VAL, :VISIT_DATE)", data)
    if err != nil {
        return err
    }
    fmt.Println("Finish insert data: ", time.Now().Sub(t))
    return nil
}

func query(conn *sql.DB) error {
    t := time.Now()
    sqlText := `SELECT visit_id, name, rnum FROM (
    SELECT visit_id, name, ROW_NUMBER() OVER (ORDER BY visit_id DESC) rnum
    FROM ttb_453)
WHERE rnum < 51`
    rows, err := conn.Query(sqlText)
    if err != nil {
        return err
    }
    var (
        id   int64
        name string
        rnum int64
    )
    for rows.Next() {
        err = rows.Scan(&id, &name, &rnum)
        if err != nil {
            return err
        }
        fmt.Println("id: ", id, "\tname: ", name, "\trnum: ", rnum)
    }
    fmt.Println("Finish query: ", time.Now().Sub(t))
    return nil
}
func main() {
    db, err := sql.Open("oracle", os.Getenv("DSN"))
    if err != nil {
        fmt.Println("can't connect: ", err)
        return
    }
    defer func() {
        err = db.Close()
        if err != nil {
            fmt.Println("can't close connection: ", err)
        }
    }()
    err = createTable(db)
    if err != nil {
        fmt.Println("Can't create table: ", err)
        return
    }
    defer func() {
        err = dropTable(db)
        if err != nil {
            fmt.Println("Can't drop table: ", err)
        }
    }()
    err = insertData(db)
    if err != nil {
        fmt.Println("Can't insert data: ", err)
        return
    }
    err = query(db)
    if err != nil {
        fmt.Println("Can't query: ", err)
    }
}